Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
// 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.