To help people to get start with programing Excel in Fsharp, I will show several common scenarios for reading and writing data from and to excel spreadsheet. The scenarios are:
Example file Input.xlsx:
Example 0: Opening an exiting\Creating a new excel worksheet
// read and write data to\from a excel workbook
// Start Excel, Open a exiting file for input and create a new file for output
let xlApp = new Excel.ApplicationClass()
let xlWorkBookInput = xlApp.Workbooks.Open(@"C:\Users\jackhu\Desktop\Input.xlsx")
let xlWorkBookOutput = xlApp.Workbooks.Add()
xlApp.Visible <- true
// Open input's 'Sheet1' and create a new worksheet in output.xlsx
let xlWorkSheetInput = xlWorkBookInput.Worksheets.["Sheet1"] :?> Excel.Worksheet
let xlWorkSheetOutput = xlWorkBookOutput.Worksheets. :?> Excel.Worksheet
xlWorkSheetOutput.Name <- "OutputSheet1"
First, I make reference to office interop dlls. I open an exiting excel workbook for input and create a new workbook for output by calling Workbooks.Open and Workbooks.Add(). The xlApp.Visible <- true property is set to display the workbooks. I then find the “Sheet1” from the input workbook by calling Worksheets.["Sheet1"]and get the first worksheet from the output worksheet by indexing Worksheets. into it.
Example 1: Reading\Writing a cell value
// EXAMPLE 1: Reading\Writing a cell value using cell index
let value1 = xlWorkSheetInput.Cells.[10,5]
xlWorkSheetOutput.Cells.[10,5] <- value1
// EXAMPLE 1.1: Reading\Writing a cell value using range
let value2 = xlWorkSheetInput.Cells.Range("E10","E10").Value2
xlWorkSheetOutput.Cells.Range("E10","E10").Value2 <- value2
I show two ways of accessing a cell value. One method is by using array indexer. Inheriting from VB array indexing convention, the Excel indexer starts from 1 instead from 0. Another method is using the Range by specifying the starting cell location and ending cell location. In my example, since I only have one cell, E10 is used for both the starting and the ending position.
Example 2: Reading\Writing a row
// EXAMPLE 2: Reading\Writing a row
let row = xlWorkSheetInput.Cells.Rows. :?> Excel.Range
(xlWorkSheetOutput.Cells.Rows. :?> Excel.Range).Value2 <- row.Value2
In above code snippet, I use :?> to up-cast object to Excel range type, so that I gets intellisense\error checking support for the row value. To figure out the exact code, I also used the debugger to exampling the values to help me figuring out the correct type casting.
Example 3: Reading\Writing a column
// EXAMPLE 3: Reading\Writing a column
let column1 = xlWorkSheetInput.Cells.Range("A:A")
xlWorkSheetOutput.Cells.Range("A:A").Value2 <- column1.Value2
Similar to the row example, we can also use a column index to select a range xlWorkSheetInput.Cells.Columns. :?> Excel.Range. Instead, I use the range parameter “A:A” for the column ( I may use “1:1” is for a row).
Example 4: Reading\Writing a Range
// EXAMPLE 4: Reading\Writing a Range
let inputRange = xlWorkSheetInput.Cells.Range("A1","E10")
for i in 1 .. inputRange.Cells.Rows.Count do
for j in 1 .. inputRange.Cells.Columns.Count do
xlWorkSheetOutput.Cells.[i,j] <- inputRange.[i,j]
Of course, there are many other ways to read and write a large range of the data. Here, I show how a two nested for loops for iterate through the worksheet as a 2D array.
Example 5: Writing a Jagged array
// EXAMPLE 5: Writing an Jagged arrays
let data = [| [|0 .. 1 .. 2|];
[|0 .. 1 .. 4|];
[|0 .. 1 .. 6|] |]
for i in 1 .. data.Length do
for j in 1 .. data.[i-1].Length do
xlWorkSheetOutput.Cells.[j, i] <- data.[i-1].[j-1]
When your data does not conform to M by N size, the jagged array is a more practical approach. I hope a few simple examples can help you to get started with excel. Happy coding!
Did you see the Excel type provider?
Steffen, yeah. excel tp is cool but still has it limitations