ICustomCalculationService Interface

Allows you to manage the worksheet calculations process.

Namespace: DevExpress.XtraSpreadsheet.Services

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public interface ICustomCalculationService

VB.NET

Public Interface ICustomCalculationService

Remarks

The ICustomCalculationService enables you to do the following.

• Determine when the calculation starts and finishes, and cancel the calculation.

• To not mark “CalculateAlways” cells as needing recalculation. “CalculateAlways” cell is a cell for which any of the following statements is true:

  • the cell contains a volatile function (IFunction.Volatile)
  • the cell references another “CalculateAlways” cell
  • the cell contains a circular reference
• 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.

Note

The service does not function if the DocumentOptions.CalculationEngineType is set to CalculationEngineType.Recursive.

To create a custom calculation service, implement the ICustomCalculationService interface in your own class and add it as a service to the SpreadsheetControl using the SpreasdheetControl.AddService method.

Example

This example illustrates the use of the service which implements the ICustomCalculationService interface and allows to manage the process of worksheet calculations.

The application creates log entries when worksheet calculation starts and finishes, when cell calculation begins and ends, when a cell with the circular reference starts and finishes its calculation.

CustomCalculationService.vb

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Formulas
Imports DevExpress.XtraTreeList.Nodes
Imports System
Imports System.Collections.Generic
    Public Class TestCustomCalculationService
        Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService

        Private loggingControl As DevExpress.XtraTreeList.TreeList
        Private rootListNode As TreeListNode = Nothing
        Public CircularReferencedCells As New HashSet(Of CellKey)()

        Public Sub New(ByVal controlForLogging As Object)
            loggingControl = TryCast(controlForLogging, DevExpress.XtraTreeList.TreeList)
        End Sub

        Public Function OnBeginCalculation() As Boolean Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService.OnBeginCalculation
            ' Set the root node for displaying info on the current calculation.
            Dim rootNode As TreeListNode = Nothing
            rootListNode = loggingControl.AppendNode(New Object() {String.Format("Calculation starts at {0}", Date.Now)}, rootNode)
            ' To highlight cells with circular references, this example uses a hash set of such cells. 
            ' Clear it when a new calculation starts.
            CircularReferencedCells.Clear()
            ' True to perform a calculation. Return False to cancel it.
            Return True
        End Function
        Public Sub OnBeginCellCalculation(ByVal args As CellCalculationArgs) Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService.OnBeginCellCalculation
            ' Add a record about the cell being calculated.
            CreateLogEntry("Cell calculation begins", New String() {String.Format("CellKey: ({0})", args.CellKey)})
        End Sub
        Public Function OnBeginCircularReferencesCalculation() As Boolean Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService.OnBeginCircularReferencesCalculation
            ' Indicate that a circular reference calculation starts. 
            ' If the SpreadsheetControl.Document.DocumentSettings.Calculation.Iterative property is false (default),
            ' this method is not called.
            ' If a circular reference calculation starts, the OnBeginCellCalculation method is called again.
            CreateLogEntry("Circular Reference calculation begins", New String() {})
            ' True to perform a calculation. Return False to cancel it.
            Return True
        End Function
        Public Sub OnEndCalculation() Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService.OnEndCalculation
            ' Add a record that the calculation has finished.
            CreateLogEntry(String.Format("Calculation finishes at {0}", Date.Now), New String() {})
            rootListNode = Nothing
        End Sub
        Public Sub OnEndCellCalculation(ByVal cellKey As CellKey, ByVal startValue As CellValue, ByVal endValue As CellValue) Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService.OnEndCellCalculation
            ' Display cell information, a cell value before calculation and the calculated cell value.
            Dim info As String = String.Format("CellKey: ({0}) Before: {1}, After: {2}", cellKey, startValue, endValue)
            CreateLogEntry("Cell calculation ends", New String() {info})
        End Sub
        Public Sub OnEndCircularReferencesCalculation(ByVal cellKeys As IList(Of CellKey)) Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService.OnEndCircularReferencesCalculation
            ' Store cell keys of cells with circular references for further use.
            CircularReferencedCells = New HashSet(Of DevExpress.Spreadsheet.CellKey)(cellKeys)
            Dim sKeys(cellKeys.Count - 1) As String
            Dim i As Integer = 0
            For Each key As CellKey In cellKeys
                sKeys(i) = key.ToString()
                i += 1
            Next key
            ' Display the information on cells with circular references.
            CreateLogEntry("Circular Reference calculation ends", sKeys)
        End Sub
        Public Function ShouldMarkupCalculateAlwaysCells() As Boolean Implements DevExpress.XtraSpreadsheet.Services.ICustomCalculationService.ShouldMarkupCalculateAlwaysCells
            ' Mark as needing calculation the "calculate always" cells, 
            ' such as cells containing volatile function or referencing another "calculate always" cell.
            Return True
        End Function

        Private Sub CreateLogEntry(ByVal actionName As String, ByVal info() As String)
            Dim firstLevelNode As TreeListNode = loggingControl.AppendNode(New Object() { actionName }, rootListNode)
            If info.Length <> 0 Then
                For i As Integer = 0 To info.Length - 1
                    Dim secondLevelNode As TreeListNode = loggingControl.AppendNode(New Object() { info(i) }, firstLevelNode)
                Next i
            End If
            firstLevelNode.ExpandAll()
            loggingControl.MakeNodeVisible(firstLevelNode)
        End Sub
    End Class

CustomCalculationService.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Formulas;
using DevExpress.XtraTreeList.Nodes;
using System;
using System.Collections.Generic;
    public class TestCustomCalculationService : DevExpress.XtraSpreadsheet.Services.ICustomCalculationService {
        private DevExpress.XtraTreeList.TreeList loggingControl;
        private TreeListNode rootListNode = null;
        public HashSet<CellKey> CircularReferencedCells = new HashSet<CellKey>();

        public TestCustomCalculationService(object controlForLogging)
        {
            loggingControl = controlForLogging as DevExpress.XtraTreeList.TreeList;
        }

        public bool OnBeginCalculation() {
            // Set the root node for displaying info on the current calculation.
            rootListNode = loggingControl.AppendNode(new object[] {String.Format("Calculation starts at {0}", DateTime.Now)}, null);
            // To highlight cells with circular references, this example uses a hash set of such cells. 
            // Clear it when a new calculation starts.
            CircularReferencedCells.Clear();
            // True to perform a calculation. Return False to cancel it.
            return true;
        }
        public void OnBeginCellCalculation(CellCalculationArgs args) {
            // Add a record about the cell being calculated.
            CreateLogEntry("Cell calculation begins", new string[] {String.Format("CellKey: ({0})", args.CellKey)} );
        }
        public bool OnBeginCircularReferencesCalculation() {
            // Indicate that a circular reference calculation starts. 
            // If the SpreadsheetControl.Document.DocumentSettings.Calculation.Iterative property is false (default),
            // this method is not called.
            // If a circular reference calculation starts, the OnBeginCellCalculation method is called again.
            CreateLogEntry("Circular Reference calculation begins", new string[] {});
            // True to perform a calculation. Return False to cancel it.
            return true;
        }
        public void OnEndCalculation() {
            // Add a record that the calculation has finished.
            CreateLogEntry(String.Format("Calculation finishes at {0}", DateTime.Now), new string[] {});
            rootListNode = null;
        }
        public void OnEndCellCalculation(CellKey cellKey, CellValue startValue, CellValue endValue) {
            // Display cell information, a cell value before calculation and the calculated cell value.
            string info = String.Format("CellKey: ({0}) Before: {1}, After: {2}", cellKey, startValue, endValue);
            CreateLogEntry("Cell calculation ends", new string[] {info} );
        }
        public void OnEndCircularReferencesCalculation(IList<CellKey> cellKeys) {
            // Store cell keys of cells with circular references for further use.
            CircularReferencedCells = new HashSet<DevExpress.Spreadsheet.CellKey>(cellKeys); 
            string[] sKeys = new string[cellKeys.Count];
            int i = 0;
            foreach (CellKey key in cellKeys) {
                sKeys[i] = key.ToString();
                i++;
            } 
            // Display the information on cells with circular references.
            CreateLogEntry("Circular Reference calculation ends", sKeys);
        }
        public bool ShouldMarkupCalculateAlwaysCells() {
            // Mark as needing calculation the "calculate always" cells, 
            // such as cells containing volatile function or referencing another "calculate always" cell.
            return true;
        }

        private void CreateLogEntry(string actionName, string[] info)
        {
            TreeListNode firstLevelNode = loggingControl.AppendNode(new object[] { actionName }, rootListNode);
            if (info.Length != 0)
            {
                for (int i = 0; i < info.Length; i++)
                {
                    TreeListNode secondLevelNode = loggingControl.AppendNode(new object[] { info[i] }, firstLevelNode);
                }
            }
            firstLevelNode.ExpandAll();
            loggingControl.MakeNodeVisible(firstLevelNode);
        }
    }