1 public class NPOIHelper
2 {
3 /// <summary>
4 /// DataTable导出到Excel文件
5 /// </summary>
6 /// <param name="dtSource">源DataTable</param>
7 /// <param name="strHeaderText">表头文本</param>
8 /// <param name="strFileName">保存位置</param>
9 public static void ExportByServer(DataTable dtSource, string strHeaderText, string strFileName)
10 {
11 using (MemoryStream ms = Export(dtSource, strHeaderText))
12 {
13 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
14 {
15 byte[] data = ms.ToArray();
16 fs.Write(data, 0, data.Length);
17 fs.Flush();
18 }
19 }
20 }
21
22
23 /// <summary>
24 /// 用于Web导出
25 /// </summary>
26 /// <param name="dtSource">源DataTable</param>
27 /// <param name="strHeaderText">表头文本</param>
28 /// <param name="strFileName">文件名</param>
29 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
30 {
31 HttpContext curContext = HttpContext.Current;
32
33 // 设置编码和附件格式
34 curContext.Response.ContentType = "application/vnd.ms-excel";
35 curContext.Response.ContentEncoding = Encoding.UTF8;
36 curContext.Response.Charset = "";
37 curContext.Response.AppendHeader("Content-Disposition",
38 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
39
40 curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
41 curContext.Response.End();
42 }
43
44 /// <summary>读取excel
45 /// 默认第一行为标头
46 /// </summary>
47 /// <param name="strFileName">excel文档路径</param>
48 /// <returns></returns>
49 public static DataTable Import(string strFileName)
50 {
51 DataTable dt = new DataTable();
52
53 HSSFWorkbook hssfworkbook;
54 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
55 {
56 hssfworkbook = new HSSFWorkbook(file);
57 }
58 ISheet sheet = hssfworkbook.GetSheetAt(0);
59 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
60
61 IRow headerRow = sheet.GetRow(0);
62 int cellCount = headerRow.LastCellNum;
63
64 for (int j = 0; j < cellCount; j++)
65 {
66 ICell cell = headerRow.GetCell(j);
67 dt.Columns.Add(cell.ToString());
68 }
69
70 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
71 {
72 IRow row = sheet.GetRow(i);
73 DataRow dataRow = dt.NewRow();
74
75 for (int j = row.FirstCellNum; j < cellCount; j++)
76 {
77 if (row.GetCell(j) != null)
78 dataRow[j] = row.GetCell(j).ToString();
79 }
80
81 dt.Rows.Add(dataRow);
82 }
83 return dt;
84 }
85
86
87 /// <summary>
88 /// DataTable导出到Excel的MemoryStream
89 /// </summary>
90 /// <param name="dtSource">源DataTable</param>
91 /// <param name="strHeaderText">表头文本</param>
92 public static MemoryStream Export(DataTable dtSource, string strHeaderText)
93 {
94 HSSFWorkbook workbook = new HSSFWorkbook();
95 ISheet sheet = workbook.CreateSheet();
96
97 #region 右击文件 属性信息
98 {
99 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
100 dsi.Company = "Sohu";
101 workbook.DocumentSummaryInformation = dsi;
102
103 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
104 si.Author = "文件作者信息"; //填加xls文件作者信息
105 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
106 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
107 si.Comments = "作者信息"; //填加xls文件作者信息
108 si.Title = "标题信息"; //填加xls文件标题信息
109 si.Subject = "主题信息";//填加文件主题信息
110 si.CreateDateTime = DateTime.Now;
111 workbook.SummaryInformation = si;
112 }
113 #endregion
114
115 ICellStyle dateStyle = workbook.CreateCellStyle();
116 IDataFormat format = workbook.CreateDataFormat();
117 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
118
119 //取得列宽
120 int[] arrColWidth = new int[dtSource.Columns.Count];
121 foreach (DataColumn item in dtSource.Columns)
122 {
123 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
124 }
125 for (int i = 0; i < dtSource.Rows.Count; i++)
126 {
127 for (int j = 0; j < dtSource.Columns.Count; j++)
128 {
129 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
130 if (intTemp > arrColWidth[j])
131 {
132 arrColWidth[j] = intTemp;
133 }
134 }
135 }
136 int rowIndex = 0;
137 foreach (DataRow row in dtSource.Rows)
138 {
139 #region 新建表，填充表头，填充列头，样式
140 if (rowIndex == 65535 || rowIndex == 0)
141 {
142 if (rowIndex != 0)
143 {
144 sheet = workbook.CreateSheet();
145 }
146
147 #region 表头及样式
148 {
149 IRow headerRow = sheet.CreateRow(0);
150 headerRow.HeightInPoints = 25;
151 headerRow.CreateCell(0).SetCellValue(strHeaderText);
152
153 ICellStyle headStyle = workbook.CreateCellStyle();
154 headStyle.Alignment = HorizontalAlignment.Center;
155 IFont font = workbook.CreateFont();
156 font.FontHeightInPoints = 20;
157 font.Boldweight = 700;
158 headStyle.SetFont(font);
159 headerRow.GetCell(0).CellStyle = headStyle;
160 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
161
162 }
163 #endregion
164
165
166 #region 列头及样式
167 {
168 IRow headerRow = sheet.CreateRow(1);
169
170 ICellStyle headStyle = workbook.CreateCellStyle();
171 headStyle.Alignment = HorizontalAlignment.Center;
172
173
174 IFont font = workbook.CreateFont();
175 font.FontHeightInPoints = 10;
176 font.Boldweight = 700;
177 headStyle.SetFont(font);
178 foreach (DataColumn column in dtSource.Columns)
179 {
180 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
181 headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
182
183 //设置列宽
184 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
185 }
186
187 }
188 #endregion
189
190 rowIndex = 2;
191 }
192 #endregion
193
194
195 #region 填充内容
196 IRow dataRow = sheet.CreateRow(rowIndex);
197 foreach (DataColumn column in dtSource.Columns)
198 {
199 ICell newCell = dataRow.CreateCell(column.Ordinal);
200
201 string drValue = row[column].ToString();
202
203 switch (column.DataType.ToString())
204 {
205 case "System.String"://字符串类型
206 newCell.SetCellValue(drValue);
207 break;
208 case "System.DateTime"://日期类型
209 DateTime dateV;
210 DateTime.TryParse(drValue, out dateV);
211 newCell.SetCellValue(dateV);
212
213 newCell.CellStyle = dateStyle;//格式化显示
214 break;
215 case "System.Boolean"://布尔型
216 bool boolV = false;
217 bool.TryParse(drValue, out boolV);
218 newCell.SetCellValue(boolV);
219 break;
220 case "System.Int16"://整型
221 case "System.Int32":
222 case "System.Int64":
223 case "System.Byte":
224 int intV = 0;
225 int.TryParse(drValue, out intV);
226 newCell.SetCellValue(intV);
227 break;
228 case "System.Decimal"://浮点型
229 case "System.Double":
230 double doubV = 0;
231 double.TryParse(drValue, out doubV);
232 newCell.SetCellValue(doubV);
233 break;
234 case "System.DBNull"://空值处理
235 newCell.SetCellValue("");
236 break;
237 default:
238 newCell.SetCellValue("");
239 break;
240 }
241
242 }
243 #endregion
244
245 rowIndex++;
246 }
247 using (MemoryStream ms = new MemoryStream())
248 {
249 workbook.Write(ms);
250 ms.Flush();
251 ms.Position = 0;
252
253 //sheet.Dispose();
254 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
255 return ms;
256 }
257 }
258
259
260 /// <summary>
261 /// DataTable按模板导出到Excel文件
262 /// </summary>
263 /// <param name="dtSource">源DataTable</param>
264 /// <param name="TempletFileName">模板文件</param>
265 /// <param name="strFileName">保存位置</param>
266 public static void ExportByTemplate(string TempletFileName, string strFileName, DataTable dt, string Type)
267 {
268 using (MemoryStream ms = ExportByTemp(TempletFileName, dt, Type))
269 {
270 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
271 {
272 byte[] data = ms.ToArray();
273 fs.Write(data, 0, data.Length);
274 fs.Flush();
275 }
276 }
277 }
278 /// <summary>
279 /// DataTable导出到Excel的MemoryStream
280 /// </summary>
281 /// <param name="dt">数据源</param>
282 /// <param name="TempletFileName">Excel模板</param>
283 /// <returns></returns>
284 public static MemoryStream ExportByTemp(string TempletFileName, DataTable dt,string Type)
285 {
286 FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
287
288 HSSFWorkbook workbook = new HSSFWorkbook(file);
289 ISheet sheet = workbook.GetSheetAt(1);
290
291 //ICellStyle dateStyle = workbook.CreateCellStyle();
292 //dateStyle.BorderBottom = BorderStyle.Thin;
293 //dateStyle.BorderLeft = BorderStyle.Thin;
294 //dateStyle.BorderRight = BorderStyle.Thin;
295 //dateStyle.BorderTop = BorderStyle.Thin;
296
297 int rowIndex = 0;
298
299 foreach (DataRow entity in dt.Rows)
300 {
301 IRow dataRow = sheet.CreateRow(rowIndex);
302
303 if (Type.Equals("SaleType"))
304 {
305 ICell newCell0 = dataRow.CreateCell(0);
306 newCell0.SetCellValue(entity[2].ToString());
307 //newCell0.CellStyle = dateStyle;
308 }
309 else {
310 ICell newCell0 = dataRow.CreateCell(0);
311 newCell0.SetCellValue(entity[1].ToString());
312 //newCell0.CellStyle = dateStyle;
313 }
314 //ICell newCell7 = dataRow.CreateCell(7);
315 //if (type.Equals("excel"))
316 //{
317 // newCell7.SetCellValue(entity.Location == "" ? "" : "第" + entity.Location + "行");
318 //}
319 //else if (type.Equals("word"))
320 //{
321 // newCell7.SetCellValue(entity.Location == "" ? "" : "第" + entity.Location + "页");
322 //}
323 //newCell7.CellStyle = dateStyle;
324
325
326
327 rowIndex++;
328 }
329 if (dt.Rows.Count == 1)
330 {
331 IRow dataRow = sheet.CreateRow(rowIndex);
332 ICell newCelllast = dataRow.CreateCell(0);
333 newCelllast.SetCellValue(" ");
334 }
335
336
337
338 using (MemoryStream ms = new MemoryStream())
339 {
340 workbook.Write(ms);
341 ms.Flush();
342 ms.Position = 0;
343
344 //sheet.Dispose();
345 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
346 return ms;
347 }
348 }
349
350
351 public static void ExportByNodeData(string TfileName, string StrfileName, DataTable dt, string Type,double Rate)
352 {
353 using (MemoryStream ms = ExportByNode(TfileName, dt, Type,Rate))
354 {
355 using (FileStream fs = new FileStream(StrfileName, FileMode.Create, FileAccess.Write))
356 {
357 byte[] data = ms.ToArray();
358 fs.Write(data, 0, data.Length);
359 fs.Flush();
360 }
361 }
362 }
363 public static MemoryStream ExportByNode(string TempletFileName, DataTable dt, string Type, double Rate)
364 {
365 FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
366
367 HSSFWorkbook workbook = new HSSFWorkbook(file);
368 ISheet sheet = workbook.GetSheetAt(0);
369
370 ICellStyle dateStyle = workbook.CreateCellStyle();
371 dateStyle.BorderBottom = BorderStyle.Thin;
372 dateStyle.BorderLeft = BorderStyle.Thin;
373 dateStyle.BorderRight = BorderStyle.Thin;
374 dateStyle.BorderTop = BorderStyle.Thin;
375
376 int rowIndex = 1;
377
378 foreach (DataRow entity in dt.Rows)
379 {
380 IRow dataRow = sheet.CreateRow(rowIndex);
381
382 if (Type.Equals("SaleTypeExport"))
383 {
384 ICell newCell0 = dataRow.CreateCell(0);
385 newCell0.SetCellValue(rowIndex);
386 newCell0.CellStyle = dateStyle;
387
388 ICell newCell1 = dataRow.CreateCell(1);
389 newCell1.SetCellValue(entity["new_nodename"].ToString());
390 newCell1.CellStyle = dateStyle;
391
392 ICell newCell2 = dataRow.CreateCell(2);
393 newCell2.SetCellValue(Convert.ToDouble(entity["new_lastyear_total"].ToString()));
394 newCell2.CellStyle = dateStyle;
395
396 ICell newCell3 = dataRow.CreateCell(3);
397 newCell3.SetCellValue(Convert.ToDouble(entity["new_total_assign2me"].ToString()));
398 newCell3.CellStyle = dateStyle;
399
400 ICell newCell4 = dataRow.CreateCell(4);
401 newCell4.SetCellValue(Convert.ToInt32(entity["new_accountadd"].ToString()));
402 newCell4.CellStyle = dateStyle;
403
404 ICell newCell5 = dataRow.CreateCell(5);
405 newCell5.SetCellValue(Convert.ToDouble(entity["new_total_assign2me"].ToString()) - (Convert.ToDouble(entity["new_total_assign2me"].ToString()) * Rate));
406 newCell5.CellStyle = dateStyle;
407
408 ICell newCell6 = dataRow.CreateCell(6);
409 newCell6.SetCellValue((Convert.ToDouble(entity["new_total_assign2me"].ToString()) * Rate));
410 newCell6.CellStyle = dateStyle;
411
412 ICell newCell7 = dataRow.CreateCell(7);
413 newCell7.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
414 newCell7.CellStyle = dateStyle;
415
416 ICell newCell8 = dataRow.CreateCell(8);
417 newCell8.SetCellValue(entity["new_description"].ToString());
418 newCell8.CellStyle = dateStyle;
419 }
420 else if (Type.Equals("AccountTypeExport"))
421 {
422 ICell newCell0 = dataRow.CreateCell(0);
423 newCell0.SetCellValue(rowIndex);
424 newCell0.CellStyle = dateStyle;
425
426 ICell newCell1 = dataRow.CreateCell(1);
427 newCell1.SetCellValue(entity["new_name"].ToString());
428 newCell1.CellStyle = dateStyle;
429
430 ICell newCell2 = dataRow.CreateCell(2);
431 newCell2.SetCellValue(Convert.ToDouble(entity["new_lastyear_total"].ToString()));
432 newCell2.CellStyle = dateStyle;
433
434 ICell newCell3 = dataRow.CreateCell(3);
435 newCell3.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()));
436 newCell3.CellStyle = dateStyle;
437
438 ICell newCell4 = dataRow.CreateCell(4);
439 newCell4.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) - (Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
440 newCell4.CellStyle = dateStyle;
441
442 ICell newCell5 = dataRow.CreateCell(5);
443 newCell5.SetCellValue((Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
444 newCell5.CellStyle = dateStyle;
445
446 ICell newCell6 = dataRow.CreateCell(6);
447 newCell6.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
448 newCell6.CellStyle = dateStyle;
449
450 ICell newCell7 = dataRow.CreateCell(7);
451 newCell7.SetCellValue(entity["new_nodetemdesc_description"].ToString());
452 newCell7.CellStyle = dateStyle;
453 }
454 else if (Type.Equals("NodeDescByAccountExport"))
455 {
456 ICell newCell0 = dataRow.CreateCell(0);
457 newCell0.SetCellValue(rowIndex);
458 newCell0.CellStyle = dateStyle;
459
460 ICell newCell1 = dataRow.CreateCell(1);
461 newCell1.SetCellValue(entity["new_name"].ToString());
462 newCell1.CellStyle = dateStyle;
463
464 ICell newCell2 = dataRow.CreateCell(2);
465 newCell2.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()));
466 newCell2.CellStyle = dateStyle;
467
468 ICell newCell3 = dataRow.CreateCell(3);
469 newCell3.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) - (Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
470 newCell3.CellStyle = dateStyle;
471
472 ICell newCell4 = dataRow.CreateCell(4);
473 newCell4.SetCellValue((Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
474 newCell4.CellStyle = dateStyle;
475
476 ICell newCell5 = dataRow.CreateCell(5);
477 newCell5.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
478 newCell5.CellStyle = dateStyle;
479
480 ICell newCell6 = dataRow.CreateCell(6);
481 newCell6.SetCellValue(entity["new_nodetemdesc_description"].ToString());
482 newCell6.CellStyle = dateStyle;
483 }
484 else if (Type.Equals("NodeDescBySaleExport"))
485 {
486 ICell newCell0 = dataRow.CreateCell(0);
487 newCell0.SetCellValue(rowIndex);
488 newCell0.CellStyle = dateStyle;
489
490 ICell newCell1 = dataRow.CreateCell(1);
491 newCell1.SetCellValue(entity["new_name"].ToString());
492 newCell1.CellStyle = dateStyle;
493
494 ICell newCell2 = dataRow.CreateCell(2);
495 newCell2.SetCellValue(Convert.ToDouble(entity["new_distributeamount"].ToString()));
496 newCell2.CellStyle = dateStyle;
497
498 ICell newCell3 = dataRow.CreateCell(3);
499 newCell3.SetCellValue(Convert.ToDouble(entity["new_distributeamount"].ToString()) - (Convert.ToDouble(entity["new_distributeamount"].ToString()) * Rate));
500 newCell3.CellStyle = dateStyle;
501
502 ICell newCell4 = dataRow.CreateCell(4);
503 newCell4.SetCellValue((Convert.ToDouble(entity["new_distributeamount"].ToString()) * Rate));
504 newCell4.CellStyle = dateStyle;
505
506 ICell newCell5 = dataRow.CreateCell(5);
507 newCell5.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
508 newCell5.CellStyle = dateStyle;
509
510 ICell newCell6 = dataRow.CreateCell(6);
511 newCell6.SetCellValue(entity["new_description"].ToString());
512 newCell6.CellStyle = dateStyle;
513 }
514 rowIndex++;
515 }
516
517 using (MemoryStream ms = new MemoryStream())
518 {
519 workbook.Write(ms);
520 ms.Flush();
521 ms.Position = 0;
522
523 //sheet.Dispose();
524 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
525 return ms;
526 }
527 }
528
529 }
