Still obsessing on FSI and Excel
20 Jan 2013I 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:
@jonharrop @dnesteruk @7sharp9 @dsyme the F# plugin for MonoDevelop & http://t.co/C81jvUjf can provide a foundation, I'd like F# in Excel ;)
— Sean's dad (@ptrelford) January 20, 2013
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:
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!
Now the question I am facing is the following: what would be a good way to expose FSI in Excel (assuming this is not a terrible idea…)? Technically, this can already be used to work against Excel. As a demo, start Excel, then the Editor, and try out the following code:
If everything works according to plan, the script should find your already-running Excel instance, and write “Hello from F#” in cell A1 of the first worksheet. Nothing spectacular, but it proves the point - I can fire up the editor, run a small script and get full Excel interop from FSI.
At that point, the obvious question is - that’s great, but how is this better from running FSI from the Console, Visual Studio or FunPad?
The answer is, there isn’t much difference. The one upside I could see is that it is feasible to add niceties like syntax highlighting or saving some configuration, but that’s pretty much it.
One thing I was considering is embedding the Editor as a VSTO add-in, which could provide a smoother integration with Excel, and open possibilities like hosting a service in the add-in for dedicated operations like “import the selected range into FSI” or “export my FSI data and make a chart from it”. That was my initial idea, but I am starting to doubt whether it’s a good one: VSTO is notoriously heavy, and comes with its own set of issues (dependence on the VSTO runtime or on specific versions of Office and Visual Studio…) and it’s not obvious what the upside is. So… if you are interested in using FSI in Excel (or think it’s the worst idea you heard in 2013 so far), I’d love to hear your thoughts! My initial use case was something along the lines of using Excel as a replacement for FSharpChart, but for this I wouldn’t need much beyond a thin DSL. What are your use cases? How would you combine F# and Excel?
Resources
ExcelDNA: if you want a lightweight way to expose .NET functions as Excel user-defined functions, this library is probably what you want. I actually don’t understand why this library hasn’t gotten more traction, it’s really neat.