Fsharp & Excel: Reading and writing from and to Excel

Fsharp & Excel: Reading and writing from and to Excel

Rate This
  • Comments 2

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:

image001

Example 0: Opening an exiting\Creating a new excel worksheet

// read and write data to\from a excel workbook

#r "Microsoft.Office.Interop.Excel"

#r "office"

open Microsoft.Office.Interop

 

// 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.[1] :?> Excel.Worksheet
xlWorkSheetOutput.Name <- "OutputSheet1"

image002

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.[1] 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
image003

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.[1] :?> Excel.Range
(xlWorkSheetOutput.Cells.Rows.[1] :?> Excel.Range).Value2 <- row.Value2

 

image004

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.

image005


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

image006

Similar to the row example, we can also use a column index to select a range xlWorkSheetInput.Cells.Columns.[1] :?> 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]

image007

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]

image008

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!

 

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
Page 1 of 1 (2 items)