jqgrid + jsp : Export To Excel not working in IE

Question

I had written the below code to display the data in JQGrid table and using the export to excel option to get the jqgrid loaded data in the excel sheet. But the option of export to excel is working correctly in Chrome and Firfox but not in JQGrid.

 {
var sampleJson = "";
var jsonObj;

function jQGridData(data) {
      sampleJson = data.toString();
      jsonObj = JSON.parse(sampleJson);

$(document).ready(function() {

                  var grid = $("#list");

           $("#list").jqGrid({   

               datatype: "local",
               data: jsonObj.data,
               autoheight:true,
               shrinkToFit: true,              
                colNames:['ID', 'NAME','DESCRIPTION', 'SCORE','STATUS'],
                  colModel:[
                            {name:'column1',index:'column1', sorttype:"text", align:"left",sortable:true,editable:true,width:60},
                              {name:'column2',index:'column2', sorttype:"text",align:"text",editable:true,width:310},
                              {name:'column3',index:'column3',sorttype:"text",align:"text",editable:true,width:600},
                              {name:'column4',index:'column4',  align:"left",sorttype:"text",editable:true,width:90},
                              {name:'column5',index:'column5',  align:"left",sorttype:"text",editable:true,width:100} 
                        ],
             search:true,
                pager:'#pager',
                jsonReader: {cell:""},
                        rowNum: 8,
                        height:"auto",
                        multiselect:true,
                        imgpath: 'css/images/',
                rowList: [8, 16, 24, 32,40],
                sortname: 'id',
                sortorder: 'asc',
                viewrecords: true,
                        loadComplete: function () {                  
                                    ModifyGridDefaultStyles();                  
                        }                
            });
                  $("#grid").jqGrid('setGridParam', {ondblClickRow: function(rowid,iRow,iCol,e){alert('double clicked');}});
           $("#list").jqGrid('navGrid','#pager',{add:false,edit:false,del:false,search:true,refresh:true}
                        ).navButtonAdd('#pager', {
            id:"ExportToExcel",
        caption: "Export to Excel",
        buttonicon: "ui-icon-disk",
        onClickButton: function(){
                  exportGrid();
        },
        position: "last"
    }).navButtonAdd('#pager',{
            id:"email_address",
            caption:"EMAIL ", 
            buttonicon:"ui-icon-mail-closed", 
            onClickButton: function(){ 
            sendMail(); 
      }, 
            position:"last"
      }).navButtonAdd( '#pager', {
    caption: "Filter",
    title: "Toggle Searching Tool bar",
    buttonicon: 'ui-icon-pin-s',
    onClickButton: function () {
        this.toggleToolbar();
        if ($.isFunction(this.p._complete)) {
            this.p._complete.call(this);
            fixPositionsOfFrozenDivs.call(this);
        }
    }
      });
            grid.jqGrid('filterToolbar',{stringResult:true, searchOnEnter:false, defaultSearch:"cn"});
            $grid[0].toggleToolbar();     
            $('#list').closest(".ui-jqgrid-bdiv").css({"overflow-y" : "scroll"});

        });
}     

function sendMail() {
   var link = 'mailto:noReply@a.com?subject=Message from '
             +document.getElementById('email_address').value
             +'&body='+document.getElementById('email_address').value;
                  window.location.href = link;
}
function ModifyGridDefaultStyles() {
   $('#' + "list" + ' tr').removeClass("ui-widget-content");
   $('#' + "list" + ' tr:nth-child(even)').addClass("evenTableRow");
   $('#' + "list" + ' tr:nth-child(odd)').addClass("oddTableRow");
}



function exportGrid()
{
    var grid = "#list";

    var mya=new Array();
    mya=$(grid).getDataIDs();  // Get All IDs

    var data=$(grid).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 columnNames = $(grid).jqGrid('getGridParam','colNames');

    for(i=0;i<columnNames.length-1;i++)
    {
        html = html + columnNames[i+1]+"\t";
    }

    html=html+"\n";

    for(i=0;i<mya.length;i++)
    {
        data=$(grid).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 + "</tr>"; // output each row with end of line
    }
    html = html + "</table></body></html>"; // end of line at the end

        window.open('data:application/vnd.ms-excel,' + $('#grid_table').html());
      e.preventDefault();
      Response.AddHeader("Content-Disposition", "attachment;filename=download.xls");
}
}

Could anyone help in achieving the Export To Excel functionality in IE from JQGrid Option without writing any any controller call for this.

score 0 · Accepted Answer · edited May 23 '17 at 10:27

You can use extra Iframe and use following code as per this post

<iframe id="frame1" style="display:none"></iframe>

var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE "); 
// If Internet Explorer
if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))     
{
frame1.document.open("txt/html","replace");
frame1.document.write(formatted excel data);
frame1.document.close();
frame1.focus();
sa=frame1.document.execCommand("SaveAs",true,"test.xls");
}  
else
{
                       //your chrome, firefox logic
}

OR

I would not recommend using ActiveX but as you need solution without writing server side (controller) code, below hack might help you just for IE. It's not safe and you might have to tweak browser setting for activex.

window.clipboardData.setData("Text", formatted excel data);
var objExcel = new ActiveXObject ("Excel.Application");
objExcel.visible = true; 
var objWorkbook = objExcel.Workbooks.Add;
var objWorksheet = objWorkbook.Worksheets(1);
objWorksheet.Paste;

OR

Use server side logic

Thank you so much for your help @amighty, I used the Iframe code from your post and it was working. and also could you please help is there possibility of setting up styles for the exported data in the excel file? — hari kishan, Jan 29 '16 at 07:23

jqgrid + jsp : Export To Excel not working in IE

1 Answers1