數據的動態合并和導出至EXCEL

2019-11-14 14:34:44

字體：大中小

來源：轉載

供稿：網友

最近一段時間都在處理數據的動態合并和導出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             // 取出已經排好版的table11             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();