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 }