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 4 parameters:
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"],
[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"]
//...
],
"spans": [
[1,1,5,1]
]
};
The "data" parameter contains data elements set as arrays of 4 elements:
"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" parameter contains styles descriptions presented as arrays of two elements:
"styles": [
["wss1",";;center;;;;;;;;;"],
["wss2",";#6E6EFF;center;;;;;;;;;"],
["wss3","#FFFFFF;#6E6EFF;center;;;;;;;;;"]
],
The "sizes" parameter 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 4 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" parameter 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]
]
Data is 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]
]
};
]
});
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 is 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"
}
// 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"],
[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
Back to top