带复杂表头合并单元格的HtmlTable转换成DataTable并导出Excel(转)
标签：style blog class c code java
步骤：
一、前台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 )
{
dataTable.Columns.Add();
}
#endregion
#region 给DataTable添加行
for (int i = 0; i //遍历行
{
DataRow dataRow = dataTable.NewRow();
for (int j = 0; j //遍历列
{
dataRow[j] = null;
}
dataTable.Rows.Add(dataRow);
}
#endregion
#region 转换
for (int i = 0; i //遍历HtmlTable行
{
HtmlTableRow htmlTableRow = htmlTable.Rows[i];
int delta = 0;//列增量
for (int j = 0; j //遍历HtmlTable列
{
HtmlTableCell htmlTableCell = htmlTableRow.Cells[j];
#region 计算delta
for (int k = j + delta; 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 )
{
for (int n = 0; 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 unitWidth * 30 ? unitWidth * 30 : width;
width = width oldWidth)
{
sheet.SetColumnWidth(j， width);
}
}
}
}
}
#endregion
#region 合并单元格
for (int i = 0; i 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
}
}
带复杂表头合并单元格的HtmlTable转换成DataTable并导出Excel(转),搜素材,soscw.com
带复杂表头合并单元格的HtmlTable转换成DataTable并导出Excel(转)
标签：style blog class c code java
原文地址：http://www.cnblogs.com/gbnw/p/3740422.html
评论