While I am working on compiling a list of .Net math libraries, I found a simple way to call Excel math library from F# FSI. In the following example, I will show how to use Excel’s Median, StDev and MInverse functions from F# scripting environment (FSI).

Software setup: VS2010 Sp1 with Office 2010 installed.

There are few benefits of using Excel math libraries:

Correctness: you can almost certain the correctness of the excel math functions.

Popularities: most the financial excel guru knows one thing or another about the excel math functions. This allow them to pickup these familiar functions very quickly.

IDE support: Basic intellisense support is very important for the user. Just a little bit of UI hit can void breaking user’s code flow experience.

but, there are also issues:

Debugging: When the math function is not working, debugging them is troublesome. you may get a comException gives no clue about the problem. A good way to debug these issues is to start with a small dataset, try it out inside the excel first.

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

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.

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

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!

Following up with the recent announcement on F# EasyChart, I like to point out a few .net charting libraries that can potentially be applied to the interactive development environment like the F# FSI window.

As the word “code-first” implies, the EF 4.1 offers a code centric data programming paradigm. From a coder’s point of view, it requires little effort to map a very clean object model to a database. This style of programming is idea for explorative, bottom-up kind fsharp programmers. Since EF 4 CTP4 release, there have already been many buzzes. The following two blogs have in-depth EF 4.1 feature overview.

In this example, I create a CLCars class. The class contains a single DbSet of Cars. In Car class I have a field of ID (serve as the primary key) and a field of Name.

In the driver code, I create a new CLCars database, added a Car to it and flush out to the database.

Step3: Pointing to a Database

I love SQL CE 4.0, especially because user no longer needs to generate a primary key while adding a record. Yeah! In this example, I add an app.config file and embed the database connection string. I also make sure the connectionString Name property is the same as my DbContext class name, so that the EF 4.1 automatically generates a database at runtime.

During EF DbContext construction, it will reflect on all its properties and initialized them with database mapping values. After base construction, if the inhered Fsharp class calls its constructor with property initialization, it will override the property value that base class already initialized.

In the following example, the DbContext constructor will initialize m_cars to a DbSet<Car> mapping value, but the inhered CLCars constructor will reinitialize n_cars to NULL. This could cause a NullReferenceException at runtime.

EF 4.1 code-first is a great tool for F# 2.0 data programming. As Fsharp 3.0 features become clearer in the next few months, I am supper exited and feel good about them. I expect Fsharp 3.0 will take these experiences to the next level. Happy coding!