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.
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(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); function FactorialFunction() { this.name = "FACTORIAL"; this.maxArgs = 1; this.minArgs = 1; } FactorialFunction.prototype = new GC.Spread.CalcEngine.Functions.Function(); FactorialFunction.prototype.evaluate = function (arg) { var result = 1; if (arguments.length === 1 && !isNaN(parseInt(arg))) { for (var i = 1; i <= arg; i++) { result = i * result; } return result; } return "#VALUE!"; }; var factorial = new FactorialFunction(); function FactorialArrayFunction() { this.name = "FACTORIAL.ARRAY"; this.maxArgs = 1; this.minArgs = 1; } FactorialArrayFunction.prototype = new GC.Spread.CalcEngine.Functions.Function(); FactorialArrayFunction.prototype.evaluate = function (arg) { var t = 1; var result = [[]]; if (arguments.length === 1 && !isNaN(parseInt(arg))) { for (var i = 1; i <= arg; i++) { t = i * t; result[0].push(t); } return new GC.Spread.CalcEngine.CalcArray(result); } return "#VALUE!"; }; var factorialArray = new FactorialArrayFunction(); document.getElementById("addCustomFunction").addEventListener('click',function() { sheet.setValue(3, 1, 'Formula'); sheet.setValue(3, 2, '=FACTORIAL(5)'); sheet.setValue(4, 1, 'Result'); sheet.addCustomFunction(factorial); 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)"); }); document.getElementById("removeCustomFunction").addEventListener('click',function() { sheet.removeCustomFunction("FACTORIAL"); sheet.removeCustomFunction("FACTORIAL.ARRAY"); sheet.recalcAll(true); }); };
<!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 type="button" value="Add a Custom Function(factorial)" id="addCustomFunction" /> <input type="button" value="Remove the Custom Function(factorial)" id="removeCustomFunction"/> </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; overflow: auto; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; } .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; }