NPOI 读取excel到DataTable读取公式列

 1 /// <summary>读取excel 到datatable    
 2         /// 默认第一行为表头，导入第一个工作表   
 3         /// </summary>      
 4         /// <param name="strFileName">excel文档路径</param>      
 5         /// <returns></returns>      
 6         public static DataTable ExcelToDataTable(string strFileName)
 7         {
 8             DataTable dt = new DataTable();
 9             FileStream file = null;
10             IWorkbook Workbook = null;
11             try
12             {
13 
14                 using (file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))//C#文件流读取文件
15                 {
16                     if (strFileName.IndexOf(".xlsx") > 0)
17                         //把xlsx文件中的数据写入Workbook中
18                         Workbook = new XSSFWorkbook(file);
19 
20                     else if (strFileName.IndexOf(".xls") > 0)
21                         //把xls文件中的数据写入Workbook中
22                         Workbook = new HSSFWorkbook(file);
23 
24                     if (Workbook != null)
25                     {
26                         ISheet sheet = Workbook.GetSheetAt(0);//读取第一个sheet
27                         System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
28                         //得到Excel工作表的行 
29                         IRow headerRow = sheet.GetRow(0);
30                         //得到Excel工作表的总列数  
31                         int cellCount = headerRow.LastCellNum;
32 
33                         for (int j = 0; j < cellCount; j++)
34                         {
35                             //得到Excel工作表指定行的单元格  
36                             ICell cell = headerRow.GetCell(j);
37                             dt.Columns.Add(cell.ToString());
38                         }
39 
40                         for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
41                         {
42                             IRow row = sheet.GetRow(i);
43                             DataRow dataRow = dt.NewRow();
44                             if (row.GetCell(0) != null && row.GetCell(0).ToString() != "")
45                             {
46                                 for (int j = row.FirstCellNum; j < cellCount; j++)
47                                 {
48                                     
49                                     try {
50                                         if (row.GetCell(j).CellType == CellType.Formula)
51                                         {
52                                             dataRow[j] = row.GetCell(j).StringCellValue;
53                                         }
54                                         else
55                                         {
56                                             dataRow[j] = row.GetCell(j)+"";
57                                         }
58                                     }
59                                     catch (Exception e)
60                                     {
61                                         var a = i;
62                                     }
63                                     
64                                 }
65                                 dt.Rows.Add(dataRow);
66                             }
67                         }
68                     }
69                     return dt;
70                 }
71             }
72 
73             catch (Exception e)
74             {
75                 if (file != null)
76                 {
77                     file.Close();//关闭当前流并释放资源
78                 }
79                 return null;
80             }
81         }