Custom Functions

In SpreadJS, you can create your own function and add it to sheet. In addition to all the 450+ 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 { Component, NgModule, enableProdMode } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@grapecity/spread-sheets-angular'; import GC from '@grapecity/spread-sheets'; import './styles.css'; class FactorialFunction extends GC.Spread.CalcEngine.Functions.Function { constructor() { super("FACTORIAL", 1, 1); } evaluate (arg: number) { let result = 1; if (arguments.length === 1 && !isNaN(arg)) { for (let 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: number) { let t = 1; let result: any[][] = [[]]; 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!"; } } @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { spread: GC.Spread.Sheets.Workbook; factorial: any; factorialArray: any; hostStyle = { width: 'calc(100% - 280px)', height: '100%', overflow: 'hidden', float: 'left' }; constructor() { } initSpread($event: any) { this.spread = $event.spread; let spread = this.spread; spread.options.allowDynamicArray = true; let sheet = spread.getSheet(0); sheet.setValue(1, 1, 'Press \'Add Custom Function\' button'); sheet.setColumnWidth(1, 225); sheet.setColumnWidth(2, 100); this.factorial = new FactorialFunction(); this.factorialArray = new FactorialArrayFunction(); } addCustomFunction ($event: any) { let 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 ($event: any) { let sheet = this.spread.sheets[0]; sheet.removeCustomFunction("FACTORIAL"); sheet.removeCustomFunction("FACTORIAL.ARRAY"); sheet.recalcAll(true); } } @NgModule({ imports: [BrowserModule, SpreadSheetsModule], declarations: [AppComponent], exports: [AppComponent], bootstrap: [AppComponent] }) export class AppModule {} enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Polyfills --> <script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script> <script src="$DEMOROOT$/en/angular/node_modules/zone.js/dist/zone.min.js"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('$DEMOROOT$/en/lib/angular/license.ts'); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html>
<div class="sample-tutorial"> <gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)"> <gc-worksheet> </gc-worksheet> </gc-spread-sheets> <div class="options-container"> <div class="option-row"> <input type="button" value="Add a Custom Function(factorial)" id="addCustomFunction" (click)="addCustomFunction($event)" /> <input type="button" value="Remove the Custom Function(factorial)" id="removeCustomFunction" (click)="removeCustomFunction($event)" /> </div> </div>  </div>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .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; }
(function (global) { System.config({ transpiler: 'ts', typescriptOptions: { tsconfig: true }, meta: { 'typescript': { "exports": "ts" }, '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'core-js': 'npm:core-js/client/shim.min.js', 'zone': 'npm:zone.js/dist/zone.min.js', 'rxjs': 'npm:rxjs/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/bundles/core.umd.min.js', '@angular/common': 'npm:@angular/common/bundles/common.umd.min.js', '@angular/compiler': 'npm:@angular/compiler/bundles/compiler.umd.min.js', '@angular/platform-browser': 'npm:@angular/platform-browser/bundles/platform-browser.umd.min.js', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/bundles/platform-browser-dynamic.umd.min.js', '@angular/http': 'npm:@angular/http/bundles/http.umd.min.js', '@angular/common/http': 'npm:@angular/common/bundles/common-http.umd.min.js', '@angular/router': 'npm:@angular/router/bundles/router.umd.min.js', '@angular/forms': 'npm:@angular/forms/bundles/forms.umd.min.js', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': 'npm:plugin-typescript/lib/plugin.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', '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-angular': 'npm:@grapecity/spread-sheets-angular/bundles/grapecity-spread-sheets-angular.umd.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'ts' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);