jQWidgets Forums
jQuery UI Widgets › Forums › Grid › Need to create grid columns dynamically from JSON
This topic contains 6 replies, has 4 voices, and was last updated by SysProfile 8 years, 9 months ago.
-
Author
-
Hi,
I had gone through the examples available in this site. I could find how to load the grid with dynamic values from JSON. But i wanted the column names also to be specified in JSON. So, when the JSON file is read, based on that the grid should be created with as many columns along with it’s values available in JSON. Kindly help me.
Regards,
Shalini S.Hello Shalini S.,
To do this, you should extract the columns information from your JSON and use it to construct the columns array.
Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/Hi Dimitar,
Thanks for your response. Can you please provide me an example where i can use AJAX and Json data to display the columns and it’s values dynamically?
Regards,
Shalini S.Hi Shalini S.,
Here is an example for dynamic creation of columns and rows via JSON:
<!DOCTYPE html><html lang="en"><head> <title id='Description'>This example shows how to create a Grid from JSON data.</title> <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" /> <script type="text/javascript" src="../../scripts/jquery-1.8.2.min.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.columnsresize.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script> <script type="text/javascript" src="../../scripts/gettheme.js"></script> <script type="text/javascript"> $(document).ready(function () { var theme = getTheme(); var json = '[{ "columns": [{ "text": "Name", "datafield": "name", "width": "250" }, { "text": "Beverage Type", "datafield": "type", "width": "250" }, { "text": "Calories", "datafield": "calories", "width": "180" }, { "text": "Total Fat", "datafield": "totalfat", "width": "120" }, { "text": "Protein", "datafield": "protein" }] }, {"rows" : [{"id": "1", "name": "Hot Chocolate", "type": "Chocolate Beverage", "calories": "370", "totalfat": "16g", "protein": "14g"}, {"id": 2, "name": "Peppermint Hot Chocolate", "type": "Chocolate Beverage", "calories": "440", "totalfat": "16g", "protein": "13g"}, {"id": "3", "name": "Salted Caramel Hot Chocolate", "type": "Chocolate Beverage","calories": "450", "totalfat": "16g", "protein": "13g"}]}]'; var obj = $.parseJSON(json); var columns = obj[0].columns; var rows = obj[1].rows; // prepare the data var source = { datatype: "json", datafields: [ { name: 'name', type: 'string' }, { name: 'type', type: 'string' }, { name: 'calories', type: 'int' }, { name: 'totalfat', type: 'string' }, { name: 'protein', type: 'string' } ], id: 'id', localdata: rows }; var dataAdapter = new $.jqx.dataAdapter(source); $("#jqxgrid").jqxGrid( { width: 670, source: dataAdapter, theme: theme, columnsresize: true, columns: columns }); }); </script></head><body class='default'> <div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;"> <div id="jqxgrid"> </div> </div></body></html>
Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/Hi Dimitar,
Thank you so much for your example. It worked as expected.
I have tried this example with JSon input as file. It also works fine. But i have used in a different way. Please tell me if it is fine to use it this way. See the below file:
This example shows how to create a Grid from JSON data.
$(document).ready(function () {
$.getJSON(‘dyna_col.txt’, function(data)
{
var columns = data[0].columns;
var rows = data[1].rows;
// prepare the data
var source =
{
datatype: “json”,
id: ‘id’,
localdata: rows};
var dataAdapter = new $.jqx.dataAdapter(source);$(‘#jqxgrid’).jqxGrid({
width: 670,
autoheight: true,
theme: ‘energyblue’,
rowsheight: 15,
altrows: true,
pageable: true,
sortable: true,
filterable: true,
source: dataAdapter,
pagesize: 5,
columnsresize: true,
columns: columns
});
});});
dyna_col.txt file:
[{ “columns”: [{ “text”: “ID”, “datafield”: “id”, “width”: “100” }, { “text”: “Name”, “datafield”: “name”, “width”: “250” }, { “text”: “Beverage Type”, “datafield”: “type”, “width”: “250” }, { “text”: “Calories”, “datafield”: “calories”, “width”: “180” }, { “text”: “Total Fat”, “datafield”: “totalfat”, “width”: “120” }, { “text”: “Protein”, “datafield”: “protein” } ] },{“rows” : [{“id”: “1”, “name”: “Hot Chocolate”, “type”: “Chocolate Beverage”, “calories”: “370”, “totalfat”: “16g”, “protein”: “14g”}, {“id”: 2, “name”: “Peppermint Hot Chocolate”, “type”: “Chocolate Beverage”, “calories”: “440”, “totalfat”: “16g”, “protein”: “13g”}, {“id”: “3”, “name”: “Salted Caramel Hot Chocolate”, “type”: “Chocolate Beverage”,”calories”: “450”, “totalfat”: “16g”, “protein”: “13g”}]}
]
I have read through this example but I have one question…
When you provide the columns dynamically, how can you call methods because the function in quotes is not valid and without quotes is invalid JSON?
var json = ‘[{
“columns”: [
{“text”: “Name”, “datafield”: “name”, “width”: “250” },
{ “text”: “Beverage Type”, “datafield”: “type”, “width”: “250” },
{ “text”: “Calories”, “datafield”: “calories”, “width”: “180”, “cellsrenderer”: “cellsrenderer”},
… etcI do not speak English, I’ll show you the code with some comments
First I leave the structure of the data to be consulted in MySQL
This table shows the text shown in my development. Ustede should change “_esAR” for which matches your language
CREATE TABLE
cmn_idiomas` (
_id
smallint(5) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘ID’,
_esAR
varchar(2048) NOT NULL DEFAULT ” COMMENT ‘Español (Argentina)’,
PRIMARY KEY (_id
),
UNIQUE KEY_esAR
(_esAR
(255)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Languages’;`
Example_id _esAR 0 1 Es necesario ingresar usuario y contraseña para poder acceder al sistema 2 Interlog - InterSys 3 Operativo 4 El usuario o la contraseña no son correctos
This table is the configuration of the fields to be displayed
CREATE TABLE
cmn_menu_webcfg_fields` (
_dfName
varchar(60) NOT NULL,
_dfText
smallint(5) unsigned NOT NULL COMMENT ‘Description (FOREIGN KEY – cmn_idiomas)’,
_dfType
enum(‘string’,’date’,’number’,’float’,’int’,’bool’) DEFAULT ‘string’ COMMENT ‘Field type’,
_dfWidth
smallint(4) unsigned DEFAULT NULL COMMENT ‘Width of column in pixels’,
_dfColLCR
enum(‘left’,’center’,’right’) DEFAULT ‘left’ COMMENT ‘Text justification column (left, center o right)’,
_dfLCR
enum(‘left’,’center’,’right’) DEFAULT ‘left’ COMMENT ‘Text justification cell (left, center o right)’,
_dfInFormat
varchar(255) NOT NULL DEFAULT ” COMMENT ‘Input format (optional, useful format for dates. For example, “yyyy-MM-dd” from MySQL)’,
_dfOutFormat
varchar(255) NOT NULL DEFAULT ” COMMENT ‘Output format (optional, useful if you need a different format to the American)’,
PRIMARY KEY (_dfName
),
KEY_dfText
(_dfText
) USING BTREE,
KEY_dfName
(_dfName
) USING BTREE,
CONSTRAINTcmn_menu_webcfg_fields_dfText
FOREIGN KEY (_dfText
) REFERENCEScmn_idiomas
(_id
) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;`
Example_dfName _dfText _dfType _dfWidth _dfColLCR _dfLCR _dfInFormat _dfOutFormat cmn_legajos_creacion 352 date 90 left right yyyy-MM-dd dd/MM/yyyy cmn_lst_clientes_desc 7 string 220 left left opr_docrec_fac_monto 192 number 100 left right d2
This table in the database has the configuration report related to specific functions of the column and behavior
CREATE TABLE
cmn_menu_webcfg` (
_id
varchar(30) NOT NULL COMMENT ‘Report ID’,
_dfName
varchar(60) NOT NULL DEFAULT ” COMMENT ‘Field name (FOREIGN KEY – cmn_menu_webcfg_fields)’,
_dfFiltrable
enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Filtrable? (true/false)’,
_dfSorteable
enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Sorteable? (true/false)’,
_dfMenu
enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Context menú ? (true/false)’,
_dfExportable
enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Exportable ? (true/false)’,
_dfTextOpt
varchar(255) DEFAULT NULL COMMENT ‘Only works if _dfTexto is = 0’,
_dfOrder
smallint(3) unsigned NOT NULL COMMENT ‘Column order’,
PRIMARY KEY (_id
,_dfName
),
KEY_dfName
(_dfName
),
KEY_dfOrder
(_dfOrder
) USING BTREE,
CONSTRAINTcmn_menu_webcfg_dfName
FOREIGN KEY (_dfName
) REFERENCEScmn_menu_webcfg_fields
(_dfName
) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Report configuration’;`
Example_id _dfName _dfFiltrable _dfSorteable _dfMenu _dfExportable _dfTextOpt _dfOrder cmn_fup_impo opr_lst_ftp_id false false false false opr_lst_ftp_id 1 cmn_fup_impo opr_carga_fc true true true true 2
The query to the database on PHP
<?php session_start(); function ConnectDB() { $_SESSION["mysqlconn"] = mysqli_connect ($_SESSION["dbHost"], $_SESSION["dbUser"], $_SESSION["dbPass"], $_SESSION["dbName"], $_SESSION["dbPort"]); if ($_SESSION["mysqlconn"] != 0 && $_SESSION["mysqlconn"] != false) { return true; } else { return false; } } function Execute($sql) { ConnectDB(); $resource = $_SESSION["mysqlconn"]; return mysqli_query($resource, $sql); } $menuID = $_POST["menuID"]; $lang = $_SESSION["strMyLang"]; $rnlf = "\r\n"; $query = "SELECT".$rnlf; $query .= "cmn_menu_webcfg._dfName AS cmn_menu_webcfg_dfName,".$rnlf; $query .= "IF(cmn_menu_webcfg_fields._dfText > 0, IF(ISNULL(cmn_menu_webcfg._dfTextOpt), cmn_idiomas._$lang, CONCAT(cmn_idiomas._$lang, cmn_menu_webcfg._dfTextOpt)), cmn_menu_webcfg._dfTextOpt) AS cmn_menu_webcfg_dfText,".$rnlf; $query .= "cmn_menu_webcfg_fields._dfType AS cmn_menu_webcfg_dfType,".$rnlf; $query .= "cmn_menu_webcfg_fields._dfWidth AS cmn_menu_webcfg_dfWidth,".$rnlf; $query .= "cmn_menu_webcfg_fields._dfColLCR AS cmn_menu_webcfg_dfColLCR,".$rnlf; $query .= "cmn_menu_webcfg_fields._dfLCR AS cmn_menu_webcfg_dfLCR,".$rnlf; $query .= "cmn_menu_webcfg_fields._dfInFormat AS cmn_menu_webcfg_dfInFormat,".$rnlf; $query .= "cmn_menu_webcfg_fields._dfOutFormat AS cmn_menu_webcfg_dfOutFormat,".$rnlf; $query .= "cmn_menu_webcfg._dfFiltrable AS cmn_menu_webcfg_dfFiltrable,".$rnlf; $query .= "cmn_menu_webcfg._dfSorteable AS cmn_menu_webcfg_dfSorteable,".$rnlf; $query .= "cmn_menu_webcfg._dfMenu AS cmn_menu_webcfg_dfMenu,".$rnlf; $query .= "cmn_menu_webcfg._dfExportable AS cmn_menu_webcfg_dfExportable".$rnlf; $query .= "FROM".$rnlf; $query .= "cmn_menu_webcfg".$rnlf; $query .= "INNER JOIN cmn_menu_webcfg_fields ON cmn_menu_webcfg._dfName = cmn_menu_webcfg_fields._dfName".$rnlf; $query .= " INNER JOIN cmn_idiomas ON cmn_menu_webcfg_fields._dfText = cmn_idiomas._id".$rnlf; $query .= "WHERE".$rnlf; $query .= "cmn_menu_webcfg._id = '$menuID'".$rnlf; $query .= "ORDER BY".$rnlf; $query .= "cmn_menu_webcfg._dfOrder ASC"; $sql = Execute($query); $rows = array(); while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)) { $rows[] = $row; } $json = json_encode($rows); echo $json; ?>
These variables are used to display icons below
/* GRID - Clip */ var imgClip = "<div style=\"margin-left: 5px; margin-top: 3px; height=16px; width=11px;\"><img src=\"images/attachs.png\" border=\"0\" /></div>"; /* GRID - PostIt */ var imgPostIt = "<div style=\"margin-left: 5px; margin-top: 3px; height=16px; width=16px;\"><img src=\"images/post_it.png\" border=\"0\" /></div>"; // Draw icon on column header var renderer = function (columnfield) { if (columnfield == "opr_lst_ftp_id") { return imgClip; } else if (columnfield == "opr_obs_id") { return imgPostIt; } } // Draw an icon in each cell, if the conditions "if" apply var cellsrenderer = function (row, columnfield, value, defaulthtml, columnproperties) { if (columnfield == "opr_lst_ftp_id") { if (value != " ") { return imgClip; } } else if (columnfield == "opr_obs_id") { if (value == "1") { return imgPostIt; } else { return " "; } } } // Change the background color if the value of each record occurs in the following ranges var cellclassname = function (row, columnfield, value, rowdata) { if (columnfield == "opr_embarque_eta_dias") { if (value < -5) { return 'red'; } else if (value >= -5 && value <= 5) { return 'orange'; } else if (value > 5) { return 'green'; } } } // Here the result is shown in "result" of the query var objColumns = jQuery.parseJSON(result); var aryDataFields = new Array(); for (var i = 0; i < objColumns.length; i++) { aryDataFields.push({ "name": objColumns[i].cmn_menu_webcfg_dfName, "type": objColumns[i].cmn_menu_webcfg_dfType, "format": objColumns[i].cmn_menu_webcfg_dfInFormat }); } var aryColumns = new Array(); for (var i = 0; i < objColumns.length; i++) { aryColumns.push({ "datafield": objColumns[i].cmn_menu_webcfg_dfName, "text": objColumns[i].cmn_menu_webcfg_dfText, "filterable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfFiltrable), "sortable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfSorteable), "menu": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfMenu), "exportable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfExportable), "cellsAlign": objColumns[i].cmn_menu_webcfg_dfLCR, "align": objColumns[i].cmn_menu_webcfg_dfColLCR, "width": objColumns[i].cmn_menu_webcfg_dfWidth, "cellsFormat": objColumns[i].cmn_menu_webcfg_dfOutFormat, "cellsrenderer": cellsrenderer, "cellclassname": cellclassname, "renderer": renderer }); } var source = { localData: queryResult, dataType: "json", dataFields: aryDataFields }; var dataAdapter = new $.jqx.dataAdapter(source, { loadComplete: function () { // Some own code (does not apply to the example) } }); $("#datacontainer").jqxGrid({ width: 800, height: 600, enablebrowserselection: true, showgroupsheader: false, groupable: true, sortable: true, filterable: true, columnsResize: true, autoRowHeight: false, columnsReorder: true, columnsheight: 40, rowsheight: 20, autoShowLoadElement: false, filterMode: "excel", theme: "custom", localization: getLocalization(), source: dataAdapter, columns: aryColumns });
If you have any questions I try to answer with my limitations with the language
-
AuthorPosts
You must be logged in to reply to this topic.