因為微軟的office成本太高了，所以開發項目的時候電腦上沒安裝office,而是安裝了wps。但開發語言用的是C#,所以直接調用微軟的office組件是很方便的，但一方面慢，一方面成本高，所以從網上找到了NPOI這個開源的項目。http://npoi.codeplex.com/,引用的dll下載目錄 http://npoi.codeplex.com/downloads/get/1476595
並且封裝了通用的處理EXCEL 跟DataSet,DataTable的方法。方便調用
以上是代碼 （當前項目是.net 2.0 下的，如果需要.net 4.0則到NPOI官網下載相應的dll就可以了）
1 using NPOI.SS.UserModel;
2 using System;
3 using System.Collections.Generic;
4 using System.Data;
5 using System.IO;
6 using System.Text;
7
8 namespace MrLiu.Tools
9 {
10 public sealed class ExcelHelper
11 {
12 #region Excel導入
13 /// <summary>
14 /// Excel 轉換為DataTable
15 /// </summary>
16 /// <param name="file">文件路徑</param>
17 /// <param name="sheetName">Sheet名稱，如果只有一個sheet可以傳 null</param>
18 /// <returns></returns>
19 public static DataTable ExcelToDataTable(string file, string sheetName)
20 {
21 try
22 {
23 DataTable dt = new DataTable();
24 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
25 {
26 var workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
27 ISheet sheet = null;
28 if (sheetName == null)
29 {
30 sheet = workbook.GetSheetAt(0);
31 }
32 else
33 {
34 sheet = workbook.GetSheet(sheetName);
35 }
36 //列名
37 IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
38 for (int i = 0; i < rowHead.LastCellNum; i++)
39 {
40 string fildName = rowHead.GetCell(i).StringCellValue;
41 dt.Columns.Add(fildName, typeof(String));
42 }
43
44 //數據
45 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
46 {
47 IRow row = sheet.GetRow(i);
48 DataRow dr = dt.NewRow();
49 for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
50 {
51 var cell = row.GetCell(j);
52 dr[j] = GetValueTypeForICell(cell);
53 if (dr[j] == null)
54 {
55 dr[j] = string.Empty;
56 }
57 }
58 dt.Rows.Add(dr);
59 }
60 }
61 return dt;
62 }
63 catch (Exception ex)
64 {
65 throw new Exception(ex.Message);
66 }
67 }
68 /// <summary>
69 /// Excel 導入為DataTable
70 /// </summary>
71 /// <param name="file">文件路徑</param>
72 /// <param name="extension">後續名 XLS XLSX</param>
73 /// <returns></returns>
74 public static DataTable ExcelToDataTable(string file)
75 {
76 try
77 {
78 DataTable dt = new DataTable();
79 string extension = Path.GetExtension(file);
80 if (extension.ToUpper() == ".XLS")
81 {
82 dt = ExcelToTableForXLS(file);
83 }
84 else if (extension.ToUpper() == ".XLS")
85 {
86 dt = ExcelToTableForXLSX(file);
87 }
88 else
89 {
90 throw new Exception("文件格式不正確");
91 }
92 return dt;
93 }
94 catch (Exception ex)
95 {
96 throw new Exception(ex.Message);
97 }
98 }
99 /// <summary>
100 /// 讀取xls格式的Excel
101 /// </summary>
102 /// <param name="file">文件全路徑</param>
103 /// <returns>返回DaTaTable</returns>
104 public static DataTable ExcelToTableForXLS(string file)
105 {
106 try
107 {
108 DataTable dt = new DataTable();
109 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
110 {
111 var hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
112 ISheet sheet = hssfworkbook.GetSheetAt(0);
113
114 //列名
115 IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
116 for (int i = 0; i < rowHead.LastCellNum; i++)
117 {
118 string fildName = rowHead.GetCell(i).StringCellValue;
119 dt.Columns.Add(fildName, typeof(String));
120 }
121
122 //數據
123 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
124 {
125 IRow row = sheet.GetRow(i);
126 DataRow dr = dt.NewRow();
127 for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
128 {
129 NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;
130 dr[j] = GetValueTypeForXLS(cell);
131 if (dr[j] == null)
132 {
133 break;
134 }
135 }
136 dt.Rows.Add(dr);
137 }
138 }
139 return dt;
140 }
141 catch (Exception ex)
142 {
143 throw new Exception(ex.Message);
144 }
145 }
146
147 /// <summary>
148 /// 獲取單元格類型
149 /// </summary>
150 /// <param name="cell"></param>
151 /// <returns></returns>
152 private static object GetValueTypeForXLS(NPOI.HSSF.UserModel.HSSFCell cell)
153 {
154 try
155 {
156 if (cell == null)
157 {
158 return null;
159 }
160 switch (cell.CellType)
161 {
162 case CellType.Blank: //BLANK:
163 return null;
164 case CellType.Boolean: //BOOLEAN:
165 return cell.BooleanCellValue;
166 case CellType.Numeric: //NUMERIC:
167 return cell.NumericCellValue;
168 case CellType.String: //STRING:
169 return cell.StringCellValue;
170 case CellType.Error: //ERROR:
171 return cell.ErrorCellValue;
172 case CellType.Formula: //FORMULA:
173 default:
174 return "=" + cell.CellFormula;
175 }
176 }
177 catch (Exception ex)
178 {
179 throw new Exception(ex.Message);
180 }
181 }
182
183 /// <summary>
184 /// 讀取xlsx格式的Excel
185 /// </summary>
186 /// <param name="file">文件全路徑</param>
187 /// <returns>返回DaTaTable</returns>
188 public static DataTable ExcelToTableForXLSX(string file)
189 {
190 try
191 {
192 DataTable dt = new DataTable();
193 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
194 {
195 var hssfworkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
196 ISheet sheet = hssfworkbook.GetSheetAt(0);
197
198 //列名
199 IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
200 for (int i = 0; i < rowHead.LastCellNum; i++)
201 {
202 string fildName = rowHead.GetCell(i).StringCellValue;
203 dt.Columns.Add(fildName, typeof(String));
204 }
205
206 //數據
207 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
208 {
209 IRow row = sheet.GetRow(i);
210 DataRow dr = dt.NewRow();
211 for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
212 {
213 NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;
214 dr[j] = GetValueTypeForXLS(cell);
215 if (dr[j] == null)
216 {
217 break;
218 }
219 }
220 dt.Rows.Add(dr);
221 }
222 }
223 return dt;
224 }
225 catch (Exception ex)
226 {
227 throw new Exception(ex.Message);
228 }
229 }
230 /// <summary>
231 /// 獲取單元格類型(xlsx)
232 /// </summary>
233 /// <param name="cell"></param>
234 /// <returns></returns>
235 private static object GetValueTypeForXLSX(NPOI.XSSF.UserModel.XSSFCell cell)
236 {
237 try
238 {
239 if (cell == null)
240 {
241 return null;
242 }
243 switch (cell.CellType)
244 {
245 case CellType.Blank: //BLANK:
246 return null;
247 case CellType.Boolean: //BOOLEAN:
248 return cell.BooleanCellValue;
249 case CellType.Numeric: //NUMERIC:
250 return cell.NumericCellValue;
251 case CellType.String: //STRING:
252 return cell.StringCellValue;
253 case CellType.Error: //ERROR:
254 return cell.ErrorCellValue;
255 case CellType.Formula: //FORMULA:
256 default:
257 return "=" + cell.CellFormula;
258 }
259 }
260 catch (Exception ex)
261 {
262 throw new Exception(ex.Message);
263 }
264 }
265
266 /// <summary>
267 /// 獲取單元格類型不定
268 /// </summary>
269 /// <param name="cell"></param>
270 /// <returns></returns>
271 private static object GetValueTypeForICell(ICell cell)
272 {
273 try
274 {
275 if (cell == null)
276 {
277 return null;
278 }
279 switch (cell.CellType)
280 {
281 case CellType.Blank: //BLANK:
282 return null;
283 case CellType.Boolean: //BOOLEAN:
284 return cell.BooleanCellValue;
285 case CellType.Numeric: //NUMERIC:
286 return cell.NumericCellValue;
287 case CellType.String: //STRING:
288 return cell.StringCellValue;
289 case CellType.Error: //ERROR:
290 return cell.ErrorCellValue;
291 case CellType.Formula: //FORMULA:
292 default:
293 return "=" + cell.CellFormula;
294 }
295 }
296 catch (Exception ex)
297 {
298 throw new Exception(ex.Message);
299 }
300 }
301
302 /// <summary>
303 /// Excel 轉換為DataSet
304 /// </summary>
305 /// <param name="fileName">文件名</param>
306 /// <returns>DataSet</returns>
307 public static DataSet ExcelToDataSet(string fileName)
308 {
309 try
310 {
311 if (!File.Exists(fileName))
312 {
313 throw new Exception("文件不存在");
314 }
315 else
316 {
317 DataSet ds = new DataSet();
318 using (FileStream reader = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
319 {
320 IWorkbook book = WorkbookFactory.Create(reader);
321 int cnt = book.NumberOfSheets;
322 if (cnt <= 0)
323 {
324 throw new Exception("文件不是Excel文件");
325 }
326
327 for (int i = 0; i < cnt; i++)
328 {
329 ISheet sheet = book.GetSheetAt(i);
330 DataTable dt = new DataTable(sheet.SheetName);
331 IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
332 for (int j = rowHead.FirstCellNum; j < rowHead.LastCellNum; j++)
333 {
334 ICell cell = rowHead.GetCell(j);
335 dt.Columns.Add(cell.StringCellValue);
336 }
337 for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
338 {
339 DataRow dr = dt.NewRow();
340 IRow row = sheet.GetRow(j);
341 for (int k = rowHead.FirstCellNum; k < rowHead.LastCellNum; k++)
342 {
343 dr[k] = row.GetCell(k).StringCellValue;
344 }
345 dt.Rows.Add(dr);
346 }
347 ds.Tables.Add(dt);
348 }
349 }
350 return ds;
351 }
352 }
353 catch (Exception ex)
354 {
355 throw new Exception(ex.Message);
356 }
357 }
358 #endregion Excel導出
359
360 #region Excel導出
361
362 /// <summary>
363 /// Excel導出
364 /// </summary>
365 /// <param name="dt">虛擬表</param>
366 /// <param name="fileName">文件路徑</param>
367 /// <param name="sheetName">Sheet路徑為空請傳null</param>
368 /// <returns></returns>
369 public static bool DataTableToXLS(DataTable dt, string fileName, string sheetName)
370 {
371 try
372 {
373 if (dt == null)
374 {
375 return false;
376 }
377 if (String.IsNullOrEmpty(sheetName))
378 {
379 sheetName = Path.GetFileName(fileName);
380 }
381 var book = new NPOI.HSSF.UserModel.HSSFWorkbook();
382 book.CreateSheet();
383 var sheet = book.CreateSheet(sheetName);
384
385 IRow rowHead = sheet.CreateRow(0);
386 for (int i = 0; i < dt.Columns.Count; i++)
387 {
388 ICell cell = rowHead.CreateCell(i);
389 cell.SetCellValue(dt.Columns[i].ColumnName);
390 }
391 for (int i = 0; i < dt.Rows.Count; i++)
392 {
393 IRow row = sheet.CreateRow(i + 1);
394 for (int j = 0; j < dt.Columns.Count; j++)
395 {
396 ICell cell = row.CreateCell(j);
397 cell.SetCellValue(dt.Rows[i][j].ToString());
398 }
399 }
400
401 using (FileStream fsWriter = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.Write))
402 {
403 book.Write(fsWriter);
404 return true;
405 }
406 }
407 catch (Exception ex)
408 {
409 throw new Exception(ex.Message);
410 }
411 }
412
413
414 /// <summary>
415 /// DataSet 導出 到Excel
416 /// </summary>
417 /// <param name="ds">DataSet 表名默認為sheet名</param>
418 /// <param name="fileName">文件路徑</param>
419 public static bool DataSetToExcel(DataSet ds, string fileName)
420 {
421 try
422 {
423 String extension = Path.GetExtension(fileName).ToUpper();
424 IWorkbook book = null;
425 if (extension == ".XLS")
426 {
427 book = DataSetToHSSFWordbook(ds);
428 }
429 else if (extension == ".XLSX")
430 {
431 book = DataSetToXSSFWorkbook(ds);
432 }
433 else
434 {
435 throw new Exception("導入格式必須為xls或者xlsx");
436 }
437
438 using (FileStream fsWriter = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write, FileShare.ReadWrite))
439 {
440 book.Write(fsWriter);
441 return true;
442 }
443 }
444 catch (Exception ex)
445 {
446 throw new Exception(ex.Message);
447 }
448 }
449 /// <summary>
450 /// DataSet 轉換為 XSSFWorkbook 07
451 /// </summary>
452 /// <param name="ds"></param>
453 /// <returns></returns>
454 private static NPOI.XSSF.UserModel.XSSFWorkbook DataSetToXSSFWorkbook(DataSet ds)
455 {
456 try
457 {
458 var book = new NPOI.XSSF.UserModel.XSSFWorkbook();
459 foreach (DataTable dt in ds.Tables)
460 {
461 ISheet sheet = book.CreateSheet(dt.TableName);
462 IRow rowHead = sheet.CreateRow(0);
463 ICellStyle style = book.CreateCellStyle();
464 style.BorderBottom = BorderStyle.Thin;
465 style.BorderTop = BorderStyle.Thin;
466 style.BorderLeft = BorderStyle.Thin;
467 style.BorderRight = BorderStyle.Thin;
468 IFont font = book.CreateFont();
469 font.FontHeightInPoints = 12;
470 font.IsBold = true;
471 style.SetFont(font);
472 for (int i = 0; i < dt.Columns.Count; i++)
473 {
474 ICell cell = rowHead.CreateCell(i);
475 cell.CellStyle = style;
476 cell.SetCellValue(dt.Columns[i].ColumnName);
477 }
478 font.IsBold = false;
479 style.SetFont(font);
480 for (int i = 0; i < dt.Rows.Count; i++)
481 {
482 IRow row = sheet.CreateRow(i + 1);
483 DataRow dr = dt.Rows[i];
484 for (int j = 0; j < dt.Columns.Count; j++)
485 {
486 ICell cell = row.CreateCell(j);
487 cell.CellStyle = style;
488 cell.SetCellValue(dr[j].ToString());
489 }
490 }
491 }
492 return book;
493 }
494 catch (Exception ex)
495 {
496 throw new Exception(ex.Message);
497 }
498 }
499
500 /// <summary>
501 /// DataSet 轉換為 HSSFWorkbook 03
502 /// </summary>
503 /// <param name="ds"></param>
504 /// <returns></returns>
505 private static NPOI.HSSF.UserModel.HSSFWorkbook DataSetToHSSFWordbook(DataSet ds)
506 {
507 try
508 {
509 var book = new NPOI.HSSF.UserModel.HSSFWorkbook();
510 var dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
511 dsi.Company = "上海金仕達衛寧軟件股份有限公司";
512 var si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
513 si.Subject = "區域HIS系統自動導出";
514 book.DocumentSummaryInformation = dsi;
515 book.SummaryInformation = si;
516
517 foreach (DataTable dt in ds.Tables)
518 {
519 ISheet sheet = book.CreateSheet(dt.TableName);
520 IRow rowHead = sheet.CreateRow(0);
521 ICellStyle style = book.CreateCellStyle();
522 style.BorderBottom = BorderStyle.Thin;
523 style.BorderTop = BorderStyle.Thin;
524 style.BorderLeft = BorderStyle.Thin;
525 style.BorderRight = BorderStyle.Thin;
526 IFont font = book.CreateFont();
527 font.FontHeightInPoints = 12;
528 font.IsBold = true;
529 style.SetFont(font);
530 for (int i = 0; i < dt.Columns.Count; i++)
531 {
532 ICell cell = rowHead.CreateCell(i);
533 cell.CellStyle = style;
534 cell.SetCellValue(dt.Columns[i].ColumnName);
535 }
536 font.IsBold = false;
537 style.SetFont(font);
538 for (int i = 0; i < dt.Rows.Count; i++)
539 {
540 IRow row = sheet.CreateRow(i + 1);
541 DataRow dr = dt.Rows[i];
542 for (int j = 0; j < dt.Columns.Count; j++)
543 {
544 ICell cell = row.CreateCell(j);
545 cell.CellStyle = style;
546 cell.SetCellValue(dr[j].ToString());
547 }
548 }
549 }
550 return book;
551 }
552 catch (Exception ex)
553 {
554 throw new Exception(ex.Message);
555 }
556 }
557
558 #endregion
559 }
560 }
