Calculation Process
- 5 minutes to read
This article provides information about the Spreadsheet’s calculation process.
Calculation Engine Type
Use the DocumentOptions.CalculationEngineType property to specify the computational model the Spreadsheet should use when it calculates formulas in a workbook.
Chain-Based (Default)
The Spreadsheet inspects existing formulas to determine dependents (CellRange.DirectDependents, CellRange.Dependents) and precedents (CellRange.DirectPrecedents, CellRange.Precedents) for cells and build the dependency tree. The Spreadsheet then constructs the calculation chain that lists cells in the order they should be calculated.
The calculation chain can change after each calculation because the calculation engine attempts to find the fastest way to calculate a workbook. The engine can calculate cells multiple times until it constructs the correct calculation sequence. The Spreadsheet saves this calculation sequence to the document and uses it in subsequent calculations.
When you change a cell value, the calculation chain determines cells that should be recalculated and marks them as needing calculation. It also recalculates cells that always need calculation.
Use the chain-based calculation engine in the WinForms Spreadsheet and WPF Spreadsheet controls. It consumes more memory but increases performance.
Recursive
When you edit data in a worksheet, all cells are marked for calculation. The Spreadsheet performs calculations on demand (for instance, to display a cell value).
The recursive engine does not need resources to store and maintain the dependency tree, so it is best suited for server-side components such as the Workbook. Server-based calculations are usually performed only once when a document is saved.
Cells That Always Need Calculation
Regardless of the calculation engine type you use, there are cells that always need recalculation (CalculateAlways cells). They include:
cells with volatile functions
cells with references to other cells that always need recalculation
cells with circular references.
A circular reference occurs when a formula directly or indirectly refers to its own cell. Set the CalculationOptions.Iterative option to true to enable iterative calculations for formulas with circular references. If this option is false, the Spreadsheet does not recalculate such formulas.
Volatile Functions
Volatile functions (for example, RAND(), NOW(), TODAY(), and so on) can return a different result on each recalculation even if its arguments are the same. Whenever the Spreadsheet recalculates a workbook, it also updates volatile function values.
If your custom function is volatile, implement the IFunction.Volatile property to return true.
Calculation Options
Use the DocumentSettings.Calculation property to access various calculation options the table below lists.
Property | Description |
|---|---|
Specifies whether to recalculate formulas when a workbook is saved. This property is ignored if Workbook.Options.CalculationMode is Manual. | |
Specifies whether the Spreadsheet should use numbers as they are displayed when it calculates formulas. | |
Specifies the calculation mode for a document. | |
Specifies whether to enable iterative calculations for formulas with circular references. Related options: | |
Specifies whether to use the 1904 date system to convert dates to serial values. | |
Specifies whether to recalculate all formulas when a workbook is opened. | |
Specifies whether to enable multi-threaded calculations. Related option: |
Calculation Mode
The CalculationOptions.Mode property specifies the document’s calculation mode.
| Mode | Description |
|---|---|
| CalculationMode.Manual | Starts calculation after the Calculate method call. |
| CalculationMode.Automatic | Recalculates all formulas each time a cell value, formula, or defined name changes. If the DocumentOptions.CalculationEngineType is ChainBased, the Spreadsheet recalculates only cells that are marked for calculation. |
| CalculationMode.AutomaticExceptTables | Disables automatic calculation for data tables. The Spreadsheet does not support this mode and treats is as CalculationMode.Automatic. |
The Workbook.Options.CalculationMode property overrides the document’s calculation mode and specifies when the Spreadsheet component calculates formulas. The default mode is Manual and formulas in the document are calculated only on demand. Assign the UseDocumentSettings value to the Workbook.Options.CalculationMode property to use the calculation mode specified in the loaded document.
Force Calculation
To recalculate formulas in | Do this |
|---|---|
A document | Use one of the following methods:
|
A worksheet | Use the Workbook.Calculate method overload with the Worksheet parameter, or call the Worksheet.Calculate method. |
A cell range | Use the Workbook.Calculate method overload with the CellRange parameter, or call the CellRange.Calculate method. |
Custom Calculation Service
Implement the ICustomCalculationService interface to adjust the calculation process when the ChainBased calculation engine is used.
This service allows you to:
Determine when the calculation process starts and ends for a workbook, and cancel the calculation.
Specify whether to mark CalculateAlways cells for calculation.
Determine when the calculation starts and ends for a cell, and cancel the calculation. The cell can get an arbitrary value in this case.
Determine when the calculation starts for cells with circular references, and cancel the calculation.
Determine when the calculation ends for cells with circular references, and obtain their list.
If you implement ICustomCalculationService, the Spreadsheet uses only one thread to calculate formulas.
Multi-Threaded Calculations
The Spreadsheet supports multi-threaded operations for the ChainBased calculation engine to increase formula calculation performance. Use the CalculationOptions.ThreadCount property to specify the number of calculation threads the Spreadsheet should use.
To disable multi-threaded calculations, set the CalculationOptions.EnableMultiThreading option to false.