public static List <DataTable> ImportAllSheetToDataTable(string excelFile) { List <DataTable> result = new List <DataTable>(); string importExcelFile = string.Empty; string extension = string.Empty; Stream stream = null; try { if (!File.Exists(excelFile)) { throw new FileNotFoundException("文件不存在!"); } else { extension = Path.GetExtension(excelFile); importExcelFile = Path.Combine(Path.GetDirectoryName(excelFile), "B667F371C4C3BD7A14346BF2F7307E31" + extension); File.Copy(excelFile, importExcelFile, true); } IWorkbook workbook = null; stream = new MemoryStream(File.ReadAllBytes(excelFile)); if (extension == ".xls") { workbook = new HSSFWorkbook(stream); } else if (extension == ".xlsx") { workbook = new XSSFWorkbook(stream); } if (workbook == null || workbook.NumberOfSheets == 0) { return(result); } for (int i = 0; i < workbook.NumberOfSheets; i++) { DataTable dt = new DataTable(); ISheet sheet = workbook.GetSheetAt(i); if (sheet.PhysicalNumberOfRows > 0) { IRow headerRow = sheet.GetRow(sheet.FirstRowNum); int cellCount = headerRow.Cells.Count; for (int j = headerRow.FirstCellNum; j < cellCount; j++) { string cellValue = headerRow.GetCellValue(j) == null ? null : headerRow.GetCellValue(j).ToString().Trim(); DataColumn column = new DataColumn(cellValue); if (dt.Columns.Contains(column.ColumnName)) { column.ColumnName += "1"; } dt.Columns.Add(column); } if (sheet.PhysicalNumberOfRows > 1) { for (int a = (sheet.FirstRowNum + 1); a < sheet.LastRowNum + 1; a++) { IRow row = sheet.GetRow(a); if (row == null) { continue; } DataRow dr = dt.NewRow(); for (int b = row.FirstCellNum; b < cellCount; b++) { dr[b] = row.GetCellValue(b); } dt.Rows.Add(dr); } } } result.Add(dt); } } catch (Exception ex) { throw ex; } finally { if (stream != null) { stream.Close(); } if (File.Exists(importExcelFile)) { File.Delete(importExcelFile); } } return(result); }