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: