[转] C# DataTable 导出 Excel 进阶 多行表头、合并单元格、中文文件名乱码

using System; 
using System.Collections.Generic; 
using System.Web; 
using System.Configuration; 
using System.Data; 
using System.Data.Common; 
using System.Data.OleDb; 
using System.Web.UI.WebControls; 
using System.Text.RegularExpressions; 
 
/// <summary> 
/// Common 的摘要说明 
/// 作者：李伟波 
/// 时间：2012-10-18 
/// </summary> 
public class Common 
{ 
    public Common() 
    { 
        // 
        //TODO: 在此处添加构造函数逻辑 
        // 
    } 
 
    /// <summary> 
    /// 描述：把DataTable内容导出excel并返回客户端  
    /// 作者：李伟波 
    /// 时间：2012-10-18 
    /// </summary> 
    /// <param name="dtData"></param> 
    /// <param name="header"></param> 
    /// <param name="fileName"></param> 
    /// <param name="mergeCellNums">要合并的列索引字典 格式：列索引-合并模式(合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项)</param> 
    /// <param name="mergeKey">作为合并项的标记列索引</param> 
    public static void DataTable2Excel(System.Data.DataTable dtData, TableCell[] header, string fileName, Dictionary<int, int> mergeCellNums, int? mergeKey) 
    { 
        System.Web.UI.WebControls.GridView gvExport = null; 
        // 当前对话  
        System.Web.HttpContext curContext = System.Web.HttpContext.Current; 
        // IO用于导出并返回excel文件  
        System.IO.StringWriter strWriter = null; 
        System.Web.UI.HtmlTextWriter htmlWriter = null; 
 
        if (dtData != null) 
        { 
            // 设置编码和附件格式  
            curContext.Response.ContentType = "application/vnd.ms-excel"; 
            curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); 
            curContext.Response.Charset = "gb2312"; 
            if (!string.IsNullOrEmpty(fileName)) 
            { 
                //处理中文名乱码问题 
                fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8); 
                curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + (fileName.ToLower().EndsWith(".xls") ? fileName : fileName + ".xls"))); 
            } 
            // 导出excel文件  
            strWriter = new System.IO.StringWriter(); 
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter); 
 
            // 重新定义一个无分页的GridView  
            gvExport = new System.Web.UI.WebControls.GridView(); 
            gvExport.DataSource = dtData.DefaultView; 
            gvExport.AllowPaging = false; 
            //优化导出数据显示，如身份证、12-1等显示异常问题 
            gvExport.RowDataBound += new System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound); 
 
            gvExport.DataBind(); 
            //处理表头 
            if (header != null && header.Length > 0) 
            { 
                gvExport.HeaderRow.Cells.Clear(); 
                gvExport.HeaderRow.Cells.AddRange(header); 
            } 
            //合并单元格 
            if (mergeCellNums != null && mergeCellNums.Count > 0) 
            { 
                foreach (int cellNum in mergeCellNums.Keys) 
                { 
                    MergeRows(gvExport, cellNum, mergeCellNums[cellNum], mergeKey); 
                } 
            } 
 
            // 返回客户端  
            gvExport.RenderControl(htmlWriter); 
            curContext.Response.Clear(); 
            curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString()); 
            curContext.Response.End(); 
        } 
    } 
    /// <summary> 
    /// 描述：行绑定事件 
    /// </summary> 
    /// <param name="sender"></param> 
    /// <param name="e"></param> 
    protected static void dgExport_RowDataBound(object sender, GridViewRowEventArgs e) 
    { 
        if (e.Row.RowType == DataControlRowType.DataRow) 
        { 
            foreach (TableCell cell in e.Row.Cells) 
            { 
                //优化导出数据显示，如身份证、12-1等显示异常问题 
                if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$") || Regex.IsMatch(cell.Text.Trim(), @"^\d+[-]\d+$")) 
                { 
                    cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@"); 
                } 
            } 
        } 
    } 
 
    /// <summary>    
    /// 描述：合并GridView列中相同的行 
    /// 作者：李伟波 
    /// 时间：2012-10-18 
    /// </summary>    
    /// <param   name="gvExport">GridView对象</param>    
    /// <param   name="cellNum">需要合并的列</param>    
    /// <param name="mergeMode">合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项</param> 
    /// <param name="mergeKey">作为合并项的标记列索引</param> 
    public static void MergeRows(GridView gvExport, int cellNum, int mergeMode, int? mergeKey) 
    { 
        int i = 0, rowSpanNum = 1; 
        System.Drawing.Color alterColor = System.Drawing.Color.LightGray; 
        while (i < gvExport.Rows.Count - 1) 
        { 
            GridViewRow gvr = gvExport.Rows[i]; 
            for (++i; i < gvExport.Rows.Count; i++) 
            { 
                GridViewRow gvrNext = gvExport.Rows[i]; 
                if ((!mergeKey.HasValue || (mergeKey.HasValue && (gvr.Cells[mergeKey.Value].Text.Equals(gvrNext.Cells[mergeKey.Value].Text) || " ".Equals(gvrNext.Cells[mergeKey.Value].Text)))) && ((mergeMode == 1 && gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text) || (mergeMode == 2 && " ".Equals(gvrNext.Cells[cellNum].Text.Trim())) || (mergeMode == 3 && (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text || " ".Equals(gvrNext.Cells[cellNum].Text.Trim()))))) 
                { 
                    gvrNext.Cells[cellNum].Visible = false; 
                    rowSpanNum++; 
                    gvrNext.BackColor = gvr.BackColor; 
                } 
                else 
                { 
                    gvr.Cells[cellNum].RowSpan = rowSpanNum; 
                    rowSpanNum = 1; 
                    //间隔行加底色，便于阅读 
                    if (mergeKey.HasValue && cellNum == mergeKey.Value) 
                    { 
                        if (alterColor == System.Drawing.Color.White) 
                        { 
                            gvr.BackColor = System.Drawing.Color.LightGray; 
                            alterColor = System.Drawing.Color.LightGray; 
                        } 
                        else 
                        { 
                            alterColor = System.Drawing.Color.White; 
                        } 
                    } 
                    break; 
                } 
                if (i == gvExport.Rows.Count - 1) 
                { 
                    gvr.Cells[cellNum].RowSpan = rowSpanNum; 
                    if (mergeKey.HasValue && cellNum == mergeKey.Value) 
                    { 
                        if (alterColor == System.Drawing.Color.White) 
                            gvr.BackColor = System.Drawing.Color.LightGray; 
                    } 
                } 
            } 
        } 
    } 
} 

页面调用如下：
[html] 
TableCell[] header = new TableCell[29]; 
for (int i = 0; i < header.Length; i++) 
{ 
    header[i] = new TableHeaderCell(); 
} 
header[0].ColumnSpan = 7; 
header[0].Text = "订单基本信息"; 
header[1].ColumnSpan = 4; 
header[1].Text = "收货人信息"; 
header[2].ColumnSpan = 4; 
header[2].Text = "快递信息"; 
header[3].ColumnSpan = 3; 
header[3].Text = "支付信息"; 
header[4].ColumnSpan = 6; 
header[4].Text = "商品信息</th></tr><tr>"; 
//第二行 
header[5].Text = "订单编号"; 
header[6].Text = "订单类型"; 
header[7].Text = "订单状态"; 
header[8].Text = "下单时间"; 
header[9].Text = "支付时间"; 
header[10].Text = "发货时间"; 
header[11].Text = "备注"; 
 
header[12].Text = "收货人姓名"; 
header[13].Text = "地址"; 
header[14].Text = "手机号码"; 
header[15].Text = "配送方式"; 
 
header[16].Text = "物流公司名称"; 
header[17].Text = "物流发货单"; 
header[18].Text = "运费收入"; 
header[19].Text = "实际配送费"; 
 
header[20].Text = "订单总额"; 
header[21].Text = "支付方式"; 
header[22].Text = "订单支付金额"; 
 
header[23].Text = "商品编号"; 
header[24].Text = "商品名称"; 
header[25].Text = "商品价格"; 
header[26].Text = "购买数量"; 
header[27].Text = "商品总金额"; 
header[28].Text = "优惠金额</th>"; 
 
DataTable dt = Common.DbHelper.DBClass_GetDataToTable(sqlDHD, sqlParam, ref rMsg); 
 
Dictionary<int, int> mergeCellNums = new Dictionary<int, int>(); 
for (int i = 0; i < dt.Columns.Count; i++) 
{ 
    mergeCellNums.Add(i, 2); 
} 
Common.DataTable2Excel(dt, header, "数据导出" + DateTime.Now.ToString("yyyyMMdd"), mergeCellNums, 0); 

以上代码未经严格测试，或有错漏，请引用或使用本文代码的诸君注意。
导出效果如下图：

转载于:https://www.cnblogs.com/longdexinoy/archive/2013/01/24/2874527.html