1         /// <summary>  2         /// 读取Excel  3         /// </summary>  4         /// <param name="filePath">路径</param>  5         /// <param name="startRow">每个表格从多少行开始 格式：["",""]</param>  6         /// <returns></returns>  7         public static DataSet ReadExcel(string filePath, string[] startRow)  8         {  9             IWorkbook book = null; 10             // XSSFWorkbook book = null; 11             DataSet ds = new DataSet(); 12             string excelType = ""; 13             string str = filePath.Substring(filePath.LastIndexOf(‘.‘) + 1); 14             if (str == "xlsx") 15                 excelType = "xlsx"; 16             else if (str == "xls") 17                 excelType = "xls"; 18             else 19                 throw new Exception("Excel格式不正确"); 20             try 21             { 22                 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 23                 { 24                     if (excelType == "xlsx") 25                         book = new XSSFWorkbook(file); 26                     else 27                         book = new HSSFWorkbook(file); 28  29                     for (int a = 0; a < book.NumberOfSheets; a++) 30                     { 31                         ISheet sheet = (ISheet)book.GetSheetAt(a); 32                         int sheetRowsCount = sheet.LastRowNum; 33  34                         int row = 0; 35                         if (startRow != null) 36                         { 37                             if (startRow.Length >= a) 38                                 row = int.Parse(startRow[a]); 39                         } 40  41                         IRow iRow = (IRow)sheet.GetRow(row); 42                         DataTable dt = new DataTable(); 43  44                         if (sheet.GetRow(row) == null) 45                             return ds; 46                         for (int i = 0; i < (sheet.GetRow(row).LastCellNum); i++) 47                         { 48                             object obj = GetValueType(iRow.GetCell(i) as ICell); 49                             if (obj == null || obj.ToString() == string.Empty) 50                                 dt.Columns.Add(new DataColumn("Columns" + i.ToString().Trim())); 51                             else 52                                 dt.Columns.Add(new DataColumn(obj.ToString().Trim())); 53                         } 54                         for (int i = row + 1; i <= sheetRowsCount; i++) 55                         { 56                             DataRow dr = dt.NewRow(); 57                             IRow ro = (IRow)sheet.GetRow(i); 58                             for (int j = 0; j < ro.LastCellNum; j++) 59                             { 60                                 dr[j] = GetValueType(sheet.GetRow(i).GetCell(j) as ICell); 61                             } 62                             dt.Rows.Add(dr); 63                         } 64                         dt.TableName = sheet.SheetName; 65                         ds.Tables.Add(dt); 66                     } 67                     return ds; 68                 } 69             } 70             catch (Exception e) 71             { 72                 throw new Exception(e.Message); 73             } 74         } 75  76         /// <summary>   77         /// 获取单元格类型(xls)   78         /// </summary>   79         /// <param name="cell"></param>   80         /// <returns></returns>   81         private static object GetValueType(ICell cell) 82         { 83             if (cell == null) 84                 return null; 85             switch (cell.CellType) 86             { 87                 case CellType.BLANK: //BLANK:   88                     return null; 89                 case CellType.BOOLEAN: //BOOLEAN:   90                     return cell.BooleanCellValue; 91                 case CellType.NUMERIC: //NUMERIC: 92                     short format = cell.CellStyle.DataFormat; 93                     if (format == 14 || format == 31 || format == 57 || format == 58) 94                     { 95                         return cell.DateCellValue.ToString("yyy-MM-dd"); 96                     } 97                     else 98                     { 99                         return cell.NumericCellValue;100                     }101                 case CellType.STRING: //STRING:  102                     return cell.StringCellValue;103                 case CellType.ERROR: //ERROR:  104                     return cell.ErrorCellValue;105                 case CellType.FORMULA: //FORMULA:  106                 default:107                     return "=" + cell.CellFormula;108             }109         }