定义 Excel专用特性名 (应用导出表格列名)
引入Nuget包
1.Microsoft.AspNetCore.Hosting
2.DotNetCore.NPOI
引入包
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Reflection;
using Microsoft.AspNetCore.Hosting;
Export类
private IHostingEnvironment _IHostingEnvironment;
public Export(IHostingEnvironment HostingEnvironment)
{
_IHostingEnvironment = HostingEnvironment;
}
/// <param name="list">数据集合</param>
/// <param name="sheetName">sheet名称</param>
/// <returns>文件流</returns>
public byte[] Excel<T>(List<T> list)
{
try
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("自定义（sheet名称）");
byte[] buffer = null;
// 填充表头
List<string> ListIt = new List<string>();
for (int i = 0; i < list.Count(); i++)
{
IRow cellsHeader = sheet.CreateRow(i);
Type ty = list[i].GetType();
if (i == 0)
{
int c = 1;
//添加列名
foreach (PropertyInfo pi in ty.GetProperties())
{
if (pi.GetCustomAttribute<DisplayNameAttribute>() != null)
{
ListIt.Add(pi?.Name); //获得字段名称
string DisplayName = pi.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName; //获取字段特性中文名称
cellsHeader.CreateCell(c).SetCellValue(DisplayName);
c++;
}
}//获取每个字段的列名
continue;
}
IRow row = sheet.CreateRow(i);
//添加数据
foreach (var item in ty.GetProperties())
{
int index = ListIt.IndexOf(item.Name);//获得字段名称
if (index > -1)
{
var Value = item.GetValue(list[i-1], null)?.ToString() ?? " ";
row.CreateCell(index + 1).SetCellValue(Value);
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
buffer = ms.GetBuffer();
ms.Close();
}
return buffer;
}
catch (Exception)
{
return null;
}
}
控制器代码
private IHostingEnvironment _IHostingEnvironment;
public Export(IHostingEnvironment HostingEnvironment)
{
_IHostingEnvironment = HostingEnvironment;
}
/// <summary>
/// 动态导出Excel
/// </summary>
/// <param name="list">数据集</param>
/// <returns>返回流</returns>
[HttpGet("ExportExcel")]
public IActionResult ExportExcel(List<SysCompany> list)
{
Tool.Export ex = new Tool.Export(_IHostingEnvironment);
byte[] bytes = ex.Excel<SysCompany>(list);
return File(bytes, "application/octet-stream");
}
注释： File是控制器自带方法，不是IO流中的File
vue前端代码
//导出
handleExportExcel() {
codeService.Export(参数).then(res => {
const link = document.createElement("a");
link.href = URL.createObjectURL(res);
link.setAttribute("download", "文件名" + ".xlsx"); //下载的文件名以及文件格式
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}).catch(err => {
console.log(err)
})
},
最终效果图
演示浏览器----Google
————————————————
版权声明：本文为CSDN博主「️渐渐」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_58852027/article/details/121654002