easyui datagrid数据导出excel文件示例

  2017-2-3更新判断列隐藏(调用过hideColumn),将不会导出隐藏的的列内容。

  2015-9-16更新判断列是否配置过formmater函数,配置则调用formmater函数得到显示的数据。否则导出的数据是原始数据源中的数据,而非formmater后的数据。

 

  2015-9-22更新如果浏览器不支持“data:application/vnd.ms-excel;base64,”协议导出excel文件(如ie8-),改为直接表单提交生成的excel文件内容,而不是用ajax动态发送,这样服务器端不需要生成临时文件,造成服务器端磁盘空间浪费。如果要永久保留,自己可以选择使用ajax或普通表单提交。

 

  easyui datagrid数据导出excel文件示例,首页由于安全问题,是否无法直接在客户端生成excel文件的,需要搭建服务器,将插件生成的excel文件需要的内容发送到动态页,动态页使用io对象将内容写入xls文件来导出。

  首先要导入datagrid的扩展方法,用于将当前的数据生成excel需要的内容。

<script>
        /**
        Jquery easyui datagrid js导出excel
        修改自extgrid导出excel
        * allows for downloading of grid data (store) directly into excel
        * Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
        * converts to Base64, then loads everything into a data URL link.
        *
        * @author Animal <extjs support team>
        *
        */
        $.extend($.fn.datagrid.methods, {
            getExcelXml: function (jq, param) {
                var worksheet = this.createWorksheet(jq, param);
                //alert($(jq).datagrid('getColumnFields'));
                var totalWidth = 0;
                var cfs = $(jq).datagrid('getColumnFields');
               // for (var i = 1; i < cfs.length; i++) {
                for (var i = 0; i < cfs.length; i++) {////////////////////更正这里,会导致第一列丢失,应该从0开始遍历
                    totalWidth += $(jq).datagrid('getColumnOption', cfs[i]).width;
                }
                //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
                return '<?xml version="1.0" encoding="utf-8"?>' +//xml申明有问题,以修正,注意是utf-8编码,如果是gb2312,需要修改动态页文件的写入编码
            '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +
            '<o:DocumentProperties><o:Title>' + param.title + '</o:Title></o:DocumentProperties>' +
            '<ss:ExcelWorkbook>' +
            '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
            '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
            '<ss:ProtectStructure>False</ss:ProtectStructure>' +
            '<ss:ProtectWindows>False</ss:ProtectWindows>' +
            '</ss:ExcelWorkbook>' +
            '<ss:Styles>' +
            '<ss:Style ss:ID="Default">' +
            '<ss:Alignment ss:Vertical="Top"  />' +
            '<ss:Font ss:FontName="arial" ss:Size="10" />' +
            '<ss:Borders>' +
            '<ss:Border  ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
            '<ss:Border  ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
            '<ss:Border  ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
            '<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
            '</ss:Borders>' +
            '<ss:Interior />' +
            '<ss:NumberFormat />' +
            '<ss:Protection />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="title">' +
            '<ss:Borders />' +
            '<ss:Font />' +
            '<ss:Alignment  ss:Vertical="Center" ss:Horizontal="Center" />' +
            '<ss:NumberFormat ss:Format="@" />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="headercell">' +
            '<ss:Font ss:Bold="1" ss:Size="10" />' +
            '<ss:Alignment  ss:Horizontal="Center" />' +
            '<ss:Interior ss:Pattern="Solid"  />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="even">' +
            '<ss:Interior ss:Pattern="Solid"  />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="even" ss:ID="evendate">' +
            '<ss:NumberFormat ss:Format="yyyy-mm-dd" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="even" ss:ID="evenint">' +
            '<ss:NumberFormat ss:Format="0" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
            '<ss:NumberFormat ss:Format="0.00" />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="odd">' +
            '<ss:Interior ss:Pattern="Solid"  />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
            '<ss:NumberFormat ss:Format="yyyy-mm-dd" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
            '<ss:NumberFormat ss:Format="0" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
            '<ss:NumberFormat ss:Format="0.00" />' +
            '</ss:Style>' +
            '</ss:Styles>' +
            worksheet.xml +
            '</ss:Workbook>';
            },
            createWorksheet: function (jq, param) {
                // Calculate cell data types and extra class names which affect formatting
                var cellType = [];
                var cellTypeClass = [];
                //var cm = this.getColumnModel();
                var totalWidthInPixels = 0;
                var colXml = '';
                var headerXml = '';
                var visibleColumnCountReduction = 0;
                var cfs = $(jq).datagrid('getColumnFields');
               var colCount = cfs.length;
               var colFormatter = [];//用来记录列的自定义函数,以便用formmater得到显示的数据,而不是原始数据
            //for (var i = 1; i < colCount; i++) {
            for (var i = 0; i < colCount; i++) {////////////////////更正这里,会导致第一列丢失,应该从0开始遍历
                if (cfs[i] != '') {
                    var cOpts = $(jq).datagrid('getColumnOption', cfs[i]);
                    colFormatter[i] = cOpts.formatter;
                    var w = cOpts.width;
                        totalWidthInPixels += w;

                    if (cfs[i] === "" || cOpts.hidden === true) {///////////////隐藏列不导出
                            cellType.push("None");
                            cellTypeClass.push("");
                            ++visibleColumnCountReduction;
                        }
                        else {
                            colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="130" />';
                            headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + $(jq).datagrid('getColumnOption', cfs[i]).title + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                            cellType.push("String");
                            cellTypeClass.push("");
                        }
                    }
                }
                var visibleColumnCount = cellType.length - visibleColumnCountReduction;
                var result = {
                    height: 9000,
                    width: Math.floor(totalWidthInPixels * 30) + 50
                };
                var rows = $(jq).datagrid('getRows');
                // Generate worksheet header details.
                var t = '<ss:Worksheet ss:Name="' + param.title + '">' +
            '<ss:Names>' +
            '<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + param.title + '\'!R1:R2" />' +
            '</ss:Names>' +
            '<ss:Table x:FullRows="1" x:FullColumns="1"' +
            ' ss:ExpandedColumnCount="' + (visibleColumnCount + 2) +
            '" ss:ExpandedRowCount="' + (rows.length + 2) + '">' +
            colXml +
            '<ss:Row ss:AutoFitHeight="1">' +
            headerXml +
            '</ss:Row>';
                // Generate the data rows from the data in the Store
                //for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
                for (var i = 0, it = rows, l = it.length; i < l; i++) {
                    t += '<ss:Row>';
                    var cellClass = (i & 1) ? 'odd' : 'even';
                    r = it[i];
                    var k = 0;
                   // for (var j = 1; j < colCount; j++) {
                    for (var j = 0; j < colCount; j++) {////////////////////更正这里,会导致第一列丢失,应该从0开始遍历
                        //if ((cm.getDataIndex(j) != '')
                        if (cfs[j] != '') {
                            //var v = r[cm.getDataIndex(j)];
                            var v = r[cfs[j]];
                            if (cellType[k] !== "None") {
                                t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                                if (cellType[k] == 'DateTime') {
                                    t += v.format('Y-m-d');
                                } else {
                                    t += colFormatter[j] ? colFormatter[j](v, r, i) : v;//判断列是否有formatter,有调用formatter得到原始数据
                                }
                                t += '</ss:Data></ss:Cell>';
                            }
                            k++;
                        }
                    }
                    t += '</ss:Row>';
                }
                result.xml = t + '</ss:Table>' +
            '<x:WorksheetOptions>' +
            '<x:PageSetup>' +
            '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
            '<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />' +
            '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
            '</x:PageSetup>' +
            '<x:FitToPage />' +
            '<x:Print>' +
            '<x:PrintErrors>Blank</x:PrintErrors>' +
            '<x:FitWidth>1</x:FitWidth>' +
            '<x:FitHeight>32767</x:FitHeight>' +
            '<x:ValidPrinterInfo />' +
            '<x:VerticalResolution>600</x:VerticalResolution>' +
            '</x:Print>' +
            '<x:Selected />' +
            '<x:DoNotDisplayGridlines />' +
            '<x:ProtectObjects>False</x:ProtectObjects>' +
            '<x:ProtectScenarios>False</x:ProtectScenarios>' +
            '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
                return result;
            }
        });
    </script>

  导出easyui datagrid需要的测试代码,base64.js这文件去这里下载:javascript base64编码解码类库

<div style="margin-bottom:5px" id="tb">
    <a href="#" class="easyui-linkbutton" onclick="return Save_Excel()" iconcls="icon-save" plain="true" title="导出excel文件"></a>
</div>
<table id="dg"></table>
<script src="base64.js"></script>
<script>
    var supportBase64Import = true;
    if (navigator.userAgent.indexOf('Safari') != -1) supportBase64Import = false; //safari直接判断不支持,走ajax支线
    //下面使用IE注释条件,IE8-也走ajax支线。chrome,firefox支持客户端直接导出base64字符串数据
</script>
<!--[if lt IE 9]><script>supportBase64Import = false;</script><![endif]-->
<script>
    function Save_Excel() {//导出Excel文件
        //getExcelXML有一个JSON对象的配置,配置项看了下只有title配置,为excel文档的标题
        var data = $('#dg').datagrid('getExcelXml', { title: 'datagrid import to excel' }); //获取datagrid数据对应的excel需要的xml格式的内容
        if (supportBase64Import) {//客户端支持base64内容导出文件,如chrome,firefox
            window.location = 'data:application/vnd.ms-excel;base64,' + Base64.encode(data);
            return false;
        }
        //不支持用表单提交,不需要生成excel临时文件
        var url = 'datagrid-to-excel.ashx'; //如果为asp注意修改后缀
        var f = $('#importForm');
        if (f.length == 0) f = $('<form id="importForm" method="post" target="_blank" action="' + url + '"><input type="hidden" name="data"/></form>');
        f.find('input').val(data);
        f.submit();//提交表单
        /*
        //用ajax发动到动态页动态写入xls文件中
        $.ajax({
            url: url, data: { data: data }, type: 'POST', dataType: 'text',
            success: function (fn) {
                alert('导出excel成功!');
                window.location = fn; //执行下载操作
            },
            error: function (xhr) {
                alert('动态页有问题\nstatus:' + xhr.status + '\nresponseText:' + xhr.responseText)
            }
        });*/
        return false;
    }
    $(function () {
        $('#dg').datagrid({
            singleSelect: true,
            toolbar: '#tb',
            url: 'product.json',
            fitColumns: true, pagination: true, pageSize: 3,
            title: 'easyui datagrid数据导出excel文件示例',
            width: 400,
            height: 300,
            columns: [[{ field: 'itemid', width: 80, title: 'Item ID' },
                 { field: 'productname', width: 100, editor: 'text', title: 'Product Name' },
                 { field: 'listprice', width: 80, align: 'right', title: 'List Pirce' },
                 { field: 'unitcost', width: 80, align: 'right', title: 'Unit Cost' }]]
        });
    });
</script>

easyui datagrid数据导出excel文件示例

导出的excel文件效果

easyui datagrid数据导出excel文件示例

 

datagrid-to-excel.ashx

<%@ WebHandler Language="C#" Class="datagrid_to_excel" %>
using System;
using System.Web;
using System.IO;
using System.Text;
public class datagrid_to_excel : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        string fn = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
        string data = context.Request.Form["data"];

        //直接输出内容,设置好响应头,客户端即可保存为excel文件
        context.Response.ContentType = "application/excel";
        context.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + fn + "\"");
        context.Response.Write(data);
        /*
        File.WriteAllText(context.Server.MapPath(fn), data, Encoding.UTF8);//如果是gb2312的xml申明,第三个编码参数修改为Encoding.GetEncoding(936)

        context.Response.Write(fn);//返回文件名提供下载*/
    }
    public bool IsReusable {
        get {
            return false;
        }
    }
}

 

datagrid-to-excel.asp

<%
data=request.Form("data")
fn=year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)&".xls"'文件名

Response.ContentType = "application/excel"
Response.AddHeader "Content-Disposition", "attachment;filename=""xxxx.xls"""
Response.Write(data)

''''''''生成临时文件代码
'set fso=server.CreateObject("scripting.filesystemobject")
'set ts=fso.CreateTextFile(server.MapPath(fn),true,true)'如果xml申明为gb2312,第三个参数改为false
'ts.Write(data)
'ts.close
'set ts=nothing
'set fso=nothing
'Response.Write(fn)'多谢网友daoyuanjiao的提示,缺少这句
 %>

 


原创文章,转载请注明出处:easyui datagrid数据导出excel文件示例
评论(8)Web开发网
阅读(4561)喜欢(4)不喜欢(1)easyui