• Jackhu's Blog

    Fsharp & Excel: Calling Excel Math Functions From F# FSI

    • 3 Comments
    Technorati Tags: ,,,,

    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).

    image

    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.

    image

    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.

    image

    Both F# and C# Code example @ http://code.msdn.microsoft.com/Calling-Excel-Math-6b811836

    Happy Coding!

    -jack

  • Jackhu's Blog

    Fsharp & Excel: Reading and writing from and to Excel

    • 3 Comments

    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!

     

  • Jackhu's Blog

    Fsharp & Excel: Creating Excel charts from F# FSI

    • 0 Comments

    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.

    • VSLAB – lots feature in 3D plot, can be run as VS2010 add-in
    • XtraCharts – Industry strength, good for drag and drop designer type of development, but not great for FSI
    • Microsoft Chat Controls – it is the base for EasyChart, it is good for apps
    • DotnetCharting – has a several unique charts, it is good for domain specific applications.

    In this blog, I also like to demonstrate how to call Excel chart libraries from the FSI window. There are several benefits for using the excel charts from FSI

    • Popularities: most people has experience with excel charting. It is easy to prettify, add a title, copy and paste the chart into your presentation.
    • Community: there are plenty C# to Excel code example. To find out how to do a 3D plot, you can simply Bing it.

    Step0: Software Setup

    • VS2010 + Office 2010

    Step1: Past in following code in FSI 

    #r "Microsoft.Office.Interop.Excel"

    #r "office"

    open Microsoft.Office.Interop

     

    let dataX = [|0.0 .. 0.1 .. 10.|]

    let dataY = [|  [|for f in dataX -> cos f|];

                    [|for f in dataX -> sin f|] |]

     

    // Update the excel charting object

    let xlApp = new Excel.ApplicationClass()

    let xlWorkBook = xlApp.Workbooks.Add()

    let xlWorkSheet = xlWorkBook.Worksheets.[1] :?> Excel.Worksheet

    let xlCharts = xlWorkSheet.ChartObjects() :?> Excel.ChartObjects

    let xlChart = xlCharts.Add(1., 1., 460., 380.)

    let myChart = xlChart.Chart

     

    // Fill in a excel worksheet with data from dataY[][]

    for i in 1 .. dataY.Length do

        for j in 1 .. dataY.[i-1].Length do

            xlWorkSheet.Cells.[j, i] <- dataY.[i-1].[j-1]

    let xlRange = xlWorkSheet.Cells.CurrentRegion

    myChart.SetSourceData(xlRange)

     

    // Set Plot type and show chart

    myChart.ChartType <- Excel.XlChartType.xlXYScatterLines

    xlApp.Visible <- true

    It result in:

    I1

    FSharp language services Intellisense fairly useful to select a right chart.

    I2

    Happy coding

  • Jackhu's Blog

    Testing out MSDN Blog using Window Live Writer

    • 0 Comments

    trying out Windows Live Writer for Blogging. Here are some screen shots

    image

    image 

    I installed Visual Studio Pasting tool, here I go. it works.

    Module Module1
    
        Sub Main()
    
        End Sub
    
    End Module
    
  • Jackhu's Blog

    F# Code-First Development with Entity Framework 4.1

    • 0 Comments

    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 blog, I demonstrate how to use EF 4.1 Code-first in Fsharp 2.0 to save a record into a SQL CE. I also provide workarounds for several practical issues may block fsharp programmers.

    Step0: Install Software Packages

    Step1: Create a New F# Application

    image001

    Add Project references: Unlike C#, Fsharp compiler requires additional references from System.Data and System.Data.Entity to resolve base types inherited by EF 4.1

    image002

    The EntityFramework.dll is under $\Program Files (x86)\Microsoft ADO.NET Entity Framework Feature CTP5\Binaries\EntityFramework.dll

    Step2: Create a Model

    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.

    image003

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

    image004

    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.

    image005

    Step4: Run and Verify data

    After build and run app, I use SQL Server Compact 4.0 Tooling for VS2010 to verify the database, the table and the data are added correctly.

    image006

    During the ad-hocking, I also encountered several issues. Here is how I work around them.

    Issue#1: EF 4.1 does not support nested type; the model can NOT be used inside a Module or a Script

    Trying to put the model inside a module, I got a run time exception: “The type 'Program+Car' is not a supported entity type.”

    image007

    Digging into fsharp assembly using reflector, we can see the Fsharp script and module generate a nested-type which is not supported in EF 4.1

    image008

    The work around is to put the model code inside a namespace DataModel

    The VB module shares the same limitation. The work around in below example is to move the Car and CLCars class outside the Module1.

    image009

    Issue#2: Property initialization on DBContext class need to use [<DefaultValue>] attribute with Explicit Fields

    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.

    image010

    image011

    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! 

    Code example is published @ http://code.msdn.microsoft.com/F-Code-First-Development-326dede1

Page 1 of 1 (5 items)