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:
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const useState = React.useState;
export function AppFunc() {
const [spread, setSpread] = useState(null);
const initSpread = (spread) => {
setSpread(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();
}
const setIterativeCalculation = (e) => {
spread.options.iterativeCalculation = e.target.checked;
}
const setMaximumIterations = (e) => {
spread.options.iterativeCalculationMaximumIterations = e.target.value;
}
const setMaximumChange = (e) => {
spread.options.iterativeCalculationMaximumChange = e.target.value;
}
const recalcAll = (e) => {
spread.getActiveSheet().recalcAll(true);
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel setIterativeCalculation={(e) => { setIterativeCalculation(e) }}
setMaximumIterations={(e) => { setMaximumIterations(e) }}
setMaximumChange={(e) => { setMaximumChange(e) }}
recalcAll={(e) => { recalcAll(e) }} />
</div>;
}
function Panel(props) {
return (
<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" defaultChecked="checked" onChange={(e) => { props.setIterativeCalculation(e) }} />
<label htmlFor="IterativeCalculation">Iterative Calculation</label>
</div>
<div class="option-row">
<label for="MaximumIterations">Maximum Iterations:</label>
<input type="number" id="MaximumIterations" defaultValue="1000" onChange={(e) => { props.setMaximumIterations(e) }} />
</div>
<div class="option-row">
<label for="MaximumChange">Maximum Change:</label>
<input type="number" id="MaximumChange" defaultValue="0.01" onChange={(e) => { props.setMaximumChange(e) }} />
</div>
<div class="option-row">
<button id="RecalcAll" onClick={(e) => { props.recalcAll(e) }} >Recalculate</button>
</div>
</div>
);
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel setIterativeCalculation={(e)=>{this.setIterativeCalculation(e)}}
setMaximumIterations={(e)=>{this.setMaximumIterations(e)}}
setMaximumChange={(e)=>{this.setMaximumChange(e)}}
recalcAll={(e)=>{this.recalcAll(e)}}/>
</div>;
}
initSpread(spread) {
this.spread = 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();
}
setIterativeCalculation (e) {
this.spread.options.iterativeCalculation = e.target.checked;
}
setMaximumIterations (e) {
this.spread.options.iterativeCalculationMaximumIterations = e.target.value;
}
setMaximumChange (e) {
this.spread.options.iterativeCalculationMaximumChange = e.target.value;
}
recalcAll (e) {
this.spread.getActiveSheet().recalcAll(true);
}
}
class Panel extends Component{
constructor(props){
super(props);
}
render(){
return(
<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" defaultChecked="checked" onChange={(e)=>{this.props.setIterativeCalculation(e)}}/>
<label htmlFor="IterativeCalculation">Iterative Calculation</label>
</div>
<div class="option-row">
<label for="MaximumIterations">Maximum Iterations:</label>
<input type="number" id="MaximumIterations" defaultValue="1000" onChange={(e)=>{this.props.setMaximumIterations(e)}}/>
</div>
<div class="option-row">
<label for="MaximumChange">Maximum Change:</label>
<input type="number" id="MaximumChange" defaultValue="0.01" onChange={(e)=>{this.props.setMaximumChange(e)}}/>
</div>
<div class="option-row">
<button id="RecalcAll" onClick={(e)=>{this.props.recalcAll(e)}} >Recalculate</button>
</div>
</div>
)
}
}
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></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;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
