Working on some new additions to MKC-shift planner I decided I would write an Excel Parser in F# to work with the main C# project. Most methods of the parser are in "Parse.fs"
"COM object that has been separated from its underlying RCW cannot be used."
Omitting the line makes my program run, but it leaves those Excel processes running somewhere in the background. Any suggestions on avoiding this ?
SOLVED:
Mathias Brandewinder(@brandewinder) was kind enough to point me towards this SO post that includes lots of useful information. By following the "never use 2 dots on COM objects" rule the snippet below shows the changes made to ExcelParser, enough to kill of the Excel squatters.
module FSpart.Parse
open FSpart.Enums
open FSpart.Types
open Converters
open Microsoft.Office.Interop.Excel
let cellToString (range :Microsoft.Office.Interop.Excel.Range) =
range.Value2.ToString()
let xlApp =
new ApplicationClass(Visible = true)
let workbook path (xlApp:Microsoft.Office.Interop.Excel.ApplicationClass) =
xlApp.Workbooks.Open(path)
let sheetFromPath path sheetName (xlApp:Microsoft.Office.Interop.Excel.ApplicationClass) =
let xlWorkbook = xlApp.Workbooks.Open(path)
let xlWorksheet = xlWorkbook.Worksheets.[sheetName] :?> Microsoft.Office.Interop.Excel.Worksheet
xlWorksheet
let sheetFromWorkbook (workbook:Microsoft.Office.Interop.Excel.Workbook) sheetname =
workbook.Worksheets.[sheetname] :?> Microsoft.Office.Interop.Excel.Worksheet
... More methods are here
let ExcelExit =
xlApp.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject xlApp
To make this accesible to the main c# project I added "ExcelParser.fs"
module FSpart.Excelparser
type ExcelParser =
static member Parse(path)=
let Excel = Parse.xlApp
let workbook = Parse.workbook path Excel
let sheet = Parse.sheetFromWorkbook workbook 1
let LongShiftPlan = Parse.longShiftAssignments sheet
Parse.ExcelExit |> ignore
LongShiftPlan
A reference to the F# project was added to the c# project and the parser can be called via.
FSpart.Excelparser.ExcelParser.Parse(path);This Works, but there is a caveat. The last line in Parse.fs:
System.Runtime.InteropServices.Marshal.FinalReleaseComObject xlAppcauses an error:
"COM object that has been separated from its underlying RCW cannot be used."
Omitting the line makes my program run, but it leaves those Excel processes running somewhere in the background. Any suggestions on avoiding this ?
SOLVED:
Mathias Brandewinder(@brandewinder) was kind enough to point me towards this SO post that includes lots of useful information. By following the "never use 2 dots on COM objects" rule the snippet below shows the changes made to ExcelParser, enough to kill of the Excel squatters.
module FSpart.Excelparser
type ExcelParser =
static member Parse(path)=
let Excel = Parse.xlApp
let workbook = Parse.workbook path Excel
let sheet = Parse.sheetFromWorkbook workbook 1
let LongShiftPlan = Parse.longShiftAssignments sheet
//Cleanup
Parse.ExcelExit |> ignore
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject sheet |>ignore
System.Runtime.InteropServices.Marshal.FinalReleaseComObject workbook |> ignore
System.Runtime.InteropServices.Marshal.FinalReleaseComObject Excel |> ignore
LongShiftPla
Thanks Mathias.
I wrote a little computation expression that helps deal with the squatters nicely that you might be interested in : http://pinksquirrellabs.com/post/2012/12/01/Monads-to-help-with-COM-cleanup.aspx
ReplyDeleteRoss