Data Loading and Saving

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 the following fields:

  • data
  • sizes
  • spans
  • styles
  • conditions
  • formats
  • table
  • ranges
  • locked
  • editors
  • filters
  • comments
  • views

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

The "data" field 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 (optional);
  • the type of data (optional). Possible values are:
    • "date"
    • "number"
    • "string"

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

The "styles" field 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 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

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

The "spans" field 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]
]

The Conditions Field

The "conditions" field is a 2D array, where each inner array is a condition with the following values (original order preserved):

  • the row ID
  • the column ID
  • a conditional rule (e.g. "greater", "less", etc.).
  • value to compare the cell value with
  • CSS class name to apply if the condition is observed.

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

The "formats" field is a 2D array, where each inner array is a format with the following values (original order preserved):

  • format name
  • format pattern.

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

The "table" field is an object with the following fields:

  • frozenColumns (number) - frozen columns
  • frozenRows (number) - frozen rows
  • gridLines (number) - table grid. 1 if enabled
  • headers (sting, number) - table header
  • hidden (object) - stores the row and column arrays for hidden cells.

For example:

"table": {
    frozenColumns: 0,
    frozenRows: 0,
    gridlines: 1,
    headers: 1,
    hidden: {row:[], column:[]}
}

The Ranges Field

The "ranges" field is a 2D array where each inner array is a named range with the following values (original order preserved):

  • range name
  • the range of the cells itself
  • visibility scope.

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

The "locked" field is a 2D array where each inner array is a locked cell with the following values (original order preserved):

  • row ID
  • column ID.
"locked": [
    [3, 2],
    // other locked cells
]

The Editors Field

The "editors" field is a 2D array where each inner array contains the following values (original order preserved):

  • row ID
  • column ID
  • editor object.

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

The "filters" field is a 2D array where each inner array contains the following values (original order preserved):

  • row ID
  • column ID
  • filter object.

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

The "comments" field is a 2D array where each inner element contains the following values (original order preserved):

  • row ID
  • column ID
  • array of comment objects.

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 Field

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.

Loading Data

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.

    • 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]
                ],
                    // other fields
            };
        ]
    });

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

  • 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",
    type:"number"
}
  • 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;;;;;"
}

Saving Data and State

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

RESTful Saving

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.

Saving and Loading States

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

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

Serializing without unused styles

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