In this article you will find all the necessary information on how to load and save data in SpreadSheet. It includes the following sections:
SpreadSheet expects the loaded data to be an object in JSON format that includes the following fields:
An example of an object for loading is given below:
var base_data = {
"styles": [
["wss1",";;center;;;;;;;;;"],
["wss2",";#6E6EFF;center;;;;;;;;;"],
["wss3","#FFFFFF;#6E6EFF;center;;;;;;;;;"]
//...
],
"sizes": [
[0,1,125],
[0,3,158],
[0,4,137]
],
"data": [
[1,1,"Report - July 2016","wss5", "string"],
[1,2,"","wss5"],
[1,3,"4500","wss5", "date"],
[2,1,"Region","wss20"],
[2,2,"Country","wss20"],
[2,3,"Sales - Group A","wss12"],
[2,4,"Sales - Group A","wss12"],
[2,5,"Total","wss13"]
//...
],
"spans": [
[1,1,5,1]
],
// other parameters
};
The "data" field contains data elements set as arrays of 4 elements:
If data type is not defined it will be assigned automatically based on cell format and value.
The only valid empty value for a cell is the empty string. For example, to pass only styling of a cell without a value, use the code: [1, 2, "", "stylename"]
"data": [
[1,1,"Report - July 2016","wss5"],
[1,2,"","wss5"],
[1,3,"","wss5"],
[2,1,"Region","wss20"],
[2,2,"Country","wss20"],
[2,3,"Sales - Group A","wss12"],
[2,4,"Sales - Group A","wss12"],
[2,5,"Total","wss13"]
]
The "styles" field contains styles descriptions presented as arrays of two elements:
"styles": [
["wss1",";;center;;;;;;;;;"],
["wss2",";#6E6EFF;center;;;;;;;;;"],
["wss3","#FFFFFF;#6E6EFF;center;;;;;;;;;"]
],
The styles in a string are listed in the following order: color; background; text-align; font-family; font-size; font-style; underline; font-weight; vertical-align; wrap; borders; format; border-right; border-bottom; border-left; border-top; strike; indent.
The "sizes" field is optional and includes a collection of sizes that are applied to specific rows and columns.
It contains descriptions of sizes, presented as arrays of 3 parameters:
The column size is specified as [0, column_number, column_width], e.g. [0,3,30].
The row size is set as [row_number, 0, row_height], e.g. [2,0,70].
"sizes": [
[1,0,60],
[0,3,158],
[0,4,137]
],
The "spans" field defines the values of spans. It's an array of arrays, the elements of which are the following:
For example:
"spans": [
[1,1,5,1]
]
The "conditions" field is a 2D array, where each inner array is a condition with the following values (original order preserved):
For example:
"conditions": [
[3, 3, "lessOrEqual", 5000, "webix_ssheet_condition_bad"],
// other condtitions
]
Read more about conditions and how to work with them here.
The "formats" field is a 2D array, where each inner array is a format with the following values (original order preserved):
For example:
"formats": [
["format6", "#,###.#"],
["format8", "[GREEN]0.00;[RED]0.00"],
// other formats
]
You can read more info about formats in the dedicated article.
The "table" field is an object with the following fields:
For example:
"table": {
frozenColumns: 0,
frozenRows: 0,
gridlines: 1,
headers: 1,
hidden: {row:[], column:[]}
}
The "ranges" field is a 2D array where each inner array is a named range with the following values (original order preserved):
For example:
"ranges": [
["my_range", "Sheet1!A2:D5", true],
["another_range", "Sheet1!C6:D7", false],
// other ranges
]
For more detailed information refer to the ranges API.
The "locked" field is a 2D array where each inner array is a locked cell with the following values (original order preserved):
"locked": [
[3, 2],
// other locked cells
]
The "editors" field is a 2D array where each inner array contains the following values (original order preserved):
For example:
"editors": [
[8, 2, {
editor: "richselect",
options: [/* options */]
}],
[8, 3, {
editor: "richselect",
options: "B3:B7"
}]
]
Read more information about editors in Spreadsheet here.
The "filters" field is a 2D array where each inner array contains the following values (original order preserved):
For example:
"filters": [
[8, 2, {
value: {
condition: {filter: "", type: "greater"},
includes: [/* values to include */]
},
options: [/* filtering options */],
mode: "number",
}],
[8, 3, {
value: {/* value config */},
options: "B3:B7",
mode: "text",
}]
]
Read more on filters in Spreadsheet in the dedicated article.
The "comments" field is a 2D array where each inner element contains the following values (original order preserved):
For example:
"comments": [
[3, 3, [
{date: "2018-06-10 18:45", text: "Is it exact data?", user_id: 1},
// ...
]],
// other comments
]
For more details on comments in Spreadsheet refer to the dedicated API article.
The "views" fields is a 2D array where each inner array is an above-cell view. Values of the inner array depend on the type of the view.
For example:
"views": [
[400, 200, "chart", {/* chart configuration */}],
[50, 300, "image", "/remote/assets.picture.jpg", {/* pic dimensions */}],
// other views
]
Read more details on the views and how to work with them in the dedicated API article.
data are loaded into SpreadSheet the same as into other Webix components. You can load data into SpreadSheet from a server or from an inline data source.
To load data from a server, you can use either the load method or the url parameter.
webix.ui({
id:"ssheet",
view:"spreadsheet"
});
$$("ssheet").load("data.js");
webix.ui({
view:"spreadsheet",
url:"data.js"
});
To load data from an inline data source, you can use
webix.ui({
id:"ssheet",
view:"spreadsheet"
});
$$("ssheet").parse(data,"json");
webix.ui({
view:"spreadsheet",
data:[
{
"styles": [
["wss1",";;center;;;;;;;;;"],
["wss2",";#6E6EFF;center;;;;;;;;;"]
...
],
"sizes": [
[0,1,125],
[0,3,158]
...
],
"data": [
[1,1,"Report - July 2016","wss5"],
[1,2,"","wss5"]
...
],
"spans": [
[1,1,5,1]
],
// other fields
};
]
});
SpreadSheet allows loading data in the CSV format. You need to specify the data source via the url parameter and define the corresponding datatype while initializing a spreadsheet:
webix.ui({
view:"spreadsheet",
url:"/data_csv.csv",
datatype:"csv",
});
Related sample: Import from CSV
On each change in a cell of SpreadSheet (editing or formatting), a POST Ajax request for saving changes is sent to the corresponding server-side script (handler), which is specified in the save property of the SpreadSheet configuration.
If you change several cells at a time, requests are sent for each of them.
webix.ready(function(){
webix.ui({
view:"spreadsheet",
url: "server/get.php",
resizeCell: true,
save: {
data:"server/data.php", // if data are changed
sizes:"server/sizes.php", // if the sizes of cells are changed
spans:"server/spans.php", // if cells are merged or split
styles:"server/styles.php" // if styles are changed or a new one created
}
});
});
You can also send requests to handlers in one common request:
webix.ready(function(){
webix.ui({
view:"spreadsheet",
url: "server/get.php",
resizeCell: true,
save:"/server"
});
});
In this case, paths to the necessary handlers will be added automatically:
Parameters of requests differ depending on the operation handler:
{
row:3,
column:9,
value: 230,
style:"wss561",
type:"number"
}
// for a column
{
row:0,
column:7,
size:179
}
// for a row
{
row:1,
column:0,
size:60
}
{
row:1,
column:9,
x: 2,
y: 2
}
{
name:"wss562",
text:"#000000;#ff0000;left;'PT Sans', Tahoma;15px;;;;top;;no,#434343;;;;;"
}
You can save to the server not only data, but also the current state of the spreadsheet. For this, enable the automatic saving mode by setting the all property of save:
webix.ready(function(){
webix.ui({
view:"spreadsheet",
url: "server/get.php",
resizeCell: true,
save: {
all:"/server/sheets/1"
}
});
});
Related sample: Spreadsheet: Loading and Saving Data Automatically
If you want to implement RESTful saving, you can use the onChange event. One of the event parameters is the operation type, so you can track all operations and send corresponding requests for saving them:
webix.ui({
view:"spreadsheet",
url: "/data/sheets",
on:{
onChange: function(mode, name, oldName){
switch (mode) {
case "update":
webix.ajax().put("/server/sheet"+name, this.serialize());
break;
// ...other operations
}
}
}
});
Related sample: Spreadsheet: Saving Pages as Separate Entities
SpreadSheet is a purely client-side widget, therefore you can implement any logic at the backend. You can find several demos that implement PHP data in the SpreadSheet package.
There are also packages for implementing Node.js and .Net data available on Github.
You can also save the current state of the spreadsheet separately from data.
To get the state, you need to use the serialize method.
var states = $$("ssheet").serialize();
It will return an object with a number of parameters that will keep all changed states.
states = {
conditions:[
[3,6,">",100,"custom_bgcolor"],
[3,7,"<",1000,"custom_less"]
],
data:[
[1,1,"Report - July 2016","wss5", "string"],
[1,2,"","wss5"]
],
editors:[
[1,8,{ editor:"richselect", options:["One", "Two", "Three"]}]
],
filters:[
[2,2, "B3:B7"]
],
locked:[
[3,2],
[3,3]
],
sizes: [
[1,0,60],
[0,3,158],
[0,4,137]
],
styles:[
["wss1",";;center;;;;;;;;;"],
["wss2",";#6E6EFF;center;;;;;;;;;"],
["wss3","#FFFFFF;#6E6EFF;center;;;;;;;;;"]
],
spans:[
[1,1,5,1]
],
table:{
frozenColumns: 0,
frozenRows: 0,
gridlines: 1,
headers: 1
}
}
It is also possible to serialize all sheets, in case you work with multiple sheets.
For this purpose, you need to pass an object with the sheets:true configuration as a parameter to the serialize() method:
var states = $$("ssheet").serialize({sheets: true});
Math formulas are serialized together with all other data of a sheet.
If you want to disable serialization of math formulas, pass an object with the math:false config to the serialize() method:
var states = $$("ssheet").serialize({math: false});
Related sample: Data serialization
You can add the compactStyles: true flag to remove unused styles for all the sheets at once. The flag is false by default.
var states = $$("ssheet").serialize({compactStyles: true});