How can I parse an excel formula?

Question

I am converting the functionalities incorporated in a complex excel sheet to an ASP.Net project. In this I have a requirement to parse/process excel like formula using VB.NET or c#.

I have a grid like structure which displays accounting figures and the user is allowed to pre-configure formula in required cells.

Example :- In my datagrid Cell[2][1], I should be able to configure

formula = Sum(Cell[1][1] + Cell[1][2]) .

Is there a way we can parse/process excel formula from vb.net/c# ?

Answer 1

You could work around FLEE library to evaluate C# like expressions

// Create the calculation engine
CalculationEngine engine = new CalculationEngine();
ExpressionContext context = new ExpressionContext();
VariableCollection variables = context.Variables;

// Add some variables
variables.Add("x", 100);            
variables.Add("y", 200);

// Add an expression to the calculation engine as "a"
engine.Add("a", "x * 2", context);

// Add an expression to the engine as "b"
engine.Add("b", "y + 100", context);

// Add an expression at "c" that uses the results of "a" and "b"
engine.Add("c", "a + b", context);

// Get the value of "c"
int result = engine.GetResult<int>("c");

// Update a variable on the "a" expression            
variables["x"] = 200;

// Recalculate it
engine.Recalculate("a");

// Get the updated result
result = engine.GetResult<int>("c");