Custom Functions

In SpreadJS, you can create your own function and add it to sheet. In addition to all the 500+ built-in functions, the SpreadJS Calculation Engine also allows you to create your own custom functions for specific business use cases. They can be defined and called as you would any of the built-in functions.

You can create your own function by inheriting the GC.Spread.CalcEngine.Functions.Function, as shown in the following code: The evaluate function can return a GC.Spread.CalcEngine.CalcArray object to represent the custom function returns an array as following code: After you have added your own function, if you want to remove it or clear all custom functions, use the removeCustomFunction or clearCustomFunctions method.
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'; class FactorialFunction extends GC.Spread.CalcEngine.Functions.Function { constructor() { super("FACTORIAL", 1, 1); } evaluate(arg) { var result = 1; if (arguments.length === 1 && !isNaN(arg)) { for (var i = 1; i <= arg; i++) { result = i * result; } return result; } return "#VALUE!"; } } class FactorialArrayFunction extends GC.Spread.CalcEngine.Functions.Function { constructor() { super("FACTORIAL.ARRAY", 1, 1); } evaluate(arg) { let t = 1; let result = [[]]; if (arguments.length === 1 && !isNaN(arg)) { for (var i = 1; i <= arg; i++) { t = i * t; result[0].push(t); } return new GC.Spread.CalcEngine.CalcArray(result); } return "#VALUE!"; } } const useState = React.useState; const factorial = new FactorialFunction(); const factorialArray = new FactorialArrayFunction(); export function AppFunc() { const [spread, setSpread] = useState(null); const initSpread = (spread) => { setSpread(spread); spread.options.allowDynamicArray = true; var sheet = spread.getSheet(0); sheet.setValue(1, 1, 'Press \'Add a Custom Function\' button'); sheet.setColumnWidth(1, 225); sheet.setColumnWidth(2, 100); } const addCustomFunction = (e) => { var sheet = spread.sheets[0]; sheet.addCustomFunction(factorial); sheet.setValue(3, 1, 'Formula'); sheet.setValue(3, 2, '=FACTORIAL(5)'); sheet.setValue(4, 1, 'Result'); sheet.setFormula(4, 2, "=FACTORIAL(5)"); sheet.setValue(6, 1, 'Formula'); sheet.setValue(6, 2, '=FACTORIAL.ARRAY(5)'); sheet.setValue(7, 1, 'Result'); sheet.addCustomFunction(factorialArray); sheet.setFormula(7, 2, "=FACTORIAL.ARRAY(5)"); } const removeCustomFunction = (e) => { var sheet = spread.sheets[0]; sheet.removeCustomFunction("FACTORIAL"); sheet.removeCustomFunction("FACTORIAL.ARRAY"); sheet.recalcAll(true); } return <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread => initSpread(spread)}> <Worksheet> </Worksheet> </SpreadSheets> </div> <Panel addCustomFunction={(e) => addCustomFunction(e)} removeCustomFunction={(e) => removeCustomFunction(e)} /> </div>; } function Panel(props) { return ( <div class="options-container"> <div class="option-row"> <input type="button" value="Add a Custom Function(factorial)" onClick={(e) => { props.addCustomFunction(e) }} /> <input type="button" value="Remove the Custom Function(factorial)" onClick={(e) => { props.removeCustomFunction(e) }} /> </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; class FactorialFunction extends GC.Spread.CalcEngine.Functions.Function { constructor() { super("FACTORIAL", 1, 1); } evaluate (arg) { var result = 1; if (arguments.length === 1 && !isNaN(arg)) { for (var i = 1; i <= arg; i++) { result = i * result; } return result; } return "#VALUE!"; } } class FactorialArrayFunction extends GC.Spread.CalcEngine.Functions.Function { constructor() { super("FACTORIAL.ARRAY", 1, 1); } evaluate (arg) { let t = 1; let result = [[]]; if (arguments.length === 1 && !isNaN(arg)) { for (var i = 1; i <= arg; i++) { t = i * t; result[0].push(t); } return new GC.Spread.CalcEngine.CalcArray(result); } return "#VALUE!"; } } 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 addCustomFunction={(e)=>{this.addCustomFunction(e)}} removeCustomFunction={(e)=>{this.removeCustomFunction(e)}} /> </div>; } initSpread(spread) { this.spread = spread; spread.options.allowDynamicArray = true; var sheet = spread.getSheet(0); sheet.setValue(1, 1, 'Press \'Add a Custom Function\' button'); sheet.setColumnWidth(1, 225); sheet.setColumnWidth(2, 100); this.factorial = new FactorialFunction(); this.factorialArray = new FactorialArrayFunction(); } addCustomFunction (e) { var sheet = this.spread.sheets[0]; sheet.addCustomFunction(this.factorial); sheet.setValue(3, 1, 'Formula'); sheet.setValue(3, 2, '=FACTORIAL(5)'); sheet.setValue(4, 1, 'Result'); sheet.setFormula(4, 2, "=FACTORIAL(5)"); sheet.setValue(6, 1, 'Formula'); sheet.setValue(6, 2, '=FACTORIAL.ARRAY(5)'); sheet.setValue(7, 1, 'Result'); sheet.addCustomFunction(this.factorialArray); sheet.setFormula(7, 2, "=FACTORIAL.ARRAY(5)"); } removeCustomFunction (e) { var sheet = this.spread.sheets[0]; sheet.removeCustomFunction("FACTORIAL"); sheet.removeCustomFunction("FACTORIAL.ARRAY"); sheet.recalcAll(true); } } class Panel extends Component{ constructor(props){ super(props); } render(){ return( <div class="options-container"> <div class="option-row"> <input type="button" value="Add a Custom Function(factorial)" onClick={(e)=>{this.props.addCustomFunction(e)}} /> <input type="button" value="Remove the Custom Function(factorial)" onClick={(e)=>{this.props.removeCustomFunction(e)}} /> </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 { margin-bottom: 12px; } input[type=button] { padding: 4px 6px; margin-bottom: 6px; } 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);