Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Collapse]Spreadsheet Formulas
    [Expand]Functions
     Operators
     Array Formulas
     Calculation
     Formula Engine
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Formula Engine

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

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

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

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

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

How would you rate this topic?​​​​​​​