Auto-Recalculation and Updates
In This Topic
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.
The Calculation enumeration controls how the spreadsheet engine calculates formulas in the worksheet. The value of this enumeration can be set to one of the three modes: Manual, Automatic, or Semiautomatic.
When the Calculation Enum is set to Automatic, the following rules apply:
- If the formula is not part of the changed cell range, relative cell references will be updated. For example, if cell C3 contains a formula that references A1 (e.g., =$A$1), and a row is inserted at the top of the sheet, the reference to A1 will automatically update to A2 because the row shift moves A1 to A2. If the worksheet did not automatically update the formula, it would refer to the wrong data.
- If the formula is not part of the changed cell range, relative cell references will be updated. For example, if a formula in C3 relative references C1 (=C1), and a row is inserted above row 3, the reference to C1 will update automatically to refer to C2, the cell that is now three rows above C3.
Note: When automatic formula updates are not required, the Calculation Enum can be set to Manual mode. In this case, even if dependent cells change, formulas will not be automatically updated.
The following GIFs illustrate the Automatic and Manual calculation modes in the worksheet, showcasing the results when the values of dependent cells are updated.
Automatic Mode:
Manual Mode:
Refer to the following example code to set the automatic mode to perform formula calculations.
