导入Excel时因为文件太大报错,终于找到了EPPLUS。直接nuget搜索使用即可。
using (FileStream file = new FileStream(importExcelPath, FileMode.Open, FileAccess.Read))
{
dt = ServiceOpt.getDataTableFromExcel(importExcelPath);
}
实现
public static DataTable getDataTableFromExcel(string path)
{
ExcelPackage.LicenseContext = LicenseContext.Commercial;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
DataTable tbl = new DataTable();
bool hasHeader = true; //调整它(我已经提到这是一个简单的方法)
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format(“Column {0}”, firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
tbl.Rows.Add(row);
}
return tbl;
}
}