FlexSheet Custom Functions

Although the functions provided in FlexSheet should cover a vast majority of use scenarios, still there may be some cases where users may need additional functions.

FlexSheet provides two methods that allow you to add your own custom functions: addFunction and unknownFunction.

The addFunction method adds a custom function to the list of built-in functions.

The addFunction method is usually the best way to add custom functions to the FlexSheet calculation engine. However, there are scenarios where the function names are variable or unknown ahead of time. For example, named ranges or value dictionaries.

In these situations, you can use the unknownFunction event to look up the value of a function dynamically. When the FlexSheet detects an unknown function name, it raises the unknownFunction event and provides parameters that contain the function name and parameters. The event handler then calculates the result and returns the value.

import * as wjFlexSheet from '@mescius/wijmo.grid.sheet';

let customFuncSheet = new wjFlexSheet.FlexSheet('#customFuncSheet');

customFuncSheet.addFunction('customSumProduct', function () {
    var result = 0,
        range1 = arguments[0],
        range2 = arguments[1];

    if (range1.length > 0 && range1.length === range2.length && range1[0].length === range2[0].length) {
        for (var i = 0; i < range1.length; i++) {
            for (var j = 0; j < range1[0].length; j++) {
                result += range1[i][j] * range2[i][j];
            }
        }
    }
    return result;
}, 'Custom SumProduct Function', 2, 2);

customFuncSheet.unknownFunction.addHandler(function (sender, e) {
    var result = '';
    if (e.params) {
        for (var i = 0; i < e.params.length; i++) {
            result += e.params[i];
        }
    }
    e.value = result;
});