Exporting to Excel is another usefull feature in jqGrid. It is important to note the the generated file in not CSV, but Microsoft Excel XML file. The reason that we do it so, is that in diffrent client machines the variables of type date and datetime and some long numbers are not displayed correct. Using XML file with the appropriate field types, make the file portable to all client machines whitout of loosing the information.

Moreover when a summary rows are used the grid creates a formula and the summary fields are displayed at end of the file.

When this method is used within jqGrid and jqGridEdit classes they should be called separatley in order to perform the export.
When used in jqGridrender class everthing is done automatically when the export to excel is enabled.

Also when used separatley and you want to display diffrent header, width and hide some fields a colmodel array should be configured and passed as parameter to the method. If none is set in this case only column names are used

The array has the following structure and properties

Array( 
    [0]=>Array("label"=>"Some label", "width"=>100, "hidden"=>true, "name"=>"colname"), 
    [1]=>Array(...),
     ...
);

Where

  • label is the header displayed for this field
  • width is the width in pixels
  • hidden (boolean) if set does not export this column
  • name is the name from column model

If set the length of this array should be equal to the number of fields in the SQL command used.

Related Methods

exportToExcel
renderGrid

Related variable(s)

gSQLMaxRows
ExportCommand

Example:
Let suppose that when we export to excel we want to show additionally the ShipAddress and ShipCity and perform a summary on the field Freight. We will use again our example.
For this purpose we will first create a custom button in the navigator and use the build in method for this purpose excelExport. The method is avialble since version 3.6.3 of jqGrid Java Script lib. When used the method passes additinally a variable oper=excel to identify that we want a export to Excel.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP jqGrid Class Example</title> 

<link rel="stylesheet" type="text/css" media="screen" href="themes/redmond/jquery-ui-1.7.1.custom.css" />
<link rel="stylesheet" type="text/css" media="screen" href="themes/ui.jqgrid.css" />

<script src="js/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
jQuery(document).ready(function(){
....
// Craeate the grid manually
jQuery("#grid").jqGrid({
"colModel":[
     {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true},
     {"name":"OrderDate","index":"OrderDate"},
     {"name":"CustomerID","index":"CustomerID"},
     {"name":"Freight","index":"Freight"},
     {"name":"ShipName","index":"ShipName"}
],
"url":"querygrid.php",
"datatype":"json",
"jsonReader":{repeatitems:false},
"pager":"#pager"
});
// Set navigator with search enabled.
jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:false,del:false});

// add custom button to export the data to excel
jQuery("#grid").jqGrid('navButtonAdd','#pager',{
       caption:"", 
       onClickButton : function () { 
           jQuery("#grid").excelExport();
       } 
});
......
});
</script>

</head>
<body>
......
<table id="grid"></table>
<div id="pager"></div>
.......
</body>
</html>

In PHP code we will use ExportCommand to perform the export.

<?php
require_once 'jq-config.php';
// include the jqGrid Class
require_once "php/jqGrid.php";
// include the PDO driver class
require_once "php/jqGridPdo.php";
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);

// Create the jqGrid instance
$grid = new jqGrid($conn);
// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';

// we want to export additinal data when excel
$grid->ExportCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName, ShipAddress, ShipCity FROM orders';

$grid->dataType = "json";

// now we should check whenever a export is lunched
$export = $_POST['oper'];

if($export == 'excel)
    // let set summary field
   $grid->exportToExcel(array('Freight'=>'Freight));
else 
   $grid->queryGrid();
?>