1、NPOI官方网站:http://npoi.codeplex.com/
可以到此网站上去下载最新的NPOI组件版本
2、NPOI在线学习教程(中文版):
http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html
感谢Tony Qu分享出NPOI组件的使用方法
3、.NET调用NPOI组件导入导出Excel的操作类
此NPOI操作类的优点如下:
(1)支持web及winform从DataTable导出到Excel;
(2)生成速度很快;
(3)准确判断数据类型,不会出现身份证转数值等问题;
(4)如果单页条数大于65535时会新建工作表;
(5)列宽自适应;
NPOI操作类
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
1
using
System;
2
using
System.Data;
3
using
System.Configuration;
4
using
System.Web;
5
using
System.Web.Security;
6
using
System.Web.UI;
7
using
System.Web.UI.HtmlControls;
8
using
System.Web.UI.WebControls;
9
using
System.Web.UI.WebControls.WebParts;
10
using
System.IO;
11
using
System.Text;
12
using
NPOI;
13
using
NPOI.HPSF;
14
using
NPOI.HSSF;
15
using
NPOI.HSSF.UserModel;
16
using
NPOI.HSSF.Util;
17
using
NPOI.POIFS;
18
using
NPOI.Util;
20
namespace
PMS.Common
21
{
22
public
class
NPOIHelper
23
{
24
///
<summary>
25
///
DataTable导出到Excel文件
26
///
</summary>
27
///
<param name="dtSource">
源DataTable
</param>
28
///
<param name="strHeaderText">
表头文本
</param>
29
///
<param name="strFileName">
保存位置
</param>
30
public
static
void
Export(DataTable dtSource,
string
strHeaderText,
string
strFileName)
31
{
32
using
(MemoryStream ms
=
Export(dtSource, strHeaderText))
33
{
34
using
(FileStream fs
=
new
FileStream(strFileName, FileMode.Create, FileAccess.Write))
35
{
36
byte
[] data
=
ms.ToArray();
37
fs.Write(data,
0
, data.Length);
38
fs.Flush();
39
}
40
}
41
}
42
43
///
<summary>
44
///
DataTable导出到Excel的MemoryStream
45
///
</summary>
46
///
<param name="dtSource">
源DataTable
</param>
47
///
<param name="strHeaderText">
表头文本
</param>
48
public
static
MemoryStream Export(DataTable dtSource,
string
strHeaderText)
49
{
50
HSSFWorkbook workbook
=
new
HSSFWorkbook();
51
HSSFSheet sheet
=
workbook.CreateSheet();
52
53
#region
右击文件 属性信息
54
{
55
DocumentSummaryInformation dsi
=
PropertySetFactory.CreateDocumentSummaryInformation();
56
dsi.Company
=
"
NPOI
"
;
57
workbook.DocumentSummaryInformation
=
dsi;
58
59
SummaryInformation si
=
PropertySetFactory.CreateSummaryInformation();
60
si.Author
=
"
文件作者信息
"
;
//
填加xls文件作者信息
61
si.ApplicationName
=
"
创建程序信息
"
;
//
填加xls文件创建程序信息
62
si.LastAuthor
=
"
最后保存者信息
"
;
//
填加xls文件最后保存者信息
63
si.Comments
=
"
作者信息
"
;
//
填加xls文件作者信息
64
si.Title
=
"
标题信息
"
;
//
填加xls文件标题信息
65
si.Subject
=
"
主题信息
"
;
//
填加文件主题信息
66
si.CreateDateTime
=
DateTime.Now;
67
workbook.SummaryInformation
=
si;
68
}
69
#endregion
70
71
HSSFCellStyle dateStyle
=
workbook.CreateCellStyle();
72
HSSFDataFormat format
=
workbook.CreateDataFormat();
73
dateStyle.DataFormat
=
format.GetFormat(
"
yyyy-mm-dd
"
);
74
75
//
取得列宽
76
int
[] arrColWidth
=
new
int
[dtSource.Columns.Count];
77
foreach
(DataColumn item
in
dtSource.Columns)
78
{
79
arrColWidth[item.Ordinal]
=
Encoding.GetEncoding(
936
).GetBytes(item.ColumnName.ToString()).Length;
80
}
81
for
(
int
i
=
0
; i
<
dtSource.Rows.Count; i
++
)
82
{
83
for
(
int
j
=
0
; j
<
dtSource.Columns.Count; j
++
)
84
{
85
int
intTemp
=
Encoding.GetEncoding(
936
).GetBytes(dtSource.Rows[i][j].ToString()).Length;
86
if
(intTemp
>
arrColWidth[j])
87
{
88
arrColWidth[j]
=
intTemp;
89
}
90
}
91
}
95
int
rowIndex
=
0
;
97
foreach
(DataRow row
in
dtSource.Rows)
98
{
99
#region
新建表,填充表头,填充列头,样式
100
if
(rowIndex
==
65535
||
rowIndex
==
0
)
101
{
102
if
(rowIndex
!=
0
)
103
{
104
sheet
=
workbook.CreateSheet();
105
}
106
107
#region
表头及样式
108
{
109
HSSFRow headerRow
=
sheet.CreateRow(
0
);
110
headerRow.HeightInPoints
=
25
;
111
headerRow.CreateCell(
0
).SetCellValue(strHeaderText);
112
113
HSSFCellStyle headStyle
=
workbook.CreateCellStyle();
114
headStyle.Alignment
=
CellHorizontalAlignment.CENTER;
115
HSSFFont font
=
workbook.CreateFont();
116
font.FontHeightInPoints
=
20
;
117
font.Boldweight
=
700
;
118
headStyle.SetFont(font);
119
headerRow.GetCell(
0
).CellStyle
=
headStyle;
120
sheet.AddMergedRegion(
new
Region(
0
,
0
,
0
, dtSource.Columns.Count
-
1
));
121
headerRow.Dispose();
122
}
123
#endregion
124
125
126
#region
列头及样式
127
{
128
HSSFRow headerRow
=
sheet.CreateRow(
1
);
131
HSSFCellStyle headStyle
=
workbook.CreateCellStyle();
132
headStyle.Alignment
=
CellHorizontalAlignment.CENTER;
133
HSSFFont font
=
workbook.CreateFont();
134
font.FontHeightInPoints
=
10
;
135
font.Boldweight
=
700
;
136
headStyle.SetFont(font);
139
foreach
(DataColumn column
in
dtSource.Columns)
140
{
141
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
142
headerRow.GetCell(column.Ordinal).CellStyle
=
headStyle;
143
144
//
设置列宽
145
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal]
+
1
)
*
256
);
147
}
148
headerRow.Dispose();
149
}
150
#endregion
151
152
rowIndex
=
2
;
153
}
154
#endregion
155
156
157
#region
填充内容
158
HSSFRow dataRow
=
sheet.CreateRow(rowIndex);
159
foreach
(DataColumn column
in
dtSource.Columns)
160
{
161
HSSFCell newCell
=
dataRow.CreateCell(column.Ordinal);
162
163
string
drValue
=
row[column].ToString();
164
165
switch
(column.DataType.ToString())
166
{
167
case
"
System.String
"
:
//
字符串类型
168
newCell.SetCellValue(drValue);
169
break
;
170
case
"
System.DateTime
"
:
//
日期类型
171
DateTime dateV;
172
DateTime.TryParse(drValue,
out
dateV);
173
newCell.SetCellValue(dateV);
174
175
newCell.CellStyle
=
dateStyle;
//
格式化显示
176
break
;
177
case
"
System.Boolean
"
:
//
布尔型
178
bool
boolV
=
false
;
179
bool
.TryParse(drValue,
out
boolV);
180
newCell.SetCellValue(boolV);
181
break
;
182
case
"
System.Int16
"
:
//
整型
183
case
"
System.Int32
"
:
184
case
"
System.Int64
"
:
185
case
"
System.Byte
"
:
186
int
intV
=
0
;
187
int
.TryParse(drValue,
out
intV);
188
newCell.SetCellValue(intV);
189
break
;
190
case
"
System.Decimal
"
:
//
浮点型
191
case
"
System.Double
"
:
192
double
doubV
=
0
;
193
double
.TryParse(drValue,
out
doubV);
194
newCell.SetCellValue(doubV);
195
break
;
196
case
"
System.DBNull
"
:
//
空值处理
197
newCell.SetCellValue(
""
);
198
break
;
199
default
:
200
newCell.SetCellValue(
""
);
201
break
;
202
}
203
204
}
205
#endregion
206
207
rowIndex
++
;
208
}
211
using
(MemoryStream ms
=
new
MemoryStream())
212
{
213
workbook.Write(ms);
214
ms.Flush();
215
ms.Position
=
0
;
216
217
sheet.Dispose();
218
//
workbook.Dispose();
//
一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
219
return
ms;
220
}
222
}
223
224
///
<summary>
225
///
用于Web导出
226
///
</summary>
227
///
<param name="dtSource">
源DataTable
</param>
228
///
<param name="strHeaderText">
表头文本
</param>
229
///
<param name="strFileName">
文件名
</param>
230
public
static
void
ExportByWeb(DataTable dtSource,
string
strHeaderText,
string
strFileName)
231
{
233
HttpContext curContext
=
HttpContext.Current;
234
235
//
设置编码和附件格式
236
curContext.Response.ContentType
=
"
application/vnd.ms-excel
"
;
237
curContext.Response.ContentEncoding
=
Encoding.UTF8;
238
curContext.Response.Charset
=
""
;
239
curContext.Response.AppendHeader(
"
Content-Disposition
"
,
240
"
attachment;filename=
"
+
HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
241
242
curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
243
curContext.Response.End();
245
}
247
248
///
<summary>
读取excel
249
///
默认第一行为标头
250
///
</summary>
251
///
<param name="strFileName">
excel文档路径
</param>
252
///
<returns></returns>
253
public
static
DataTable Import(
string
strFileName)
254
{
255
DataTable dt
=
new
DataTable();
256
257
HSSFWorkbook hssfworkbook;
258
using
(FileStream file
=
new
FileStream(strFileName, FileMode.Open, FileAccess.Read))
259
{
260
hssfworkbook
=
new
HSSFWorkbook(file);
261
}
262
HSSFSheet sheet
=
hssfworkbook.GetSheetAt(
0
);
263
System.Collections.IEnumerator rows
=
sheet.GetRowEnumerator();
264
265
HSSFRow headerRow
=
sheet.GetRow(
0
);
266
int
cellCount
=
headerRow.LastCellNum;
267
268
for
(
int
j
=
0
; j
<
cellCount; j
++
)
269
{
270
HSSFCell cell
=
headerRow.GetCell(j);
271
dt.Columns.Add(cell.ToString());
272
}
273
274
for
(
int
i
=
(sheet.FirstRowNum
+
1
); i
<=
sheet.LastRowNum; i
++
)
275
{
276
HSSFRow row
=
sheet.GetRow(i);
277
DataRow dataRow
=
dt.NewRow();
278
279
for
(
int
j
=
row.FirstCellNum; j
<
cellCount; j
++
)
280
{
281
if
(row.GetCell(j)
!=
null
)
282
dataRow[j]
=
row.GetCell(j).ToString();
283
}
284
285
dt.Rows.Add(dataRow);
286
}
287
return
dt;
288
}
289
}
290
}
291
4、NPOI操作类的调用方法
DataTable dt_Grade = Tools.Data.GetDataTable(strSQL);
filename += "绩效考核结果分数统计表";
PMS.Common.NPOIHelper.ExportByWeb(dt_Grade, filename, filename+".xls");
5、效果展示
![](https://i-blog.csdnimg.cn/blog_migrate/0c92d3725dd7e9a5c7f077a2e14624f8.jpeg)
6、NPOI类库下载:http://files.cnblogs.com/dreamof/NPOI类库.rar
信息来源:http://www.cnblogs.com/yongfa365/archive/2010/05/10/NPOI-MyXls-DataTable-To-Excel-From-Excel.html
1、NPOI官方网站:http://npoi.codeplex.com/
可以到此网站上去下载最新的NPOI组件版本
2、NPOI在线学习教程(中文版):
http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html
感谢Tony Qu分享出NPOI组件的使用方法
3、.NET调用NPOI组件导入导出Excel的操作类
此NPOI操作类的优点如下:
(1)支持web及winform从DataTable导出到Excel;
(2)生成速度很快;
(3)准确判断数据类型,不会出现身份证转数值等问题;
(4)如果单页条数大于65535时会新建工作表;
(5)列宽自适应;
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> 1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.HtmlControls;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.IO;
11 using System.Text;
12 using NPOI;
13 using NPOI.HPSF;
14 using NPOI.HSSF;
15 using NPOI.HSSF.UserModel;
16 using NPOI.HSSF.Util;
17 using NPOI.POIFS;
18 using NPOI.Util;
20 namespace PMS.Common
21 {
22 public class NPOIHelper
23 {
24 /// <summary>
25 /// DataTable导出到Excel文件
26 /// </summary>
27 /// <param name="dtSource"> 源DataTable </param>
28 /// <param name="strHeaderText"> 表头文本 </param>
29 /// <param name="strFileName"> 保存位置 </param>
30 public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
31 {
32 using (MemoryStream ms = Export(dtSource, strHeaderText))
33 {
34 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
35 {
36 byte [] data = ms.ToArray();
37 fs.Write(data, 0 , data.Length);
38 fs.Flush();
39 }
40 }
41 }
42
43 /// <summary>
44 /// DataTable导出到Excel的MemoryStream
45 /// </summary>
46 /// <param name="dtSource"> 源DataTable </param>
47 /// <param name="strHeaderText"> 表头文本 </param>
48 public static MemoryStream Export(DataTable dtSource, string strHeaderText)
49 {
50 HSSFWorkbook workbook = new HSSFWorkbook();
51 HSSFSheet sheet = workbook.CreateSheet();
52
53 #region 右击文件 属性信息
54 {
55 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
56 dsi.Company = " NPOI " ;
57 workbook.DocumentSummaryInformation = dsi;
58
59 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
60 si.Author = " 文件作者信息 " ; // 填加xls文件作者信息
61 si.ApplicationName = " 创建程序信息 " ; // 填加xls文件创建程序信息
62 si.LastAuthor = " 最后保存者信息 " ; // 填加xls文件最后保存者信息
63 si.Comments = " 作者信息 " ; // 填加xls文件作者信息
64 si.Title = " 标题信息 " ; // 填加xls文件标题信息
65 si.Subject = " 主题信息 " ; // 填加文件主题信息
66 si.CreateDateTime = DateTime.Now;
67 workbook.SummaryInformation = si;
68 }
69 #endregion
70
71 HSSFCellStyle dateStyle = workbook.CreateCellStyle();
72 HSSFDataFormat format = workbook.CreateDataFormat();
73 dateStyle.DataFormat = format.GetFormat( " yyyy-mm-dd " );
74
75 // 取得列宽
76 int [] arrColWidth = new int [dtSource.Columns.Count];
77 foreach (DataColumn item in dtSource.Columns)
78 {
79 arrColWidth[item.Ordinal] = Encoding.GetEncoding( 936 ).GetBytes(item.ColumnName.ToString()).Length;
80 }
81 for ( int i = 0 ; i < dtSource.Rows.Count; i ++ )
82 {
83 for ( int j = 0 ; j < dtSource.Columns.Count; j ++ )
84 {
85 int intTemp = Encoding.GetEncoding( 936 ).GetBytes(dtSource.Rows[i][j].ToString()).Length;
86 if (intTemp > arrColWidth[j])
87 {
88 arrColWidth[j] = intTemp;
89 }
90 }
91 }
95 int rowIndex = 0 ;
97 foreach (DataRow row in dtSource.Rows)
98 {
99 #region 新建表,填充表头,填充列头,样式
100 if (rowIndex == 65535 || rowIndex == 0 )
101 {
102 if (rowIndex != 0 )
103 {
104 sheet = workbook.CreateSheet();
105 }
106
107 #region 表头及样式
108 {
109 HSSFRow headerRow = sheet.CreateRow( 0 );
110 headerRow.HeightInPoints = 25 ;
111 headerRow.CreateCell( 0 ).SetCellValue(strHeaderText);
112
113 HSSFCellStyle headStyle = workbook.CreateCellStyle();
114 headStyle.Alignment = CellHorizontalAlignment.CENTER;
115 HSSFFont font = workbook.CreateFont();
116 font.FontHeightInPoints = 20 ;
117 font.Boldweight = 700 ;
118 headStyle.SetFont(font);
119 headerRow.GetCell( 0 ).CellStyle = headStyle;
120 sheet.AddMergedRegion( new Region( 0 , 0 , 0 , dtSource.Columns.Count - 1 ));
121 headerRow.Dispose();
122 }
123 #endregion
124
125
126 #region 列头及样式
127 {
128 HSSFRow headerRow = sheet.CreateRow( 1 );
131 HSSFCellStyle headStyle = workbook.CreateCellStyle();
132 headStyle.Alignment = CellHorizontalAlignment.CENTER;
133 HSSFFont font = workbook.CreateFont();
134 font.FontHeightInPoints = 10 ;
135 font.Boldweight = 700 ;
136 headStyle.SetFont(font);
139 foreach (DataColumn column in dtSource.Columns)
140 {
141 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
142 headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
143
144 // 设置列宽
145 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1 ) * 256 );
147 }
148 headerRow.Dispose();
149 }
150 #endregion
151
152 rowIndex = 2 ;
153 }
154 #endregion
155
156
157 #region 填充内容
158 HSSFRow dataRow = sheet.CreateRow(rowIndex);
159 foreach (DataColumn column in dtSource.Columns)
160 {
161 HSSFCell newCell = dataRow.CreateCell(column.Ordinal);
162
163 string drValue = row[column].ToString();
164
165 switch (column.DataType.ToString())
166 {
167 case " System.String " : // 字符串类型
168 newCell.SetCellValue(drValue);
169 break ;
170 case " System.DateTime " : // 日期类型
171 DateTime dateV;
172 DateTime.TryParse(drValue, out dateV);
173 newCell.SetCellValue(dateV);
174
175 newCell.CellStyle = dateStyle; // 格式化显示
176 break ;
177 case " System.Boolean " : // 布尔型
178 bool boolV = false ;
179 bool .TryParse(drValue, out boolV);
180 newCell.SetCellValue(boolV);
181 break ;
182 case " System.Int16 " : // 整型
183 case " System.Int32 " :
184 case " System.Int64 " :
185 case " System.Byte " :
186 int intV = 0 ;
187 int .TryParse(drValue, out intV);
188 newCell.SetCellValue(intV);
189 break ;
190 case " System.Decimal " : // 浮点型
191 case " System.Double " :
192 double doubV = 0 ;
193 double .TryParse(drValue, out doubV);
194 newCell.SetCellValue(doubV);
195 break ;
196 case " System.DBNull " : // 空值处理
197 newCell.SetCellValue( "" );
198 break ;
199 default :
200 newCell.SetCellValue( "" );
201 break ;
202 }
203
204 }
205 #endregion
206
207 rowIndex ++ ;
208 }
211 using (MemoryStream ms = new MemoryStream())
212 {
213 workbook.Write(ms);
214 ms.Flush();
215 ms.Position = 0 ;
216
217 sheet.Dispose();
218 // workbook.Dispose(); // 一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
219 return ms;
220 }
222 }
223
224 /// <summary>
225 /// 用于Web导出
226 /// </summary>
227 /// <param name="dtSource"> 源DataTable </param>
228 /// <param name="strHeaderText"> 表头文本 </param>
229 /// <param name="strFileName"> 文件名 </param>
230 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
231 {
233 HttpContext curContext = HttpContext.Current;
234
235 // 设置编码和附件格式
236 curContext.Response.ContentType = " application/vnd.ms-excel " ;
237 curContext.Response.ContentEncoding = Encoding.UTF8;
238 curContext.Response.Charset = "" ;
239 curContext.Response.AppendHeader( " Content-Disposition " ,
240 " attachment;filename= " + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
241
242 curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
243 curContext.Response.End();
245 }
247
248 /// <summary> 读取excel
249 /// 默认第一行为标头
250 /// </summary>
251 /// <param name="strFileName"> excel文档路径 </param>
252 /// <returns></returns>
253 public static DataTable Import( string strFileName)
254 {
255 DataTable dt = new DataTable();
256
257 HSSFWorkbook hssfworkbook;
258 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
259 {
260 hssfworkbook = new HSSFWorkbook(file);
261 }
262 HSSFSheet sheet = hssfworkbook.GetSheetAt( 0 );
263 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
264
265 HSSFRow headerRow = sheet.GetRow( 0 );
266 int cellCount = headerRow.LastCellNum;
267
268 for ( int j = 0 ; j < cellCount; j ++ )
269 {
270 HSSFCell cell = headerRow.GetCell(j);
271 dt.Columns.Add(cell.ToString());
272 }
273
274 for ( int i = (sheet.FirstRowNum + 1 ); i <= sheet.LastRowNum; i ++ )
275 {
276 HSSFRow row = sheet.GetRow(i);
277 DataRow dataRow = dt.NewRow();
278
279 for ( int j = row.FirstCellNum; j < cellCount; j ++ )
280 {
281 if (row.GetCell(j) != null )
282 dataRow[j] = row.GetCell(j).ToString();
283 }
284
285 dt.Rows.Add(dataRow);
286 }
287 return dt;
288 }
289 }
290 }
291
4、NPOI操作类的调用方法
DataTable dt_Grade = Tools.Data.GetDataTable(strSQL);
filename += "绩效考核结果分数统计表";
PMS.Common.NPOIHelper.ExportByWeb(dt_Grade, filename, filename+".xls");
5、效果展示
6、NPOI类库下载:http://files.cnblogs.com/dreamof/NPOI类库.rar
信息来源:http://www.cnblogs.com/yongfa365/archive/2010/05/10/NPOI-MyXls-DataTable-To-Excel-From-Excel.html