Asp.NET Core 导出数据到 Excel 文件

大槐树人家 · 发表于 2023-2-23 19:32:18

在Asp.Net Core开发中，使用NPOI将数据导出到Excel文件中，并返回给前端。
service 层代码：

/// <summary>
/// 将数据导出到excel
/// </summary>
/// <param name="projectId"></param>
/// <param name="ids"></param>
/// <returns></returns>
public async Task<IWorkbook> ExportToExcel(Guid projectId, List<Guid> ids = null)
{
var entities = await attendanceRecordRepository.Find(x =>
x.ProjectId == projectId)
.ToListAsync();
if (entities == null || entities.Count == 0) return null;
//创建工作簿
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("考勤记录");
//添加表头
IRow tableHeader = sheet.CreateRow(0);
var colNames = new List<string>()
{
"姓名", "身份证号", "工号", "人员类型", "办公室", "工种", "电话号码", "状态", "打卡时间"
};
for (int i = 0; i < colNames.Count; i++)
{
tableHeader.CreateCell(i).SetCellValue(colNames[i]);
// 自适应宽高
sheet.AutoSizeColumn(i);
}
// 将数据写入表格中
if (ids == null || ids.Count == 0)
{
// 导出全部
for (int i = 0; i < entities.Count; i++)
{
// 跳过表头
var row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(entities[i].Name);
row.CreateCell(1).SetCellValue(entities[i].ID_card);
row.CreateCell(2).SetCellValue(entities[i].EmployeeNumber);
row.CreateCell(3).SetCellValue(entities[i].PersonnelType);
row.CreateCell(4).SetCellValue(entities[i].OfficeLocation);
row.CreateCell(5).SetCellValue(entities[i].PostName);
row.CreateCell(6).SetCellValue(entities[i].PhoneNumber);
row.CreateCell(7).SetCellValue(entities[i].Type);
row.CreateCell(8).SetCellValue(entities[i].CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
else
{
// 导出部分
int rowIndex = 1;
foreach (var entity in entities)
{
foreach (var id in ids)
{
if (entity.Id == id)
{
var row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(entity.Name);
row.CreateCell(1).SetCellValue(entity.ID_card);
row.CreateCell(2).SetCellValue(entity.EmployeeNumber);
row.CreateCell(3).SetCellValue(entity.PersonnelType);
row.CreateCell(4).SetCellValue(entity.OfficeLocation);
row.CreateCell(5).SetCellValue(entity.PostName);
row.CreateCell(6).SetCellValue(entity.PhoneNumber);
row.CreateCell(7).SetCellValue(entity.Type);
row.CreateCell(8).SetCellValue(entity.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
rowIndex++;
}
}
}
}
return workbook;
}

复制代码

controller 层代码：

/// <summary>
/// 将数据导出为Excel文件
/// </summary>
/// <param name="projectId"></param>
/// <param name="ids"></param>
/// <returns></returns>
[HttpPost("export-to-excel")]
public async Task<IActionResult> ExportToExcel(Guid projectId, List<Guid> ids = null)
{
var workbook = await _attendanceRecordService.ExportToExcel(projectId, ids);
if(workbook != null)
{
var path = Path.Combine(webHostEnvironment.ContentRootPath, "FileName");
if (!Directory.Exists(path)) //没有此路径就新建
{
Directory.CreateDirectory(path);
}
var fileFullName = Path.Combine(path, $"{DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss")}.xlsx");
// 将表格写入文件流
FileStream creatStream = new FileStream(fileFullName, FileMode.Create, FileAccess.Write);
workbook.Write(creatStream);
creatStream.Close();
// 将表格文件转换成可读的文件流
FileStream fileStream = new FileStream(fileFullName, FileMode.Open, FileAccess.Read, FileShare.Read); //读
// 将可读文件流写入 byte[]
byte[] bytes = new byte[fileStream.Length];
fileStream.Read(bytes, 0, bytes.Length);
fileStream.Close();
// 把 byte[] 转换成 Stream （创建其支持存储区为内存的流。）
MemoryStream stream = new(bytes);
try
{
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
$"{DateTime.Now.ToString("yyyyMMddHHmmss")}考勤记录");
}
finally
{
System.IO.File.Delete(fileFullName);
}
}
return BadRequest();
}

复制代码

来源:https://www.cnblogs.com/rockrose/archive/2023/02/23/17149093.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！