首页 > 代码库 > 【转】C# DataTable 导出 Excel 进阶 多行表头、合并单元格、中文文件名乱码
【转】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-18114 /// </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 else136 {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 else148 {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);
导出效果如下图：
【转】C# DataTable 导出 Excel 进阶 多行表头、合并单元格、中文文件名乱码
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。