Spread Windows Forms Product Documentation

Recalculating and Updating Formulas Automatically

Auto Calculation

By default, Spread allows you to recalculate the formulas in the cells when the values of the dependent cells change. The value in dependent cells changes in following scenarios:

Adding, inserting or removing any row/column
Moving/swapping the block of cells
On saving/loading a spreadsheet having formulas in cells.

The AutoCalculation property is enabled automatically whenever there is a change of value in the dependent cells and auto calculation of formulas in cells occurs. However, you can disable this property to prevent automatic calculations in cells.

Note: This property does not control the 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 the 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 the SuspendCalculation property is false. This property works for suspending calculations while saving/loading a spreadsheet.

Note: Once the calculations are suspended, use the RecalculateAll() method to recalculate the formulas in cells.

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

private void Form1_Load(object sender, EventArgs e)
 {
    IWorksheet sheet = fpSpread1.AsWorkbook().ActiveSheet;
    fpSpread1.Sheets[0].AutoCalculation = false;
    sheet.Cells["B2"].Value = 1;
    sheet.Cells["C2"].Formula = "B2";
 }

private void saveBtn_Click(object sender, EventArgs e)
 {
     fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalculateBeforeSave = false;
     fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.SuspendCalculation = true;
     fpSpread1.Sheets[0].Cells["B2"].Value = 50;
     
     // For recalculating again
     //fpSpread1.ActiveSheet.RecalculateAll();
     fpSpread1.SaveExcel("D:\\SampleCalculation.xlsx", ExcelSaveFlags.UseOOXMLFormat);
     
 }

private void loadBtn_Click(object sender, EventArgs e)
 {
     fpSpread1.OpenExcel("D:\\SampleCalculation.xlsx");
 }

  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
      Dim sheet As IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
      FpSpread1.Sheets(0).AutoCalculation = False
      sheet.Cells("B2").Value = 1
      sheet.Cells("C2").Formula = "B2"
  End Sub

  Private Sub saveBtn_Click(sender As Object, e As EventArgs) Handles saveBtn.Click
      FpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalculateBeforeSave = False
      FpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.SuspendCalculation = True
      FpSpread1.Sheets(0).Cells("B2").Value = 50
      
     'For recalculating again
     'FpSpread1.ActiveSheet.RecalculateAll()
      FpSpread1.SaveExcel("D:\SampleCalculation.xlsx", ExcelSaveFlags.UseOOXMLFormat)   
  End Sub

  Private Sub loadBtn_Click(sender As Object, e As EventArgs) Handles loadBtn.Click
     FpSpread1.OpenExcel("D:\SampleCalculation.xlsx")
  End Sub

Recalculating and Updating Formulas Automatically

Auto Calculation

Suspend Calculation

See Also