jqGrid - Export to Excel with PHP server

Question

I have setup jqGrid as a replacement for DataTables, but one important piece I'm missing is the ability to Export to Excel. DataTables handles this with html5 and uses Flash as a backup method (I do not wish to use flash at all).

When searching for a way to do this with jqGrid I see several posts that are quite old, but nothing recent. I am using a php server backend with the data sourced from JSON files. I see the paid version of jqGrid has a php version. Is there a method compatible with the free version of jqGrid that I can use to export a real Excel file (not a csv)?

Answer 1

I am using DataTables as well for an internal site I created. I use this script to download the table data to a CSV file and it works great. You may have to change a few things so the Id's and classes match your table, but give this a try.

    // export data function    
$('#exportDataTable').click(function() {
  var titles = [];
  var data = [];
 /* * Get the table headers, this will be CSV headers
   * The count of headers will be CSV string separator */
  $('.dataTable th').each(function() {
    titles.push($(this).text());
  });
/* * Get the actual data, this will contain all the data, in 1 array */
  $('.dataTable td').each(function() {
    data.push($(this).text());
  });
/* * Convert our data to CSV string */
  var CSVString = prepCSVRow(titles, titles.length, '');
  CSVString = prepCSVRow(data, titles.length, CSVString);
/* * Make CSV downloadable*/
  var downloadLink = document.createElement("a");
  var blob = new Blob(["\ufeff", CSVString]);
  var url = URL.createObjectURL(blob);
  downloadLink.href = url;
  downloadLink.download = "data.csv";
/** Actually download CSV */
  document.body.appendChild(downloadLink);
  downloadLink.click();
  document.body.removeChild(downloadLink);
});
/** Convert data array to CSV string
* @param arr {Array} - the actual data
* @param columnCount {Number} - the amount to split the data into columns
* @param initial {String} - initial string to append to CSV string
* return {String} - ready CSV string */
function prepCSVRow(arr, columnCount, initial) {
  var row = ''; // this will hold data
  var delimeter = ','; // data slice separator, in excel it's `;`, in usual CSv it's `,`
  var newLine = '\r\n'; // newline separator for CSV row
/* * Convert [1,2,3,4] into [[1,2], [3,4]] while count is 2
   * @param _arr {Array} - the actual array to split
   * @param _count {Number} - the amount to split
   * return {Array} - splitted array */
  function splitArray(_arr, _count) {
    var splitted = [];
    var result = [];
    _arr.forEach(function(item, idx) {
      if ((idx + 1) % _count === 0) {
        splitted.push('"' + item + '"');
        result.push(splitted);
        splitted = [];
      } else {
        splitted.push('"' + item + '"');
      }
    });
    return result;
  }
  var plainArr = splitArray(arr, columnCount);
  // don't know how to explain this
  // you just have to like follow the code
  // and you understand, it's pretty simple
  // it converts `['a', 'b', 'c']` to `a,b,c` string
  plainArr.forEach(function(arrItem) {
    arrItem.forEach(function(item, idx) {
      row += item + ((idx + 1) === arrItem.length ? '' : delimeter);
    });
    row += newLine;
  });
  return initial + row;
} 
// end export to CSV file

Answer 2

Button for jqGrid

 $grid.jqGrid("navButtonAdd", {
            caption: "Export<br />To Excel",
            buttonicon: "ui-pg-button-text ui-pg-button-icon-over-text fa-file-excel-o",
            title: "Export to Excel",
            onClickButton: function () {
                exportExcel();
            }
        });

HTML for Form Input

       <form id='_export' method="post" action="jqGrid/export_excel.php">
                    <input type="hidden" name="csvBuffer" id="csvBuffer" value="" />
        </form>

JAVASCRIPT for Export to Excel function

function exportExcel()
{
        var keys=[], ii=0, rows="";
                  var ids=$("#list").getDataIDs(); 
                  var row=$("#list").getRowData(ids[0]);     
                  var p = $("#list").jqGrid("getGridParam");
                  var rows="<table><thead><tr>";
                  for (var k in row) {
                    keys[ii++]=k;   
                    rows=rows+'<th>'+p.colNames[ii+1]+'</th>';    
                  }
                  rows=rows+"</tr></thead><tbody>";  
                  for(i=0;i<ids.length;i++) {
                    row=$("#list").getRowData(ids[i]); 
                    rows=rows+"<tr>";
                    for(j=0;j<keys.length;j++){
                        rows=rows+'<td>'+row[keys[j]]+'</td>'; 
                    }
                    rows=rows+"</tr></tbody>"; 
                  }
                  rows=rows+"</table>";
    document.getElementById('csvBuffer').value=rows;
    document.getElementById('_export').submit();
}

PHP for Downloading XLS file

$buffer = $_POST['csvBuffer'];

  // file name for download
  $filename = "baplie_" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

try{
    echo $buffer;
}catch(Exception $e){

}