ExcelRandomCursor.fsx

// This is am example of an immediate write / random access cursor for Excel with basic formatting options.
// Implementation is based on a concrete, non generic writer monad with no payload ("do!"" only) (only state).
// Instead of directl writing to excel, an alternatives would be a random acces to a 
// copy-on-write list (or even a mutable array) and then bulk-write the result to excel in one shot.
// When only forward access would have been required, a simple seq expression with yields would have been enough.
// Anyway, it is a demonstration on how to "hide" pseudo-mutable state that is passed through a computation.
// 
// I personally use it for generating reports based on various data sources.

// paket.dependencies:
// nuget Microsoft.Office.Interop.Excel

#r @".\packages\Microsoft.Office.Interop.Excel\lib\net20\Microsoft.Office.Interop.Excel.dll"

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

[<AutoOpen>]
module CursorApi =

    type WriterContext = { sheet: Worksheet; row: int; col: int }
    type Writer = Writer of (WriterContext -> WriterContext)
    let run (f:Writer) = match f with | Writer f -> f
    let execute seed (f:Writer) = (run f) seed

    type Cursor() =
        member this.Bind(m: Writer, f: unit -> Writer) =
            Writer(fun ctx ->
                let res = f() |> run
                let newCtx = ctx |> run m
                res newCtx)
        member this.Return (x:Unit) = Writer (fun a -> a)
    let cursor = Cursor()

[<AutoOpen>]
module ExcelApi =

    let openWb path =
        let app = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") :?> Application
        app.Visible <- true
        app.Workbooks.Open path

    let getSheet index (wb:Workbook) = wb.Worksheets.[index + 1] :?> Worksheet
    let sheetName name (sheet:Worksheet) =
        sheet.Name <- name
        sheet
    let addSheetFirst name (wb:Workbook) =
        wb.Worksheets.Add() :?> Worksheet |> sheetName name
    let addSheetLast name (wb:Workbook) =
        wb.Worksheets.Add(After = (wb.Worksheets.[wb.Worksheets.Count])) :?> Worksheet |> sheetName name

    let autoFitWidth (sheet:Worksheet) =
        sheet.get_Range( sheet.Cells.[1, 1], sheet.Cells.[1, 100]).EntireColumn.AutoFit()

    let readRow index (sheet:Worksheet) =
        let row = index + 1
        let range = sheet.get_Range(sheet.Cells.[row, 1], sheet.Cells.[row,50]).Value2 :?> obj[,]
        range.[1, *]

    let writeRows (formatter: Range -> unit) (items: obj list list) =
        let writer (cell:WriterContext) =
            let itemsWithIndex = items |> List.mapi (fun i x -> i,x)
            for (rowIndex,row) in itemsWithIndex do
                let absoluteRowIndex = cell.row + rowIndex
                let range = cell.sheet.get_Range(
                                cell.sheet.Cells.[absoluteRowIndex, cell.col],
                                cell.sheet.Cells.[absoluteRowIndex, cell.col + row.Length - 1])
                range.Value2 <- row |> List.toArray
                range.NumberFormat <-
                    row 
                    |> List.map (fun item ->
                        match item with
                        | :? float -> "0.00"
                        | :? DateTime -> "m/d/yyyy"
                        | _ -> "")
                    |> List.toArray
                formatter range
            { cell with row = cell.row + items.Length }
        Writer writer

    let writeRow (formatter: Range -> unit) (items: obj list) =
        writeRows formatter [items]

    let down () =
        let writer (ctx:WriterContext) = { ctx with row = ctx.row + 1 }
        Writer writer

    let defaultFormat (r:Range) = ()
    let sectionHeading (r:Range) = 
        r.Font.Bold <- true
        r.Font.Size <- 20.0
    let lightHeading (r:Range) = 
        r.Font.Bold <- true
        r.Font.Size <- 15.0
    let tableHeading (r:Range) =
        r.Font.Bold <- true

let xlsFile = (Path.Combine (__SOURCE_DIRECTORY__, @"MyExcelFile.xlsx"))
let wb = openWb xlsFile
let dataSheet = wb |> getSheet 0
let reportSheet = wb |> addSheetLast (sprintf "My Report")

let rows =
    Seq.init Int32.MaxValue (fun i -> dataSheet |> readRow (i+1))
    |> Seq.takeWhile (fun row -> row.[0] <> null)
    |> Seq.map (fun row -> (*TODO: whetaver you want here*) [ row.[0]; row.[3]; row.[5] ])
    |> Seq.toList

cursor {
    do! writeRow lightHeading [ "From"; DateTime.Now ]
    do! writeRow lightHeading [ "To"; DateTime.Now.AddDays 5.0 ]
    do! down()
    do! writeRow tableHeading [ "ColHeader 1"; "ColHeader 2" ]
    do! writeRows defaultFormat rows
    do! down()
}
|> execute { sheet = reportSheet; row = 1; col = 1 }

autoFitWidth reportSheet