(.Net Core EPPlus使用)Aspose.Cells替代方案
由于之前使用的Aspose.Cells会有数量限制
所以转换一个excel解析包
首先引入NuGet包
点击查看代码
/// 根据文件路径获取datatable
/// <param name="filePath">文件路径</param>
private DataTable GetDataTableByFilePath(string filePath)
{
//最终的datatable
var resultData = new DataTable();
try
{
using (var stream = new FileStream(filePath, FileMode.Open))
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//不加会报错 : Epplus: Please set the ExcelPackage.LicenseContext property
using (ExcelPackage package = new ExcelPackage(stream))
{
ExcelWorksheet sheet = package.Workbook.Worksheets[0];
var fileName = Path.GetFileName(filePath).ToLower();
int beginRowIndex = 0;//数据开始行下标
int columnCount = sheet.Dimension.End.Column;//最大列号
int rowCount = sheet.Dimension.End.Row;//最大行号
//数据总表
var data = (object[,])sheet.Cells.Value;
//时间类型的列
List<string> datetimeColumnNameList = new List<string>()
{
"TimeCalendarDay"
};
for (int i = 0; i < columnCount; i++)
{
var colname = (data[beginRowIndex, i] ?? "").ToString();
if (!string.IsNullOrEmpty(colname))
{
resultData.Columns.Add(colname);
}
}
for (int i = beginRowIndex + 1; i < rowCount; i++)
{
DataRow dataRow = resultData.NewRow();
for (int j = 0; j < columnCount; j++)
{
var value = data[i, j];
//判断为日期的字段将特殊处理
if (datetimeColumnNameList.Contains(resultData.Columns[j].ColumnName))
{
value = DateTime.FromOADate(double.Parse(value.ToString())).ToString("yyyy/MM/dd", System.Globalization.DateTimeFormatInfo.InvariantInfo);
}
dataRow[j] = value;
}
resultData.Rows.Add(dataRow);
}
}
}
}
catch (Exception ex)
{
_logger.LogError(ex, ex.Message);
throw new Exception(ex.Message);
}
return resultData;
}