SpreadJS 14
Features / Formulas / Create Custom Formulas
In This Topic
    Create Custom Formulas
    In This Topic

    If you have functions that you use on a regular basis that are not in the built-in functions or if you wish to combine some of the built-in functions into a single function, you can do so by defining your own custom functions. They can be called as you would call any of the built-in functions. These custom functions can return either specified values or arrays. This is explained in the sections below:

    Return Single Value in Custom Function

    This example creates a custom function that returns a value.

    JavaScript
    Copy Code
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
    var activeSheet = spread.getActiveSheet();
    // Add Custom function
    // Type =myfunc(1)
    // in a cell to see the result
    function myfunc() {}
    myfunc.prototype = new GC.Spread.CalcEngine.Functions.Function("myfunc", 0, 0, {name: "myfunc",description: "This is my first function"});
    myfunc.prototype.evaluate = function (args) {
        return 100;}
    spread.addCustomFunction(new myfunc());
    

    Return Array in Custom Function

    This example creates a custom function that returns an array.

    JavaScript
    Copy Code
    <script>
            $(document).ready(function () {
                // initializing Spread
                var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
                // Get the activesheet
                var sheet = spread.getSheet(0);
                // allow dynamic array
                spread.options.allowDynamicArray = true;
                // create a factorial function
                function FactorialFunction() {
                    this.name = "FACTORIAL";
                    this.maxArgs = 1;
                    this.minArgs = 1;
                }
                FactorialFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
                //evaluating the result of the formula
                FactorialFunction.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.push(t);
                        }
                        //returning the CALCArray Object as a result of the function
                        return new GC.Spread.CalcEngine.CalcArray([result]);
                    }
                    return "#VALUE!";
                };
                //Adding description to the custom Function
                FactorialFunction.prototype.description = function () {
                    return {
                        name: "FACTORIAL",
                        description:
                            "This is function which calculates the factorial from 1 and show in different cells of a row",
                    };
                };
                var factorial = new FactorialFunction();
                sheet.addCustomFunction(factorial);
                // Merge three columns with origin at cell(0,0)
                sheet.addSpan(0, 0, 1, 7, GC.Spread.Sheets.SheetArea.viewport);
                sheet.setValue(0, 0, "This custom factorial function calculates the factorial from 1 to 7 ");
                for (var i = 1; i < 8; i++) {
                    sheet.setText(2, i - 1, i + "!");
                }
                //set custom formula
                sheet.setFormula(3, 0, "=Factorial(7)");
                var style = new GC.Spread.Sheets.Style();
                style.font = "bold 12px Arial";
                style.foreColor = "white";
                style.backColor = "#5B9BD5";
                style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
                style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
                sheet.setStyle(0, 0, style, GC.Spread.Sheets.SheetArea.viewport);
            });
        </script>