Excel formular to C# code

Question

I'd like to convert an excel spreadsheet to a component of c# code. To get it goin' I like to get all cells of a sheet containing fomulars into c# via interop. In the c# module I'd like to solve the defined fomulars with different values replacing the cell references in the fomular. Is there a framework where I can for example convert a "SUM($C3 : $C5)" from excel to a "var sum = c3 + c4 + c5;" or semilar formulars?

regards gordon

Answer 1

I think you are looking for an expression evaluator in C#.

Google found a few that I think you should look at:

• The Fast Lightweight Expression Evaluator on Codeplex.
• ILCalc on CodePlex - "This project was inspired by the FLEE project, but written in different, much easier way."
• NCalc is another one on CodePlex - evaluates custom functions and works with Unicode, which might be important for you. There is a related CodeProject article.
• There's this C# Expression Evaluator article by Jonathan Wood.
• There's one in the Spring.net Framework.
• A CodeProject article by Pascal Ganaye.
• Or you can work on your own starting with the DynCalc example by Bart de Smet.

You might also need implementations of all those built-in Excel functions. There's a library with all the financial functions re-implemented in F# by Luca Bolognese - find it on MSDN.

Answer 2

More power than what you need, but with some luck only a thin interface wrapper is needed to pump data out of execl into formula engine:

FormulaEngine is a .NET assembly that enables you to add formula support to your application. It takes care of parsing and evaluating formulas, tracking their dependencies, and recalculating in natural order. The formula syntax and much of the engine's functionality are direct replicas of Excel ensuring a low learning curve for users.

Link: FormulaEngine

---

Update: I recently started a similar python library: pycel