Excel formular to C# code

问题:

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

回答1:

Is this something you'll do repeatedly or just one time?

We're using www.spreadsheetgear.com to manipulate Excel spreadsheets from C# and it works wonderfully. Our needs are different, so you should study the API to see if it gives you access to the text of the forumulas, but I think it might.

No affiliation - just a satisfied customer.

回答2:

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.

回答3:

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

回答4:

Check out ActiveMesa X2C, which does precisely that. (Disclaimer: I'm the author of said product.)