php + jqgrid + 导出到excel

php + jqgrid + export to excel

提问by Paulo

Somebody knows a way to export the data from a jqgrid to excel?

有人知道一种将数据从 jqgrid 导出到 excel 的方法吗？

I want to do a report using this jqgrid that i think is awsome. But i need to save or print this report somehow, because is information to be keeped. Somebody knows any way??

我想使用这个我认为很棒的 jqgrid 做一个报告。但是我需要以某种方式保存或打印此报告，因为要保留信息。有人知道有什么办法吗？？

回答by Felix

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

这是我的方法，只需将此代码添加到您的 js/html 文件中

$("#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

PHP脚本

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){

}

回答by Patrick

very good question, i was scratching my head off about this as well. I made it by choosing Felix's suggestion, let me complete it by adding following lines to your html body.

很好的问题，我也对这个问题嗤之以鼻。我通过选择 Felix 的建议来完成它，让我通过在您的 html 正文中添加以下几行来完成它。

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

The only problem i have is the excel file exported doesnt include my column names in jqgrid, also is there a way to exclude a particular or several columns when exporting to excel file?

我唯一的问题是导出的 excel 文件在 jqgrid 中不包含我的列名，还有没有办法在导出到 excel 文件时排除特定或几列？

thank you ~

谢谢~

回答by wagner

Great function!
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));
    }
  });
}

回答by Suhail Gupta

Here is a clever solution to save the jqGriddata as excel sheet without calling the phpscript: (You just need to call this function with GridIDand an optional Filename)

这是一个聪明的解决方案，可以在jqGrid不调用php脚本的情况下将数据保存为 excel 表：（您只需要使用GridID和一个可选的调用此函数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();
}

We first create a CSVstring delimited with ;. Then an anchortag is created with certain attributes. Finally clickis called on ato download the file.

我们首先创建一个CSV以;. 然后anchor创建具有某些属性的标签。最后click被调用a来下载文件。

You could have a look at several excel MIME Types : MIME Type List

您可以查看几个 excel MIME 类型：MIME 类型列表

回答by Vijay

I solved your problem .and now iam able to export data excel with column names please refer my code.

我解决了你的问题。现在我可以用列名导出数据 excel 请参考我的代码。

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 face any problem.

如果您遇到任何问题，请告诉我。

回答by Antonia

create a form and a hidden element with the name "csvBuffer". This element gets set by the function. I had to change the line

创建一个表单和一个名为“csvBuffer”的隐藏元素。该元素由函数设置。我不得不改变线路

html = html+"\n"

to

到

html = html+"\n"

in order to escape it properly.

以便正确逃脱。