Calculation Mode

SpreadJS automatically updates formulas when the dependent values (cells, values, or names referenced in a formula) change. However, you can switch automatic calculations to manual if you want to control when the formulas are recalculated and not as soon as changes are made to any referenced cells. The CalculationMode enumeration in the SpreadJS API allows you to set calculation options as auto or manual.

When the CalculationMode is set to auto, which is the default mode, SpreadJS automatically calculates all dirty cells every time the referenced cells are changed. For example, during copy-paste or cell input. Note that SpreadJS considers the cells as dirty when the cells are changed and need recalculation consequently.

On the other hand, when the CalculationMode is set to manual, SpreadJS will only calculate and update the formulas when you explicitly request it. This can be useful in situations where you have a large worksheet with many complex formulas, and you want to avoid frequent recalculations to improve performance and responsiveness. For example, SpreadJS resets the formula and the cell value during the cut or copy-paste of a value, but it doesn't recalculate any formulas.

Note: SpreadJS supports the CalculationMode option on exporting to Excel.

The following code sample shows how to set the manual calculation option when initializing the workbook.

// Switch to manual mode when initializing the workbook.
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {calculationMode: GC.Spread.Sheets.CalculationMode.manual});

Or

spread.options.calculationMode = GC.Spread.Sheets.CalculationMode.manual;

Once you switch to manual calculation mode, you need to manually update the formulas in the worksheet using the calculate() method which accepts the CalculationType enum value as a parameter.

The CalculationType enumeration specifies the calculation type. The available members of this enumeration are mentioned below.

Look at the table below to understand SpreadJS’s behavior when utilizing automatic and manual calculation modes with various formulas and their dependencies.

Note: For recalculating formulas in cells, the existing sheet.recalcAll() method has been deprecated from the SpreadJS v16.2 release. Instead of this, you can use spread.calculate() for higher versions.

The following code samples show various calculations using different calculation modes and types in SpreadJS.

spread.sheets[0].setFormula(0,0,"RAND()");
spread.sheets[0].setFormula(1,0,"=Sheet2!A1");
spread.sheets[0].setFormula(2,0,"=1+2"); 
spread.sheets[1].setFormula(0,0,"RAND()"); 
spread.sheets[1].setFormula(1,0,"=Sheet1!A1");

// All the cell is recalculated. 
spread.calculate(); spread.calculate(GC.Spread.Sheets.CalculationType.regular); 

// Sheet1!A1 Sheet2!A2 are evaluated to new number, and Sheet1!A2 Sheet1!A3 are evaluated. 
spread.calculate(GC.Spread.Sheets.CalculationType.all, "Sheet1"); 

// Sheet1!A1 Sheet2!A2 are evaluated to new number. 
spread.calculate(GC.Spread.Sheets.CalculationType.regular, "Sheet1!A1"); 

// No cells are evaluated.
spread.calculate(GC.Spread.Sheets.CalculationType.regular, "Sheet1!A2"); 

// Sheet1!A1 is evaluated to new number, Sheet1!A2 Sheet1!A3 are evaluated but don't changed, Sheet2!A2 keeps dirty in manual mode. 
spread.calculate(GC.Spread.Sheets.CalculationType.all, "Sheet1"); 

// Sheet1!A1 is evaluated to new number, Sheet2!A2 keeps dirty in manual mode. 
spread.calculate(GC.Spread.Sheets.CalculationType.regular, "Sheet1"); 

// Sheet2!A2 is evaluated because it is dirty. 
spread.calculate(GC.Spread.Sheets.CalculationType.minimal);

Using Designer

You can set the required calculation options using SpreadJS Designer by selecting the 'Calculation Options' button available inside the FORMULAS > Calculations tab group.

In the case of manual calculation mode, you need to select which section of the workbook you would like to be recalculated.

• Click the Calculate Now button to update changes across all open worksheets.

• Click the Calculate Sheet button if you only want the modifications to be reflected in the same worksheet.