Loading and Saving Data in SpreadSheet

In this article you will find all the necessary information on how to load and save data in SpreadSheet. It includes the following sections:

Data Loading Format

SpreadSheet expects the loaded data to be an object in JSON format that includes 4 parameters:

  • data
  • sizes
  • spans
  • styles

An example of the 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

The "data" parameter contains data elements set as arrays of 4 elements:

  • the number of the row
  • the number of the column
  • the cell's content
  • the CSS class of the cell
"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

The "styles" parameter contains styles descriptions presented as arrays of two elements:

  • the style's name
  • a string containing the styles of the CSS rule
"styles": [
    ["wss1",";;center;;;;;;;;;"],
    ["wss2",";#6E6EFF;center;;;;;;;;;"],
    ["wss3","#FFFFFF;#6E6EFF;center;;;;;;;;;"]
],

The Sizes parameter

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 number of the row or 0
  • the number of the column or 0
  • the size of an element (a row or a column)

The column's size is specified as [0, column_number, column_width], e.g. [0,3,30].

The row's 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

The "spans" parameter defines the values of spans. It's an array of arrays the elements of which are the following:

  • the number of the row that starts the span
  • the number of the column that starts the span
  • the number of columns in a span
  • the number of rows in a span

For example:

"spans": [
    [1,1,5,1]
]

Loading Data

Data is loaded into SpreadSheet the same as into other Webix components.

To load data from server, you can use either the load method or the url parameter.

  • using the load method:
webix.ui({
    id:"ssheet",
    view:"spreadsheet"
});
$$("ssheet").load("data.js");
  • using the url parameter
webix.ui({
    view:"spreadsheet",
    url:"data.js"
});

To load data from an inline data source, you can use either the parse method:

webix.ui({
    id:"ssheet",
    view:"spreadsheet"
});
$$("ssheet").parse(data,"json");

or the data parameter and specify the dataset directly in the spreadsheet configuration:

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]
            ]
        };
    ]
});

Saving Data

On each change (editing or formatting) in a cell of SpreadSheet, a POST Ajax request for saving changes is sent to the corresponding server-side script (handler) 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 cells' sizes 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:

  • data => /server/data
  • styles => /server/styles
  • sizes => /server/sizes
  • spans => /server/spans

Parameters of requests differ depending on the operation handler:

  • data.php
{
    row:3,
    column:9,
    value: 230,
    style:"wss561"
}
  • sizes.php
// for a column
{
    row:0,
    column:7,
    size:179
}
// for a row
{
    row:1,
    column:0,
    size:60
}
  • spans.php
{
    row:1,
    column:9,
    x: 2,
    y: 2
}
  • styles.php
{
    name:"wss562",
    text:"#000000;#ff0000;left;'PT Sans', Tahoma;15px;;;;top;;no,#434343;;;;;"
}

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.

Saving and Loading States

You can save the current state of the spreadsheet on the server and restore it later to continue your work.

To save 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
    }
}

Serializing multiple sheets

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});

Serializing math formulas

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
If you have not checked yet, be sure to visit site of our main product Webix js framework
and page of javascript spreadsheet library product.