将excel文件保存为csv格式，无需在.net core 3.1中打开

Question

我遇到一种情况，需要在.net核心控制台应用程序中下载excel文件(.xlsx(并将其保存为.CSV格式。

由于Microsoft.Interop软件包与.Net core 3.1不兼容，我还可以使用其他什么方法将Excel文件保存为.CSV？

感谢您的建议。

Answer 1

这是SO上多个现有答案的组合。

第一个是从这里使用ClosedXML 将xlsx转换为DataTable

using ClosedXML.Excel;
...
public static DataTable GetDataFromExcel(string path, dynamic worksheet)
{
//Save the uploaded Excel file.

DataTable dt = new DataTable();
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(path))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(worksheet);
//Create a new DataTable.
//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
if (!string.IsNullOrEmpty(cell.Value.ToString()))
{
dt.Columns.Add(cell.Value.ToString());
}
else
{
break;
}
}
firstRow = false;
}
else
{
int i = 0;
DataRow toInsert = dt.NewRow();
foreach (IXLCell cell in row.Cells(1, dt.Columns.Count))
{
try
{
toInsert[i] = cell.Value.ToString();
}
catch (Exception ex)
{
//Handle this, or don't.
}
i++;
}
dt.Rows.Add(toInsert);
}
}
return dt;
}

如果需要进行任何数据转换，请在数据位于DataTable中时进行。

然后使用CSVHelper导出为CSV(所以我发现有一个解决方案不使用文化信息，这是几次更新前添加到库中的要求(：

using CSVHelper;
using System.Globilization;
....
public static void SaveCSV(DataTable records)
{
string newFile = @"C:somePath.csv";
using (StreamWriter writer = new StreamWriter(newFile))
{
using (CsvWriter csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
//add headers 
foreach (DataColumn dc in records.Columns)
{
csv.WriteField(dc.ColumnName);
}
csv.NextRecord();
foreach(DataRow dr in records.Rows)
{
for (int i = 0; i< records.Columns.Count; i++)
{
csv.WriteField(dr[i]);
}
csv.NextRecord();
}
}
}
}