Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 1. December 2012 13:55

I have been obsessing about the following idea lately – what if I could run a FSI session from within Excel? The motivation behind this is double. First, one thing Excel is good at is creating and formatting charts. If I could use F# for data manipulation, and Excel for data visualization, I would be a happy camper. Then, I think F# via FSI could provide an interesting alternative for Excel automation. I’d much rather leverage existing .NET libraries to, say, grab data from the internet, than write some VBA to do that – and the ability to write live code in FSI would be less heavy handed that VSTO automation, and closer to what people typically do in Excel, that is, explore data. Having the ability to execute F# scripts would be, at least for me, very useful.

Seeing Tim Robinson’s awesome job with FsNotebook.net kicked me out of procrastination. Even though FsNotebook is still in early development, it provides a very nice user experience – on the web. If something that nice can be done on the web, it should be feasible on a local machine.

As an aside, Tim is looking for feedback and input on FsNotebook – go try it out, it’s really fun:

Anyways – this is the grand plan, now we need to start with baby steps. If I want to embed FSI in Excel (presumably via a VSTO add-in), I need a way to talk to FSI from .NET, so that I can create a Session and send arbitrary strings of code to be evaluated.

As usual, StackOverflow provided two good starting points (this answer, and this answer) – so I set out to look into the Process class, which I didn’t know much about, and attempted to spawn a FSI.EXE process, redirecting input and output. Turns out it’s not overly complicated – here are the 34 lines of code I ended up with so far (see it on GitHub):

namespace ClearLines.FsiRunner

open System.Diagnostics

type public FsiSession(fsiPath: string) =

    let info = new ProcessStartInfo()
    let fsiProcess = new Process()

    do
        info.RedirectStandardInput <- true
        info.RedirectStandardOutput <- true
        info.UseShellExecute <- false
        info.CreateNoWindow <- true
        info.FileName <- fsiPath

        fsiProcess.StartInfo <- info

    [<CLIEvent>]
    member this.OutputReceived = fsiProcess.OutputDataReceived

    [<CLIEvent>]
    member this.ErrorReceived = fsiProcess.ErrorDataReceived

    member this.Start() =
        fsiProcess.Start()
        fsiProcess.BeginOutputReadLine()

    member this.AddLine(line: string) =
        fsiProcess.StandardInput.WriteLine(line)

    member this.Evaluate() =
        this.AddLine(";;")
        fsiProcess.StandardInput.Flush()

This is a fairly straightforward class. The constructor expects the path to FSI.EXE, and sets up the process in the constructor (the do block) to run headless and redirect the stream of inputs and outputs. Start() simply starts the process, and begins reading asynchronously the output of FSI, AddLine(line) is used to add an arbitrary string of F# code, and Evaluate() sends all lines currently buffered to FSI for evaluation – and flushes the buffer. The 2 events OutputReceived and ErrorReceived are provided for the client to listen to the FSI results.

More...

by Mathias 6. November 2011 15:20

I am somewhat tests-obsessed, and as a result, often find Excel frustrating to work with, because writing automated tests against it isn’t trivial. So recently, while perusing the chapter on Scripting in Programming F#, I came across an Office automation example, and started wondering whether this would be a practical way to write automated tests against Excel.

The use case I have in mind is an existing Excel Workbook, which contains a model (say, your typical Financial model), with a fixed structure, and maybe a sprinkle of VBA, and no .NET.

For illustration purposes, let’s work with the following: our workbook, Model.xlsx, contains one worksheet, “Finances”, with a Profit cell in B3, computed as the difference between the revenue and cost named cells. Pretty impressive stuff.

image

What I want is a way to automatically set the Revenue and Cost to some arbitrary value, and check that the result in Profit is what it should be – so that I don’t have to do it myself by hand, and don’t have to remember how this Workbook was supposed to work later on.

Here is how this could look like in a F# script – create a Script file, say WorkbookTest.fsx, with the following code inside:

#r "Microsoft.Office.Interop.Excel"

open System
open Microsoft.Office.Interop.Excel
      
Console.WriteLine("Press [Enter] to start")
Console.ReadLine()

let excel = new ApplicationClass(Visible=false)
let workbooks = excel.Workbooks

let workbookPath = @"C:\Users\Mathias\Desktop\Model.xlsx"

let workbook = workbooks.Open(workbookPath)
let worksheets = workbook.Worksheets
let sheet = worksheets.["Finances"]
let worksheet = sheet :?> Worksheet

let revenueCell = worksheet.Range "Revenue"
revenueCell.Value2 <- 100

let costCell = worksheet.Range "Cost"
costCell.Value2 <- 10

let profitCell = worksheet.Range "Profit"
let profit = profitCell.Value2

Console.WriteLine("Check profit calculations")
Console.WriteLine("Expected: {0}, Actual {1}", 90, profit)

workbook.Close(false, false, Type.Missing)
excel.Quit()

Console.WriteLine("Done, press [Enter] to close")
Console.ReadLine()

The script launches Excel in Invisible mode, opens the workbook, sets the Revenue and Cost to 100 and 10, retrieves the value from Profit, printouts the value it found as well as the expected value – and closes back the Workbook without saving any of the changes.

The nice thing here is that I can now drop that file on my desktop, and simply right-click and select “Run with F# Interactive” to execute it, without building anything, and I’ll see something like this happen:

image

Nothing earth shattering, but still pretty nice: now I got a script which I can run anytime I want, to check whether the Workbook is behaving properly. Furthermore, what’s nice is that I don’t need to open Visual Studio to work with it: I can simply open WorkbookTest.fsx with Notepad, edit my code, and run it again.

There are some clear issues with the code in its current form. For instance, if anything goes wrong in the code (say, for instance, that I mis-typed a name which doesn’t exist with the workbook), the script will crash miserably, and let the hidden Excel instance hang in the background, waiting for someone to kill it manually. This would require some work to make sure that if exceptions are raised, everything is properly disposed, and no matter what, the file gets closed without saving any modification.

In any case, I thought it was worth sharing, even in its rough state – if only because it was fun, and also because the F# code looks surprisingly more appealing than the usual C# Interop code. Now the fun part would be to turn this into a decent testing framework for Excel…

Comments

Comment RSS