Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 20. January 2013 13:17

I am still toying with the idea of using FSI from within Excel – wouldn’t it be nice if, instead of having to resort to VBA or C# via VSTO, I could leverage F#, with unfettered access to .NET and a nice scripting language, while having at my disposal things like the charting abilities of Excel?

Judging from the discussion on Twitter this morning, it seems I am not the only one to like the idea of F# in Excel:

I am still far from this perfect world, and wouldn’t mind some input from the F# community, because I am having a hard time figuring out where the sweet spot is. At that point, what I have is a pretty rudimentary WPF FSI editor, written in C#.

Note: yes, I should have written it in F#, shame on me! I am still more comfortable with the WPF/C# combo at the moment, but getting increasingly uncomfortable with XAML and the amount of magic string involved in data binding. Jon Harrop presented some very stimulating ideas on this topic at the last San Francisco F# user group, I intend to try the NoXAML route at a later point.

Anyways, you can find my rudimentary editor here on GitHub, with a crude WPF demo. Running it should produce something like this:

Editor

I quite liked how FsNotebook organized the code into blocks and separated the inputs and outputs, so I followed the same idea: you can add new sections at the top and evaluate each one separately, and see the result at the bottom. There is obviously plenty of work to do still, but at least this is a working prototype.

Note: the code is still pretty ugly, and totally not ready for prime time. Specifically, resources aren’t disposed properly at all – use at your own peril!

More...

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

Comments

Comment RSS