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<float>
    |> Seq.sum

// maprc example:
let evenTotal = 
    exampleRange
    |> maprc (fun r _ cell -> 
           if r % 2 = 0 then cellDouble cell
           else 0.0)
    |> Seq.cast<float>
    |> 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 AddComment : unit -> Comment + 1 overload
  member AddIndent : obj with get, set
  member Address : string
  member AddressLocal : string
  member AdvancedFilter : action:XlFilterAction -> obj + 3 overloads
  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 Add : unit -> obj + 4 overloads
  member Add2 : unit -> obj + 4 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 Gradient : obj
  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>
Raw view New version

More information

Link:http://fssnip.net/ly
Posted:6 years ago
Author:Phil Brooks
Tags: excel