npoi 执行公式_C# 开源组件--NPOI读取Excel单元格中的公式值

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

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

1 /// 读取excel

2 /// 默认第一行为表头

3 ///

4 /// excel文档绝对路径

5 /// 内容行偏移量，第一行为表头，内容行从第二行开始则为1

6 ///

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 }