Formula Engine
- 4 minutes to read
Overview
The FormulaEngine is an object that provides the capability to calculate and parse worksheet formulas. It includes a built-in formula parser, as well as the flexibility to evaluate formulas in any range of any worksheet using the FormulaEngine.Evaluate method. You can parse a formula using the FormulaEngine.Parse method, analyze the resulting expression tree, make the required modifications and rebuild the formula string from the modified expression tree.
Note
You can get a parsed expression of a formula contained in a cell by using the Cell.ParsedExpression property. To obtain a parsed expression of a formula specified by defined name, use the DefinedName.ParsedExpression property.
Context
The formula context determines the environment that affects the function name recognition, formula calculation and reference resolution. The context is defined by the cell or range to which the formula belongs, the worksheet, the culture settings and the cell reference style.
The ExpressionContext object serves as a container to hold context settings.
You can pass the context to the FormulaEngine.Evaluate or FormulaEngine.Parse method. If the context is not specified explicitly, the currently selected range, active cell and active worksheet are used.
Expression
The ParsedExpression object can be obtained by parsing an expression by using the FormulaEngine.Parse method, by using the Cell.ParsedExpression or the DefinedName.ParsedExpression properties. An expression is parsed into an expression tree, which is made available by using the ParsedExpression.Expression property.
The nodes in the expression tree implement the IExpression interface. The technique to traverse the tree is based on the Visitor pattern. The Visitor pattern is briefly explained below.
To traverse the tree and visit all nodes, we need a starting (entry) point and a visitor. The entry point can be any tree node, but the root node would be an obvious choice. The root node is accessible from the ParsedExpression.Expression property. To start traversing, call the IExpression.Visit method of the node with the visitor as the method parameter. The node will subsequently call the Visit method of the visitor with the node itself as the parameter. Thus, the Visit method overload is called, which has a parameter type that is equal to the type of the node. Then, the visitor’s Visit method is called for sibling nodes if they exist.
For example, take the formula SUM(A4:A6)+10. The root node is the AdditionExpression (corresponding to the “+” operand in the formula) and the Visit(AdditionExpression expression) method of the visitor is called. This method calls the VisitBinary(BinaryOperatorExpression expression) method, which in turn, calls the Visit(FunctionExpression expression) method for the left operand (the SUM function). This method calls the Visit(CellReferenceExpression expression) for the function argument, which is the A4:A6 cell range reference. The node containing the cell reference has no siblings, so all nodes of the left branch of the root addition expression are visited, and the Visit method of the right operand is called. The right operand is the constant value 10, which has no child branches. The Visit(DevExpress.Spreadsheet.Formulas.ConstantExpression expression) method overload is called for that node. Expression tree iteration is now complete.
To create a string formula from the expression tree, use the ParsedExpression.ToString method.
It is often useful to be able to refer to the cells whose values are used in the formula. The ParsedExpression.GetRanges method returns a collection of ranges referenced by the formula.
Visitor
A visitor is required to visit all nodes of the expression tree. Implement a descendant of the ExpressionVisitor class and override the ExpressionVisitor.Visit method overloads, which obtain nodes of the required types. After obtaining the node using the visitor, you can modify node properties (e.g., change cell reference) or navigate to child nodes using UnaryOperatorExpression.InnerExpression, BinaryOperatorExpression.LeftExpression or BinaryOperatorExpression.RightExpression, depending on the type of the obtained node.