帶復雜表頭合并單元格的HtmlTable轉換成DataTable并導出Excel

步驟：

一、前臺JS取HtmlTable數據，根據設定的分隔符把數據拼接起來

<!--導出Excel--><script type="text/javascript">    //導出Excel    function exportExcel() {        var data = "";        $("#divRptTable").find("table").find("tr").each(function () {            var tr = $(this);            tr.find("td").each(function () {                var td = $(this);                var rowspan = td.attr("rowspan") ? td.attr("rowspan") : 1;                var colspan = td.attr("colspan") ? td.attr("colspan") : 1;                data = data + td.text() + "#{valueSeparater}" + rowspan + "#{valueSeparater}" + colspan + "#{columnSeparater}";            });            data = data + "#{rowSeparater}";        });        $("#hidTableData").val(data);        var year = $("select[name='year']").find("option[value='" + $("select[name='year']").val() + "']").text();        var sem = $("select[name='sem']").find("option[value='" + $("select[name='sem']").val() + "']").text();        var scl = $("select[name='school']").find("option[value='" + $("select[name='school']").val() + "']").text();        var grade = $("select[name='grade']").find("option[value='" + $("select[name='grade']").val() + "']").text();        var exam = $("select[name='exam']").find("option[value='" + $("select[name='exam']").val() + "']").text();        $("#hidTitle").val(scl + " " + grade + " " + year + " " + sem + " " + exam);        $("#frm").submit();    }</script>

二、后臺先把前臺傳過來的字符串轉換成HtmlTable實體，然后再轉換成DataTable實體

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Web.UI.HtmlControls;// 2014年4月2日namespace QuaEdu.Controller.Core.Utils{    /// <summary>    /// HtmlTable工具類    /// 提供字符串轉換成HtmlTable，HtmlTable轉換成DataTable    /// </summary>    public class HtmlTableUtil    {        #region 變量        /// <summary>        /// 行分隔        /// </summary>        public static readonly string rowSeparater = "|||||";        /// <summary>        /// 列分隔        /// </summary>        public static readonly string columnSeparater = "@@@@@";        /// <summary>        /// 值分隔        /// </summary>        public static readonly string valueSeparater = "$$$$$";        /// <summary>        /// 空值標識        /// </summary>        public static readonly string nullFlag = "HtmlTableUtil_NULL_FLAG";        #endregion        #region 字符串轉換成HtmlTable        /// <summary>        /// 字符串轉換成HtmlTable        /// </summary>        public static HtmlTable String2HtmlTable(string data)        {            HtmlTable htmlTable = new HtmlTable();            string[] rowArray = data.Split(new string[] { rowSeparater }, StringSplitOptions.RemoveEmptyEntries);            foreach (string row in rowArray)//遍歷行            {                HtmlTableRow htmlTableRow = new HtmlTableRow();                string[] colArray = row.Split(new string[] { columnSeparater }, StringSplitOptions.RemoveEmptyEntries);                foreach (string col in colArray)//遍歷列                {                    HtmlTableCell htmlTableCell = new HtmlTableCell();                    string[] valArr = col.Split(new string[] { valueSeparater }, StringSplitOptions.None);                    string val = valArr[0];                    int rowspan = int.Parse(valArr[1]);                    int colspan = int.Parse(valArr[2]);                    htmlTableCell.InnerText = val;                    htmlTableCell.RowSpan = rowspan;                    htmlTableCell.ColSpan = colspan;                    htmlTableRow.Cells.Add(htmlTableCell);                }                htmlTable.Rows.Add(htmlTableRow);            }            return htmlTable;        }        #endregion        #region HtmlTable轉換成DataTable        /// <summary>        /// HtmlTable轉換成DataTable        /// </summary>        public static DataTable HtmlTable2DataTable(HtmlTable htmlTable)        {            DataTable dataTable = new DataTable();            #region DataTable列數            int colCount = 0;            if (htmlTable.Rows.Count > 0)            {                foreach (HtmlTableCell htmlTableCell in htmlTable.Rows[0].Cells)                {                    colCount += htmlTableCell.ColSpan;                }            }            #endregion            #region DataTable行數            int rowCount = htmlTable.Rows.Count;            #endregion            #region 給DataTable添加列            for (int i = 0; i < colCount; i++)            {                dataTable.Columns.Add();            }            #endregion            #region 給DataTable添加行            for (int i = 0; i < rowCount; i++)//遍歷行            {                DataRow dataRow = dataTable.NewRow();                for (int j = 0; j < colCount; j++)//遍歷列                {                    dataRow[j] = null;                }                dataTable.Rows.Add(dataRow);            }            #endregion            #region 轉換            for (int i = 0; i < htmlTable.Rows.Count; i++)//遍歷HtmlTable行            {                HtmlTableRow htmlTableRow = htmlTable.Rows[i];                int delta = 0;//列增量                for (int j = 0; j < htmlTableRow.Cells.Count; j++)//遍歷HtmlTable列                {                    HtmlTableCell htmlTableCell = htmlTableRow.Cells[j];                    #region 計算delta                    for (int k = j + delta; k < colCount; k++)                    {                        string cellValue = dataTable.Rows[i][k].ToString();                        if (cellValue != null)                        {                            if (cellValue.IndexOf(nullFlag) == 0)                            {                                delta++;                                continue;                            }                        }                        break;                    }                    #endregion                    #region 填充DataTable                    dataTable.Rows[i][j + delta] = htmlTableCell.InnerText + valueSeparater + htmlTableCell.RowSpan + valueSeparater + htmlTableCell.ColSpan;                    if (htmlTableCell.RowSpan > 1 || htmlTableCell.ColSpan > 1)                    {                        for (int m = 0; m < htmlTableCell.RowSpan; m++)                        {                            for (int n = 0; n < htmlTableCell.ColSpan; n++)                            {                                if (!(m == 0 && n == 0))                                {                                    int ii = i + m;                                    int jj = j + delta + n;                                    dataTable.Rows[ii][jj] = nullFlag + valueSeparater + "1" + valueSeparater + "1";                                }                            }                        }                    }                    #endregion                }            }            #endregion            return dataTable;        }        #endregion    }}

三、將DataTable導出到Excel