15 people like it.

Higher-Order Functions for Excel

Some of the standard higher-order functions (like Seq.map, Seq.iter, Seq.filter) but implemented for Excel interop. Effortlessly iterate across ranges of cells, reading them, updating them or formatting them. NB. Type-information won't be displayed correctly on fssnip (Office not installed on server presumably), so to get this working paste the code into VS, make yourself a spreadsheet with a range called 'MyRange' and use FSI to explore.

  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: 
126: 
127: 
128: 
129: 
130: 
131: 
132: 
133: 
134: 
135: 
136: 
137: 
138: 
139: 
140: 
141: 
142: 
143: 
144: 
145: 
146: 
147: 
148: 
149: 
150: 
151: 
152: 
153: 
154: 
155: 
156: 
157: 
158: 
159: 
160: 
161: 
162: 
163: 
164: 
165: 
166: 
167: 
168: 
169: 
170: 
171: 
172: 
173: 
174: 
175: 
176: 
177: 
178: 
module Excel

// Add references to Excel interop:
#if INTERACTIVE
#r "Microsoft.Office.Interop.Excel"
#endif

open System
open Microsoft.Office.Interop.Excel

/// Helper function to represent string or floating point cell content as a string.
let cellContent (range : Range) = 
    match range.Value2 with
    | :? string as _string -> sprintf "string: %s" _string
    | :? double as _double -> sprintf "double: %f" _double
    | _ -> "(unknown type)"

/// Helper function to return cell content as float if possible, if not as 0.0.
let cellDouble (range : Range) = 
    match range.Value2 with
    | :? double as _double -> _double
    | _ -> 0.0

/// Returns the specified worksheet range as a sequence of indvidual cell ranges.
let toSeq (range : Range) =
    seq {
            for r in 1 .. range.Rows.Count do
                for c in 1 .. range.Columns.Count do
                    let cell = range.Item(r, c) :?> Range
                    yield cell
    }

/// Returns the specified worksheet range as a sequence of indvidual cell ranges, together with a 0-based
/// row-index and column-index for each cell.
let toSeqrc (range : Range) =
    seq {
            for r in 1 .. range.Rows.Count do
                for c in 1 .. range.Columns.Count do
                    let cell = range.Item(r, c) :?> Range
                    yield r, c, cell
    }

/// Takes a sequence of individual cell-ranges and returns an Excel range representation of the cells 
/// (using Excel 'union' representation - eg. "R1C1, R2C1, R5C4").
let toRange (workSheet : Worksheet) (rangeSeq : seq<Range>) =
    let csvSeq sequence =
        let result = 
            sequence
            |> Seq.fold (fun acc x -> acc + x + ",") ""
        result.Remove(result.Length-1)
    let rangeName = 
        rangeSeq
        |> Seq.map (fun cell -> cell.Address())
        |> csvSeq
    workSheet.Range(rangeName)

/// Takes a function and an Excel range, and returns the results of applying the function to each individual cell.
let map (f : Range -> 'T) (range : Range) =
    range
    |> toSeq
    |> Seq.map f

/// Takes a function and an Excel range, and returns the results of applying the function to each individual cell,
/// providing 0-based row-index and column-index for each cell as arguments to the function.
let maprc (f : int -> int -> Range -> 'T) (range : Range) =
    range
    |> toSeqrc
    |> Seq.map (fun item -> match item with
                            | (r, c, cell) -> f r c cell)

/// Takes a function and an Excel range, and applies the function to each individual cell.
let iter (f : Range -> unit) (range : Range) =
    range
    |> toSeq
    |> Seq.iter (fun cell -> f cell)

/// Takes a function and an Excel range, and applies the function to each individual cell,
/// providing 0-based row-index and column-index for each cell as arguments to the function.
let iterrc (f : int -> int -> Range -> unit) (range : Range) =
    range
    |> toSeqrc
    |> Seq.iter (fun item -> match item with
                                | (r, c, cell) -> f r c cell)

/// Takes a function and an Excel range, and returns a sequence of individual cell ranges where the result
/// of applying the function to the cell is true.
let filter (f : Range -> bool) (range : Range) =
    range
    |> toSeq
    |> Seq.filter (fun cell -> f cell)

///// Examples /////

// Start Excel.
let excel = ApplicationClass(Visible = true)

// Open a workbook:
let workbookDir = @"C:\Users\kit\Documents\Visual Studio 2010\Projects\ExcelTest\Spreadsheets"
let workbook = excel.Workbooks.Open(workbookDir + @"\Example1.xlsx")

// Get a reference to the workbook:
let exampleSheet = workbook.Sheets.["ExampleSheet"] :?> Worksheet

// Get a reference to a named range:
let exampleRange = exampleSheet.Range("MyRange")

// toSeq example:
let cellCount =
    exampleRange
    |> toSeq 
    |> Seq.length
// 4

// toSeqrc example:
let listCellRC =
    exampleRange
    |> toSeqrc 
    |> Seq.iter (fun item -> match item with
                             | (r, c, cell) -> printfn "row:%i col:%i cell:%s" r c (cellContent cell))
// row:1 col:1 cell:string: A
// row:2 col:1 cell:double: 1.000000
// row:3 col:1 cell:double: 2.000000
// row:4 col:1 cell:double: 3.000000
// ...
// row:4 col:3 cell:double: 9.000000

// toRange example:
let rangeAddress =
    let range = 
        exampleRange
        |> toSeq
        |> toRange exampleRange.Worksheet
    printfn "Range: %s" (range.Address())
// Range: $A$1,$B$1,$C$1,$A$2,$B$2,$C$2,$A$3,$B$3,$C$3,$A$4,$B$4,$C$4

// map example:
let floatTotal =
    exampleRange
    |> map (fun cell -> cellDouble cell)
    |> Seq.sum
// 42.0

// maprc example:
let evenTotal =
    exampleRange
    |> maprc (fun r _ cell -> if r % 2 = 0 then
                                  cellDouble cell
                              else 
                                  0.0)
    |> Seq.sum
// 28.0

// iter example
let highlightRange =
    exampleRange
    |> iter (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      
                                    cell.Interior.Color <- 65535 // Yellow
                                else
                                    cell.Interior.Color <- 255) // Red
// Range is fetchingly chequered in red and yellow

// filter and toRange example:
let colourOddInts =
    let oddIntRange = 
        exampleRange 
        |> filter (fun cell -> let cellVal = cellDouble cell
                               (cellVal = float(int(cellVal)))
                               && (int(cellVal)) % 2 <> 0)
        |> toRange exampleSheet
    oddIntRange.Interior.Color <- 255 // Red
// Cells containing odd integers are coloured red; other colours are unchanged
module Excel
namespace System
namespace Microsoft
namespace Microsoft.Office
namespace Microsoft.Office.Interop
namespace Microsoft.Office.Interop.Excel
val cellContent : range:Range -> string

Full name: Excel.cellContent


 Helper function to represent string or floating point cell content as a string.
val range : Range
type Range =
  member Activate : unit -> obj
  member AddComment : ?Text:obj -> Comment
  member AddIndent : obj with get, set
  member Address : obj * obj * XlReferenceStyle * obj * obj -> string
  member AddressLocal : obj * obj * XlReferenceStyle * obj * obj -> string
  member AdvancedFilter : Action:XlFilterAction * ?CriteriaRange:obj * ?CopyToRange:obj * ?Unique:obj -> obj
  member AllocateChanges : unit -> unit
  member AllowEdit : bool
  member Application : Application
  member ApplyNames : ?Names:obj * ?IgnoreRelativeAbsolute:obj * ?UseRowColumnNames:obj * ?OmitColumn:obj * ?OmitRow:obj * ?Order:XlApplyNamesOrder * ?AppendLast:obj -> obj
  ...

Full name: Microsoft.Office.Interop.Excel.Range
property Range.Value2: obj
Multiple items
val string : value:'T -> string

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

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string
val _string : string
val sprintf : format:Printf.StringFormat<'T> -> 'T

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

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

--------------------
type double = Double

Full name: Microsoft.FSharp.Core.double
val _double : double
val cellDouble : range:Range -> double

Full name: Excel.cellDouble


 Helper function to return cell content as float if possible, if not as 0.0.
val toSeq : range:Range -> seq<Range>

Full name: Excel.toSeq


 Returns the specified worksheet range as a sequence of indvidual cell ranges.
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

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

--------------------
type seq<'T> = Collections.Generic.IEnumerable<'T>

Full name: Microsoft.FSharp.Collections.seq<_>
val r : int
property Range.Rows: Range
property Range.Count: int
val c : int
property Range.Columns: Range
val cell : Range
property Range.Item: obj * obj -> obj
val toSeqrc : range:Range -> seq<int * int * Range>

Full name: Excel.toSeqrc


 Returns the specified worksheet range as a sequence of indvidual cell ranges, together with a 0-based
 row-index and column-index for each cell.
val toRange : workSheet:Worksheet -> rangeSeq:seq<Range> -> Range

Full name: Excel.toRange


 Takes a sequence of individual cell-ranges and returns an Excel range representation of the cells
 (using Excel 'union' representation - eg. "R1C1, R2C1, R5C4").
val workSheet : Worksheet
type Worksheet =

Full name: Microsoft.Office.Interop.Excel.Worksheet
val rangeSeq : seq<Range>
val csvSeq : (seq<string> -> string)
val sequence : seq<string>
val result : string
module Seq

from Microsoft.FSharp.Collections
val fold : folder:('State -> 'T -> 'State) -> state:'State -> source:seq<'T> -> 'State

Full name: Microsoft.FSharp.Collections.Seq.fold
val acc : string
val x : string
String.Remove(startIndex: int) : string
String.Remove(startIndex: int, count: int) : string
property String.Length: int
val rangeName : string
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
property Range.Address: obj * obj * XlReferenceStyle * obj * obj -> string
property _Worksheet.Range: obj * obj -> Range
val map : f:(Range -> 'T) -> range:Range -> seq<'T>

Full name: Excel.map


 Takes a function and an Excel range, and returns the results of applying the function to each individual cell.
val f : (Range -> 'T)
val maprc : f:(int -> int -> Range -> 'T) -> range:Range -> seq<'T>

Full name: Excel.maprc


 Takes a function and an Excel range, and returns the results of applying the function to each individual cell,
 providing 0-based row-index and column-index for each cell as arguments to the function.
val f : (int -> int -> Range -> '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 item : int * int * Range
val iter : f:(Range -> unit) -> range:Range -> unit

Full name: Excel.iter


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

Full name: Microsoft.FSharp.Core.unit
val iter : action:('T -> unit) -> source:seq<'T> -> unit

Full name: Microsoft.FSharp.Collections.Seq.iter
val iterrc : f:(int -> int -> Range -> unit) -> range:Range -> unit

Full name: Excel.iterrc


 Takes a function and an Excel range, and applies the function to each individual cell,
 providing 0-based row-index and column-index for each cell as arguments to the function.
val f : (int -> int -> Range -> unit)
val filter : f:(Range -> bool) -> range:Range -> seq<Range>

Full name: Excel.filter


 Takes a function and an Excel range, and returns a sequence of individual cell ranges where the result
 of applying the function to the cell is true.
val f : (Range -> bool)
type bool = Boolean

Full name: Microsoft.FSharp.Core.bool
val filter : predicate:('T -> bool) -> source:seq<'T> -> seq<'T>

Full name: Microsoft.FSharp.Collections.Seq.filter
val excel : ApplicationClass

Full name: Excel.excel
Multiple items
type ApplicationClass =
  new : unit -> ApplicationClass
  member ActivateMicrosoftApp : Index:XlMSApplication -> unit
  member ActiveCell : Range
  member ActiveChart : Chart
  member ActiveDialog : DialogSheet
  member ActiveEncryptionSession : int
  member ActiveMenuBar : MenuBar
  member ActivePrinter : string with get, set
  member ActiveProtectedViewWindow : ProtectedViewWindow
  member ActiveSheet : obj
  ...

Full name: Microsoft.Office.Interop.Excel.ApplicationClass

--------------------
ApplicationClass() : unit
val workbookDir : string

Full name: Excel.workbookDir
val workbook : Workbook

Full name: Excel.workbook
Workbooks.Open(Filename: string, ?UpdateLinks: obj, ?ReadOnly: obj, ?Format: obj, ?Password: obj, ?WriteResPassword: obj, ?IgnoreReadOnlyRecommended: obj, ?Origin: obj, ?Delimiter: obj, ?Editable: obj, ?Notify: obj, ?Converter: obj, ?AddToMru: obj, ?Local: obj, ?CorruptLoad: obj) : Workbook
val exampleSheet : Worksheet

Full name: Excel.exampleSheet
val exampleRange : Range

Full name: Excel.exampleRange
val cellCount : int

Full name: Excel.cellCount
val length : source:seq<'T> -> int

Full name: Microsoft.FSharp.Collections.Seq.length
val listCellRC : unit

Full name: Excel.listCellRC
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
val rangeAddress : unit

Full name: Excel.rangeAddress
property Range.Worksheet: Worksheet
val floatTotal : double

Full name: Excel.floatTotal
val sum : source:seq<'T> -> 'T (requires member ( + ) and member get_Zero)

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

Full name: Excel.evenTotal
val highlightRange : unit

Full name: Excel.highlightRange
property Range.Interior: Interior
property Interior.Color: obj
val chequerRange : unit

Full name: Excel.chequerRange
val colourOddInts : unit

Full name: Excel.colourOddInts
val oddIntRange : Range
val cellVal : double
Multiple items
val float : value:'T -> float (requires member op_Explicit)

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

--------------------
type float = Double

Full name: Microsoft.FSharp.Core.float

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

Full name: Microsoft.FSharp.Core.float<_>
Raw view New version

More information

Link:http://fssnip.net/aV
Posted:3 years ago
Author:Kit Eason
Tags: Excel , interop