步骤：
一、前台JS取HtmlTable数据，根据设定的分隔符把数据拼接起来
二、后台先把前台传过来的字符串转换成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{ /// /// HtmlTable工具类 /// 提供字符串转换成HtmlTable，HtmlTable转换成DataTable /// public class HtmlTableUtil { #region 变量 /// /// 行分隔 /// public static readonly string rowSeparater = "|||||"; /// /// 列分隔 /// public static readonly string columnSeparater = "@@@@@"; /// /// 值分隔 /// public static readonly string valueSeparater = "$$$$$"; /// /// 空值标识 /// public static readonly string nullFlag = "HtmlTableUtil_NULL_FLAG"; #endregion #region 字符串转换成HtmlTable /// /// 字符串转换成HtmlTable /// 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 /// /// HtmlTable转换成DataTable /// 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{ /// /// Excel工具类 /// 利用NPOI生成Excel /// public class ExcelUtil { #region 生成Excel /// /// 生成Excel /// DataTable必须使用HtmlTableUtil.HtmlTable2DataTable生成 /// 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 }}