Chat Now

## Formula Engine## 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 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 Take, for example, 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 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 |