Calculation
- 5 minutes to read
This document provides information about the Spreadsheet calculation process.
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.
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..
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.
Methods to Force Calculation
Document
To force the calculation of the document in the CalculationMode.Manual mode, call the IWorkbook.Calculate method that calculates only cells marked as needing recalculation. The IWorkbook.CalculateFull method calculates all cells regardless of the CalculationOptions.Mode value. The IWorkbook.CalculateFullRebuild reconstructs the dependency tree for the ChainBased engine type and subsequently calls the IWorkbook.CalculateFull method for any engine type.
Worksheet
Use the Worksheet.Calculate method to recalculate all cells in the current Worksheet.
Range and Cell
Use the IWorkbook.Calculate overload that takes Range as an argument, or call the Range.Calculate method. Calculate all cells in the specified range.
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.
Tip
A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T270403.
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. |