Exploring Supported Data Formats

dhtmlxGrid can be populated with data of 4 formats:

• XML.
• JSON.
• CSV.
• JSArray.
• + you can define a custom data format.

Let's take a collection of books as a data source and present it in different formats.

XML format

In the XML format the data source will be presented as in:

data.xml file

<?xml version='1.0' encoding='iso-8859-1'?>
<rows> 
    <row id="1"> 
        <cell>A Time to Kill</cell> 
        <cell>John Grisham</cell> 
        <cell>12.99</cell> 
    </row>
    <row id="2"> 
        <cell>Blood and Smoke</cell> 
        <cell>Stephen King</cell> 
        <cell>10</cell> 
    </row>
    <row id="3">
        <userdata name="lowprice"></userdata>
        <cell>The Rainmaker</cell> 
        <cell>Stephen King</cell> 
        <cell><![CDATA[<font color="red">7.99</font>]]></cell> 
    </row>
</rows>

'index.html' file

mygrid.load("data.xml", "xml");

Related sample:  Loading from XML

Available tags

afterInit	groups all command calls which will be executed after grid structure is initialized. A command from this block will be executed even if no data structure tags (column) are defined in the header
beforeInit	groups all command calls which will be executed before grid structure is initialized. A command from this block will be executed only if full grid structure is defined in XML (column tags)
call	represents the call of grid command, must be placed in afterInit or beforeInit section. The attribute is: command - name of the grid method which will be called
cell	represents a cell of grid, the value of the tag will be used as the value of a cell in grid. The attributes are: class - css class which will be attached to a cell; colspan - a few cells can be grouped in a rowspan, please pay attention that other cells which will be included in colspan still need to be present in XML. The support of the attribute must be enabled by API call; rowspan - a few cells can be grouped in a rowspan, this feature is a mutual exclusion of a colspan (a cell can have both colspan and rowspan at the same time). Please note that a multiple rowspan will slow grid down. _rowspan.js extension is required to activate the feature; style - a css style string, a value mapped to the style attribute of an HTML cell; type - the type of a used cell defined per column ( setColTypes ), but each cell in grid can have independent settings, so even if you have an editable column, you can define specified cells as readonly or checkboxes or some other control, by using this attribute.
column	in case of configuration from XML it represents a column of grid; the value of the tag is used as a column label The attributes are: align - horizontal align of a column (left, right, center; equal to setColAlign); color - color of a column (equal to setColumnColor); format - for "edn" and "calendar" excells format can be specified (equal to calling setNumberFormat or setDateFormat against the related column); will not affect any other column types; id - each column has its ID, it makes sense when you are using movable columns and a column index isn't consistent enough. While grid API uses indexes for all operations, it provides API to convert IDs to indexes and back (getColumnId, getColIndexById); sort - allows setting a sorting type (equal to setColSotring command - str,int,date). If you need to set a custom routine, just set the name of a javascript function which will be used for such a task here. (This name must have more than 4 chars); type - the default column type (equal to setColTypes - ro,ed,co and etc.); width - the width of column in percents or pixels, needs to be specified as a pure int, without ending px. By default is treated as width in pixels, can be switched to percents by using the "setting/colwidth" tag.
colwidth	allows specifying the way of treating column width specified in "column@width". The tag's value can be: px - width will be set as pixels (default value, tag may be skipped in such a case); % - width will be set as percents.
head	an optional tag used for grouping configuration tags (necessary only if you want to define configuration in XML); no attributes are supported
option	allows defining a list of options for excell which can work with such a conception (co, coro, clist, in theory any custom excell which has an inner combo property). The content of the tag is used as an option's label. The "value" attribute is mandatory. The attribute is: value - a value for the related option inside of combobox
param	represents a parameter of the method's call, there must be as many param tags as necessary
row	the tag represents a row of grid, one tag for each row The attributes are: bgColor - the color of a row, it is highly recommended to use this parameter to set color of row (all other ways will override the colors' selection); class - the name of the css class which will be attached to row; id - a unique identificator, a combination of chars/numbers. It shouldn't be set to 0; locked - a row will be locked - switched to the read-only state, can be reverted by JS API; selected - after data loading, a row will be pre-selected; call - makes sense only with the enabled "selected" attribute, if both attributes set to true - a row will be selected and the onSelectRow event will be called; style - a string with css definitions which will be set as a style attribute of the related row.
rows	just the top xml tag, has no special meaning The attributes are: pos - in case of dynamic smart rendering XML is fetched from server by chunks, the "pos" attribute shows in which place of grid the data from incoming XML must be inserted. In most cases (the exceptions are pretty specific) this value will be equal to the url parameter "posStart" (which is automatically added to all calls in SRND mode). The attribute makes sense only in SRND mode and will be ignored in any other one; total - both dynamic paging and dynamic rendering modes require specifying the maximum expected count of rows (paging can work without such a limit, but it will not show all available pages from the start in such a case). As the hardcoding count or rows in javascript code is rare possible, it can be specified directly in XML. If the value is skipped in SRND mode, the total count is set to the size of incoming XML (basically, the dynamic smart rendering switches to the static smart rendering). If the value is skipped in paging mode, the grid will request additional data from server until an empty XML set is returned. The attribute makes no sense in non-dynamic modes.
settings	groups a grid's configuration settings, has no individual meaning
splitat	executes the splitAt command in the required position, functionality is kind of deprecated, and it's recommended to use afterInit/call to initiate splitting
userdata	to store some additional data which doesn't need to be displayed, but may be useful for some client-side operations, userdata sections can be used. The attribute is: name - as there may be many userdata tags per row/grid, they differ by the name attribute. The value of the name attribute is used on the client side to get the related value

Additional XML formats

There are also 2 additional XML formats that are based on columns' ids:

1. "xmlA"
2. "xmlB"

Both formats can be customized in the same manner as the default XML format.

xmlA format

grid.setColumnIds("first,second,third");
grid.load(url,"xmlA");

<rows>
    <row id="some1" first="11" second="12" third="13" />
    <row id="some2" first="21" second="22" third="23" />
    <row id="some3" first="31" second="32" third="33" />
</rows>

xmlB format

grid.setColumnIds("first,second,third");
grid.load(url,"xmlB");

<rows>
    <row id="some1"><first>11</first><second>12</second><third>13</third></row>
    <row id="some2"><first>21</first><second>22</second><third>23</third></row>
    <row id="some3"><first>31</first><second>32</second><third>33</third></row>
</rows>

Related sample:  Different XML formats

JSON format

There are 2 JSON formats you can use for specifying data for dhtmlxGrid

• Basic (dhtmlxGrid-oriented) JSON format.
• Native JSON format.

Basic JSON format

The basic dhtmlxGrid-oriented JSON format allows you to:

• specify data of the grid
• set additional configuration parameters

{
    rows:[
        { id:1, data: ["A Time to Kill", "John Grisham", "100"]},
        { id:2, data: ["Blood and Smoke", "Stephen King", "1000"]},
        { id:3, data: ["The Rainmaker", "John Grisham", "-200"]}
    ]
};

Related sample:  Loading from JSON

Row-related settings

1. id - (string|number) the row's id. Note, that is shouldn't be set to 0.
2. bgColor - (string) the row's color. It's strongly recommended to use this property to set the row's color (all other ways will override selection colors)
3. class - (string) the name of a CSS class that will be applied to the row
4. style - (string) a string with CSS definitions which will be set as the 'style' attribute of the row
5. locked - (boolean) specifies whether the row should be read-only (true) or editable (false)
6. selected -(boolean) selects the row initially
7. call -(boolean) makes sense only with the "selected" property enabled. If both properties set to true - the row will be selected and the onRowSelect event will be called
8. userdata - (object) key-value pairs of row's userdata

In Native JSON format attributes of the extended dhtmlxGrid-oriented JSON format, such as 'style', 'selected' are not available.

{
    rows:[
       {  id:1,
          selected:true,  
          bgColor: "red",
          userdata:{"priority":"normal"},        // sets the row userdata
          data:[
            "A Time to Kill",
            "John Grisham",
            {"value":"100","type":"ed"}          // changes the cell type
          ]
       },
       {  id:2,
          style:"font-weight:bold;", 
          userdata:{"priority":"High","hotdeal":"yes"}, // sets the row userdata
          data:[
            "Blood and Smoke",
            "Stephen King",
            "1000"/
          ]
       }       
]}

Native JSON format

Using the native JSON format you can set just row values and that's it.
Attributes of the extended dhtmlxGrid-oriented JSON format, such as 'style', 'selected', are not available.

{
    "total_count":50000, "pos":0, "data":[
        {   
            "col1": "A Time to Kill",
            "col2": "John Grisham",
            "col3": "100"
        },
        { 
            "col1": "Blood and Smoke",
            "col2": "Stephen King",
            "col3": "1000"
        },
        { 
            "col1": "The Rainmaker",
            "col2": "John Grisham",
            "col3": "-200"
        }
    ]
};

where 'col1', 'col2', 'col3' are colums ids set with the setColumnIds method:

mygrid = new dhtmlXGridObject('gridbox');
mygrid.setColumnIds("col1,col2,col3");

Note, to set the standard JSON format for data call the method load with the value 'js' as the second parameter:

myGrid.load(url,"js");

Dynamic loading

Available properties:

1. total_count - sets the maximum number of rows retrieved from the server. Used in the dynamic "Paging" and dynamic "Smart Rendering" modes only.
   • If ommited in the "Smart Rendering" mode - the total number of rows is set to the size of the incoming data and "Smart Rendering" is switched from the dynamic to the static mode.
   • If ommited in the "Paging" mode - paging will work but won't show all available pages from the start. Also, the grid will request additional data from the server until gets an empty data set.
2. pos - in case of dynamic "Smart Rendering", data is fetched from the server by chunks and the "pos" attribute shows which place of the grid the incoming data must be inserted in. Used in the dynamic "Smart Rendering" mode only
3. userdata - (object) key-value pairs of grid's userdata

{
    total_count:50000,
    pos:0,
    rows:[
        { id:1, data: ["A Time to Kill", "John Grisham", "100"]},
        { id:2, data: ["Blood and Smoke", "Stephen King", "1000"]},
        { id:3, data: ["The Rainmaker", "John Grisham", "-200"]}
    ] 
]}

Related sample:  Configuration from JSON - Common JSON

CSV format

var  csvstr="A Time to Kill,John Grisham,100"
            +"\nBlood and Smoke,Stephen King,1000"
            +"\nThe Rainmaker,John Grisham,-200";
grid.parse(csvstr,"csv");

Setting row/cell delimiter

By default, dhtmlxGrid uses:

• \n - as the row delimiter.
• , - as the cell delimiter.

You can set your custom delimiters using the csv.row and csv.cell properties:

grid.csv.row = "your custom row delimiter";
grid.csv.cell = "your custom cell delimiter";

Note, for backward compatibility you can also use the setCSVDelimiter() method.

Related sample:  Import/export from/to CSV

JSArray format

In the JSArray format, the data source will be presented as in:

var data = [
    ["A Time to Kill","John Grisham","100"],
    ["Blood and Smoke", "Stephen King", "1000"],
    ["The Rainmaker","John Grisham","-200"]
];

Related sample:  Loading from JS array

Custom data format

Generally, to create a custom data format you need to:

1. Set the name for the new format.
2. Define the row-level parser.
3. Define the cell-level parser.

Let's assume, you have data specified as in:

"data.xml" file

<data>
    <item name="my item A">
        <size>15</size>
        <mode step="2" online="off">active</mode>
    </item>
    <item name="my item B">
        <size>15</size>
        <mode step="2" online="on">active</mode>
    </item>
</data>

The format of this data doesn't fit the predefined grid's formats. As a possible solution, you can add XSLT transformation that will convert data to the format acceptable by the grid, but if you have a big dataset -
creating a custom data format is a much more preffered way.

Step 1. Choose a name for your custom data format

The names "xml","xmla","xmlb", "json","js", "jsarray","csv" are already taken, so any other name is valid. For example, you can use "custom_xml" as the name for your new data format.

Step 2. Defining the row-level parser

To specify a new data format you will need to use 3 methods:

1. grid.process[formatName] - the row parser function. Processes the incoming stream of data and separates it in rows.
2. grid.process[formatName]_row - the cell parser method. Takes the data related to rows and processes it.
3. grid.get[formatName]_data - the data locator method. Provides the way to locate the value of a column inside a data row. The method is required only if you are planning to use the smart rendering mode (it locates the data inside unparsed chunks). In other cases, the method shouldn't be used.

The row parser function must separate incoming data in rows and assign IDs to them. As our data source is XML, we will use XPath to parse it into rows:

grid._process_custom_xml=function(xml){
    this._parsing=true;
    var rows = xml.doXPath("//item");       //gets all row elements from XML
    for (var i = 0; i < rows.length; i++){
        var id = this.getUID();      //XML has no native ids,so custom ids are generated
        this.rowsBuffer[i]={                //stores references to each row element
            idd: id,
            data: rows[i],
            _parser: this._process_custom_xml_row, //cell parser method
            _locator: this._get_custom_xml_data    //data locator method
        };
 
        this.rowsAr[id]=rows[i];             //stores id reference
    }
    this.render_dataset();            //forces update of grid's view after data loading
    this._parsing=false;
};

Step 3. Define cell-level parser

The cell parser must take the data for each column of the grid from the row related XML. Before starting to implement cell parser, you need to specify what data will be mapped for which column.

grid._process_custom_xml_row=function(r, xml){
    var size = this.xmlLoader.doXPath("./size", xml)[0]; //gets size sub-tag
    var mode = this.xmlLoader.doXPath("./mode", xml)[0]; //gets mode sub-tag
    var strAr = [  
        xml.getAttribute("name"),
        size.firstChild.data,
        mode.firstChild.data,
        mode.getAttribute("online")
    ];
    //sets just a plain array as no custom attributes are needed
    r._attrs={};
    for (j=0; j < r.childNodes.length; j++) r.childNodes[j]._attrs={};
 
    //finishes data loading 
    this._fillRow(r, strAr);
    return r;
}

That's all. Now you can load data in the custom format as in:

grid.load("data.xml","custom_xml");

Handling error response

Starting from version 4.1, to handle error response from server, use the onLoadXMLError event instead of the dhtmlxError.catchError.

window.dhx.attachEvent("onLoadXMLError", function(request object){
    // your code here
});

Back to top