jQWidgets Forums

jQuery UI Widgets Forums Grid Need to create grid columns dynamically from JSON

Tagged: , , ,

This topic contains 6 replies, has 4 voices, and was last updated by  SysProfile 8 years, 9 months ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author

  • shalini
    Member

    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.


    Dimitar
    Participant

    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,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    shalini
    Member

    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.


    Dimitar
    Participant

    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,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    shalini
    Member

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

    ]


    bmarques3
    Participant

    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”},
    … etc


    SysProfile
    Participant

    I 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 TABLEcmn_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 TABLEcmn_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,
    CONSTRAINT cmn_menu_webcfg_fields_dfText FOREIGN KEY (_dfText) REFERENCES cmn_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 TABLEcmn_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,
    CONSTRAINT cmn_menu_webcfg_dfName FOREIGN KEY (_dfName) REFERENCES cmn_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 != "&nbsp;") {
          return imgClip;
        }
      } else if (columnfield == "opr_obs_id") {
        if (value == "1") {
          return imgPostIt;
        } else {
          return "&nbsp;";
        }
      }
    }
    
    // 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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.