Suspend Calculation Service
Users can suspend the calculation service for a better experience when calling API in batches.
For example:
Calculation Options
SpreadJS supports a CalculationMode which can be set to manual or automatic.
Automatic Calculation (default)
Calculate all dirty cells every time the relevant data is changed, like when something has been entered or copy-pasted in a cell.
Manual Calculation
Only evaluate a formula when that formula is entered or updated, and keep its dependencies dirty. When cutting/copying-pasting, this mode will set the formula and the cell value, but won’t recalculate any formulas.
Calculate API
You can force calculation by calling the calculate API.
The Calculate function will rebuild, mark dirty, broadcast dirty and calculate the dirty cells.
First rebuild and mark cells in the formula reference as dirty using the calculation type. The list of `GC.Spread.Sheets.CalculationType` is as follows:
all - The default calculation type that mark cells in the range as dirty for calculation.
rebuild - Rebuild all the formula models in the range and then mark them as dirty for calculation.
minimal - Keep the current cell calculation dirty status.
regular - Mark the volatile and circular reference cells as dirty for calculation.
Broadcasting dirty cells will recursively set the dependents of dirty cells to dirty in the workbook.
The last step is calculate. In automatic mode, all the dirty cells will be calculated.
In manual mode, only the cells in the formula reference are calculated, other cells will keep their dirty state.
For example:
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
initSpread(spread);
};
function initSpread(spread) {
var sheet = spread.getActiveSheet();
spread.setSheetCount(2);
var sheet2 = spread.sheets[1];
spread.suspendPaint();
spread.suspendCalcService();
spread.options.iterativeCalculationMaximumIterations = 1;
spread.options.iterativeCalculationMaximumChange = 10;
sheet.setValue(0,0,"Tick:");
sheet.setFormula(0,1,"=REFRESH(B1+1,2,80)");
sheet.setValue(2,0,"Value:");
sheet.setFormula(2,1,"=IFERROR(CHOOSE(MOD(B1,16),5,-5,20,-2),0)+RAND()");
sheet.setValue(0,4,"Values:");
sheet.getRange(1,4,60,1).formula("=IF(MOD($B$1,60)=(ROW()-2),$B$3,E2)", true);
sheet2.setValue(0,6,"Values:");
sheet2.getRange(1,6,60,1).formula("=OFFSET(Sheet1!$E$2,MOD(Sheet1!$B$1-1+ROW(),60),0)", true);
sheet.setValue(1,6,'Value Sparkline:');
sheet.addSpan(2,6,6,6);
sheet.setFormula(2,6,'=LINESPARKLINE(E2:E61,0,,,)');
sheet.setValue(10,6,'Sheet2 reorder values:');
sheet.addSpan(11,6,6,6);
sheet.setFormula(11,6,'=LINESPARKLINE(Sheet2!G2:G61,0,,,)');
spread.resumeCalcService();
spread.resumePaint();
bindEvent(spread);
}
function bindEvent (spread) {
_getElementById("enableCalcService").addEventListener('change', function () {
if (this.checked) {
spread.resumeCalcService();
} else {
spread.suspendCalcService();
}
});
_getElementById("calculationMode").addEventListener('change', function () {
console.log(JSON.stringify(this.value));
if (this.value === "0") {
spread.options.calculationMode = GC.Spread.Sheets.CalculationMode.auto;
spread.calculate(false);
} else {
spread.options.calculationMode = GC.Spread.Sheets.CalculationMode.manual;
}
});
_getElementById("calculateSpread").addEventListener('click', function () {
let type = _getElementById("calculationType").value;
spread.calculate(GC.Spread.Sheets.CalculationType[type]);
});
_getElementById("calculateSheet").addEventListener('click', function () {
let type = _getElementById("calculationType").value;
spread.calculate(GC.Spread.Sheets.CalculationType[type], spread.getActiveSheet().name());
});
_getElementById("calculateRange").addEventListener('click', function () {
let type = _getElementById("calculationType").value;
let sheet = spread.getActiveSheet();
let range = sheet.getSelections()[0];
spread.calculate(GC.Spread.Sheets.CalculationType[type], sheet.name()+"!"+GC.Spread.Sheets.CalcEngine.rangeToFormula(range));
});
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="option-row">
<input style="width: 20px;float: left;" type="checkbox" id="enableCalcService" checked="checked"/>
<label for="enableCalcService">enable Calculation Service</label>
</div>
<div class="option-row">
<p>Change the Calculation Mode and input formulas to see the calculation behavior. </p>
<label for="calculationMode">Calculation Mode</label>
<select id="calculationMode">
<option value="0">Automatic</option>
<option value="1">Manual</option>
</select>
</div>
<div class="option-row">
<p>Check the different calculate scopes with different calculation types in different calculation modes. </p>
<label for="calculationType">Calculation Type</label>
<select id="calculationType">
<option value="all">All</option>
<option value="rebuild">Rebuild</option>
<option value="minimal">Minimal</option>
<option value="regular">Regular</option>
</select>
<button id="calculateSpread">Calculate Spread</button>
<br />
<button id="calculateSheet">Calculate Sheet</button>
<br />
<button id="calculateRange">Calculate Selection</button>
</div>
</div>
</div></body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
input {
margin-bottom: 5px;
padding: 2px 4px;
width: 100%;
box-sizing: border-box;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
