使用NPOI将TABLE内容导出到EXCEL
项目中需要将页面中的table内容导出到EXCEL,在用了几种方法后发现NPO是最快&最好的
需要应用 NPOI.dll 还有个Ionic.Zip.dll不知道有用没,没去研究,两个DLL都放到bin目录里了
假设页面中有个<div id="excelTable"><table>.....</table></div>需要导出到EXCEL
在页面中加一个button
<input type="button" name="excelBut" value="导出Excel" onclick="toExcel()" class="sgbtn" />
页面任意部分插入一段javascript:
function toExcel() { post("tools/toExcel.aspx", {act:'tabletoexcel', html:$('.excelTable').html() }); } function post(URL, PARAMS) { var temp = document.createElement("form"); temp.action = URL; temp.method = "post"; temp.style.display = "none"; for (var x in PARAMS) { var opt = document.createElement("textarea"); opt.name = x; opt.value = PARAMS[x]; temp.appendChild(opt); } document.body.appendChild(temp); temp.submit(); return temp; }
toExcel.aspx文件空白即可
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="toExcel.aspx.cs" Inherits="tools_toExcel" %>
toExcel.aspx.cs代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Text; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using NPOI.HSSF.UserModel; using NPOI.HPSF; using NPOI.POIFS.FileSystem; using NPOI.HSSF.Util; using System.Text.RegularExpressions; using System.IO; public partial class tools_toExcel : System.Web.UI.Page { protected void Page_Load( object sender, EventArgs e) { string act = GetValue( "act" ); //toExcel.aspx?act=tabletoexcel&html=<table class="reportstable">..</table> if (act == "tabletoexcel" ) { TableToExcel(); } } public void TableToExcel() { string tableHtml = Request.Form[ "html" ]; //接受前台table 数值字符串 if ( string .IsNullOrEmpty(tableHtml)) { return ; } InitializeWorkbook(); HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet( "Sheet1" ); string rowContent = string .Empty; MatchCollection rowCollection = Regex.Matches(tableHtml, @"<tr[^>]*>[\s\S]*?<\/tr>" , RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对tr进行筛选 NPOI.SS.UserModel.IFont fontSubTitle = hssfworkbook.CreateFont(); fontSubTitle.Boldweight = 800; //加粗 NPOI.SS.UserModel.IFont fontBody = hssfworkbook.CreateFont(); fontBody.Boldweight = 500; //加粗 for ( int i = 0; i < rowCollection.Count; i++) { HSSFRow row = (HSSFRow)sheet1.CreateRow(i); rowContent = rowCollection[i].Value; MatchCollection columnCollection = Regex.Matches(rowContent, @"<th[^>]*>[\s\S]*?<\/th>" , RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选 for ( int td = 0; td < columnCollection.Count; td++) { row.CreateCell(td).SetCellValue(StrTools.HtmlToTxt(columnCollection[td].Value)); } columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>" , RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选 for ( int td = 0; td < columnCollection.Count; td++) { row.CreateCell(td).SetCellValue(StrTools.HtmlToTxt(columnCollection[td].Value)); } } WriteToFile(); downFile(ppath); } static HSSFWorkbook hssfworkbook; public string ppath; public void WriteToFile() { string year = DateTime.Now.Year.ToString(); ppath = HttpContext.Current.Server.MapPath(DateTime.Now.ToString( "yyyyMMddmmss" ) + ".xls" ); FileStream file = new FileStream(ppath, FileMode.Create); hssfworkbook.Write(file); file.Close(); } public void InitializeWorkbook() { hssfworkbook = new HSSFWorkbook(); ////create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "company" ; hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "xxx" ; hssfworkbook.SummaryInformation = si; } public void downFile( string ppath) { if (File.Exists(ppath)) { Response.ClearHeaders(); Response.Clear(); Response.Expires = 0; Response.Buffer = true ; Response.AddHeader( "Accept-Language" , "zh-cn" ); string name = System.IO.Path.GetFileName(ppath); System.IO.FileStream files = new FileStream(ppath, FileMode.Open, FileAccess.Read, FileShare.Read); byte [] byteFile = null ; if (files.Length == 0) { byteFile = new byte [1]; } else { byteFile = new byte [files.Length]; } files.Read(byteFile, 0, ( int )byteFile.Length); files.Close(); File.Delete(files.Name); Response.AddHeader( "Content-Disposition" , "attachment;filename=" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8)); Response.ContentType = "application/octet-stream;charset=gbk" ; Response.BinaryWrite(byteFile); Response.End(); } } /// <summary> /// POST/GET 参数获取 /// </summary> /// <param name="context"></param> /// <param name="name"></param> /// <returns></returns> private string GetValue( string name) { string result = ConvertData.ConvertToString(Request.QueryString[name], "" ); if ( string .IsNullOrEmpty(result)) { result = ConvertData.ConvertToString(Request.Form[name], "" ); } return StrTools.SafeSqlstr(result); } } |
这样就可以将HTML中的TABLE方便地导出EXCEL了~代码有点乱,别介意哈。