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!

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

Current code on GitHub

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.

Comments

1/21/2013 6:01:19 AM #

govert

Here's a good starter for talking to Excel from F#: msdn.microsoft.com/.../hh297098(v=vs.100).aspx.

With Excel-DNA you can make a .dna file that points to an .fs source file, and then functions, macros etc. are recompiled when you reload the add-in. One button on your "IDE" could reload the add-in, updating the functions available in Excel. It would help Excel-DNA a bit if this F# PowerPack bug could get checked out: http://fsharppowerpack.codeplex.com/workitem/7214. It prevents us from pointing to multiple .fs files in the .dna file and recompiling on load.

The new version of Excel-DNA makes async and event streams from F# really easy to expose as RTD data in Excel.

govert South Africa | Reply

1/21/2013 6:03:13 AM #

govert

Oops - no "." in that first link. It should be: msdn.microsoft.com/.../hh297098(v=vs.100).aspx

govert South Africa | Reply

1/21/2013 6:04:34 AM #

govert

Or in the second one, which should be: http://fsharppowerpack.codeplex.com/workitem/7214

govert South Africa | Reply

1/27/2013 2:55:20 AM #

pingback

Pingback from sergeytihon.wordpress.com

F# Weekly #4, 2013 « Sergey Tihon's Blog

sergeytihon.wordpress.com | Reply

1/27/2013 8:00:34 AM #

pingback

Pingback from sergeytihon.wordpress.com

F# Weekly #4, 2013 « Sergey Tihon's Blog

sergeytihon.wordpress.com | Reply

2/13/2013 7:13:45 AM #

pingback

Pingback from easypic.biz

InterServer

easypic.biz | Reply

3/31/2013 9:05:25 PM #

pingback

Pingback from autobacklinkbuilder.com

Università studiare in romania medicina, studiare odontoiatria, studiare farmacia

autobacklinkbuilder.com | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS