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);
}
