数据的动态合并和导出至EXCEL

最近一段时间都在处理数据的动态合并和导出EXCEL的问题,写个demo记录下,希望和我碰到同样问题的博友可以顺利解决;后面会提供demo下载链接。

(VS2012,ASP.NET)

一、主要解决以下问题:

1、根据业务逻辑动态合并table单元格
2、动态合并后序号重写
3、datatable导出excel
4、datatable导出excel特殊字符处理
5、在实际问题中可能还有少许BUG,自行调试

 

 

二、重要代码摘录:

1、前台获取table HTML代码: 

1 <input type="hidden" runat="server" id="lblTableHtml" />
View Code
1 function getDataHtml() {
2             var table = document.getElementById("gridTable");
3             if (table != null) {
4                 var html = table.innerHTML;
5                 document.getElementById("lblTableHtml").value = html;
6             }
7         }
View Code

 2、自定义合并单元格:

 1 // 1、通过关联字段分组
 2                 for (int i = gridTable.Rows.Count - 1; i > 0; i--)
 3                 {
 4                     HtmlTableCell oCell_previous = gridTable.Rows[i - 1].Cells[4];
 5                     HtmlTableCell oCell = gridTable.Rows[i].Cells[4];
 6                     if (oCell_previous != null && oCell != null)
 7                     {
 8                         if (oCell.InnerText == oCell_previous.InnerText)
 9                         {
10                             for (int j = 4; j <= 7; j++)
11                             {
12                                 HtmlTableCell oCell_previousI = gridTable.Rows[i - 1].Cells[j];
13                                 HtmlTableCell oCellI = gridTable.Rows[i].Cells[j];
14                                 if (oCell_previousI != null && oCellI != null)
15                                 {
16                                     oCell_previousI.RowSpan = (oCell_previousI.RowSpan == -1) ? 1 : oCell_previousI.RowSpan;
17                                     oCellI.RowSpan = (oCellI.RowSpan == -1) ? 1 : oCellI.RowSpan;
18                                 }
19 
20                                 oCellI.Visible = false;
21                                 oCell_previousI.RowSpan += oCellI.RowSpan;
22                             }
23                         }
24                     }
25                 }
View Code

3、导出EXCEL逻辑:

 1 Response.Clear();
 2             Response.ContentType = "application/vnd.ms-excel";
 3             string excelname = sFileName + DateTime.Now.ToString("yyyyMMddHHmmss");
 4             Response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(excelname, System.Text.Encoding.UTF8) + ".xls");
 5             this.EnableViewState = false;
 6             System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
 7             System.Web.UI.HtmlTextWriter writer = new System.Web.UI.HtmlTextWriter(oStringWriter);
 8             System.Text.StringBuilder builder = new System.Text.StringBuilder();
 9             builder.Append("<table border='1'>");
10             // 取出已经排好版的table
11             string sHtml = lblTableHtml.Value;
12             builder.Append(sHtml);
13             builder.Append("</table>");
14             writer.Write(builder.ToString());
15             writer.Close();
16             Response.Write(oStringWriter.ToString());
17             Response.End();
View Code

 

 

三、Demo下载链接:

 https://files.cnblogs.com/files/zhaosx/ExcelExportDemo.zip

posted @ 2015-12-04 17:29  失落の熊熊  阅读(1075)  评论(0编辑  收藏  举报