[Expand]General Information
[Collapse]WinForms Controls
  .NET Core Support
  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]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gantt Control
  [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]Formulas
    [Expand]Functions
     Operators
     Array Formulas
     Calculation Process
     Formula Engine
     Formula AutoComplete
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Tables
   [Expand]Charts and Graphics
    Printing
    Events
   [Expand]Mail Merge
    Protection
    Operation Restrictions
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Keyboard 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]Sunburst Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Calculation Process

This article provides information about the WinForms Spreadsheet's calculation process.

Expanded Calculation Engine Type

Use the DocumentOptions.CalculationEngineType property to specify the computational model the Spreadsheet control should use when it calculates formulas in a workbook.

Chain-Based (Default)

The Spreadsheet control 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 control 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 control 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 control 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.

Expanded 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 control does not recalculate such formulas and displays a warning message.

Expanded 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 control recalculates a workbook, it also updates volatile function values.

If your custom function is volatile, implement the IFunction.Volatile property to return true.

Expanded Calculation Options

Use the DocumentSettings.Calculation property to access various calculation options the table below lists.

Property

Description

CalculationOptions.RecalculateBeforeSaving

Specifies whether to recalculate formulas when a workbook is saved.

CalculationOptions.PrecisionAsDisplayed

Specifies whether the Spreadsheet control should use numbers as they are displayed when it calculates formulas.

CalculationOptions.Mode

Specifies the calculation mode.

CalculationOptions.Iterative

Specifies whether to enable iterative calculations for formulas with circular references.

Related options:

CalculationOptions.Use1904DateSystem

Specifies whether to use the 1904 date system to convert dates to serial values.

CalculationOptions.FullCalculationOnLoad

Specifies whether to recalculate all formulas when a workbook is opened.

CalculationOptions.EnableMultiThreading

Specifies whether to enable multi-threaded calculations.

Related option:

Expanded Calculation Mode

Use the CalculationOptions.Mode property to specify the 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 control recalculates only cells that are marked for calculation.
CalculationMode.AutomaticExceptTables Disables automatic calculation for data tables. The Spreadsheet control does not support this mode and treats is as CalculationMode.Automatic.

Use the OpenXmlDocumentImporterOptions.OverrideCalculationMode and XlsDocumentImporterOptions.OverrideCalculationMode properties to change the calculation mode for a loaded XLSX or XLS document.

Expanded Force Calculation

To recalculate formulas in

Do this

A document

Use one of the following methods:

A worksheet

Use the IWorkbook.Calculate method overload with the Worksheet parameter, or call the Worksheet.Calculate method.

A cell range

Use the IWorkbook.Calculate method overload with the CellRange parameter, or call the CellRange.Calculate method.

Shortcuts

You can also use the following key combinations to recalculate spreadsheet formulas.

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.

Expanded Custom Calculation Service

Implement the ICustomCalculationService interface to adjust the calculation process when the CalculationEngineType.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.

Tip

You can find an example on how to implement ICustomCalculationService in the DevExpress-Examples/how-to-implement-a-service-to-manage-the-process-of-worksheet-cell-calculation-t270403 repository on GitHub.

If you implement ICustomCalculationService, the Spreadsheet control uses only one thread to calculate formulas.

Expanded Multi-Threaded Calculations

The Spreadsheet control supports multi-threaded operations for the CalculationEngineType.ChainBased calculation engine to increase formula calculation performance. Use the CalculationOptions.ThreadCount property to specify the number of calculation threads the Spreadsheet control should use.

To disable multi-threaded calculations, set the CalculationOptions.EnableMultiThreading option to false.

Is this topic helpful?​​​​​​​