【转】C# DataTable 导出 Excel 进阶 多行表头、合并单元格、中文文件名乱码
本文原创地址：http://blog.csdn.net/ranbolwb/article/details/8083983 ，转载请保留本行。
本例子是上一篇 DataTable 导出 Excel 的进阶，除了上一篇提到的处理乱码问题，本例还添加了处理多行表头、合并单元格的功能及处理中文文件名乱码问题，应该可以满足日常开发的需要了。
废话不多说了，直接上代码：
[C#] 可以写单独类
1 using System; 2 using System.Collections.Generic; 3 using System.Web; 4 using System.Configuration; 5 using System.Data; 6 using System.Data.Common; 7 using System.Data.OleDb; 8 using System.Web.UI.WebControls; 9 using System.Text.RegularExpressions; 10 11 /// <summary> 12 /// Common 的摘要说明 13 /// 作者：李伟波 14 /// 时间：2012-10-18 15 /// </summary> 16 public class Common 17 { 18 public Common() 19 { 20 // 21 //TODO: 在此处添加构造函数逻辑 22 // 23 } 24 25 /// <summary> 26 /// 描述：把DataTable内容导出excel并返回客户端 27 /// 作者：李伟波 28 /// 时间：2012-10-18 29 /// </summary> 30 /// <param name="dtData"></param> 31 /// <param name="header"></param> 32 /// <param name="fileName"></param> 33 /// <param name="mergeCellNums">要合并的列索引字典 格式：列索引-合并模式(合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项)</param> 34 /// <param name="mergeKey">作为合并项的标记列索引</param> 35 public static void DataTable2Excel(System.Data.DataTable dtData, TableCell[] header, string fileName, Dictionary<int, int> mergeCellNums, int? mergeKey) 36 { 37 System.Web.UI.WebControls.GridView gvExport = null; 38 // 当前对话 39 System.Web.HttpContext curContext = System.Web.HttpContext.Current; 40 // IO用于导出并返回excel文件 41 System.IO.StringWriter strWriter = null; 42 System.Web.UI.HtmlTextWriter htmlWriter = null; 43 44 if (dtData != null) 45 { 46 // 设置编码和附件格式 47 curContext.Response.ContentType = "application/vnd.ms-excel"; 48 curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); 49 curContext.Response.Charset = "gb2312"; 50 if (!string.IsNullOrEmpty(fileName)) 51 { 52 //处理中文名乱码问题 53 fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8); 54 curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + (fileName.ToLower().EndsWith(".xls") ? fileName : fileName + ".xls"))); 55 } 56 // 导出excel文件 57 strWriter = new System.IO.StringWriter(); 58 htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter); 59 60 // 重新定义一个无分页的GridView 61 gvExport = new System.Web.UI.WebControls.GridView(); 62 gvExport.DataSource = dtData.DefaultView; 63 gvExport.AllowPaging = false; 64 //优化导出数据显示，如身份证、12-1等显示异常问题 65 gvExport.RowDataBound += new System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound); 66 67 gvExport.DataBind(); 68 //处理表头 69 if (header != null && header.Length > 0) 70 { 71 gvExport.HeaderRow.Cells.Clear(); 72 gvExport.HeaderRow.Cells.AddRange(header); 73 } 74 //合并单元格 75 if (mergeCellNums != null && mergeCellNums.Count > 0) 76 { 77 foreach (int cellNum in mergeCellNums.Keys) 78 { 79 MergeRows(gvExport, cellNum, mergeCellNums[cellNum], mergeKey); 80 } 81 } 82 83 // 返回客户端 84 gvExport.RenderControl(htmlWriter); 85 curContext.Response.Clear(); 86 curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString()); 87 curContext.Response.End(); 88 } 89 } 90 /// <summary> 91 /// 描述：行绑定事件 92 /// </summary> 93 /// <param name="sender"></param> 94 /// <param name="e"></param> 95 protected static void dgExport_RowDataBound(object sender, GridViewRowEventArgs e) 96 { 97 if (e.Row.RowType == DataControlRowType.DataRow) 98 { 99 foreach (TableCell cell in e.Row.Cells) 100 { 101 //优化导出数据显示，如身份证、12-1等显示异常问题 102 if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$") || Regex.IsMatch(cell.Text.Trim(), @"^\d+[-]\d+$")) 103 { 104 cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@"); 105 } 106 } 107 } 108 } 109 110 /// <summary> 111 /// 描述：合并GridView列中相同的行 112 /// 作者：李伟波 113 /// 时间：2012-10-18 114 /// </summary> 115 /// <param name="gvExport">GridView对象</param> 116 /// <param name="cellNum">需要合并的列</param> 117 /// <param name="mergeMode">合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项</param> 118 /// <param name="mergeKey">作为合并项的标记列索引</param> 119 public static void MergeRows(GridView gvExport, int cellNum, int mergeMode, int? mergeKey) 120 { 121 int i = 0, rowSpanNum = 1; 122 System.Drawing.Color alterColor = System.Drawing.Color.LightGray; 123 while (i < gvExport.Rows.Count - 1) 124 { 125 GridViewRow gvr = gvExport.Rows[i]; 126 for (++i; i < gvExport.Rows.Count; i++) 127 { 128 GridViewRow gvrNext = gvExport.Rows[i]; 129 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()))))) 130 { 131 gvrNext.Cells[cellNum].Visible = false; 132 rowSpanNum++; 133 gvrNext.BackColor = gvr.BackColor; 134 } 135 else 136 { 137 gvr.Cells[cellNum].RowSpan = rowSpanNum; 138 rowSpanNum = 1; 139 //间隔行加底色，便于阅读 140 if (mergeKey.HasValue && cellNum == mergeKey.Value) 141 { 142 if (alterColor == System.Drawing.Color.White) 143 { 144 gvr.BackColor = System.Drawing.Color.LightGray; 145 alterColor = System.Drawing.Color.LightGray; 146 } 147 else 148 { 149 alterColor = System.Drawing.Color.White; 150 } 151 } 152 break; 153 } 154 if (i == gvExport.Rows.Count - 1) 155 { 156 gvr.Cells[cellNum].RowSpan = rowSpanNum; 157 if (mergeKey.HasValue && cellNum == mergeKey.Value) 158 { 159 if (alterColor == System.Drawing.Color.White) 160 gvr.BackColor = System.Drawing.Color.LightGray; 161 } 162 } 163 } 164 } 165 } 166 }
函数调用如下：
[C#]
1 TableCell[] header = new TableCell[29]; 2 for (int i = 0; i < header.Length; i++) 3 { 4 header[i] = new TableHeaderCell(); 5 } 6 header[0].ColumnSpan = 7; 7 header[0].Text = "订单基本信息"; 8 header[1].ColumnSpan = 4; 9 header[1].Text = "收货人信息"; 10 header[2].ColumnSpan = 4; 11 header[2].Text = "快递信息"; 12 header[3].ColumnSpan = 3; 13 header[3].Text = "支付信息"; 14 header[4].ColumnSpan = 6; 15 header[4].Text = "商品信息</th></tr><tr>"; 16 //第二行 17 header[5].Text = "订单编号"; 18 header[6].Text = "订单类型"; 19 header[7].Text = "订单状态"; 20 header[8].Text = "下单时间"; 21 header[9].Text = "支付时间"; 22 header[10].Text = "发货时间"; 23 header[11].Text = "备注"; 24 25 header[12].Text = "收货人姓名"; 26 header[13].Text = "地址"; 27 header[14].Text = "手机号码"; 28 header[15].Text = "配送方式"; 29 30 header[16].Text = "物流公司名称"; 31 header[17].Text = "物流发货单"; 32 header[18].Text = "运费收入"; 33 header[19].Text = "实际配送费"; 34 35 header[20].Text = "订单总额"; 36 header[21].Text = "支付方式"; 37 header[22].Text = "订单支付金额"; 38 39 header[23].Text = "商品编号"; 40 header[24].Text = "商品名称"; 41 header[25].Text = "商品价格"; 42 header[26].Text = "购买数量"; 43 header[27].Text = "商品总金额"; 44 header[28].Text = "优惠金额</th>"; 45 46 DataTable dt = Common.DbHelper.DBClass_GetDataToTable(sqlDHD, sqlParam, ref rMsg); 47 48 Dictionary<int, int> mergeCellNums = new Dictionary<int, int>(); 49 for (int i = 0; i < dt.Columns.Count; i++) 50 { 51 mergeCellNums.Add(i, 2); 52 } 53 Common.DataTable2Excel(dt, header, "数据导出" + DateTime.Now.ToString("yyyyMMdd"), mergeCellNums, 0);
导出效果如下图：
