NPOIHelper.cs
13.6 KB
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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
using System;
using System.Data;
using System.Web;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Windows.Forms;
namespace XinYa.BLL
{
public static class NpoiHelper
{
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="Dt">要导出的DataTable</param>
/// <param name="ExelName">Excel名称</param>
public static void ExportForm(DataTable Dt, string ExelName)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = ExelName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
using (MemoryStream ms = Export(Dt, ExelName))
{
using (var fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// DataGridView导出到Excel文件
/// </summary>
/// <param name="ExportGridView">要导出的DataGridView</param>
/// <param name="ExelName">要保存的名称</param>
public static void ExportForm(DataGridView ExportGridView, string ExelName)
{
DataTable dtSource = (DataTable)((BindingSource)ExportGridView.DataSource).DataSource;
if (dtSource == null) return;
DataTable Dt = dtSource.Copy();
for (int i = 0; i < Dt.Columns.Count; i++)
{
if (ExportGridView.Columns[Dt.Columns[i].ColumnName] != null)
Dt.Columns[i].ColumnName = ExportGridView.Columns[Dt.Columns[i].ColumnName].HeaderText;
}
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = ExelName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
using (MemoryStream ms = Export(Dt, ExelName))
{
using (var fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
var workbook = new HSSFWorkbook();
var sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
var dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
var format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
dateStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//单元格样式
var cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//取得列宽
var arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
var rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
var headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
var font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = 600;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// headerRow.Dispose();
}
#endregion
#region 列头及样式
{
var headerRow = (HSSFRow)sheet.CreateRow(1);
var headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
var font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
var dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = cellStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
var boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
var intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return ms;
}
}
/// <summary>
/// 用于Web导出
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
//public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
//{
// HttpContext curContext = HttpContext.Current;
// // 设置编码和附件格式
// curContext.Response.ContentType = "application/vnd.ms-excel";
// curContext.Response.ContentEncoding = Encoding.UTF8;
// curContext.Response.Charset = "";
// curContext.Response.AppendHeader("Content-Disposition",
// "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
// curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
// curContext.Response.End();
//}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
var dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (var file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
var sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
var headerRow = (HSSFRow)sheet.GetRow(1);
var cellCount = headerRow.LastCellNum;
for (var j = 0; j < cellCount; j++)
{
var cell = (HSSFCell)headerRow.GetCell(j);
if(cell!=null)
{
dt.Columns.Add(cell.ToString());
}
}
for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
var row = (HSSFRow)sheet.GetRow(i);
var dataRow = dt.NewRow();
for (var j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
}
}