Jaa

Fsharp & Excel: Reading and writing from and to Excel

  • Artikkeli

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!

Comments

  • Anonymous
    May 30, 2012
    Did you see the Excel type provider? github.com/.../ExcelProvider.fs

  • Anonymous
    May 31, 2012
    Steffen, yeah. excel tp is cool but still has it limitations