NPOI讀取Excel幫助類，支持xls與xlsx，實現公式解析，空行的處理，npoixlsx
NPOI讀取Excel（2003或者2010）返回DataTable。支持公式解析，空行處理。
1 /// <summary>讀取excel
2 /// 默認第一行為表頭
3 /// </summary>
4 /// <param name="strFileName">excel文檔絕對路徑</param>
5 /// <param name="rowIndex">內容行偏移量，第一行為表頭，內容行從第二行開始則為1</param>
6 /// <returns></returns>
7 public static DataTable Import(string strFileName, int rowIndex)
8 {
9 DataTable dt = new DataTable();
10
11 IWorkbook hssfworkbook;
12 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
13 {
14 hssfworkbook = WorkbookFactory.Create(file);
15 }
16 ISheet sheet = hssfworkbook.GetSheetAt(0);
17
18 IRow headRow = sheet.GetRow(0);
19 if (headRow != null)
20 {
21 int colCount = headRow.LastCellNum;
22 for (int i = 0; i < colCount; i++)
23 {
24 dt.Columns.Add("COL_" + i);
25 }
26 }
27
28 for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++)
29 {
30 IRow row = sheet.GetRow(i);
31 bool emptyRow = true;
32 object[] itemArray = null;
33
34 if (row != null)
35 {
36 itemArray = new object[row.LastCellNum];
37
38 for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
39 {
40
41 if (row.GetCell(j) != null)
42 {
43
44 switch (row.GetCell(j).CellType)
45 {
46 case CellType.NUMERIC:
47 if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期類型
48 {
49 itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
50 }
51 else//其他數字類型
52 {
53 itemArray[j] = row.GetCell(j).NumericCellValue;
54 }
55 break;
56 case CellType.BLANK:
57 itemArray[j] = string.Empty;
58 break;
59 case CellType.FORMULA:
60 if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx")
61 {
62 XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook);
63 if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
64 {
65 itemArray[j] = eva.Evaluate(row.GetCell(j)).NumberValue;
66 }
67 else
68 {
69 itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
70 }
71 }
72 else
73 {
74 HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook);
75 if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
76 {
77 itemArray[j] = eva.Evaluate(row.GetCell(j)).NumberValue;
78 }
79 else
80 {
81 itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
82 }
83 }
84 break;
85 default:
86 itemArray[j] = row.GetCell(j).StringCellValue;
87 break;
88
89 }
90
91 if (itemArray[j] != null && !string.IsNullOrEmpty(itemArray[j].ToString().Trim()))
92 {
93 emptyRow = false;
94 }
95 }
96 }
97 }
98
99 //非空數據行數據添加到DataTable
100 if (!emptyRow)
101 {
102 dt.Rows.Add(itemArray);
103 }
104 }
105 return dt;
106 }
