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.

Copy Source
Copy Link
Tools:
  1: module Excel
  2: 
  3: // Add references to Excel interop:
  4: #if INTERACTIVE
  5: #r "Microsoft.Office.Interop.Excel"
  6: #endif
  7: 
  8: open System
  9: open Microsoft.Office.Interop.Excel
 10: 
 11: /// Helper function to represent string or floating point cell content as a string.
 12: let cellContent (range : Range) = 
 13:     match range.Value2 with
 14:     | :? string as _string -> sprintf "string: %s" _string
 15:     | :? double as _double -> sprintf "double: %f" _double
 16:     | _ -> "(unknown type)"
 17: 
 18: /// Helper function to return cell content as float if possible, if not as 0.0.
 19: let cellDouble (range : Range) = 
 20:     match range.Value2 with
 21:     | :? double as _double -> _double
 22:     | _ -> 0.0
 23: 
 24: /// Returns the specified worksheet range as a sequence of indvidual cell ranges.
 25: let toSeq (range : Range) =
 26:     seq {
 27:             for r in 1 .. range.Rows.Count do
 28:                 for c in 1 .. range.Columns.Count do
 29:                     let cell = range.Item(r, c) :?> Range
 30:                     yield cell
 31:     }
 32: 
 33: /// Returns the specified worksheet range as a sequence of indvidual cell ranges, together with a 0-based
 34: /// row-index and column-index for each cell.
 35: let toSeqrc (range : Range) =
 36:     seq {
 37:             for r in 1 .. range.Rows.Count do
 38:                 for c in 1 .. range.Columns.Count do
 39:                     let cell = range.Item(r, c) :?> Range
 40:                     yield r, c, cell
 41:     }
 42: 
 43: /// Takes a sequence of individual cell-ranges and returns an Excel range representation of the cells 
 44: /// (using Excel 'union' representation - eg. "R1C1, R2C1, R5C4").
 45: let toRange (workSheet : Worksheet) (rangeSeq : seq<Range>) =
 46:     let csvSeq sequence =
 47:         let result = 
 48:             sequence
 49:             |> Seq.fold (fun acc x -> acc + x + ",") ""
 50:         result.Remove(result.Length-1)
 51:     let rangeName = 
 52:         rangeSeq
 53:         |> Seq.map (fun cell -> cell.Address())
 54:         |> csvSeq
 55:     workSheet.Range(rangeName)
 56: 
 57: /// Takes a function and an Excel range, and returns the results of applying the function to each individual cell.
 58: let map (f : Range -> 'T) (range : Range) =
 59:     range
 60:     |> toSeq
 61:     |> Seq.map f
 62: 
 63: /// Takes a function and an Excel range, and returns the results of applying the function to each individual cell,
 64: /// providing 0-based row-index and column-index for each cell as arguments to the function.
 65: let maprc (f : int -> int -> Range -> 'T) (range : Range) =
 66:     range
 67:     |> toSeqrc
 68:     |> Seq.map (fun item -> match item with
 69:                             | (r, c, cell) -> f r c cell)
 70: 
 71: /// Takes a function and an Excel range, and applies the function to each individual cell.
 72: let iter (f : Range -> unit) (range : Range) =
 73:     range
 74:     |> toSeq
 75:     |> Seq.iter (fun cell -> f cell)
 76: 
 77: /// Takes a function and an Excel range, and applies the function to each individual cell,
 78: /// providing 0-based row-index and column-index for each cell as arguments to the function.
 79: let iterrc (f : int -> int -> Range -> unit) (range : Range) =
 80:     range
 81:     |> toSeqrc
 82:     |> Seq.iter (fun item -> match item with
 83:                                 | (r, c, cell) -> f r c cell)
 84: 
 85: /// Takes a function and an Excel range, and returns a sequence of individual cell ranges where the result
 86: /// of applying the function to the cell is true.
 87: let filter (f : Range -> bool) (range : Range) =
 88:     range
 89:     |> toSeq
 90:     |> Seq.filter (fun cell -> f cell)
 91: 
 92: ///// Examples /////
 93: 
 94: // Start Excel.
 95: let excel = ApplicationClass(Visible = true)
 96: 
 97: // Open a workbook:
 98: let workbookDir = @"C:\Users\kit\Documents\Visual Studio 2010\Projects\ExcelTest\Spreadsheets"
 99: let workbook = excel.Workbooks.Open(workbookDir + @"\Example1.xlsx")
100: 
101: // Get a reference to the workbook:
102: let exampleSheet = workbook.Sheets.["ExampleSheet"] :?> Worksheet
103: 
104: // Get a reference to a named range:
105: let exampleRange = exampleSheet.Range("MyRange")
106: 
107: // toSeq example:
108: let cellCount =
109:     exampleRange
110:     |> toSeq 
111:     |> Seq.length
112: // 4
113: 
114: // toSeqrc example:
115: let listCellRC =
116:     exampleRange
117:     |> toSeqrc 
118:     |> Seq.iter (fun item -> match item with
119:                              | (r, c, cell) -> printfn "row:%i col:%i cell:%s" r c (cellContent cell))
120: // row:1 col:1 cell:string: A
121: // row:2 col:1 cell:double: 1.000000
122: // row:3 col:1 cell:double: 2.000000
123: // row:4 col:1 cell:double: 3.000000
124: // ...
125: // row:4 col:3 cell:double: 9.000000
126: 
127: // toRange example:
128: let rangeAddress =
129:     let range = 
130:         exampleRange
131:         |> toSeq
132:         |> toRange exampleRange.Worksheet
133:     printfn "Range: %s" (range.Address())
134: // 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
135: 
136: // map example:
137: let floatTotal =
138:     exampleRange
139:     |> map (fun cell -> cellDouble cell)
140:     |> Seq.sum
141: // 42.0
142: 
143: // maprc example:
144: let evenTotal =
145:     exampleRange
146:     |> maprc (fun r _ cell -> if r % 2 = 0 then
147:                                   cellDouble cell
148:                               else 
149:                                   0.0)
150:     |> Seq.sum
151: // 28.0
152: 
153: // iter example
154: let highlightRange =
155:     exampleRange
156:     |> iter (fun cell -> cell.Interior.Color <- 65535) // Yellow
157: // Entire range is yellow
158: 
159: // iterrc example
160: let chequerRange =
161:     exampleRange
162:     |> iterrc (fun r c cell -> if    (r % 2 = 0) && (c % 2 <> 0) 
163:                                     || (r % 2 <> 0) && (c % 2 = 0) then      
164:                                     cell.Interior.Color <- 65535 // Yellow
165:                                 else
166:                                     cell.Interior.Color <- 255) // Red
167: // Range is fetchingly chequered in red and yellow
168: 
169: // filter and toRange example:
170: let colourOddInts =
171:     let oddIntRange = 
172:         exampleRange 
173:         |> filter (fun cell -> let cellVal = cellDouble cell
174:                                (cellVal = float(int(cellVal)))
175:                                && (int(cellVal)) % 2 <> 0)
176:         |> toRange exampleSheet
177:     oddIntRange.Interior.Color <- 255 // Red
178: // Cells containing odd integers are coloured red; other colours are unchanged
179: 
module Excel
namespace System
namespace Microsoft
val cellContent : 'a -> 'b

Full name: Excel.cellContent

Helper function to represent string or floating point cell content as a string.
val range : 'a
Multiple items
val string : 'T -> string

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

--------------------

type string = String

Full name: Microsoft.FSharp.Core.string

  type: string
  implements: IComparable
  implements: ICloneable
  implements: IConvertible
  implements: IComparable<string>
  implements: seq<char>
  implements: Collections.IEnumerable
  implements: IEquatable<string>
val sprintf : Printf.StringFormat<'T> -> 'T

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

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

--------------------

type double = Double

Full name: Microsoft.FSharp.Core.double

  type: double
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<float>
  implements: IEquatable<float>
  inherits: ValueType
val cellDouble : 'a -> 'b

Full name: Excel.cellDouble

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

Full name: Excel.toSeq

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

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

--------------------

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

Full name: Microsoft.FSharp.Collections.seq<_>

  type: seq<'T>
  inherits: Collections.IEnumerable
val r : int

  type: int
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<int>
  implements: IEquatable<int>
  inherits: ValueType
val c : int

  type: int
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<int>
  implements: IEquatable<int>
  inherits: ValueType
val cell : 'b
val toSeqrc : 'a -> seq<int * int * 'b>

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 : 'a -> seq<'b> -> 'c

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 : 'a
val rangeSeq : seq<'b>

  type: seq<'b>
  inherits: Collections.IEnumerable
val csvSeq : (seq<string> -> string)
val sequence : seq<string>

  type: seq<string>
  inherits: Collections.IEnumerable
val result : string

  type: string
  implements: IComparable
  implements: ICloneable
  implements: IConvertible
  implements: IComparable<string>
  implements: seq<char>
  implements: Collections.IEnumerable
  implements: IEquatable<string>
module Seq

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

Full name: Microsoft.FSharp.Collections.Seq.fold
val acc : string

  type: string
  implements: IComparable
  implements: ICloneable
  implements: IConvertible
  implements: IComparable<string>
  implements: seq<char>
  implements: Collections.IEnumerable
  implements: IEquatable<string>
val x : string

  type: string
  implements: IComparable
  implements: ICloneable
  implements: IConvertible
  implements: IComparable<string>
  implements: seq<char>
  implements: Collections.IEnumerable
  implements: IEquatable<string>
Multiple overloads
String.Remove(startIndex: int) : string
String.Remove(startIndex: int, count: int) : string
property String.Length: int
val rangeName : string

  type: string
  implements: IComparable
  implements: ICloneable
  implements: IConvertible
  implements: IComparable<string>
  implements: seq<char>
  implements: Collections.IEnumerable
  implements: IEquatable<string>
val map : ('T -> 'U) -> seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
val map : ('a -> 'T) -> 'b -> 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 : ('a -> 'T)
val range : 'b
val maprc : (int -> int -> 'a -> 'T) -> 'b -> 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 -> 'a -> 'T)
Multiple items
val int : 'T -> int (requires member op_Explicit)

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

--------------------

type int<'Measure> = int

Full name: Microsoft.FSharp.Core.int<_>

  type: int<'Measure>
  implements: IComparable
  implements: IConvertible
  implements: IFormattable
  implements: IComparable<int<'Measure>>
  implements: IEquatable<int<'Measure>>
  inherits: ValueType


--------------------

type int = int32

Full name: Microsoft.FSharp.Core.int

  type: int
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<int>
  implements: IEquatable<int>
  inherits: ValueType
val item : int * int * 'a
val cell : 'a
val iter : ('a -> unit) -> 'b -> unit

Full name: Excel.iter

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

Full name: Microsoft.FSharp.Core.unit

  type: unit
  implements: IComparable
val iter : ('T -> unit) -> seq<'T> -> unit

Full name: Microsoft.FSharp.Collections.Seq.iter
val iterrc : (int -> int -> 'a -> unit) -> 'b -> 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 -> 'a -> unit)
val filter : ('a -> bool) -> 'b -> seq<'a>

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 : ('a -> bool)
type bool = Boolean

Full name: Microsoft.FSharp.Core.bool

  type: bool
  implements: IComparable
  implements: IConvertible
  implements: IComparable<bool>
  implements: IEquatable<bool>
  inherits: ValueType
val filter : ('T -> bool) -> seq<'T> -> seq<'T>

Full name: Microsoft.FSharp.Collections.Seq.filter
val excel : 'a

Full name: Excel.excel
val workbookDir : string

Full name: Excel.workbookDir

  type: string
  implements: IComparable
  implements: ICloneable
  implements: IConvertible
  implements: IComparable<string>
  implements: seq<char>
  implements: Collections.IEnumerable
  implements: IEquatable<string>
val workbook : 'a

Full name: Excel.workbook
val exampleSheet : 'a

Full name: Excel.exampleSheet
val exampleRange : 'a

Full name: Excel.exampleRange
val cellCount : int

Full name: Excel.cellCount

  type: int
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<int>
  implements: IEquatable<int>
  inherits: ValueType
val length : seq<'T> -> int

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

Full name: Excel.listCellRC

  type: unit
  implements: IComparable
val printfn : Printf.TextWriterFormat<'T> -> 'T

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

Full name: Excel.rangeAddress

  type: unit
  implements: IComparable
val floatTotal : obj

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

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

Full name: Excel.evenTotal

  type: float
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<float>
  implements: IEquatable<float>
  inherits: ValueType
val highlightRange : unit

Full name: Excel.highlightRange

  type: unit
  implements: IComparable
val chequerRange : unit

Full name: Excel.chequerRange

  type: unit
  implements: IComparable
val colourOddInts : 'a

Full name: Excel.colourOddInts
val oddIntRange : 'a
val cellVal : float

  type: float
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<float>
  implements: IEquatable<float>
  inherits: ValueType
Multiple items
val float : 'T -> float (requires member op_Explicit)

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

--------------------

type float<'Measure> = float

Full name: Microsoft.FSharp.Core.float<_>

  type: float<'Measure>
  implements: IComparable
  implements: IConvertible
  implements: IFormattable
  implements: IComparable<float<'Measure>>
  implements: IEquatable<float<'Measure>>
  inherits: ValueType


--------------------

type float = Double

Full name: Microsoft.FSharp.Core.float

  type: float
  implements: IComparable
  implements: IFormattable
  implements: IConvertible
  implements: IComparable<float>
  implements: IEquatable<float>
  inherits: ValueType

More information

Link: http://fssnip.net/aV
Posted: 2 years ago
Author: Kit Eason (website)
Tags: Excel, interop