Copy deedle frame to excel

Question

My goal is to efficiently copy a deedle frame to excel in F#. I have never worked with excel from F#. I have been inspired by this tutorial.

This is my code:

#r "Microsoft.Office.Interop.Excel"
open Microsoft.Office.Interop.Excel
/// Export data frame to excel
let exportFrameToExcel (frame: Frame<DateTime, string>) (x,y) = 
    // Run Excel as a visible application
    let app = new ApplicationClass(Visible = true) 
    // Create new file and get the first worksheet
    let workbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet) 
    // Note that worksheets are indexed from one instead of zero
    let worksheet = (workbook.Worksheets.[1] :?> Worksheet) 

    let mutable col = y+1
    for key in frame.ColumnKeys do
        worksheet.Cells.[x, col] <- key
        col <- col + 1
    for i = 0 to frame.RowCount-1 do
        let row = frame.GetRowAt(i)
        let rowKey = frame.GetRowKeyAt(i)
        worksheet.Cells.[i+x+1, y] <- rowKey
        for j = 0 to frame.ColumnCount-1 do
            let value = row.GetAtAs<float>(j)
            worksheet.Cells.[i+x+1, j+y+1] <- value

It is slow and may crash if I touch the excel window while data is being copied. I am sure there is plenty of room for improvement, for example I think ApplicationClass should be instead turned to visibleonly when the data has been copied.

I would appreciate some guidance / help in doing this task in an efficient and idiomatic way. Thanks.

score 5 · Accepted Answer · answered Jan 16 '14 at 16:42

At BlueMountain (where we did most of the work on Deedle), they actually had a utility for passing data to Excel. We wanted to include this in Deedle, but never managed to publish it, because it would take some time to polish & document things.

We actually put the source code on GitHub (as part of one of my demo talks), so you can get it here.
Using this, you should be able to just write:

xl?A1 <- deedleFrame

There is a lot more you can do - have a look at the members of the Xl type.

Also, we originally used NetOffice which is an easier to use wrapper for the Excel COM interop - the referenced sample is not using it, but it is probably a good idea.

Copy deedle frame to excel

1 Answers1