c#调用excel模板页，然后套用模板页导出多个sheet

最近接手一个导出Excel表格的任务，通过两天的查找资料，终于解决了，过程很痛苦，但结局是开心的

由于这种方法不能导出第六个sheet页超出模板行数的数据，所以，这种方法又被我重写了一遍，可以动态添加更多数据导出，补充的FileReadUtil代码放在最后面，其他类代码不变

Controller层

namespace Srm.Audit.HttpApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    [Authorize("Permission")]
    public class AuditReportController : ControllerBase
    {
        public IAuditReportAppService AuditReportAppService;
        public IAuditReportQueryService AuditReportQueryService;
        public AuditReportController(IAuditReportAppService AuditReportAppService, IAuditReportQueryService AuditReportQueryService)
        {
            this.AuditReportAppService = AuditReportAppService;
            this.AuditReportQueryService = AuditReportQueryService;
        }
        [HttpGet]
        [Route("downloadExecl")]
        public async Task<IActionResult> DownloadExcel([FromQuery] AuditReportDto AuditReportDto, CancellationToken cancellationToken = default)
        {
            byte[] buffer = await AuditReportAppService.DownloadExecl(AuditReportDto, cancellationToken);
            return File(buffer, "application/octect-stream");
        }
    }
}

IAuditReportAppService层

namespace Srm.Audit.Application.Contracts.AuditReport
{
    public interface IAuditReportAppService : IAppService
    {
        public Task<byte[]> DownloadExecl(AuditReportDto auditReportDto, CancellationToken cancellationToken = default(CancellationToken));     
    }
}

AuditReportAppService层

namespace Srm.Audit.Application.AuditReport
{
    public class AuditReportAppService: IAuditReportAppService
    {
        private IMediator mediator;
        public AuditReportAppService(IMediator mediator)
        {
            this.mediator = mediator;
        }
        public async Task<byte[]> DownloadExecl(AuditReportDto auditReportDto, CancellationToken cancellationToken = default)
        {
            return await mediator.Send(new DownloadExeclCommand(auditReportDto), cancellationToken);
        }
    }
}

DownloadExeclCommand层

namespace Srm.Audit.Application.AuditReport.Commands
{
    public class DownloadExeclCommand : IRequest<byte[]>
    {
        public AuditReportDto AuditReportDto { get; }

        public DownloadExeclCommand(AuditReportDto AuditReportDto)
        {
            this.AuditReportDto = AuditReportDto;
        }
    }
    public class DownloadExeclCommandHandler : CommandRequestHandler<DownloadExeclCommand, byte[]>
    {
        public DownloadExeclCommandHandler(IMediator mediator, IMapper mapper, IDbContext dbContext) : base(mediator, mapper, dbContext)
        {
        }

        public override async Task<byte[]> HandleAsync(DownloadExeclCommand request, IDbConnection dbConnection, CancellationToken cancellationToken)
        {
            
            var auditFindings =   ServiceLocator.GetService<IAuditFindingsQueryService>();
            byte[] bytes;
            try
            {
                byte[] buffer = GetTemplateFromServer();
                FileReadUtil FRU = new FileReadUtil(buffer, "供应商稽核报告.xlsx");
                //（以下五行为查询内容，后面sheet 第六页（sheet = wb.GetSheetAt(5);）内容需要用到的数据）
                List<AuditFindingsDto> auditFindingsDtos = null;
                if (!request.AuditReportDto.ParentId.IsNullOrEmpty())
                {
                     auditFindingsDtos =await auditFindings.GetByPidAsync(request.AuditReportDto.ParentId, cancellationToken);
                }
                
                MemoryStream ms = FRU.AuditWriteContent(request.AuditReportDto, auditFindingsDtos);
                bytes = ms.ToArray();
            }
            catch (Exception ex)
            {
                throw new BusinessException("供应商稽核报告下载失败:" + ex.Message);
            }
            return bytes;
        }
        //获取Excel模板 需要配置获取本地的Excel模板
        private byte[] GetTemplateFromServer()
        {
            IConfiguration confing = SrmConfig.GetConfig();
            IConfigurationSection Section = confing.GetSection("AuditURL");
            string URL = Section["URL"];
            FileStream fs = new FileStream(URL, FileMode.Open, FileAccess.Read, FileShare.Read);
            byte[] data = new byte[fs.Length];
            fs.Read(data, 0, data.Length);
            fs.Close();
            return data;
        }
    }
    }

  //供应商稽核模板地址 在appsettings.Development.json配置
  "AuditURL": {
    "URL": "F:/APP/down/供应商稽核模板.xlsm"
  },

FileReadUtil

namespace Srm.Excel
{
    public class FileReadUtil
    {
        IWorkbook wb = null;
        ISheet sheet = null;
        IRow row = null;
        IFormulaEvaluator evaluator = null;

        public FileReadUtil(IFormFile file)
        {
            MemoryStream ms = new MemoryStream();
            file.OpenReadStream().CopyTo(ms);
            ms.Position = 0;
            //  if (filePath == null || "".Equals(filePath)) { return; }
            string fileName = file.FileName;
            string fileType = fileName.Substring(fileName.LastIndexOf("."));
            try
            {
                //  FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite); 

                if (".xls".Equals(fileType))
                {
                    wb = new HSSFWorkbook(ms);
                }
                else if (".xlsx".Equals(fileType))
                {
                    wb = new XSSFWorkbook(ms);
                }
                else if (".xlsm".Equals(fileType))
                {
                    wb = new XSSFWorkbook(ms);

                }
                else
                {
                    throw new BusinessException("不支持这种格式");
                }
                if (wb != null)
                {
                    evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
                }
            }
            catch (Exception ex)
            {
                throw new BusinessException(ex.Message);
            }
        }
        public FileReadUtil(byte[] buffer, string FileName)
        {
            MemoryStream ms = new MemoryStream(buffer);

            if (ms == null)
            {
                throw new BusinessException("excel文件生成错误");
            }
            string fileType = FileName.Substring(FileName.LastIndexOf("."));
            try
            {
                //  FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite); 

                if (".xls".Equals(fileType))
                {
                    wb = new HSSFWorkbook(ms);
                }
                else if (".xlsx".Equals(fileType))
                {
                    wb = new XSSFWorkbook(ms);
                }
                else if (".xlsm".Equals(fileType))
                {
                    wb = new XSSFWorkbook(ms);

                }
                else
                {
                    throw new BusinessException("不支持这种格式");
                }
                if (wb != null)
                {
                    evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
                }
            }
            catch (Exception ex)
            {
                throw new BusinessException(ex.Message);
            }
        }
    
              //Audit Excel中填写内容
        public  MemoryStream AuditWriteContent(AuditReportDto audit, List<AuditFindingsDto> auditFindingsDtos)
        {           
            //var insCount = 0;
            int defGuise;
            //string samsguise = "0";
            byte[] bytes;
            //if (!string.IsNullOrEmpty(Cmer.InsLotNo))
            //{
            //    insCount = Cmer.InsLotNo.Split(",").Length - 1;
            //}
            MemoryStream ms = new MemoryStream();
            if (wb == null)
            {
                throw new BusinessException("Workbook对象为空！");
            }
            //sheet1  稽核主页
            sheet = wb.GetSheetAt(1);
            row = sheet.GetRow(8);
            DateTime start