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         //非空数据行数据添加到DataTable100         if (!emptyRow)101         {102           dt.Rows.Add(itemArray);103         }104       }105       return dt;106     }

原标题：NPOI读取Excel帮助类，支持xls与xlsx，实现公式解析，空行的处理

关键词：excel