步骤：
一、前台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[];
int rowspan = int.Parse(valArr[]);
int colspan = int.Parse(valArr[]); 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 = ;
if (htmlTable.Rows.Count > )
{
foreach (HtmlTableCell htmlTableCell in htmlTable.Rows[].Cells)
{
colCount += htmlTableCell.ColSpan;
}
}
#endregion #region DataTable行数
int rowCount = htmlTable.Rows.Count;
#endregion #region 给DataTable添加列
for (int i = ; i < colCount; i++)
{
dataTable.Columns.Add();
}
#endregion #region 给DataTable添加行
for (int i = ; i < rowCount; i++)//遍历行
{
DataRow dataRow = dataTable.NewRow();
for (int j = ; j < colCount; j++)//遍历列
{
dataRow[j] = null;
}
dataTable.Rows.Add(dataRow);
}
#endregion #region 转换
for (int i = ; i < htmlTable.Rows.Count; i++)//遍历HtmlTable行
{
HtmlTableRow htmlTableRow = htmlTable.Rows[i];
int delta = ;//列增量 for (int j = ; 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) == )
{
delta++;
continue;
}
}
break;
}
#endregion #region 填充DataTable
dataTable.Rows[i][j + delta] = htmlTableCell.InnerText + valueSeparater + htmlTableCell.RowSpan + valueSeparater + htmlTableCell.ColSpan;
if (htmlTableCell.RowSpan > || htmlTableCell.ColSpan > )
{
for (int m = ; m < htmlTableCell.RowSpan; m++)
{
for (int n = ; n < htmlTableCell.ColSpan; n++)
{
if (!(m == && n == ))
{
int ii = i + m;
int jj = j + delta + n;
dataTable.Rows[ii][jj] = nullFlag + valueSeparater + "" + valueSeparater + "";
}
}
}
}
#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(), );
if (dataTable.Columns.Count > )
{
dataTable.Rows[][] = dataTable.TableName + HtmlTableUtil.valueSeparater + "" + HtmlTableUtil.valueSeparater + dataTable.Columns.Count;
}
for (int i = ; i < dataTable.Columns.Count; i++)
{
dataTable.Rows[][i] = HtmlTableUtil.nullFlag + HtmlTableUtil.valueSeparater + "" + HtmlTableUtil.valueSeparater + "";
}
}
#endregion #region 填充数据
for (int i = ; i < dataTable.Rows.Count; i++)//遍历DataTable行
{
DataRow dataRow = dataTable.Rows[i];
IRow row = sheet.CreateRow(i);//在工作表中添加一行
row.HeightInPoints = ; for (int j = ; 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[] != HtmlTableUtil.nullFlag)
{
double d;
if (double.TryParse(valArr[], out d))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(d);//设置列的内容
}
else
{
cell.SetCellValue(valArr[]);//设置列的内容
if (i != ) cellStyle.Alignment = HorizontalAlignment.Center;
}
if (i != )
{
int unitWidth = ;
int oldWidth = sheet.GetColumnWidth(j);
int width = (int)Math.Round(CommonHelper.GetWidthUnitCount(valArr[])) * unitWidth;
width = width > unitWidth * ? unitWidth * : width;
width = width < unitWidth * ? unitWidth * : width;
if (width > oldWidth)
{
sheet.SetColumnWidth(j, width);
}
}
}
}
}
#endregion #region 合并单元格
for (int i = ; i < dataTable.Rows.Count; i++)//遍历DataTable行
{
DataRow dataRow = dataTable.Rows[i]; for (int j = ; 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[]);
int colSpan = int.Parse(valArr[]);
if (rowSpan > || colSpan > )
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i + rowSpan - , j, j + colSpan - ));
}
}
}
#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, , bArr.Length);
fs.Flush();
}
#endregion }
#endregion }
}