C# DataTable 导出 Excel 进阶 多行表头、合并单元格、中文

[csharp] 
 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 的摘要说明 
 /// </summary> 
 public class Common 
 { 
     public Common() 
     { 
         // 
         //TODO: 在此处添加构造函数逻辑 
         // 
     } 
  
     /// <summary> 
     /// 描述：把DataTable内容导出excel并返回客户端  
     /// </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列中相同的行 
     /// </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; 
                     } 
                 } 
             } 
         } 
     } 
 }