带复杂表头合并单元格的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

 

using System;
using System.Collections.Generic;
using System.Text;
using NPOI.HSSF.UserModel;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using QuaEdu.Helper;
// 2014年4月2日
namespace QuaEdu.Controller.Core.Utils
{
    /// <summary>
    /// Excel工具类
    /// 利用NPOI生成Excel
    /// </summary>
    public class ExcelUtil
    {
        #region 生成Excel
        /// <summary>
        /// 生成Excel
        /// DataTable必须使用HtmlTableUtil.HtmlTable2DataTable生成
        /// </summary>
        public static void CreateExcel(DataTable dataTable， string path)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = string.IsNullOrEmpty(dataTable.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dataTable.TableName);//创建工作表
            #region 标题
            if (!string.IsNullOrEmpty(dataTable.TableName))
            {
                dataTable.Rows.InsertAt(dataTable.NewRow()， 0);
                if (dataTable.Columns.Count > 0)
                {
                    dataTable.Rows[0][0] = dataTable.TableName + HtmlTableUtil.valueSeparater + "1" + HtmlTableUtil.valueSeparater + dataTable.Columns.Count;
                }
                for (int i = 1; i < dataTable.Columns.Count; i++)
                {
                    dataTable.Rows[0][i] = HtmlTableUtil.nullFlag + HtmlTableUtil.valueSeparater + "1" + HtmlTableUtil.valueSeparater + "1";
                }
            }
            #endregion
            #region 填充数据
            for (int i = 0; i < dataTable.Rows.Count; i++)//遍历DataTable行
            {
                DataRow dataRow = dataTable.Rows[i];
                IRow row = sheet.CreateRow(i);//在工作表中添加一行
                row.HeightInPoints = 20;
                for (int j = 0; j < dataTable.Columns.Count; j++)//遍历DataTable列
                {
                    string cellValue = dataRow[j].ToString();
                    ICell cell = row.CreateCell(j);//在行中添加一列
                    ICellStyle cellStyle = workbook.CreateCellStyle();
                    cellStyle.BorderBottom = BorderStyle.Thin;
                    cellStyle.BorderLeft = BorderStyle.Thin;
                    cellStyle.BorderRight = BorderStyle.Thin;
                    cellStyle.BorderTop = BorderStyle.Thin;
                    cellStyle.VerticalAlignment = VerticalAlignment.Top;
                    cell.CellStyle = cellStyle;
                    string[] valArr = cellValue.Split(new string[] { HtmlTableUtil.valueSeparater }， StringSplitOptions.None);
                    if (valArr[0] != HtmlTableUtil.nullFlag)
                    {
                        double d;
                        if (double.TryParse(valArr[0]， out d))
                        {
                            cell.SetCellType(CellType.Numeric);
                            cell.SetCellValue(d);//设置列的内容              
                        }
                        else
                        {
                            cell.SetCellValue(valArr[0]);//设置列的内容
                            if (i != 0) cellStyle.Alignment = HorizontalAlignment.Center;
                        }
                        if (i != 0)
                        {
                            int unitWidth = 400;
                            int oldWidth = sheet.GetColumnWidth(j);
                            int width = (int)Math.Round(CommonHelper.GetWidthUnitCount(valArr[0])) * unitWidth;
                            width = width > unitWidth * 30 ? unitWidth * 30 : width;
                            width = width < unitWidth * 2 ? unitWidth * 2 : width;
                            if (width > oldWidth)
                            {
                                sheet.SetColumnWidth(j， width);
                            }
                        }
                    }
                }
            }
            #endregion
            #region 合并单元格
            for (int i = 0; i < dataTable.Rows.Count; i++)//遍历DataTable行
            {
                DataRow dataRow = dataTable.Rows[i];
                for (int j = 0; j < dataTable.Columns.Count; j++)//遍历DataTable列
                {
                    string cellValue = dataRow[j].ToString();
                    string[] valArr = cellValue.Split(new string[] { HtmlTableUtil.valueSeparater }， StringSplitOptions.None);
                    int rowSpan = int.Parse(valArr[1]);
                    int colSpan = int.Parse(valArr[2]);
                    if (rowSpan > 1 || colSpan > 1)
                    {
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i， i + rowSpan - 1， j， j + colSpan - 1));
                    }
                }
            }
            #endregion
            #region 输出到Excel
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            using (FileStream fs = new FileStream(path， FileMode.Create， FileAccess.Write))
            {
                byte[] bArr = ms.ToArray();
                fs.Write(bArr， 0， bArr.Length);
                fs.Flush();
            }
            #endregion
        }
        #endregion
    }
}