CalculationOptions.CalculationId Property

Allows specifying the version of the calculation engine used to calculate values in the workbook.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

C#

int CalculationId { get; set; }

VB.NET

Property CalculationId As Integer

Property Value

Type	Description
Int32	An integer that identifies the version of the calculation engine.

Property Paths

You can access this nested property as listed below:

Object Type	Path to CalculationId
DocumentSettings	.Calculation.CalculationId

Remarks

The OpenXML specification defines the CalculationId property. This property specifies the version of the calculation engine used to calculate values in the workbook. Microsoft Excel initializes this property according to the application version, for instance, the CalculationId property value for Microsoft Excel 2010 is 145621. The SpreadsheetControl resets this property to 0 after calculating formulas in a document.

The OpenXML specification states that “when you open a workbook created in the current version, the application recalculates only the formulas that depend on cells that have changed. When you open a workbook that was created in an earlier version of the application, all the formulas in the workbook— those that depend on cells that have changed and those that do not— are recalculated.”

Example

C#

// To calculate all formulas in the document created in Excel 2010 and earlier Excel versions, use the following code:

const int Excel2010CalcId = 145621;
spreadsheetControl1.DocumentLoaded += spreadsheetControl1_DocumentLoaded;

private void spreadsheetControl1_DocumentLoaded(object sender, EventArgs e) {
if (spreadsheetControl1.Document.DocumentSettings.Calculation.CalculationId < Excel2010CalcId) {
spreadsheetControl1.Document.CalculateFull();
}

// To not calculate formulas in .XLS/XLSX document when the document is opened in Excel 2010, save the document using the following code:

const int Excel2010CalcId = 145621;
spreadsheetControl1.BeforeExport += spreadsheetControl1_BeforeExport;

private void spreadsheetControl1_BeforeExport(object sender, SpreadsheetBeforeExportEventArgs e) {
    spreadsheetControl1.Document.DocumentSettings.Calculation.CalculationId = Excel2010CalcId;
} 

VB.NET

' To calculate all formulas in the document created in Excel 2010 and earlier Excel versions, use the following code:

Private Const Excel2010CalcId As Integer = 145621
Private spreadsheetControl1.DocumentLoaded += AddressOf spreadsheetControl1_DocumentLoaded

Private Sub spreadsheetControl1_DocumentLoaded(ByVal sender As Object, ByVal e As EventArgs)
If spreadsheetControl1.Document.DocumentSettings.Calculation.CalculationId < Excel2010CalcId Then
spreadsheetControl1.Document.CalculateFull()
End If

' To not calculate formulas in .XLS/XLSX document when the document is opened in Excel 2010, save the document using the following code:

Const Excel2010CalcId As Integer = 145621
spreadsheetControl1.BeforeExport += spreadsheetControl1_BeforeExport

private void spreadsheetControl1_BeforeExport(Object sender, SpreadsheetBeforeExportEventArgs e)
If True Then
    spreadsheetControl1.Document.DocumentSettings.Calculation.CalculationId = Excel2010CalcId
End If