2 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
val cellContent : range:Range -> 'a


 Helper function to represent string or floating point cell content as a string.
val range : Range
Multiple items
type Range =
  struct
    new : start:Index * end:Index -> Range
    member End : Index
    member Equals : value:obj -> bool + 1 overload
    member GetHashCode : unit -> int
    member GetOffsetAndLength : length:int -> ValueTuple<int, int>
    member Start : Index
    member ToString : unit -> string
    static member All : Range
    static member EndAt : end:Index -> Range
    static member StartAt : start:Index -> Range
  end

--------------------
Range ()
Range(start: Index, end: Index) : Range
Multiple items
val string : value:'T -> string

--------------------
type string = String
val sprintf : format:Printf.StringFormat<'T> -> 'T
Multiple items
val double : value:'T -> double (requires member op_Explicit)

--------------------
type double = Double
val cellDouble : range:Range -> 'a


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


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

--------------------
type seq<'T> = Collections.Generic.IEnumerable<'T>
val r : int
val c : int
val cell : Range
val toSeqrc : range:Range -> seq<int * int * Range>


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


 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<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
val acc : string
val x : string
val rangeName : string
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>
val map : f:(Range -> 'T) -> range:Range -> seq<'T>


 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>


 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)

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

--------------------
type int<'Measure> = int
val item : int * int * Range
val iter : f:(Range -> unit) -> range:Range -> unit


 Takes a function and an Excel range, and applies the function to each individual cell.
val f : (Range -> unit)
type unit = Unit
val iter : action:('T -> unit) -> source:seq<'T> -> unit
val iterrc : f:(int -> int -> Range -> unit) -> range:Range -> unit


 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>


 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
val filter : predicate:('T -> bool) -> source:seq<'T> -> seq<'T>
val excel : obj
val workbookDir : string
val workbook : obj
val exampleSheet : obj
val exampleRange : Range
val cellCount : int
val length : source:seq<'T> -> int
val listCellRC : unit
val printfn : format:Printf.TextWriterFormat<'T> -> 'T
val rangeAddress : unit
val range : obj
val floatTotal : obj
val sum : source:seq<'T> -> 'T (requires member ( + ) and member get_Zero)
val evenTotal : float
val highlightRange : unit
val chequerRange : unit
val colourOddInts : obj
val oddIntRange : obj
val cellVal : float
Multiple items
val float : value:'T -> float (requires member op_Explicit)

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

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

More information

Link:http://fssnip.net/aV
Posted:9 months ago
Author:Kit Eason
Tags: excel , interop