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.
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 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.