C# 开源组件--NPOI读取Excel单元格中的公式值

from:https://www.cnblogs.com/guojingmail2009/archive/2004/01/13/6271005.html

今天在项目中碰到了EXCEL导入的数据是用公式生成，直接导入不了数据，写在博客中方便自已查询也可以给想找这方面的参考一下：

 

用NPOI导入时，在OFFICE 2007中的文件导入时一般会用XSSF，所以得到的类型会是CellType.FORMULA

 

  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         }