4 people like it.

# High-Order Functions for Excel using NetOffice

Similar to the snippet by Kit Eason, but using Net Office. Also handles large spreadsheets, but with some compromises, e.g. using Array2D rather than Seq and not supporting filter. To use, paste code into VS, open Excel (as the code works on the default workbook loaded) and then use FSI.

 ``` 1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: ``` ``````// Add references to NetOffice: #if INTERACTIVE #r "NetOffice.dll" #r "OfficeApi.dll" #r "VBIDEApi.dll" #r "ExcelApi.dll" #endif open NetOffice open NetOffice.ExcelApi open NetOffice.ExcelApi.Enums /// Helper function to return cell content as float if possible, if not as 0.0. let cellDouble (cell : obj) = match cell with | :? double as _double -> _double | _ -> 0.0 /// Returns the specified worksheet range as a 2D array of objects. let toArray2D (range : Range) = range.Value2 :?> obj [,] /// Returns the specified worksheet range as a 2D array of objects, together with a 1-based /// row-index and column-index for each cell. let toArray2Drc (range : Range) = range |> toArray2D |> Array2D.mapi (fun i j o -> (i, j, o)) /// Takes a function and an Excel range, and returns the results of applying the function to each individual cell. let map (f : obj -> 'T) (range : Range) = range |> toArray2D |> Array2D.map f /// Takes a function and an Excel range, and returns the results of applying the function to each individual cell. let maprc (f : int -> int -> obj -> 'T) (range : Range) = range |> toArray2Drc |> Array2D.map (fun (r, c, o) -> f r c o) /// Takes a function and an Excel range, and applies the function to each individual cell. let iter (f : obj -> (Range -> unit) option) (range : Range) = let fc = range |> map f for r = 1 to range.Rows.Count do for c = 1 to range.Columns.Count do let fcrw = fc.[r, c] if fcrw.IsSome then let cell = range.[r, c] fcrw.Value cell cell.Dispose() //very important! /// Takes a function and an Excel range, and applies the function to each individual cell, /// providing 1-based row-index and column-index for each cell as arguments to the function. let iterrc (f : int -> int -> obj -> (Range -> unit) option) (range : Range) = let fc = range |> maprc f for r = 1 to range.Rows.Count do for c = 1 to range.Columns.Count do let fcrw = fc.[r, c] if fcrw.IsSome then let cell = range.[r, c] fcrw.Value cell cell.Dispose() //very important! ///// Examples ///// //open Excel first before running! // use active workbook: let xlapp = Application.GetActiveInstance(true) let wb = xlapp.ActiveWorkbook // Get a reference to the workbook: let sh = wb.Sheets.["Sheet1"] :?> Worksheet // Get a reference to a named range: let exampleRange = sh.Range(sh.Cells.[1, 1], sh.Cells.[300, 50]) // populate let vals = Array2D.init 300 50 (fun i j -> i * j) exampleRange.Value2 <- vals // toArray2D example: let cellCount = let arr = exampleRange |> toArray2D Array2D.length1 arr * Array2D.length2 arr // toArray2Drc example: let listCellRC = exampleRange |> toArray2Drc |> Array2D.iter (fun (r, c, o) -> printfn "row:%i col:%i cell:%s" r c (o.ToString())) // map example: let floatTotal = exampleRange |> map (fun cell -> cellDouble cell) |> Seq.cast |> Seq.sum // maprc example: let evenTotal = exampleRange |> maprc (fun r _ cell -> if r % 2 = 0 then cellDouble cell else 0.0) |> Seq.cast |> Seq.sum // iter example let highlightRange = exampleRange |> iter (fun o -> Some(fun cell -> cell.Interior.Color <- 65535)) // Yellow // Entire range is yellow // iterrc example let chequerRange = exampleRange |> iterrc (fun r c cell -> if (r % 2 = 0) && (c % 2 <> 0) || (r % 2 <> 0) && (c % 2 = 0) then Some(fun cell -> cell.Interior.Color <- 65535) // Yellow else Some(fun cell -> cell.Interior.Color <- 255)) // Red // Range is fetchingly chequered in red and yellow // filtered example: let colourOddInts = let oddIntRange o = let cellVal = cellDouble o ((int cellVal) % 2) <> 0 exampleRange |> iter (fun o -> if (oddIntRange o) then Some(fun cell -> cell.Interior.Color <- 65535) else None) ``````
namespace NetOffice
namespace NetOffice.ExcelApi
namespace NetOffice.ExcelApi.Enums
val cellDouble : cell:obj -> double

Full name: Script.cellDouble

Helper function to return cell content as float if possible, if not as 0.0.
val cell : obj
type obj = System.Object

Full name: Microsoft.FSharp.Core.obj
Multiple items
val double : value:'T -> float (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.double

--------------------
type double = System.Double

Full name: Microsoft.FSharp.Core.double
val _double : double
val toArray2D : range:Range -> obj [,]

Full name: Script.toArray2D

Returns the specified worksheet range as a 2D array of objects.
val range : Range
Multiple items
type Range =
inherit Range_
new : unit -> Range + 6 overloads
member Activate : unit -> obj
member AddIndent : obj with get, set
member AllocateChanges : unit -> unit
member AllowEdit : bool
member Application : Application
...

Full name: NetOffice.ExcelApi.Range

--------------------
val toArray2Drc : range:Range -> (int * int * obj) [,]

Full name: Script.toArray2Drc

Returns the specified worksheet range as a 2D array of objects, together with a 1-based
row-index and column-index for each cell.
module Array2D

from Microsoft.FSharp.Collections
val mapi : mapping:(int -> int -> 'T -> 'U) -> array:'T [,] -> 'U [,]

Full name: Microsoft.FSharp.Collections.Array2D.mapi
val i : int
val j : int
val o : obj
val map : f:(obj -> 'T) -> range:Range -> 'T [,]

Full name: Script.map

Takes a function and an Excel range, and returns the results of applying the function to each individual cell.
val f : (obj -> 'T)
val map : mapping:('T -> 'U) -> array:'T [,] -> 'U [,]

Full name: Microsoft.FSharp.Collections.Array2D.map
val maprc : f:(int -> int -> obj -> 'T) -> range:Range -> 'T [,]

Full name: Script.maprc

Takes a function and an Excel range, and returns the results of applying the function to each individual cell.
val f : (int -> int -> obj -> 'T)
Multiple items
val int : value:'T -> int (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.int

--------------------
type int = int32

Full name: Microsoft.FSharp.Core.int

--------------------
type int<'Measure> = int

Full name: Microsoft.FSharp.Core.int<_>
val r : int
val c : int
val iter : f:(obj -> (Range -> unit) option) -> range:Range -> unit

Full name: Script.iter

Takes a function and an Excel range, and applies the function to each individual cell.
val f : (obj -> (Range -> unit) option)
type unit = Unit

Full name: Microsoft.FSharp.Core.unit
type 'T option = Option<'T>

Full name: Microsoft.FSharp.Core.option<_>
val fc : (Range -> unit) option [,]
val fcrw : (Range -> unit) option
property Option.IsSome: bool
val cell : Range
property Option.Value: Range -> unit
val iterrc : f:(int -> int -> obj -> (Range -> unit) option) -> range:Range -> unit

Full name: Script.iterrc

Takes a function and an Excel range, and applies the function to each individual cell,
providing 1-based row-index and column-index for each cell as arguments to the function.
val f : (int -> int -> obj -> (Range -> unit) option)
val xlapp : obj

Full name: Script.xlapp
Multiple items
type Application =
inherit _Application
new : unit -> Application + 6 overloads
member CreateEventBridge : unit -> unit
member Dispose : unit -> unit + 1 overload
member DisposeEventBridge : unit -> unit
member EventBridgeInitialized : bool
member GetCountOfEventRecipients : eventName:string -> int
member GetEventRecipients : eventName:string -> Delegate[]
member HasEventRecipients : unit -> bool
member RaiseCustomEvent : eventName:string * paramsArray:obj[] -> int
event NewWorkbookEvent : Application_NewWorkbookEventHandler
...

Full name: NetOffice.ExcelApi.Application

--------------------
val wb : obj

Full name: Script.wb
val sh : Worksheet

Full name: Script.sh
Multiple items
type Sheets =
inherit COMObject
new : unit -> Sheets + 6 overloads
member Application : Application
member Copy : unit -> unit + 2 overloads
member Count : int
member Creator : XlCreator
member Delete : unit -> unit
member FillAcrossSheets : range:Range -> unit + 1 overload
member GetEnumerator : unit -> IEnumerator<obj>
...

Full name: NetOffice.ExcelApi.Sheets

--------------------
Multiple items
type Worksheet =
inherit _Worksheet
new : unit -> Worksheet + 6 overloads
member CreateEventBridge : unit -> unit
member DisposeEventBridge : unit -> unit
member EventBridgeInitialized : bool
member GetCountOfEventRecipients : eventName:string -> int
member GetEventRecipients : eventName:string -> Delegate[]
member HasEventRecipients : unit -> bool
member RaiseCustomEvent : eventName:string * paramsArray:obj[] -> int
event SelectionChangeEvent : Worksheet_SelectionChangeEventHandler
event BeforeDoubleClickEvent : Worksheet_BeforeDoubleClickEventHandler
...

Full name: NetOffice.ExcelApi.Worksheet

--------------------
val exampleRange : Range

Full name: Script.exampleRange
val vals : int [,]

Full name: Script.vals
val init : length1:int -> length2:int -> initializer:(int -> int -> 'T) -> 'T [,]

Full name: Microsoft.FSharp.Collections.Array2D.init
val cellCount : int

Full name: Script.cellCount
val arr : obj [,]
val length1 : array:'T [,] -> int

Full name: Microsoft.FSharp.Collections.Array2D.length1
val length2 : array:'T [,] -> int

Full name: Microsoft.FSharp.Collections.Array2D.length2
val listCellRC : unit

Full name: Script.listCellRC
val iter : action:('T -> unit) -> array:'T [,] -> unit

Full name: Microsoft.FSharp.Collections.Array2D.iter
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
System.Object.ToString() : string
val floatTotal : float

Full name: Script.floatTotal
module Seq

from Microsoft.FSharp.Collections
val cast : source:System.Collections.IEnumerable -> seq<'T>

Full name: Microsoft.FSharp.Collections.Seq.cast
Multiple items
val float : value:'T -> float (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.float

--------------------
type float = System.Double

Full name: Microsoft.FSharp.Core.float

--------------------
type float<'Measure> = float

Full name: Microsoft.FSharp.Core.float<_>
val sum : source:seq<'T> -> 'T (requires member ( + ) and member get_Zero)

Full name: Microsoft.FSharp.Collections.Seq.sum
val evenTotal : float

Full name: Script.evenTotal
val highlightRange : obj

Full name: Script.highlightRange
union case Option.Some: Value: 'T -> Option<'T>
Multiple items
type Interior =
inherit COMObject
new : unit -> Interior + 6 overloads
member Application : Application
member Color : obj with get, set
member ColorIndex : obj with get, set
member Creator : XlCreator
member InvertIfNegative : obj with get, set
member Parent : obj
member Pattern : obj with get, set
member PatternColor : obj with get, set
...

Full name: NetOffice.ExcelApi.Interior

--------------------
val chequerRange : obj

Full name: Script.chequerRange
val colourOddInts : obj

Full name: Script.colourOddInts
val oddIntRange : ('a -> bool)
val o : 'a
val cellVal : double
union case Option.None: Option<'T>