Monday, August 4, 2014

C# F# Excel parsing

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"
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 xlApp 
causes 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.