最近一段时间都在处理数据的动态合并和导出EXCEL的问题,写个demo记录下,希望和我碰到同样问题的博友可以顺利解决;后面会提供demo下载链接。
(VS2012,ASP.NET)
一、主要解决以下问题:
1、根据业务逻辑动态合并table单元格
2、动态合并后序号重写
3、datatable导出excel
4、datatable导出excel特殊字符处理
5、在实际问题中可能还有少许BUG,自行调试
二、重要代码摘录:
1、前台获取table HTML代码:
<input type="hidden" runat="server" id="lblTableHtml" />
function getDataHtml() {
var table = document.getElementById("gridTable");
if (table != null) {
var html = table.innerHTML;
document.getElementById("lblTableHtml").value = html;
}
}
2、自定义合并单元格:
// 1、通过关联字段分组
for (int i = gridTable.Rows.Count - ; i > ; i--)
{
HtmlTableCell oCell_previous = gridTable.Rows[i - ].Cells[];
HtmlTableCell oCell = gridTable.Rows[i].Cells[];
if (oCell_previous != null && oCell != null)
{
if (oCell.InnerText == oCell_previous.InnerText)
{
for (int j = ; j <= ; j++)
{
HtmlTableCell oCell_previousI = gridTable.Rows[i - ].Cells[j];
HtmlTableCell oCellI = gridTable.Rows[i].Cells[j];
if (oCell_previousI != null && oCellI != null)
{
oCell_previousI.RowSpan = (oCell_previousI.RowSpan == -) ? : oCell_previousI.RowSpan;
oCellI.RowSpan = (oCellI.RowSpan == -) ? : oCellI.RowSpan;
} oCellI.Visible = false;
oCell_previousI.RowSpan += oCellI.RowSpan;
}
}
}
}
3、导出EXCEL逻辑:
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
string excelname = sFileName + DateTime.Now.ToString("yyyyMMddHHmmss");
Response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(excelname, System.Text.Encoding.UTF8) + ".xls");
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter writer = new System.Web.UI.HtmlTextWriter(oStringWriter);
System.Text.StringBuilder builder = new System.Text.StringBuilder();
builder.Append("<table border='1'>");
// 取出已经排好版的table
string sHtml = lblTableHtml.Value;
builder.Append(sHtml);
builder.Append("</table>");
writer.Write(builder.ToString());
writer.Close();
Response.Write(oStringWriter.ToString());
Response.End();
三、Demo下载链接:
http://files.cnblogs.com/files/zhaosx/ExcelExportDemo.zip