Php + jqgrid + export to excel

Question

Does anyone know a way to export data from jqgrid to excel?

I want to make a report using this jqgrid, which I think is awsome. But I need to somehow save or print this report, because the information must be stored. Does anyone know some way?

Answer 1

This is my approach, just add this code to your js / html file

$("#list").jqGrid('navGrid', '#pager',{view:true, del:false, add:false, edit:false, excel:true}) .navButtonAdd('#pager',{ caption:"Export to Excel", buttonicon:"ui-icon-save", onClickButton: function(){ exportExcel(); }, position:"last" }); function exportExcel() { var mya=new Array(); mya=$("#list").getDataIDs(); // Get All IDs var data=$("#list").getRowData(mya[0]); // Get First row to get the labels var colNames=new Array(); var ii=0; for (var i in data){colNames[ii++]=i;} // capture col names var html=""; for(i=0;i<mya.length;i++) { data=$("#list").getRowData(mya[i]); // get each row for(j=0;j<colNames.length;j++) { html=html+data[colNames[j]]+"\t"; // output each column as tab delimited } html=html+"\n"; // output each row with end of line } html=html+"\n"; // end of line at the end document.forms[0].csvBuffer.value=html; document.forms[0].method='POST'; document.forms[0].action='csvExport.php'; // send it to server which will open this contents in excel file document.forms[0].target='_blank'; document.forms[0].submit(); } 

Php script

 header('Content-type: application/vnd.ms-excel'); header("Content-Disposition: attachment; filename=file.xls"); header("Pragma: no-cache"); $buffer = $_POST['csvBuffer']; try{ echo $buffer; }catch(Exception $e){ } 

Answer 2

Very good question, I also scratched my head about it. I did this by selecting Felix's suggestion, let me fill it out by adding the following lines to your html body.

 <form method="post" action="csvExport.php"> <input type="hidden" name="csvBuffer" id="csvBuffer" value="" /> </form> 

The only problem I encountered is the exported excel file does not include my column names in jqgrid, is there also a way to exclude a specific or multiple columns when exporting to excel file?

thanks ~

Answer 3

Great feature!
I have made changes.

 function exportExcel ($ id) {
   var keys = [], ii = 0, rows = "";
   var ids = $ id.getDataIDs ();  // Get All IDs
   var row = $ id.getRowData (ids [0]);  // Get First row to get the labels
   for (var k in row) {
     keys [ii ++] = k;  // capture col names
     rows = rows + k + "\ t";  // output each Column as tab delimited
   }
   rows = rows + "\ n";  // Output header with end of line
   for (i = 0; i <ids.length; i ++) {
     row = $ id.getRowData (ids [i]);  // get each row
     for (j = 0; j <keys.length; j ++) rows = rows + row [keys [j]] + "\ t";  // output each Row as tab delimited
     rows = rows + "\ n";  // output each row with end of line
   }
   rows = rows + "\ n";  // end of line at the end
   var form = "<form name = 'csvexportform' action = '" + php_path + "csvexport.php' method = 'post'>";
   form = form + "<input type = 'hidden' name = 'csvBuffer' value = '" + rows + "'>";
   form = form + "</form> <script> document.csvexportform.submit (); </ sc" + "ript>";
   OpenWindow = window.open ('', '');
   OpenWindow.document.write (form);
   OpenWindow.document.close ();
 }

 function gridcsvexport (id) {
   $ ('#' + id) .jqGrid ('navButtonAdd', '#' + id + '_ pager', {
     caption: '',
     title: 'export',
     buttonicon: 'ui-icon-newwin',
     position: 'last',
     onClickButton: function () {
       exportExcel ($ (this));
     }
   });
 }

Answer 4

I solved your problem. Now I can export excel data with column names, please write my code.

 function exportExcel() { var mya=new Array(); mya=$("#tblnoupdate").getDataIDs(); // Get All IDs var data=$("#tblnoupdate").getRowData(mya[0]); // Get First row to get the labels var colNames=new Array(); var ii=0; for (var i in data){colNames[ii++]=i;} // capture col names var html=""; for(k=0;k<colNames.length;k++) { html=html+colNames[k]+"\t"; // output each Column as tab delimited } html=html+"\n"; // Output header with end of line for(i=0;i<mya.length;i++) { data=$("#tblnoupdate").getRowData(mya[i]); // get each row for(j=0;j<colNames.length;j++) { html=html+data[colNames[j]]+"\t"; // output each Row as tab delimited } html=html+"\n"; // output each row with end of line } html=html+"\n"; // end of line at the end document.forms[0].csvBuffer.value=html; document.forms[0].method='POST'; document.forms[0].action='<?php echo $baseurl;?>csvexport.php'; // send it to server which will open this contents in excel file document.forms[0].target='_blank'; document.forms[0].submit(); } 

Please let me know if you have any problems.

Answer 5

Here is a smart solution to save jqGrid data as an excel sheet without calling the php script: (you just need to call this function using the GridID and optional Filename )

 var createExcelFromGrid = function(gridID,filename) { var grid = $('#' + gridID); var rowIDList = grid.getDataIDs(); var row = grid.getRowData(rowIDList[0]); var colNames = []; var i = 0; for(var cName in row) { colNames[i++] = cName; // Capture Column Names } var html = ""; for(var j=0;j<rowIDList.length;j++) { row = grid.getRowData(rowIDList[j]); // Get Each Row for(var i = 0 ; i<colNames.length ; i++ ) { html += row[colNames[i]] + ';'; // Create a CSV delimited with ; } html += '\n'; } html += '\n'; var a = document.createElement('a'); a.id = 'ExcelDL'; a.href = 'data:application/vnd.ms-excel,' + html; a.download = filename ? filename + ".xls" : 'DataList.xls'; document.body.appendChild(a); a.click(); // Downloads the excel document document.getElementById('ExcelDL').remove(); } 

First, create a CSV string labeled ; . Then an anchor tag with specific attributes is created. Finally click is called on a to load the file.

You can take a look at a few examples of MIME types: List of MIME types

Answer 6

create a form and a hidden element named "csvBuffer". This item is set by the function. I had to change the line

 html = html+"\n" 

to

 html = html+"\\n" 

to avoid this.

Answer 7

see this link showing how to export jqgrid for excel sheet: http://www.trirand.com/blog/phpjqgrid/examples/export/excel/default.php