easyui-datagrid-JavaScript-导出Excel、合并单元格

项目参考：

EasyUI-datagrid-自动合并单元格（转） http://www.cnblogs.com/shenyixin/p/3927688.html

Jquery easyui datagrid 导出Excel（转）http://www.cnblogs.com/weiqt/articles/4022399.html

我自己的项目中需要使用easyui 的datagrid，同时实现合并单元格和excel导出，参考上列两个文档，进行修改后发出来供大家参考。

1、exportExcel.js

function ExporterExcel(dataTable, exportName) {
    //获取Datagride的列  
    var rows = $(dataTable).datagrid('getRows');
    var columns = $(dataTable).datagrid("options").columns[0];
    var oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel   
    var oWB = oXL.Workbooks.Add(); //获取workbook对象   
    var oSheet = oWB.ActiveSheet; //激活当前sheet  
    //设置工作薄名称  
    oSheet.name = exportName;
    //设置表头  
    for (var i = 0; i < columns.length; i++) {
        oSheet.Cells(1, i + 1).value = columns[i].title;
    }
    //设置内容部分  
    for (var i = 0; i < rows.length; i++) {
        //动态获取每一行每一列的数据值  
        for (var j = 0; j < columns.length; j++) {
            oSheet.Cells(i + 2, j + 1).value = rows[i][columns[j].field];
        }
    }
    oXL.Visible = true; //设置excel可见属性  
}
function SaveExcel(dataTable) {
    var data = $(dataTable).datagrid('getExcelXml', { title: 'datagrid import to excel' });
    var url = 'datagrid-to-excel.ashx';
    $.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;
}
/**
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++) {
            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，需要修改动态页文件的写入编码
            '<?mso-application progid="Excel.Sheet"?>'+
            '<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"  xmlns:html="http://www.w3.org/TR/REC-html40">' +
             '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Title>' + param.title + '</Title><Version>15.00</Version></DocumentProperties>' +
            '<ss:ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">' +
            '<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 colOption = [];
        for (var i = 0; i < colCount; i++) {
            if (cfs[i] != '') {
                var w = $(jq).datagrid('getColumnOption', cfs[i]).width;
                //colOption.push($(jq).datagrid('getColumnOption', cfs[i]));
                totalWidthInPixels += w;
                if (cfs[i] === "") {
                    cellType.push("None");
                    cellTypeClass.push("");
                    ++visibleColumnCountReduction;
                }
                else {
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    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++) {
        var fields = cfs;
        var i = 0,
            j = 0,
            temp = {};
        for (i; i < rows.length; i++) {
            var row = rows[i];
            j = 0;
            for (j; j < fields.length; j++) {
                var field = fields[j];
                var option = $(jq).datagrid("getColumnOption", field);

                var tf = temp[field];
                if (!tf) {
                    tf = temp[field] = {};
                    if (option.mergeCell) {
                        temp[field].mergeCell = true;
                    }
                    tf[row[field]] = [i];
                } else {
                    var tfv = tf[row[field]];
                    if (tfv) {
                        tfv.push(i);
                    } else {
                        tfv = tf[row[field]] = [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 = 0; j < fields.length; j++) {
                //if ((cm.getDataIndex(j) != '')
                if (temp[fields[j]].mergeCell) {
                    if (cfs[j] != '') {
                        //var v = r[cm.getDataIndex(j)];
                        var v = r[cfs[j]];
                        if (cellType[k] !== "None") {
                            if (i == temp[fields[j]][r[cfs[j]]][0]) {
                                t += '<ss:Cell ss:Index="' + (j + 1) + '" ss:MergeDown="' + (temp[fields[j]][r[cfs[j]]].length - 1) + '" ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                                if (cellType[k] == 'DateTime') {
                                    t += v.format('Y-m-d');
                                } else {
                                    t += v;
                                }
                                t += '</ss:Data></ss:Cell>';
                            }
                        }
                        k++;
                    }
                } else {
                    if (cfs[j] != '') {
                        //var v = r[cm.getDataIndex(j)];
                        var v = r[cfs[j]];
                        if (cellType[k] !== "None") {
                            t += '<ss:Cell ss:Index="'+(j+1)+'" ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                            if (cellType[k] == 'DateTime') {
                                t += v.format('Y-m-d');
                            } else {
                                t += v;
                            }
                            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 &P of &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>'+
            '<ss:ExcelWorkbook>'+
   '<ss:WindowHeight>9000</ss:WindowHeight>'+
   '<ss:WindowWidth>19340</ss:WindowWidth>'+
   '<ss:ProtectStructure>False</ss:ProtectStructure>'+
   '<ss:ProtectWindows>False</ss:ProtectWindows>'+
  '</ss:ExcelWorkbook>';
        return result;
    }
});
/**
* author ____′↘夏悸
* create date 2012-11-5
*
**/
$.extend($.fn.datagrid.methods, {
    autoMergeCells: function (jq, fields) {
        return jq.each(function () {
            var target = $(this);
            if (!fields) {
                fields = target.datagrid("getColumnFields");
            }
            var rows = target.datagrid("getRows");
            var i = 0,
            j = 0,
            temp = {};
            for (i; i < rows.length; i++) {
                var row = rows[i];
                j = 0;
                for (j; j < fields.length; j++) {
                    var field = fields[j];
                    var option = target.datagrid("getColumnOption", field);

                    var tf = temp[field];
                    if (!tf) {
                        tf = temp[field] = {};
                        if (option.mergeCell) {
                            temp[field].mergeCell = true;
                        }
                        tf[row[field]] = [i];
                    } else {
                        var tfv = tf[row[field]];
                        if (tfv) {
                            tfv.push(i);
                        } else {
                            tfv = tf[row[field]] = [i];
                        }
                    }
                }
            }
            $.each(temp, function (field, colunm) {
                $.each(colunm, function () {
                    var group = this;

                    if (group.length > 1) {
                        if (colunm.mergeCell) {
                            var before,
                        after,
                        megerIndex = group[0];
                            for (var i = 0; i < group.length; i++) {
                                before = group[i];
                                after = group[i + 1];
                                if (after && (after - before) == 1) {
                                    continue;
                                }
                                var rowspan = before - megerIndex + 1;
                                if (rowspan > 1) {
                                    target.datagrid('mergeCells', {
                                        index: megerIndex,
                                        field: field,
                                        rowspan: rowspan
                                    });
                                }
                                if (after && (after - before) != 1) {
                                    megerIndex = after;
                                }
                            } 
                        }
                    }
                });
            });
        });
    }
});

2、asp.net后台代码

<%@ 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) {
        //context.Response.ContentType = "text/plain";
        //context.Response.Write("Hello World");
        string fn = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
        string data = context.Request.Form["data"];
        File.WriteAllText(context.Server.MapPath("~/UploadFiles/"+fn), data, Encoding.UTF8);
        context.Response.Write("../UploadFiles/" + fn);
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

}

3、html页面

<script src="exportExcel.js" type="text/javascript"></script>

<script type="text/javascript">
<span style="white-space:pre">	</span>    var sortFlag = false;
            $('#datagrid').datagrid({
                fitColumns: false, nowarp: false,
                singleSelect: true,
                columns: [[
                    { field: 'CookDate', title: '送餐日期',  mergeCell: true },//需要导出的列添加mergeCell:true属性
                    { field: 'OrderID', title: '订单号', mergeCell:true},
                    { field: 'Address', title: '送餐地点',  mergeCell: true },
                    { field: 'PersonName', title: '顾客',  mergeCell: true },
                    { field: 'Telephone', title: '联系方式',  mergeCell: true },
                    { field: 'Cook', title: '菜品' },
                    { field: 'Count', title: '数量' }
                ]],
                onSortColumn: function (sort, order) {
                    sortFlag = true;
                    if ("OrderID" == sort) {
                        $(this).datagrid("autoMergeCells", [sort]);
                    } else {
                        $(this).datagrid("autoMergeCells");
                    }
                },
                onLoadSuccess: function (data) {
                    if (!sortFlag) $(this).datagrid("autoMergeCells");
                }
            });

</script>

    <table class="easyui-datagrid" id="datagrid" width="100%" rownumbers="true" loadmsg="正在查询..." pagination="false" toolbar="#tb">
        <thead>
        </thead>
    </table>

<div id="tb">      
        <a href="#" class="easyui-linkbutton" iconcls="icon-save" plain="true" οnclick="SaveExcel($('#datagrid'));">保存</a>//导出Excel事件<span style="font-family: Arial, Helvetica, sans-serif;">SaveExcel($('#datagrid'));</span>
    </div>

注意：如果在使用中报“从客户端(exportContent="<xml version="1.0" e...")中检测到有潜在危险的 Request.Form 值。”，需要修改一下webconfig文件

在Web.Config文件中的配置节</system.web>之前加上如下一句配置就可以了
<httpRuntime requestValidationMode="2.0" />

效果如图：请忽略掉无关的内容，谢谢