//引入 ClosedXML插件
using ClosedXML.Excel;
//将 Excel2007 格式的文件 解析到 DataTable
static DataTable RenderFromExcel2007(Stream excelFilesStream)
{
DataTable table = new DataTable();
var workbook = new XLWorkbook(excelFilesStream);
//构造表头
foreach (var sheet in workbook.Worksheets)
{
int rows = sheet.RowCount();
var cloumns = sheet.Columns();
foreach (var xlColumn in cloumns)
{
DataColumn column = new DataColumn(xlColumn.Cell(1).Value.ToString());
table.Columns.Add(column);
}
}
foreach (var sheet in workbook.Worksheets)
{
var cloumns = sheet.Columns();
int j = 1;
var a = cloumns.Count();
foreach (var row in sheet.Rows())
{
if (row.RowNumber() == 1)
continue;
if (row != null)
{
DataRow dataRow = table.NewRow();
for (j = 1; j < a; j++)
{
dataRow[j - 1] = row.Cell(j).Value.ToString();
}
table.Rows.Add(dataRow);
}
}
}
return table;
}
//引入NPOI 插件
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
//将 Excel2003 格式的文件 解析到 DataTable
static DataTable RenderFromExcel(Stream excelFileStream)
{
using (excelFileStream)
{
var workbook = new HSSFWorkbook(excelFileStream);
ISheet sheet = workbook.GetSheetAt(0);//取第一个表
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
return table;
}
}