NET Core导出Excel表格

1、首先用到Get包

NPOI

2、命名空间

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

3、返回前台的是下载连接

接口：

 #region 导出工匠信息
        [HttpGet]
        public IActionResult DownloadOrders(string sysdepartmentid)
        {
            using (var lifetimeScop = Container.BeginLifetimeScope())
            {
                IPictureMethodsService _PictureMethods = lifetimeScop.Resolve<IPictureMethodsService>();
                var export = _PictureMethods.Excelex(sysdepartmentid);
                return File(export, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "工匠信息.xlsx");
            }
        }
        #endregion

Excelex方法：

  #region 导出excel表格数据
        public MemoryStream Excelex(string sysdepartmentid)
        {
            CraftsmanService service = new CraftsmanService();
            var memory = new MemoryStream();
            var query = service.Postlist(o => string.IsNullOrEmpty(sysdepartmentid) ? o.Isdeleted == 0 : o.SysdepartmentId == int.Parse(sysdepartmentid) && o.Isdeleted == 0).ToList();
            
                var newFile = Utils.ConfigUtil.GetSection("ExportTemplate").Value;
                if (System.IO.File.Exists(newFile))
                {
                    System.IO.File.Delete(newFile);
                }
                using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
                {
                //HSSFWorkbook workbook = new HSSFWorkbook();.xls（选用xls的话，最后返回下载的后缀名改为xls）
                XSSFWorkbook workbook = new XSSFWorkbook();//.xlsx，（推荐xlsx，2007版本以上都是xlsx的）
                var sheet = workbook.CreateSheet("orders");
                    var header = sheet.CreateRow(0);
                    header.CreateCell(0).SetCellValue("证书类型");
                    header.CreateCell(1).SetCellValue("姓名");
                    header.CreateCell(2).SetCellValue("性别");
                    header.CreateCell(3).SetCellValue("学历");
                    header.CreateCell(4).SetCellValue("手机号");
                    header.CreateCell(5).SetCellValue("身份证号");
                    header.CreateCell(6).SetCellValue("户籍地址");
                    header.CreateCell(7).SetCellValue("常住地址");
                    header.CreateCell(8).SetCellValue("头像");
                    header.CreateCell(9).SetCellValue("身份证正面");
                    header.CreateCell(10).SetCellValue("身份证反面");
                    header.CreateCell(11).SetCellValue("所属乡镇");
                    header.CreateCell(12).SetCellValue("证书链接");
                    header.CreateCell(13).SetCellValue("证书编号");
                    header.CreateCell(14).SetCellValue("证书有效期");

                    var rowIndex = 1;
                    for (int i = 0; i < query.Count; i++)
                    {
                        var datarow = sheet.CreateRow(rowIndex);

                        datarow.CreateCell(0).SetCellValue(query[i].CertificateType);
                        datarow.CreateCell(1).SetCellValue(Base.SM4.Sm4Crypto.DecryptECB(query[i].CraftsmanName));
                        datarow.CreateCell(2).SetCellValue(query[i].CraftsmanSex);
                        datarow.CreateCell(3).SetCellValue(query[i].CraftsmanEducation);
                        datarow.CreateCell(4).SetCellValue(Base.SM4.Sm4Crypto.DecryptECB(query[i].CraftsmanPhone));
                        datarow.CreateCell(5).SetCellValue(Base.SM4.Sm4Crypto.DecryptECB(query[i].IdCard));
                        datarow.CreateCell(6).SetCellValue(query[i].HjAddress);
                        datarow.CreateCell(7).SetCellValue(query[i].CzAddress);
                        datarow.CreateCell(8).SetCellValue(query[i].CraftsmanUrl);
                        datarow.CreateCell(9).SetCellValue(query[i].IcFrontUrl);
                        datarow.CreateCell(10).SetCellValue(query[i].IcBackUrl);
                        datarow.CreateCell(11).SetCellValue(query[i].Township);
                        datarow.CreateCell(12).SetCellValue(query[i].CertificateUrl);
                        datarow.CreateCell(13).SetCellValue(query[i].CertificateId);
                        datarow.CreateCell(14).SetCellValue(query[i].CertificateTimes);

                        rowIndex++;
                    }
                    workbook.Write(fs);
                }
                using (var stream = new FileStream(newFile, FileMode.Open))
                {
                    stream.CopyTo(memory);
                }
                memory.Position = 0;
            return memory;
        }
        #endregion

 4.返回前台的是路径

public string Excelexport(string sysdepartmentid)
        {
            CraftsmanService service = new CraftsmanService();
            var query = service.Postlist(o =>string.IsNullOrEmpty(sysdepartmentid)?o.Isdeleted==0:o.SysdepartmentId== int.Parse(sysdepartmentid)&& o.Isdeleted == 0).ToList();
            if (query.Count!=0)
            {
                //var newFile = Utils.ConfigUtil.GetSection("ExportTemplate").Value;
                string rootUrl = Directory.GetCurrentDirectory();
                string date = DateTime.Now.ToString("yyyyMMdd");
                string rootPath= "Upload/DownloadXls/" + date;
                string fileName= Guid.NewGuid().ToString() + Path.GetExtension("craftsman.xlsx");
                string newFile = rootPath + "/" + fileName;
                string url = rootUrl + "/"+ rootPath;
                if (!Directory.Exists(url))
                {
                    Directory.CreateDirectory(url);
                }
                //if (System.IO.File.Exists(newFile))
                //{
                //    System.IO.File.Delete(newFile);
                //}
                using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
                {
                    XSSFWorkbook workbook = new XSSFWorkbook();
                    var sheet = workbook.CreateSheet("orders");

                    var header = sheet.CreateRow(0);
                    header.CreateCell(0).SetCellValue("证书类型");
                    header.CreateCell(1).SetCellValue("姓名");
                    header.CreateCell(2).SetCellValue("性别");
                    header.CreateCell(3).SetCellValue("学历");
                    header.CreateCell(4).SetCellValue("手机号");
                    header.CreateCell(5).SetCellValue("身份证号");
                    header.CreateCell(6).SetCellValue("户籍地址");
                    header.CreateCell(7).SetCellValue("常住地址");
                    header.CreateCell(8).SetCellValue("头像");
                    header.CreateCell(9).SetCellValue("身份证正面");
                    header.CreateCell(10).SetCellValue("身份证反面");
                    header.CreateCell(11).SetCellValue("所属乡镇");
                    header.CreateCell(12).SetCellValue("证书链接");
                    header.CreateCell(13).SetCellValue("证书编号");
                    header.CreateCell(14).SetCellValue("证书有效期");

                    var rowIndex = 1;
                    for (int i = 0; i < query.Count; i++)
                    {
                        var datarow = sheet.CreateRow(rowIndex);

                        datarow.CreateCell(0).SetCellValue(query[i].CertificateType);
                        datarow.CreateCell(1).SetCellValue(Base.SM4.Sm4Crypto.DecryptECB(query[i].CraftsmanName));
                        datarow.CreateCell(2).SetCellValue(query[i].CraftsmanSex);
                        datarow.CreateCell(3).SetCellValue(query[i].CraftsmanEducation);
                        datarow.CreateCell(4).SetCellValue(StringSensitiveHelper.SensitiveString(Base.SM4.Sm4Crypto.DecryptECB(query[i].CraftsmanPhone), Enums.String.StringSensitiveType.Mobile)); 
                        datarow.CreateCell(5).SetCellValue(StringSensitiveHelper.SensitiveString(Base.SM4.Sm4Crypto.DecryptECB(query[i].IdCard), Enums.String.StringSensitiveType.IdCardNo));
                        datarow.CreateCell(6).SetCellValue(query[i].HjAddress);
                        datarow.CreateCell(7).SetCellValue(query[i].CzAddress);
                        datarow.CreateCell(8).SetCellValue(query[i].CraftsmanUrl);
                        datarow.CreateCell(9).SetCellValue(query[i].IcFrontUrl);
                        datarow.CreateCell(10).SetCellValue(query[i].IcBackUrl);
                        datarow.CreateCell(11).SetCellValue(query[i].Township);
                        datarow.CreateCell(12).SetCellValue(query[i].CertificateUrl);
                        datarow.CreateCell(13).SetCellValue(query[i].CertificateId);
                        datarow.CreateCell(14).SetCellValue(query[i].CertificateTimes);

                        rowIndex++;
                    }
                    workbook.Write(fs);
                }
                return newFile;
            }