SpreadJS 13
GC.Spread.Sheets Namespace / Worksheet type / addCustomFunction Method
The function to add.
In This Topic
    addCustomFunction Method
    In This Topic
    Adds a custom function.
    Syntax
    var instance = new GC.Spread.Sheets.Worksheet(name);
    var value; // Type: any
    value = instance.addCustomFunction(fn);
    function addCustomFunction( 
       fn : Function
    ) : any;

    Parameters

    fn
    The function to add.
    Example
    These examples create custom functions.
    function FactorialFunction() {
        this.name = "FACTORIAL";
        this.maxArgs = 1;
        this.minArgs = 1;
    }
    FactorialFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
    FactorialFunction.prototype.evaluate = function () {
        var result = 1, args = arguments;
        if (args.length === 1 && !isNaN(parseInt(args[0]))) {
            for (var i = 1; i < args[0]; i++) {
                result = i * result;
            }
            return result;
        }
        return "#VALUE!";
    }
    
    var factorial = new FactorialFunction();
    activeSheet.addCustomFunction(factorial);
    activeSheet.getCell(0,0).formula("factorial(5)");
    var name = activeSheet.getCustomFunction("factorial");
    alert(name);
    // Type =factorial(5) in a cell to see the result
    function FactorialFunction() {
        this.name = "FACTORIAL";
        this.maxArgs = 1;
        this.minArgs = 1;
    }
    FactorialFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
    FactorialFunction.prototype.evaluate = function () {
        var result = 1, args = arguments;
        if (args.length === 1 && !isNaN(parseInt(args[0]))) {
            for (var i = 1; i < args[0]; i++) {
                result = i * result;
            }
            return result;
        }
        return "#VALUE!";
    }
    
    var factorial = new FactorialFunction();
    activeSheet.addCustomFunction(factorial);
    <!DOCTYPE html>
    <html>
    <head>
        <title>SpreadJS V11 Development Sample</title>
        <link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2016colorful.12.0.0.css" rel="stylesheet" type="text/css"/>
        <script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
        <script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.12.0.0.min.js"></script>
        <script type="text/javascript">
            function CustomFunctionWithoutContext() {
                this.name = "FactorialAfterSum";
                this.maxArgs = 255;
                this.minArgs = 1;
            }
            CustomFunctionWithoutContext.prototype = new GC.Spread.CalcEngine.Functions.Function();
            CustomFunctionWithoutContext.prototype.description = function () {
                return {
                    description: "The function returns the factorial of the sum result",
                    parameters: [{
                        name: "value",
                        repeatable: true,
                        optional: true
                    }],
                }
            }
            CustomFunctionWithoutContext.prototype.evaluate = function () {
                //If the isContextSensitive return false.
                //The arguments of evaluate will be the real function args.
                //For example, =XXXFunction(1,2,3,4,5), the arguments will be 1,2,3,4,5.
                //Hence, for this case, if you use =FactorialAfterSum(1,2,3), the arguments will be 1,2,3
                if (arguments.length > 0) {
                    var sum = 0, factoral = 1;
                    //Calc Sum
                    for (var i = 0; i < arguments.length; i++) {
                        if (!isNaN(arguments[i])) {
                            sum += parseInt(arguments[i]);
                        }
                    }
                    //Calc factoral
                    do {
                        factoral *= sum;
                    } while (--sum >= 1)
                    return factoral;
                }
                return GC.Spread.CalcEngine.CalcError.parse("#Value!");
            }
    
    
    
    
    
            function CustomFunctionWithContext() {
                this.name = "FactorialAfterSum2";
                this.maxArgs = 255;
                this.minArgs = 1;
            }
            CustomFunctionWithContext.prototype = new GC.Spread.CalcEngine.Functions.Function();
            CustomFunctionWithContext.prototype.description = function () {
                return {
                    description: "The function returns the factorial of sum result",
                    parameters: [{
                        name: "value",
                        repeatable: true,
                        optional: true
                    }],
                }
            }
            CustomFunctionWithContext.prototype.evaluate = function () {
                //If the isContextSensitive return true.
                //The first argument will be evaluateContext, used for function calculation.
                //The following arguments will be the real function args.
                //For example, =XXXFunction(1,2,3,4,5), the arguments will be context, 1, 2, 3, 4, 5.
                //Hence, for this case, if customer use =FactorialAfterSum2(1,2,3), the arguments will be context, 1,2,3
                if (arguments.length >= 1) {
                    var sum = 0, factoral = 1, context = arguments[0];
                    //Calc Sum
                    for (var i = 1; i < arguments.length; i++) {
                        if (!isNaN(arguments[i]) && arguments[i] !== null) {
                            sum += parseInt(arguments[i]);
                        }
                    }
                    //Calc factoral
                    do {
                        factoral *= sum;
                    } while (--sum >= 1)
                    return factoral;
                }
                return GC.Spread.CalcEngine.CalcError.parse("#Value!");
            }
            CustomFunctionWithContext.prototype.isContextSensitive = function () {
                return true;
            }
    
    
            $(document).ready(function () {
                var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
                var sheet = spread.getActiveSheet();
                sheet.addCustomFunction(new CustomFunctionWithContext());
                sheet.addCustomFunction(new CustomFunctionWithoutContext());
    
                sheet.setValue(0,0,1);
                sheet.setValue(1,0,2);
                sheet.setValue(2,0,3);
                sheet.setFormula(4,1,"=FactorialAfterSum2(1,2,3)");
                sheet.setFormula(5,1,"=FactorialAfterSum2(A1,A2,A3)");
            });
        </script>
    </head>
    <body>
    <div id="ss" style="width:70%;height:500px;border: 1px solid gray;font-size:10.5pt"></div>
    </body>
    </html>
    See Also

    Reference

    Worksheet type