在.net core中导入Excel
引言
之前在.net Framework中经常有Excel的导入导出，现在.net core越来越流行，我们就来讲一下在.net core中是如何导入Excel到DataSet中的。
环境
- .net Core
- NPOI （通过Nuget进行安装）
创建ExcelHelper
public class ExcelHeler
{
private static ILogger<ExcelHeler> logger;
/// <summary>
/// 读取Excel多Sheet数据
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="sheetName">Sheet名</param>
/// <returns></returns>
public static DataSet ReadExcelToDataSet(string filePath, string sheetName = null)
{
if (!File.Exists(filePath))
{
//logger.LogError($"未找到文件{filePath}");
return null;
}
//获取文件信息
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
IWorkbook workbook = WorkbookFactory.Create(fs);
//获取sheet信息
ISheet sheet = null;
DataSet ds = new DataSet();
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
//logger.LogError($"{filePath}未找到sheet:{sheetName}");
return null;
}
DataTable dt = ReadExcelFunc(workbook, sheet);
ds.Tables.Add(dt);
}
else
{
//遍历获取所有数据
int sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; i++)
{
sheet = workbook.GetSheetAt(i);
if (sheet != null)
{
DataTable dt = ReadExcelFunc(workbook, sheet);
if (dt != null) ds.Tables.Add(dt);
}
}
}
return ds;
}
/// <summary>
/// 读取Excel多Sheet数据
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="sheetName">Sheet名</param>
/// <returns></returns>
public static DataSet ReadExcelToDataSet(FileStream fs, string sheetName = null)
{
IWorkbook workbook = WorkbookFactory.Create(fs);
//获取sheet信息
ISheet sheet = null;
DataSet ds = new DataSet();
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
//logger.LogError($"{filePath}未找到sheet:{sheetName}");
return null;
}
DataTable dt = ReadExcelFunc(workbook, sheet);
ds.Tables.Add(dt);
}
else
{
//遍历获取所有数据
int sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; i++)
{
sheet = workbook.GetSheetAt(i);
if (sheet != null)
{
DataTable dt = ReadExcelFunc(workbook, sheet);
if(dt !=null)
ds.Tables.Add(dt);
}
}
}
return ds;
}
/// <summary>
/// 读取Excel信息
/// </summary>
/// <param name="workbook">工作区</param>
/// <param name="sheet">sheet</param>
/// <returns></returns>
private static DataTable ReadExcelFunc(IWorkbook workbook, ISheet sheet)
{
DataTable dt = new DataTable();
//获取列信息
IRow cells = sheet.GetRow(sheet.FirstRowNum);
//空数据化返回
if (cells == null) return null;
int cellsCount = cells.PhysicalNumberOfCells;
//空列返回
if (cellsCount == 0) return null;
int emptyCount = 0;
int cellIndex = sheet.FirstRowNum;
List<string> listColumns = new List<string>();
bool isFindColumn = false;
while (!isFindColumn)
{
emptyCount = 0;
listColumns.Clear();
for (int i = 0; i < cellsCount; i++)
{
if (string.IsNullOrEmpty(cells.GetCell(i).StringCellValue))
{
emptyCount++;
}
listColumns.Add(cells.GetCell(i).StringCellValue);
}
//这里根据逻辑需要，空列超过多少判断
if (emptyCount == 0)
{
isFindColumn = true;
}
cellIndex++;
cells = sheet.GetRow(cellIndex);
}
foreach (string columnName in listColumns)
{
if (dt.Columns.Contains(columnName))
{
//如果允许有重复列名，自己做处理
continue;
}
dt.Columns.Add(columnName, typeof(string));
}
//开始获取数据
int rowsCount = sheet.PhysicalNumberOfRows;
var rowIndex = 1;
DataRow dr = null;
//空数据化返回
if(rowsCount <= 1) { return null; }
for (int i = rowIndex; i < rowsCount; i++)
{
cells = sheet.GetRow(i);
dr = dt.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
//这里可以判断数据类型
switch (cells.GetCell(j).CellType)
{
case CellType.String:
dr[j] = cells.GetCell(j).StringCellValue;
break;
case CellType.Numeric:
dr[j] = cells.GetCell(j).NumericCellValue.ToString();
break;
case CellType.Unknown:
dr[j] = cells.GetCell(j).StringCellValue;
break;
}
}
dt.Rows.Add(dr);
}
return dt;
}
}
如何调用
/// <summary>
/// 通过Excel上传所有导游的返款
/// </summary>
/// <returns></returns>
[HttpPost]
public ResultModel<bool> UploadGuideRebates()
{
var account = GetAccountInformation();
AccountInfo accountInfo = new AccountInfo()
{
UserName = account.Log_OperationPerson,
DeviceId = account.Log_Device
};
var resultModel = new ResultModel<bool>();
try
{
var files = Request.Form.Files;
long size = files.Sum(f => f.Length);
//判断是否存在该设备的日志文件夹
string webRootPath = hostingEnv.WebRootPath;
string filePath = webRootPath + $"/GuideRebatesFiles/";
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
foreach (var formFile in files)
{
if (formFile.Length > 0)
{
string fileExt = formFile.FileName.Split('.')[1]; //文件扩展名，不含“.”
long fileSize = formFile.Length; //获得文件大小，以字节为单位
string newFileName = Guid.NewGuid() + "." + fileExt; //随机生成新的文件名
string fileNamePath = Path.Combine(filePath, newFileName);
using (var stream = new FileStream(fileNamePath, FileMode.Create))
{
formFile.CopyTo(stream);
stream.Flush();
}
//调用ExcelHelper方法
DataSet ds = ExcelHeler.ReadExcelToDataSet(fileNamePath);
guideService.UpdateGuideRebatesList(accountInfo, ds.Tables[0]);
}
}
//var guideList = guideService.GetGuideList(accountInfo);
resultModel.Success = true;
}
catch (Exception ex)
{
resultModel.SetResult(false, ErrorType.InnerExction, null, new List<string> { "内部异常,请稍后重试!" });
}
return resultModel;
}
