我使用Epplus从流中读取xlsx文件.
它有一个错误,它无法读取我的工作簿中的一些列.如何在没有epplus的情况下将xlsx文件从流读取到数据表？
我的旧代码：
public static DataSet ReadExcelFile(Stream stream)
{
try
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader =
ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
DataSet result = excelReader.AsDataSet();
return result;
}
catch (Exception x)
{
throw x;
}
}
我没有报告它,但我尝试了很多组合.如果工作表中有空列,epplus阅读器无法正确读取列值.
解决方法
“It has a bug,it cant read some columns in my workbook”
你能描述一下这个bug,你有reported它还是已经知道的,你用的是什么版本？
这是一个使用EPPlus将excel文件加载到DataTable的简单方法.
public static DataTable getDataTableFromExcel(string path)
{
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; // adjust it accordingly( i've mentioned that this is a simple approach)
foreach (var firstRowCell in ws.Cells[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,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;
}
}
