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:
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()); |
|
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>
|
|