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 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

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

  • the number of a row
  • the number of a column
  • the content of a cell
  • the CSS class of a 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 name of a style
  • a string containing the styles of a 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 a row or 0
  • the number of a column or 0
  • the size of an element (a row or a column)

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

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. 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.

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

Loading data in CSV format

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

Saving Data

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:

  • 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.