Sunday, April 17, 2016

Excel calculations in F# - Tools and Requirements

So we want to be able to do better vector arithmetic in Excel.

As this is primarily about doing something in F#, that means we need some way of interfacing with it from .NET code. Enter Excel-DNA. For those unfamiliar, this is a fantastic open-source tool that let’s you create Excel add-ins, and I highly recommend reading their blurb.

Getting it up and running really is as simple as it suggests, which leaves us plenty of time to think about our requirements. At the core, we want to be able to write the following in the Excel formula bar

=Calculate(expression)

and have it correctly evaluate the expression in the following cases:

  • a constant, e.g. 1;
  • a standard arithmetic expressions e.g. 1+2;
  • a reference to another cell e.g. A1 or $BQ$192;
  • a vector e.g. {1;2;3};

and any combination of the cases above, the example in the previous post being =B2+C2 where B2={1;2;3} and C2={4;5;6}.

This elicits the first hard requirement: we’re going to need direct access to the raw text of the calling cell, and any cells that it references.

We can do this at a pretty low level using the Microsoft.Office.Interop.Excel namespace, which gives us direct access to things like workbooks, worksheets, cell ranges and cell formulas. Alternatively, we can harness Excel-DNA’s Integration package to perform a variety of tasks using the XlCall class. In a subsequent post I’ll show F# snippets using both techniques.

The second requirement should be fairly obvious: we need to be able to correctly calculate arithmetic expressions. Thankfully this is a well-trodden path, and involves the following:

  • Parse the incoming text and make sure it’s a valid expression (for example, no mismatched brackets or odd characters);
  • Convert the expression into a format from which it can be calculated (for example, to Reverse Polish Notation);
  • Evaluate the expression.

We’ll say much more about each of these steps from the perspective of F# and functional programming in the future, but suffice to say there’s no breakthrough in Computer Science with the above routine.

Finally, I’m going to introduce a requirement borne out of greed. If we can handle a reference to a vector, surely we can try and handle a reference to anything? From a code perspective, this is equivalent to saying something that fits a given function signature in F# (or implements a given interface in an OO language).

Expanding on this, what if we could:

  • store any .NET object via a user-defined function in Excel;
  • construct a method signature that allows evaluation of an expression involving such objects;
  • store the result of our computations in another .NET object; and
  • retrieve the results in a polymorphic fashion (constant, vector, matrix, …) with another user-defined function.

Much of this is helped by wrapping the Excel Object Handler, but it still sounds like a lofty goal.

In the next post, I’m going to start showing exactly how this is done.

No comments:

Post a Comment