F# and Excel integration for .NET 4.0 (Visual Studio 2010 Beta 1)

Accepted answer +12 upvotes

There is no ‘secret sauce’ added to the latest F# CTP release (Visual Studio 2010 Beta1) to improve Office interop. Perhaps you have F# confused with C#’s new support for Dynamic.

However, Office interop in F# is the same as C# – you can use the native COM APIs or the newer, managed Visual Studio Tools for Office (VSTO) libraries. Unfortunately F# doesn’t have the UI-designers for creating VSTO add-ins like C#, so the simplest way to do Office interop is to use the COM APIs.

The following snippet creates an Excel worksheet with information about the pictures in your My Pictures folder:

#r "Microsoft.Office.Interop.Excel"

open System
open System.IO
open System.Reflection
open Microsoft.Office.Interop.Excel


let app = ApplicationClass(Visible = true)


let sheet = app.Workbooks
               .Add()
               .Worksheets.[1] :?> _Worksheet


let setCellText (x : int) (y : int) (text : string) = 
    let range = sprintf "%c%d" (char (x + int 'A')) (y+1)
    sheet.Range(range).Value(Missing.Value) <- text


let printCsvToExcel rowIdx (csvText : string) =
    csvText.Split([| ',' |])
    |> Array.iteri (fun partIdx partText -> setCellText partIdx rowIdx partText)


let rec filesUnderFolder basePath = 
    seq {
        yield! Directory.GetFiles(basePath)
        for subFolder in Directory.GetDirectories(basePath) do
            yield! filesUnderFolder subFolder 
    }


// Print header
printCsvToExcel 0 "Directory, Filename, Size, Creation Time"


// Print rows
filesUnderFolder (Environment.GetFolderPath(Environment.SpecialFolder.MyPictures))
|> Seq.map (fun filename -> new FileInfo(filename))
|> Seq.map (fun fileInfo -> sprintf "%s, %s, %d, %s" 
                                fileInfo.DirectoryName 
                                fileInfo.Name 
                                fileInfo.Length 
                                (fileInfo.CreationTime.ToShortDateString()))
|> Seq.iteri (fun idx str -> printCsvToExcel (idx + 1) str)