SpreadSheet allows performing operations with simple cell content (numbers, strings) and operators (arithmetic, comparison, etc.) as well as with built-in functions and custom functions. It is also possible to combine regular operands and functions in more complex formulas. The component processes them and displays the result in the related cells.
The syntax of a formula is the same as in Excel.
"data": [
[ 3, 2, "=A1+B2",""],
[ 4, 2, "=7+8",""],
[ 5, 2, "=7+B2",""],
[ 6, 2, "=SUM(A3+D7)",""]
...
]
4 . There are two variants of recording a formula, both produce different results:
"data": [
[ 7, 2, "=SUM(A3,D7)",""]
]
"data": [
[ 7, 2, "=SUM(A3:D7)",""]
]
You can concatenate string content of cells. There are three possible ways for this:
1 . the & operator with strings or cell references: "=nice"&"trip" or =A1&B2
2 . the + operator with same operands: ="nice"+"trip" or =A1+B2. The "+" operator behaves just as an ordinary JS addition operator (e.g. ="nice"+12).
3 . apply the CONCATENATE() function, like this: =CONCATENATE("nice","trip" or A1,B2). It's also possible to connect strings from a range of cells to produce one continuous text: =CONCATENATE(C2:D4)
You can specify a name for a particular range of cells and then use it in formulas for calculations. Ranges can belong to a particular sheet or the whole Spreadsheet.
Thus, the formula will be easier to understand and work with.
You can add a named range and manipulate it through the ranges module:
Used to add new named range globally or to the active/specified sheet. The method takes the following parameters:
To add a range to the active sheet, do not pass the 3rd parameter
// add "MYRANGE" range to Sheet 1
$$("ssheet").ranges.add("MYRANGE", "B2:C2", "Sheet 1");
You can link data of several sheets and use these cross references in formulas to process them together.
The rules of creating a reference to an external sheet are quite simple:
Here are some use cases that show how to use cross references in multi sheets:
Related sample: Spreadsheet: Multiple Sheets
Spreadsheet possesses an advanced formula editor. Its main features are:
It's possible to disable the formula editor, just set the liveEditor property to false in the spreadsheet configuration:
webix.ui({
view: "spreadsheet",
data: base_data,
liveEditor: false
});
Math methods are divided by categories: Common, Date, Text, Number, Financial. The Common math methods are available by default.
To enable math methods in the UI, you need to use the methods parameter and specify the category.
The methods parameter can be set as a string, e.g.: methods: "financial"
or as an array, e.g.: methods:["number", "text"]
.
webix.ui({
view:"spreadsheet",
methods:["date", "text"],
toolbar:"full",
data:spreadsheet_data
});
To enable all the categories, use methods: "all". If the methods parameter is not specified, only the Common math methods are added to SpreadSheet.
You can search by category (note, that the Category field is hidden if the methods parameter is not specified):
It is also possible to specify a custom category while adding a new math method:
webix.ui({
view:"spreadsheet",
id: "sheets",
methods:"all",
toolbar:"full",
});
webix.i18n.spreadsheet.liveEditor.custom = "Custom";
$$("sheets").registerMathMethod("returnone", () => 1, "custom");
$$("sheets").setCellValue(1, 1, "=RETURNONE()")
The formula editor is supplied with built-in suggestions for parameters of an entered formula. The suggestion contains a category of a math formula.
The tooltip with suggestions is available with the enabled liveEditor property.
The descriptions of the methods parameters are stored in the locale named webix.i18n.spreadsheet.liveEditor["functions-syntax"]. Thus you can translate them into the necessary language. The structure of methods in the locale is the following:
{
methodName: [
[parameter, description],
...
]
}
In case you have added your own method, you can add suggestions for its parameters by updating the locale.
Check the example below:
const ssheet = webix.ui({
view:"spreadsheet",
toolbar:"full"
});
ssheet.registerMathMethod("RANDOM", function(value){ value = value || 100; return Math.round(Math.random()*value); });
webix.i18n.spreadsheet.liveEditor["functions-syntax"].RANDOM = [ ["Digit", "Optional. The number digit."] ];
ssheet.setCellValue(1,1,"=RANDOM(100)")
You can reach the popup with suggestions for formula parameters using the following syntax: ssheet.$$("liveEditor").paramsPopup. For example, if you want to hide the tooltip popup, you can add a handler for the popup and return false from the callback function of the onBeforeShow event:
ssheet.$$("liveEditor").paramsPopup.attachEvent("onBeforeShow", ()=> false);
or simply remove all the methods from the locale:
webix.i18n.spreadsheet.liveEditor["functions-syntax"] = {};
Back to top