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 0: Opening an exiting\Creating a new excel worksheet
- Example 1: Reading\Writing a cell value
- Example 2: Reading\Writing a row of values
- Example 3: Reading\Writing a column of values
- Example 4: Reading\Writing a Range of values
- Example 4: Writing a Jagged array to excel
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!