Users can enable/disable iterative calculation in the workbook options.
When iterative calculation is disabled, all the circular referenced cells will have 0 for the value, and the other cells referencing them will be 0.
When iterative calculation is enabled, all the circular references will calculate iteratively until all the value changes are smaller than the iterativeCalculationMaximumChange or the iteratation count is iterativeCalculationMaximumIterations.
API is as follows:
iterativeCalculation: Enable or Disable the Iterative Calculation
iterativeCalculationMaximumIterations: The Maximum Iterations when Iterative Calculation, default value is 1000, value range is 1~32767
iterativeCalculationMaximumChange: The Maximum Change when Iterative Calculation, default 0.01, value range is 0~MaxDouble(1.79769313486232e308)
The isCircularReference property is added for the Events.UserFormulaEntered, it will be true if user entered a circular reference. For example:
You can get all the circular references in the workbook by function getCircularReference. For example:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<label>Change the <b>Maximum Iterations</b> and <b>Maximum Change</b> options below then press <b>Recalculate</b> to see how this affects the calculations in cell F7.</label>
<div class="option-row">
<input style="width: 20px;float: left;" type="checkbox" id="IterativeCalculation" checked="checked" @change="setIterativeCalculation($event)"/>
<label for="IterativeCalculation">Iterative Calculation</label>
</div>
<div class="option-row">
<label for="MaximumIterations">Maximum Iterations:</label>
<input type="number" id="MaximumIterations" value="1000" @change="setMaximumIterations($event)" >
</div>
<div class="option-row">
<label for="MaximumChange">Maximum Change:</label>
<input type="number" id="MaximumChange" value="0.01" @change="setMaximumChange($event)" >
</div>
<div class="option-row">
<button id="RecalcAll" @click="recalcAll($event)" >Recalculate</button>
</div>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import { ref } from 'vue';
const spreadRef = ref(null);
function initSpread (spread) {
spreadRef.value = spread;
var sheet = spread.getActiveSheet();
spread.suspendPaint();
spread.suspendCalcService();
sheet.setColumnWidth(0, 120);
sheet.setColumnWidth(1, 100);
sheet.setColumnWidth(2, 120);
sheet.setColumnWidth(4, 120);
sheet.setColumnWidth(5, 130);
sheet.getCell(1, 1).foreColor("blue");
sheet.getCell(5, 1).foreColor("blue");
sheet.getCell(9, 1).foreColor("blue").formatter("0.0%");
sheet.getRange(1, 1, 7, 1).formatter("0.0");
sheet.setFormula(6, 5, '=F7+1');
sheet.setValue(0, 0, "Details");
sheet.setValue(1, 0, "Cash Revenue");
sheet.setValue(2, 0, "Interest Expense");
sheet.setValue(3, 0, "Cash Profit");
sheet.setValue(5, 0, "Beginning Debt");
sheet.setValue(6, 0, "Ending Debt");
sheet.setValue(7, 0, "Average Debt");
sheet.setValue(9, 0, "Interest");
sheet.setValue(0, 1, "Amount");
sheet.setValue(1, 1, 100);
sheet.setFormula(2, 1, '=B10*B8');
sheet.setFormula(3, 1, '=B2-B3');
sheet.setValue(5, 1, 150);
sheet.setFormula(6, 1, '=B6-B4');
sheet.setFormula(7, 1, '=AVERAGE(B6:B7)');
sheet.setValue(9, 1, 0.05);
sheet.setValue(0, 2, "Formula")
sheet.setFormula(2, 2, '=FORMULATEXT(B3)');
sheet.setFormula(3, 2, '=FORMULATEXT(B4)');
sheet.setFormula(6, 2, '=FORMULATEXT(B7)');
sheet.setFormula(7, 2, '=FORMULATEXT(B8)');
sheet.getRange(0, 0, 1, 3).backColor("#f2f2f2").foreColor("black");
sheet.getRange(6, 5, 1, 1).backColor("#009e00").foreColor("white");
sheet.getRange(1, 4, 1, 2).backColor("#f2f2f2").foreColor("black");
sheet.getRange(1, 0, 9, 3).setBorder(new GC.Spread.Sheets.LineBorder("#f2f2f2", GC.Spread.Sheets.LineStyle.thin), {all: true});
sheet.getRange(2, 4, 3, 2).setBorder(new GC.Spread.Sheets.LineBorder("#f2f2f2", GC.Spread.Sheets.LineStyle.thin), {all: true});
sheet.setValue(1, 4, "Use the Leibniz formula to approximate π")
sheet.setValue(2, 4, "n: 1→∞")
sheet.setValue(3, 4, {"richText":[{"text":"Pn: 4*(-1)"},{"style":{"vertAlign":1},"text":"n+1"},{"text":"/(2n-1)"}],"text":"Pn=4*(-1)n+1/(2n-1)"});
sheet.setValue(4, 4, "π: P1+P2+P3+...+Pn")
sheet.setFormula(4, 5, '=IFERROR(F4,0)+F5');
sheet.setFormula(3, 5, '=IF(F3<1,0,4/(2*F3-1)*POWER(-1,F3+1))');
sheet.setFormula(2, 5, '=F3+1'); // set the n in the last to make sure that added from n=1
spread.resumeCalcService();
spread.resumePaint();
}
function setIterativeCalculation(e) {
spreadRef.value.options.iterativeCalculation = e.target.checked;
}
function setMaximumIterations(e) {
spreadRef.value.options.iterativeCalculationMaximumIterations = e.target.value;
}
function setMaximumChange(e) {
spreadRef.value.options.iterativeCalculationMaximumChange = e.target.value;
}
function recalcAll(e) {
spreadRef.value.getActiveSheet().recalcAll(true);
}
</script>
<style scoped>
#app {
height: 100%;
}
.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;
}
input {
display:block;
width: 100%;
margin: 8px 0;
box-sizing: border-box;
}
input[type=checkbox] {
display: inline-block;
width: initial;
margin: 0;
vertical-align: middle;
}
label, input {
padding: 4px 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html lang="en" style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
