dhtmlxGrid can be populated with data of 4 formats:
Let's take a collection of books as a data source and present it in different formats.
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");
Please note that the row id shouldn't be set to the 0 value
Related sample: Loading from XML
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:
|
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:
|
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:
|
colwidth | allows specifying the way of treating column width specified in "column@width". The tag's value can be:
|
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:
|
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:
|
rows |
just the top xml tag, has no special meaning The attributes are:
|
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:
|
There are also 2 additional XML formats that are based on columns' ids:
To use "xmla", "xmlb" formats you need to include the "ext/dhtxmlgrid_data.js" file on the page.
Both formats can be customized in the same manner as the default XML 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>
If the user uses MSSQL, he can use SELECT * FROM Some FOR XML AUTO to get data exactly in the necessary 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
There are 2 JSON formats you can use for specifying data for dhtmlxGrid
The basic dhtmlxGrid-oriented JSON format allows you to:
{
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"]}
]
};
Please note that the row id shouldn't be set to the 0 value
Related sample: Loading from JSON
Row-related settings
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"/
]
}
]}
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");
Available properties:
{
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
var csvstr="A Time to Kill,John Grisham,100"
+"\nBlood and Smoke,Stephen King,1000"
+"\nThe Rainmaker,John Grisham,-200";
grid.parse(csvstr,"csv");
By default, dhtmlxGrid uses:
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
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
Generally, to create a custom data format you need to:
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.
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.
To specify a new data format you will need to use 3 methods:
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;
};
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");
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