npoi 读取 html,将HtmlTable内容导出到Excel，使用NPOI组件

///

/// 将DataTable导出到Excel

///

/// html表格内容

/// 仅文件名(非路径)

/// 返回Excel文件绝对路径

public static string ExportHtmlTableToExcel(string htmlTable, string fileName)

{

string result;

try

{

#region 第一步：将HtmlTable转换为DataTable

htmlTable = htmlTable.Replace("\"", "'");

var trReg = new Regex(pattern: @"(?<=())");

var trMatchCollection = trReg.Matches(htmlTable);

DataTable dt = new DataTable("data");

for (int i = 0; i < trMatchCollection.Count; i++)

{

var row = "

";

var tdReg = new Regex(pattern: @"(?<=())");

var tdMatchCollection = tdReg.Matches(row);

if (i == 0)

{

foreach (var rd in tdMatchCollection)

{

var tdValue = RemoveHtml("

");

DataColumn dc = new DataColumn(tdValue);

dt.Columns.Add(dc);

}

}

if (i > 0)

{

DataRow dr = dt.NewRow();

for (int j = 0; j < tdMatchCollection.Count; j++)

{

var tdValue = RemoveHtml("

");

dr[j] = tdValue;

}

dt.Rows.Add(dr);

}

}

#endregion

#region 第二步：将DataTable导出到Excel

result = "ok_" + ExportDataSetToExcel(dt, fileName);

#endregion

}

catch (Exception ex)

{

result = "err_" + ex.Message;

}

return result;

}

///

/// 将DataTable导出到Excel

///

/// DataTable

/// 仅文件名(非路径)

/// 返回Excel文件绝对路径

public static string ExportDataSetToExcel(DataTable dt, string fileName)

{

#region 表头

HSSFWorkbook hssfworkbook = new HSSFWorkbook();

NPOI.SS.UserModel.Sheet hssfSheet = hssfworkbook.CreateSheet(fileName);

hssfSheet.DefaultColumnWidth = 13;

hssfSheet.SetColumnWidth(0, 25 * 256);

hssfSheet.SetColumnWidth(3, 20 * 256);

// 表头

NPOI.SS.UserModel.Row tagRow = hssfSheet.CreateRow(0);

tagRow.Height = 22 * 20;

// 标题样式

NPOI.SS.UserModel.CellStyle cellStyle = hssfworkbook.CreateCellStyle();

cellStyle.Alignment = HorizontalAlignment.CENTER;

cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

cellStyle.BorderBottom = CellBorderType.THIN;

cellStyle.BorderBottom = CellBorderType.THIN;

cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;

cellStyle.BorderLeft = CellBorderType.THIN;

cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;

cellStyle.BorderRight = CellBorderType.THIN;

cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;

cellStyle.BorderTop = CellBorderType.THIN;

cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;

int colIndex;

for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)

{

tagRow.CreateCell(colIndex).SetCellValue(dt.Columns[colIndex].ColumnName);

tagRow.GetCell(colIndex).CellStyle = cellStyle;

}

#endregion

#region 表数据

// 表数据

for (int k = 0; k < dt.Rows.Count; k++)

{

DataRow dr = dt.Rows[k];

NPOI.SS.UserModel.Row row = hssfSheet.CreateRow(k + 1);

for (int i = 0; i < dt.Columns.Count; i++)

{

row.CreateCell(i).SetCellValue(dr[i].ToString());

row.GetCell(i).CellStyle = cellStyle;

}

}

#endregion

FileStream file = new FileStream(HttpContext.Current.Request.PhysicalApplicationPath + "Temp/" + fileName + ".xls", FileMode.Create);

hssfworkbook.Write(file);

file.Close();

var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);

return (basePath + "Temp/" + fileName + ".xls");

}

///

/// 去除HTML标记

///

///

/// 已经去除后的文字

public static string RemoveHtml(string htmlstring)

{

//删除脚本

htmlstring =

Regex.Replace(htmlstring, @"",

"", RegexOptions.IgnoreCase);

//删除HTML

htmlstring = Regex.Replace(htmlstring, @"]*)>", "", RegexOptions.IgnoreCase);

htmlstring = Regex.Replace(htmlstring, @"([\r\n])[\s]+", "", RegexOptions.IgnoreCase);

htmlstring = Regex.Replace(htmlstring, @"-->", "", RegexOptions.IgnoreCase);

htmlstring = Regex.Replace(htmlstring, @"