使用EPPLus将CSV或XLSX导入数据表

Question

我在SO上找到了一个线程，并试图将csv、xls或xlsx导入C#数据表，但我收到了以下堆栈跟踪错误：

System.Runtime.InteropServices.COMException
HResult=0x8003001D
Message=A disk error occurred during a write operation. (Exception from HRESULT: 0x8003001D (STG_E_WRITEFAULT))
Source=EPPlus

这是我试图用来导入文件的代码——我需要更改什么才能正常执行？

private OpenFileDialog openFileDialog1 = new OpenFileDialog();
public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true)
{
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();
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 (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.Rows.Add();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
}
return tbl;
}
}
private void btnImportSpreadsheet_Click(object sender, EventArgs e)
{
DataTable spreadsheetImport = new DataTable();
openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.csv;";
DialogResult result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
string file = openFileDialog1.FileName;
spreadsheetImport = GetDataTableFromExcel(file);
}
}

Answer 1

要使用csv，必须使用LoadFromText。另外请注意，EPPlus不能使用xls，它可以读取xlsx和xlsm并写入xlsx。

char csvDelimiter = ';';
using(var pck = new ExcelPackage())
{
ExcelWorksheet ws = null;
if(path.EndsWith(".csv"))
{
ws = pck.Workbook.Worksheets.Add("Sheet1");
ExcelTextFormat format = new ExcelTextFormat()
{
Delimiter = csvDelimiter 
};
ws.Cells[1, 1].LoadFromText(File.ReadAllText(path), format);
}
else
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
ws = pck.Workbook.Worksheets.First();
}
//The rest of your code
}

另一种选择是使用ExcelDataReader，因为它可以读取xls、xlsx和csv，但不能写入。它有一个扩展名，可以将文件转换为DataSet