Auto-Recalculation and Updates

Auto Calculation

Spread for WPF supports automatic recalculation and auto-update of formulas in worksheets.

Formulas are automatically recalculated and updated under the following conditions:

When the values of dependent cells are changed.
When rows or columns are added or deleted.
When worksheet operations that change the position of data (e.g., moving data, switching cell ranges) are executed.
When the worksheet having formulas in cells is saved or loaded.

The Calculation enumeration having values Manual, Automatic, or Semiautomatic controls how the spreadsheet engine calculates formulas in the worksheet.

When automatic formula updates are not required, set the Calculation enum to Manual. In this case, any change in dependent cells doesn’t automatically update the formula values.

Note: The Calculation enum does not control automatic calculation of formulas while saving/loading a spreadsheet.

In case you wish to enable/disable the automatic calculation of formulas while saving/loading a spreadsheet even when the values in dependent cells change, you can use the CalculateBeforeSave property. The default value of CalculateBeforeSave property is true.

Suspend Calculation

Additionally, you can also suspend the calculation of formulas in cell by using the SuspendCalculation property, so that the cell containing formula is not updated whenever there is a change in value in dependent cells. The default value of SuspendCalculation property is false. This property works for suspending calculations while saving/loading a spreadsheet.

Following code is used to disable the automatic calculation of formulas in cells while saving/loading a file.

private void Window_Loaded(object sender, RoutedEventArgs e)
{
    IWorksheet sheet = spreadSheet1.Workbook.ActiveSheet;

    // Set the calculation mode to "Automatic".
    spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Calculation = GrapeCity.Spreadsheet.Calculation.Automatic;
    sheet.Cells["B2"].Value = 1;
    sheet.Cells["C2"].Formula = "B2";
}
private void savebtn_Click(object sender, RoutedEventArgs e)
{
    spreadSheet1.Workbook.WorkbookSet.CalculationEngine.CalculateBeforeSave = false;
    spreadSheet1.Workbook.WorkbookSet.CalculationEngine.SuspendCalculation = true;
    spreadSheet1.Workbook.ActiveSheet.Cells["B2"].Value = 200;

    //For enabling autocalculation
    //spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Calculation = GrapeCity.Spreadsheet.Calculation.Automatic;
    spreadSheet1.Workbook.SaveAs("D:\\SampleCalculation.xlsx", GrapeCity.Spreadsheet.IO.FileFormat.OpenXMLWorkbook);
}

private void loadbtn_Click_1(object sender, RoutedEventArgs e)
{
       spreadSheet1.OpenExcel("D:\\SampleCalculation.xlsx");
}

Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            Dim sheet As IWorksheet = spreadSheet1.Workbook.ActiveSheet
            
            'Set the calculation mode to "Automatic"
            spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Calculation = GrapeCity.Spreadsheet.Calculation.Automatic
            sheet.Cells("B2").Value = 1
            sheet.Cells("C2").Formula = "B2"
End Sub

Private Sub Savebtn_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
            spreadSheet1.Workbook.WorkbookSet.CalculationEngine.CalculateBeforeSave = False
            spreadSheet1.Workbook.WorkbookSet.CalculationEngine.SuspendCalculation = True
            spreadSheet1.Workbook.ActiveSheet.Cells("B2").Value = 200
            
            'For enabling autocalculation
            'spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Calculation = GrapeCity.Spreadsheet.Calculation.Automatic
            spreadSheet1.Workbook.SaveAs("D:\SampleCalculation.xlsx", GrapeCity.Spreadsheet.IO.FileFormat.OpenXMLWorkbook)
End Sub

Private Sub loadbtn_Click_1(ByVal sender As Object, ByVal e As RoutedEventArgs)
            spreadSheet1.OpenExcel("D:\SampleCalculation.xlsx")
End Sub