Loading and Storing Data

You can populate dhtmlxSpreadSheet with a ready dataset that may include the data itself and styling for cells. The component supports two ways of data loading:

  • load from an external file
  • load from a local source

Preparing Data

dhtmlxSpreadSheet expects data in the JSON format. It is an array with data objects each of which has two properties:

  • cell - (string) the id of a cell that is formed as "id of the column + id of the row", e.g. A1
  • value - (string,number) the value of a cell

The example below demonstrates a simple data set for SpreadSheet:

 var data = [
    { cell: "a1", value: "Country" },
    { cell: "b1", value: "Product" },
    { cell: "c1", value: "Price" },
    { cell: "d1", value: "Amount" },
    { cell: "e1", value: "Total Price" },
 
    { cell: "a2", value: "Ecuador" },
    { cell: "b2", value: "Banana" },
    { cell: "c2", value: 6.68, css: "someclass" },
    { cell: "d2", value: 430 },
    { cell: "e2", value: 2872.4 },
 
    { cell: "a3", value: "Belarus" },
    { cell: "b3", value: "Apple" },
    { cell: "c3", value: 3.75, css: "someclass" },
    { cell: "d3", value: 600 },
    { cell: "e3", value: 2250 },
 
     // more data
];

You can also define the cells styling in the data set. In this case the data should be an object with separate parameters that describe data objects and CSS classes applied to particular cells.

var styledData = {
    data: [
        { cell: "a1", value: "Country" },
        { cell: "b1", value: "Product" },
        { cell: "c1", value: "Price" },
        { cell: "d1", value: "Amount" },
        { cell: "e1", value: "Total Price" },
 
        { cell: "a2", value: "Ecuador" },
        { cell: "b2", value: "Banana" },
        { cell: "c2", value: 6.68, css: "someclass" },
        { cell: "d2", value: 430, css: "someclass" },
        { cell: "e2", value: 2872.4 }
    ];
    styles: {
        someclass: {
            background: "#F2F2F2",
            color: "#F57C00"
        }
    }
}

External data loading

Loading JSON data

By default, SpreadSheet expects data in JSON format. To load data from an external source, use the load method. It takes the URL of the file with data as a parameter:

var spreadsheet = new dhx.Spreadsheet(document.body);
spreadsheet.load("../common/data.json");

Related sample:  Load JSON data

Loading CSV data

You can also load data in the CSV format. For this, you need to call the load method and pass the name of the format ("csv") as the second parameter:

var spreadsheet = new dhx.Spreadsheet("cont");
spreadsheet.load("../common/data.csv", "csv");

Related sample:  Load CSV data

The component will make an AJAX call and expect the remote URL to provide valid data. Data loading is asynchronous, so you need to wrap any after-loading code into a promise:

spreadsheet.load("/some/data").then(function(){
   // do something
});

Loading from local source

To load data from a local source, make use of the parse method. The method takes an array with data objects as a parameter:

var data = [
    { cell: "a1", value: "Country" },
    { cell: "b1", value: "Product" },
    { cell: "c1", value: "Price" },
 
    { cell: "a2", value: "Ecuador" },
    { cell: "b2", value: "Banana" },
    { cell: "c2", value: 6.68},
 
    { cell: "a3", value: "Belarus" },
    { cell: "b3", value: "Apple" },
    { cell: "c3", value: 3.75}
];
 
var spreadsheet = new dhx.Spreadsheet(document.body);
spreadsheet.parse(data);

Related sample:  Basic spreadsheet

Saving and Restoring State

To save the current state of a spreadsheet, use the serialize method. It converts data into an array of JSON objects. Each JSON object contains the configuration of a separate shape.

// saving state of the spreadsheet1
var state = spreadsheet1.serialize();

Then you can parse the data stored in the saved state array to a different spreadsheet. For example:

// creating a new spreadsheet
var spreadsheet2 = new dhx.Spreadsheet(document.body);
// parsing the state of the spreadsheet1 into spreadsheet2
spreadsheet2.parse(state);
Back to top