Exporting Specific jqGrid columns to Excel Sheet

Question

I am exporting my grid data to an excel sheet and everything is working awesome. Only thing is I want to include only certain columns. Only the following: id,name,assign date,check date,due date, notes. How to do this? Here is what I have so far:

jQgrid:

$(function(){ 
  $("#list").jqGrid({
    url:'request.php',
    editurl: "jqGridCrud.php",
    datatype: 'xml',
    mtype: 'GET',
    height: 530,
    width: 850,
        scrollOffset:0,



    colNames:['id','Project', 'Assigned To','Assign Date','Check Date','Due Date','Attached','Notes',''],
    colModel :[ 

      {name:'id', index:'id', width:25}, 
      {name:'name', index:'name', width:235, align:'left',editable:true, editoptions:{
            size:60} }, 
      {name:'Assigned to', index:'id_continent', width:55, align:'right',editable:true,edittype:'select', 
      editoptions:{value: "Henry:Henry; Ramon:Ramon; Paul:Paul" },mtype:'POST'  }, 

      {name:'assign date', index:'lastvisit', width:70, align:'right',formatter: 'date',srcformat:'yyyy-mm-dd',newformat: 'm/dd/yy',editable:true, edittype: 'text',mtype:'POST' ,editoptions:{size:10, dataInit:function(elem){$(elem).datepicker({dateFormat:'m/dd/yy'});}}} ,


      {name:'check date', index:'cdate', width:70, align:'right',formatter: 'date',srcformat:'yyyy-mm-dd',newformat: 'm/dd/yy', edittype: 'text',editable:true ,mtype:'POST' ,editoptions:{size:10, dataInit:function(elem){$(elem).datepicker({dateFormat:'m/dd/yy'});}}} ,

      {name:'due date', index:'ddate', width:70, align:'right',formatter: 'date',srcformat:'yyyy-mm-dd',newformat: 'm/dd/yy',editable:true, edittype: 'text',editoptions:{size:10, dataInit:function(elem){$(elem).datepicker({dateFormat:'m/dd/yy'});}}} ,


      {name:'files', index:'email', width:40,align:'center',sortable:false,mtype:'POST', formatter:function(cellvalue, options, rowObject){
            return '<a href="' + cellvalue + '" target="_blank">FILES </a> '
        } },
        {name:'notes', index:'notes', width:100, align:'left',editable:true,edittype:'textarea', editoptions:{
            rows:5,cols:60,maxlength:200} },
        {name:'act',index:'act',width:25 ,align:'left', sortable:false,formatter: "actions",cellattr: function () { return ' title="Delete Project"'; },
formatoptions: {
    keys: true,
    delbutton: true,
    editbutton:false,


    delOptions: {
        url: 'jqGridCrud.php',
        afterShowForm: function ($form) {
    $("#dData", $form.parent()).click();
},
        beforeShowForm: function ($form) {
    $("td.delmsg", $form[0]).html("Do you really want delete the row with <b>id=" +
         $("#list").jqGrid('getGridParam','selrow') + "</b>?");
},
        msg: "Remove Selected Project?",
        bSubmit: "Remove",
        bCancel: "Cancel"
    } 
}}, 


    ],
    pager: '#pager',


    rowNum:20,
    rowList:[20,40,80],
    sortname: 'id',
    sortorder: 'desc',
    viewrecords: true,
    gridview: true,
    caption: 'Pending Assignments',



    ondblClickRow: function(rowid) {

    $(this).jqGrid('viewGridRow', rowid,
                        {width:550,Height:550,recreateForm:true,
                         closeOnEscape:true,reloadAfterSubmit:true, modal:true,mtype:'post',top:350,left: 30});}


            });

$.extend($.jgrid.nav, {delicon: "ui-icon-circle-check", deltext: "Project Complete", 
});

 $("#list").jqGrid("navGrid", "#pager", { add: false, search: false, edit:false, refresh:false }) .navButtonAdd('#pager',{


                                caption:"Export to Excel", 
                                buttonicon:"ui-icon-save", 
                                onClickButton: function(){ 
                                  exportExcel();
                                }, 
                                position:"last"
                            });
  // setup grid print capability. 
    setPrintGrid('list','pager','Print');
    });

Excel Export Function:

  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="";
        var filtered = [ 6, 8];
            for(k=0;k<colNames.length;k++)
            { 
            if (filtered.indexOf(k) >= 0)
            {
            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=$("#list").getRowData(mya[i]); // get each row
            for(j=0;j<colNames.length;j++)
                { 
                if (filtered.indexOf(j) >= 0){
             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='csvExport.php';  // send it to server which will open this contents in excel file
        document.forms[0].target='_blank';
        document.forms[0].submit();
    }

PHP on server:

<?php 

header('Content-type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=current-projects.xls");
header("Pragma: no-cache");

$buffer = $_POST['csvBuffer'];

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

}

?>

Answer 1

Based on your code, you could just modify exportExcel to filter out those columns that you do not want. It could be as simple as an array of indices that you want to filter from the output. If the current value of j or k is filtered, then do not include that column in the header or data output.

For example, to filter the header rows using the indexOf array function:

var filtered = [2, 6, 8];
...
for(k=0;k<colNames.length;k++) {
    if (filtered.indexOf(k) >= 0){
        html=html+colNames[k]+"\t";     // output each Column as tab delimited
    }
    html=html+"\n"; 
}

You can use the same type of code to filter the data itself.

Does that help?