.NET小笔记

下载比较新的NPOI组件支持excel2007以上的,把.dll添加引用

引入命名空间

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

调用下面方法，可以读取到比较齐全类型的值

 1 /// <summary> 2     /// 获取excel内容 3     /// </summary> 4     /// <param name="filePath">excel文件路径</param> 5     /// <returns></returns> 6     public static DataTable ImportExcel(string filePath) 7     { 8       DataTable dt = new DataTable(); 9       using (FileStream fsRead = File.OpenRead(filePath))10       {11         IWorkbook wk = null;12         //获取后缀名13         string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();14        //判断是否是excel文件15         if (extension == ".xlsx" || extension == ".xls")16         {17           //判断excel的版本18           if (extension== ".xlsx")19           {20             wk = new XSSFWorkbook(fsRead);21           }22           else23           {24             wk = new HSSFWorkbook(fsRead);25           }26         27         //获取第一个sheet28         ISheet sheet = wk.GetSheetAt(0);29         //获取第一行30         IRow headrow=sheet.GetRow(0);31         //创建列32         for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)33         {34           DataColumn datacolum = new DataColumn(headrow.GetCell(i).StringCellValue);35           dt.Columns.Add(datacolum);36         }37         //读取每行,从第二行起38         for (int r = 1; r <= sheet.LastRowNum; r++)39         {40           DataRow dr = dt.NewRow();41           //获取当前行42           IRow row = sheet.GetRow(r);43           //读取每列44           for (int j = 0; j < row.Cells.Count; j++)45           {46             ICell cell = row.GetCell(j); //一个单元格47             dr[j] = GetCellValue(cell); //获取单元格的值48            49           }50            51           dt.Rows.Add(dr); //把每行追加到DataTable52         }53         }54         55       }56       return dt;57     }58     //对单元格进行判断取值59     private static string GetCellValue(ICell cell)60     {61       if (cell == null)62         return string.Empty;63       switch (cell.CellType)64       {65         case CellType.BLANK: //空数据类型66           return string.Empty;67         case CellType.BOOLEAN: //bool类型68           return cell.BooleanCellValue.ToString();69         case CellType.ERROR:70           return cell.ErrorCellValue.ToString();71         case CellType.NUMERIC: //数字类型72           if(HSSFDateUtil.IsCellDateFormatted(cell))//日期类型73           {74             return cell.DateCellValue.ToString();75           }76           else //其它数字77           {78             return cell.NumericCellValue.ToString ();79           }80         case CellType.Unknown: //无法识别类型81         default: //默认类型82           return cell.ToString();//83         case CellType.STRING: //string 类型84           return cell.StringCellValue;85         case CellType.FORMULA: //带公式类型86           try87           {88             HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);89             e.EvaluateInCell(cell);90             return cell.ToString();91           }92           catch93           {94             return cell.NumericCellValue.ToString();95           }96       }97     }