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

Calculation

This document provides information about the Spreadsheet calculation process.

Expanded Engine Type

The Spreadsheet calculation engine can use two types of computational models. You can switch between them by changing the DocumentOptions.CalculationEngineType property value. The difference between calculation engine types is explained below in more detail.

  • ChainBased

    This is the default calculation engine type.

    The Spreadsheet analyzes cell formulas to build the tree of dependencies between cells and to determine its Range.DirectDependents and Range.Dependents (or equivalently, the Range.DirectPrecedents and Range.Precedents) for each cell. Subsequently, it constructs the calculation chain, which lists the cells in the order in which they should be calculated.

    The calculation chain may change dynamically, after each calculation, because the calculation engine tries to discover the fastest way to calculate the workbook. The engine can calculate particular cells multiple times until it constructs a correct calculation sequence. The calculation sequence discovered in such a way will be used in subsequent recalculations.

    When cell content is modified, the calculation chain provides a way to determine which cells should be calculated. Those cells are marked as needing recalculation.

    There are also cells that are always marked as needing calculation ("CalculateAlways" cells). They are listed below:

    • containing volatile function;
    • referencing another cell that always needs recalculation;
    • containing a circular reference.

    If a cell depends on itself, the calculation engine warns the user about a circular reference. A circular reference can be considered an error condition, or it can be created intentionally to perform an iterative calculation. To calculate a circular reference, set the CalculationOptions.Iterative option to true.

    This engine type consumes more memory, but improves performance. It is recommended for use in a visual control (WinForms SpreadsheetControl, WPF SpreadsheetControl).

  • Recursive

    This type of calculation engine was used by default before v15.1.

    When cell content is modified, all cells are marked as needing recalculation. Calculation of a particular cell is performed when needed - in a situation when its actual value is required for rendering or when the calculation engine attempts to obtain a value of another cell, which references the current cell.

    This type of engine does not allocate resources for dependence tree storage and maintenance, so it is best suited for server-based components, such as Workbook. Server-based calculations are usually performed only once, before a document is saved.

Expanded Volatile Functions

A volatile function is the function whose value is assumed different at different moments even if its arguments are the same. Cells containing volatile functions and their dependents are reevaluated on each recalculation.

Examples of built-in volatile functions are RAND(), NOW(), TODAY().

For a custom function (User-Defined Function (UDF)) implement the IFunction.Volatile property to return true if the function is volatile..

Expanded Modes and Options

A calculation mode can be specified using the CalculationOptions.Mode property.

The CalculationMode.Manual mode requires a call to one of the Calculate methods to start calculation.

Switching the mode to the CalculationMode.Automatic forces recalculation of all cells marked as needing calculation if the ChainBased engine type is in effect, and recalculates all cells for the Recursive engine type. The Automatic mode starts recalculation after every data input.

The CalculationMode.AutomaticExceptTables mode allows you to disable the automatic calculation of data tables.

Various calculation settings regarding iterative calculations, options which specify whether a document should be recalculated immediately after loading and before saving, and some other options are contained in the CalculationOptions object, which is accessible using the following notation.

To prevent automatic calculation when a document is loaded, use the OpenXmlDocumentImporterOptions.OverrideCalculationMode and XlsDocumentImporterOptions.OverrideCalculationMode properties.

Expanded Methods to Force Calculation

Expanded Service

You can implement the ICustomCalculationService to fine-tune the process of worksheet calculations.

It enables you to do the following.

  • Determine when the calculation starts and finishes, and cancel the calculation.
  • To not mark "CalculateAlways" (see definition above) cells as needing recalculation.
  • Determine when the calculation begins for a particular cell. Subsequently, the calculation can be canceled and the cell can get an arbitrary value.
  • Determine when the calculation ends for a particular cell (if the calculation is not canceled).
  • Determine when cells with circular references start calculating, and cancel the calculation.
  • Determine when circular references finish their calculations and obtain a list of cells whose formulas contain circular references.

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T270403.

Expanded Shortcuts

The key combinations used to initialize spreadsheet recalculation are listed in the table below.

Shortcut

Description

F9 Calculates the entire workbook.
SHIFT+F9 Calculates the active worksheet.
CTRL+ALT+F9 Calculates the entire workbook, regardless of whether its data has changed since the last calculation.
CTRL+ALT+SHIFT+F9 Calculates the entire workbook, regardless of whether its data has changed since the last calculation, and rebuilds the dependencies.

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