Create a class by inheriting GC.Spread.CalcEngine.Functions.AsyncFunction.
Create a display string with the defaultValue method.
Use the evaluateAsync method to return the result. Set the result with context.setAsyncResult.
Use AsyncFunctionEvaluateMode to represent the async function evaluate mode, There are three modes:
0 means the AsyncFunction should recalculate when the cell needs to recalculate. For example: B1='MyAsync1(A1) + MyAsync2(A2)'. When the value of A1 changes, the formula of B1 will recalculate, so both 'MyAsync1(A1)' and 'MyAsync2(A2)' will recalculate.
1 means the formula in the REFRESH function only evaluates once.
2 means the formula in the REFRESH function will recalculate based on an interval.
Set the refresh method
Can use any formula on the first argument of the REFRESH function.
The evaluateMode value should be one of the GC.Spread.CalcEngine.Functions.AsyncFunctionEvaluateMode enumeration values.
The interval can only take effect when the evaluateMode is 2.
Normally, use the REFRESH function as the outermost function.
When setting the AsyncFunction inside the REFRESH function, it uses the REFRESH function settings. The AsyncFunction settings do not take effect.
Use the addCustomFunction method to add the defined async function, then use it in a formula, such as by using the setFormula method
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<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 asyncSum = function () {
};
asyncSum.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("ASUM", 1, 255);
asyncSum.prototype.defaultValue = function () {
return "Loading...";
};
asyncSum.prototype.evaluateAsync = function (context) {
// use setTimeout to simulate server side evaluation
// in read world it maybe an ajax post to server for evaluation
var args = arguments;
setTimeout(function () {
var result = 0;
for (var i = 1; i < args.length; i++) {
result += args[i];
}
result *= 2;
context.setAsyncResult(result);
}, 2000);
};
var GetNumberFromServer = function () {
};
GetNumberFromServer.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("GETNUMBERFROMSERVER", 1, 2);
GetNumberFromServer.prototype.evaluate = function (context, arg1, arg2) {
setTimeout(function () {
var value = Math.random() + 1;
context.setAsyncResult(value);
}, 500);
};
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("GETNUMBERFROMSERVER", new GetNumberFromServer());
var GetTimeFromServer = function () {
};
GetTimeFromServer.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("GETTIMEFROMSERVER");
GetTimeFromServer.prototype.evaluate = function (context) {
setTimeout(function () {
var date = new Date();
context.setAsyncResult(date);
}, 500);
};
GetTimeFromServer.prototype.evaluateMode = function () {
return 2;
};
GetTimeFromServer.prototype.interval = function () {
return 1000;
};
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("GETTIMEFROMSERVER", new GetTimeFromServer());
var sheet = spread.sheets[0];
sheet.suspendPaint();
sheet.options.allowCellOverflow = true;
sheet.setArray(0, 0, [[5, 15]]);
sheet.addCustomFunction(new asyncSum());
sheet.setText(1, 1, 'ASUM(A1,B1)');
sheet.setText(2, 1, 'SUM(A1,B1)');
sheet.setFormula(1, 2, "ASUM(A1,B1)");
sheet.getCell(1, 2).foreColor("green");
sheet.setFormula(2, 2, "SUM(A1,B1)");
sheet.setValue(4, 0, "Edit the formula of C2 or referenced cell' value to see how async function works.");
sheet.setValue(8, 0, 'CHANGEVALUE');
sheet.setValue(8, 1, 'FORMULA');
sheet.setValue(8, 2, 'RESULT');
sheet.setValue(8, 3, 'COMMENTS');
sheet.setValue(9, 3, 'On A10 changed');
sheet.setValue(10, 3, 'On A10 changed');
sheet.setValue(11, 3, 'Evaluate once');
sheet.setValue(12, 3, 'Every 2 seconds');
sheet.setValue(9, 0, 1);
sheet.setValue(9, 1, '=GetNumberFromServer(A10)');
sheet.setValue(10, 1, '=Refresh(GetNumberFromServer(A10), 0)');
sheet.setValue(11, 1, '=Refresh(GetNumberFromServer(A10), 1)');
sheet.setValue(12, 1, '=Refresh(GetNumberFromServer(A10), 2, 2000)');
sheet.setFormula(9, 2, '=GetNumberFromServer(A10)');
sheet.setFormula(10, 2, '=Refresh(GetNumberFromServer(A10), 0)');
sheet.setFormula(11, 2, '=Refresh(GetNumberFromServer(A10), 1)');
sheet.setFormula(12, 2, '=Refresh(GetNumberFromServer(A10), 2, 2000)');
sheet.getCell(12, 2).foreColor("green");
sheet.setColumnWidth(0, 100);
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 200);
sheet.setColumnWidth(3, 200);
sheet.setValue(15, 1, "=Refresh(now(), 2, 1000)");
sheet.setValue(15, 3, "Every 1 second");
sheet.setFormula(15, 2, "=Refresh(now(), 2, 1000)");
sheet.getCell(15, 2).foreColor("green");
sheet.setValue(18, 1, "=GetTimeFromServer()");
sheet.setValue(18, 3, "Every 1 second");
sheet.setFormula(18, 2, "=GetTimeFromServer()");
sheet.getCell(18, 2).foreColor("green");
sheet.getCell(18, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
sheet.resumePaint();
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</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>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>;
}
initSpread(spread) {
this.spread = spread;
var asyncSum = function () {
};
asyncSum.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("ASUM", 1, 255);
asyncSum.prototype.defaultValue = function () {
return "Loading...";
};
asyncSum.prototype.evaluateAsync = function (context) {
// use setTimeout to simulate server side evaluation
// in read world it maybe an ajax post to server for evaluation
var args = arguments;
setTimeout(function () {
var result = 0;
for (var i = 1; i < args.length; i++) {
result += args[i];
}
result *= 2;
context.setAsyncResult(result);
}, 2000);
};
var GetNumberFromServer = function () {
};
GetNumberFromServer.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("GETNUMBERFROMSERVER", 1, 2);
GetNumberFromServer.prototype.evaluate = function (context, arg1, arg2) {
setTimeout(function () {
var value = Math.random() + 1;
context.setAsyncResult(value);
}, 500);
};
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("GETNUMBERFROMSERVER", new GetNumberFromServer());
var GetTimeFromServer = function () {
};
GetTimeFromServer.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("GETTIMEFROMSERVER");
GetTimeFromServer.prototype.evaluate = function (context) {
setTimeout(function () {
var date = new Date();
context.setAsyncResult(date);
}, 500);
};
GetTimeFromServer.prototype.evaluateMode = function () {
return 2;
};
GetTimeFromServer.prototype.interval = function () {
return 1000;
};
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("GETTIMEFROMSERVER", new GetTimeFromServer());
var sheet = spread.sheets[0];
sheet.suspendPaint();
sheet.options.allowCellOverflow = true;
sheet.setArray(0, 0, [[5, 15]]);
sheet.addCustomFunction(new asyncSum());
sheet.setText(1, 1, 'ASUM(A1,B1)');
sheet.setText(2, 1, 'SUM(A1,B1)');
sheet.setFormula(1, 2, "ASUM(A1,B1)");
sheet.getCell(1, 2).foreColor("green");
sheet.setFormula(2, 2, "SUM(A1,B1)");
sheet.setValue(4, 0, "Edit the formula of C2 or referenced cell' value to see how async function works.");
sheet.setValue(8, 0, 'CHANGEVALUE');
sheet.setValue(8, 1, 'FORMULA');
sheet.setValue(8, 2, 'RESULT');
sheet.setValue(8, 3, 'COMMENTS');
sheet.setValue(9, 3, 'On A10 changed');
sheet.setValue(10, 3, 'On A10 changed');
sheet.setValue(11, 3, 'Evaluate once');
sheet.setValue(12, 3, 'Every 2 seconds');
sheet.setValue(9, 0, 1);
sheet.setValue(9, 1, '=GetNumberFromServer(A10)');
sheet.setValue(10, 1, '=Refresh(GetNumberFromServer(A10), 0)');
sheet.setValue(11, 1, '=Refresh(GetNumberFromServer(A10), 1)');
sheet.setValue(12, 1, '=Refresh(GetNumberFromServer(A10), 2, 2000)');
sheet.setFormula(9, 2, '=GetNumberFromServer(A10)');
sheet.setFormula(10, 2, '=Refresh(GetNumberFromServer(A10), 0)');
sheet.setFormula(11, 2, '=Refresh(GetNumberFromServer(A10), 1)');
sheet.setFormula(12, 2, '=Refresh(GetNumberFromServer(A10), 2, 2000)');
sheet.getCell(12, 2).foreColor("green");
sheet.setColumnWidth(0, 100);
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 200);
sheet.setColumnWidth(3, 200);
sheet.setValue(15, 1, "=Refresh(now(), 2, 1000)");
sheet.setValue(15, 3, "Every 1 second");
sheet.setFormula(15, 2, "=Refresh(now(), 2, 1000)");
sheet.getCell(15, 2).foreColor("green");
sheet.setValue(18, 1, "=GetTimeFromServer()");
sheet.setValue(18, 3, "Every 1 second");
sheet.setFormula(18, 2, "=GetTimeFromServer()");
sheet.getCell(18, 2).foreColor("green");
sheet.getCell(18, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
sheet.resumePaint();
}
}
<!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: 100%;
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;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
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/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.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);
