NET CORE+Vue 动态导出Excel

实体类

定义 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

 前端代码

 //导出
 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