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 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 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 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{public AuditReportDto AuditReportDto { get; }public DownloadExeclCommand(AuditReportDto AuditReportDto){this.AuditReportDto = AuditReportDto;}}public class DownloadExeclCommandHandler : CommandRequestHandler{public DownloadExeclCommandHandler(IMediator mediator, IMapper mapper, IDbContext dbContext) : base(mediator, mapper, dbContext){}public override async Task HandleAsync(DownloadExeclCommand request, IDbConnection dbConnection, CancellationToken cancellationToken){var auditFindings =   ServiceLocator.GetService();byte[] bytes;try{byte[] buffer = GetTemplateFromServer();FileReadUtil FRU = new FileReadUtil(buffer, "供应商稽核报告.xlsx");//（以下五行为查询内容，后面sheet 第六页（sheet = wb.GetSheetAt(5);）内容需要用到的数据）List 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 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 = (DateTime)audit.StartAuditDate;DateTime end = (DateTime)audit.EndAuditDate;row.GetCell(1).SetCellValue(start.ToString("d") + "~" + end.ToString("d"));row = sheet.GetRow(9);row.GetCell(1).SetCellValue(audit.SupplierName);row = sheet.GetRow(10);row.GetCell(1).SetCellValue(audit.SuppliedMaterial);row = sheet.GetRow(11);row.GetCell(1).SetCellValue(audit.Address);row = sheet.GetRow(12);row.GetCell(1).SetCellValue(audit.AuditPurpose);row = sheet.GetRow(13);row.GetCell(1).SetCellValue(audit.Auditor);row = sheet.GetRow(14);row.GetCell(1).SetCellValue(audit.AccompanyAuditee);// TODO Check list导出待做row = sheet.GetRow(19);row.GetCell(2).SetCellValue(audit.AuditLeader);row = sheet.GetRow(20);row.GetCell(2).SetCellValue(audit.AuditMember);// 稽核行程//稽核结果//sheet5 Findings&Corrective Actionsheet = wb.GetSheetAt(5);List list1= new List();List list2 = new List();List list3 = new List();List list4 = new List();List list5 = new List();foreach (var audits in auditFindingsDtos){if(audits.SIGN == "1"){list1.Add(audits);var count = list1.Count;if (count <= 3){var i = 0;foreach (var list in list1){                                                     row = sheet.GetRow(6+i);row.GetCell(1).SetCellValue(list.NO);                           row.GetCell(2).SetCellValue(list.DESCRIPTION);i++;}}//count > 3 应该怎么处理 //else//{                         //}}else if(audits.SIGN == "2"){list2.Add(audits);var count = list2.Count;if (count <= 6){var i = 0;foreach (var list in list2){row = sheet.GetRow(12 + i);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i++;}}//count > 6 应该怎么处理}else if(audits.SIGN == "3"){list3.Add(audits);var count = list3.Count;if (count <= 6){var i = 0;foreach (var list in list3){row = sheet.GetRow(21 + i);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i++;}}//count > 6 应该怎么处理}else if (audits.SIGN == "4"){list4.Add(audits);var count = list4.Count;if (count <= 5){var i = 0;foreach (var list in list4){row = sheet.GetRow(30 + i);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i++;}}//count > 5 应该怎么处理}else if(audits.SIGN == "5"){list5.Add(audits);var count = list5.Count;if (count <= 5){var i = 0;foreach (var list in list5){row = sheet.GetRow(38 + i);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i++;}}//count > 5 应该怎么处理}}wb.Write(ms);return ms;}}
}

前端代码

// 供应商稽核报告数据导出downloadExecl() {const paramStr = qs.stringify(this.data)this.$axios({method: 'get',url: `/AuditReport/downloadExecl?${paramStr}`,responseType: 'blob'}).then((res) => {if (res) {if ('msSaveOrOpenBlob' in navigator) {// Microsoft Edge and Microsoft Internet Explorer 10-11window.navigator.msSaveOrOpenBlob(res, '供应商稽核报告' + new Date().getTime() + '.xlsx')} else {// standard code for Google Chrome, Mozilla Firefox etcconst url = window.URL.createObjectURL(res)const link = document.createElement('a')// link.style.display = 'none'link.href = urllink.setAttribute('download', '供应商稽核报告' + new Date().getTime() + '.xlsx')document.body.appendChild(link)link.click()window.URL.revokeObjectURL(url)link.remove()}} else {alert('提示', '系统出错,请将该信息提供给代维人员寻求帮助')}})}

页面显示

Excel表导出效果

补充

重写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 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 = (DateTime)audit.StartAuditDate;DateTime end = (DateTime)audit.EndAuditDate;row.GetCell(1).SetCellValue(start.ToString("d") + "~" + end.ToString("d"));row = sheet.GetRow(9);row.GetCell(1).SetCellValue(audit.SupplierName);row = sheet.GetRow(10);row.GetCell(1).SetCellValue(audit.SuppliedMaterial);row = sheet.GetRow(11);row.GetCell(1).SetCellValue(audit.Address);row = sheet.GetRow(12);row.GetCell(1).SetCellValue(audit.AuditPurpose);row = sheet.GetRow(13);row.GetCell(1).SetCellValue(audit.Auditor);row = sheet.GetRow(14);row.GetCell(1).SetCellValue(audit.AccompanyAuditee);// TODO Check list导出待做row = sheet.GetRow(19);row.GetCell(2).SetCellValue(audit.AuditLeader);row = sheet.GetRow(20);row.GetCell(2).SetCellValue(audit.AuditMember);// 稽核行程//稽核结果//sheet5 Findings&Corrective Actionsheet = wb.GetSheetAt(5);List list1= new List();List list2 = new List();List list3 = new List();List list4 = new List();List list5 = new List();foreach (var audits in auditFindingsDtos){if (audits.SIGN == "1"){list1.Add(audits);}else if (audits.SIGN == "2"){list2.Add(audits);}else if (audits.SIGN == "3"){list3.Add(audits);}else if (audits.SIGN == "4"){list4.Add(audits);}else if (audits.SIGN == "5"){list5.Add(audits);}}//rowAdd 记录每个表超出模板的行数var rowAdd1 = list1.Count - 3 <= 0 ? 0 : list1.Count - 3;var rowAdd2 = list2.Count - 6 <= 0 ? 0 : list2.Count - 6;var rowAdd3 = list3.Count - 6 <= 0 ? 0 : list3.Count - 6;var rowAdd4 = list4.Count - 5 <= 0 ? 0 : list4.Count - 5;// 1 Advantages:表格填充数据var i = 0;//记录添加的行数foreach (var list in list1.Take(3).ToList()){row = sheet.GetRow(6 + i);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);i++;}if (list1.Count > 3){int startRow = 9;sheet.ShiftRows(startRow, sheet.LastRowNum, list1.Count - 3, true, false);var rowSource = sheet.GetRow(6);var rowStyle = rowSource.RowStyle;for (int j = startRow; j < startRow + list1.Count - 3; j++){var rowInsert = sheet.CreateRow(j);if (rowStyle != null){rowInsert.RowStyle = rowStyle;rowInsert.Height = rowSource.Height;}for (int col = 1; col < rowSource.LastCellNum; col++){var cellsource = rowSource.GetCell(col);var cellInsert = rowInsert.CreateCell(col);var cellStyle = cellsource.CellStyle;if (cellStyle != null){cellInsert.CellStyle = cellsource.CellStyle;}}//合并单元格 第一个参数：从第几行开始合并 第二个参数：到第几行结束合并 第三个参数：从第几列开始合并 第四个参数：到第几列结束合CellRangeAddress region = new CellRangeAddress(j, j, 2, 12);sheet.AddMergedRegion(region);}foreach (var list in list1.Skip(3).ToList()){row = sheet.GetRow(6 + i);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);i++;}}//2 Observation / Concern / Suggestion / AR:表格填充数据var i2 = 0;//记录添加的行数foreach (var list in list2.Take(6).ToList()){row = sheet.GetRow(12 + rowAdd1 + i2);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i2++;}if (list2.Count > 6){int startRow = 18 + rowAdd1;//int startRow, int endRow, int n, bool copyRowHeight, bool resetOriginalRowHeightsheet.ShiftRows(startRow, sheet.LastRowNum, list2.Count - 6, true, false);var rowSource = sheet.GetRow(13 + rowAdd1);var rowStyle = rowSource.RowStyle;for (int j = startRow; j < startRow + list2.Count - 6; j++){var rowInsert = sheet.CreateRow(j);if (rowStyle != null){rowInsert.RowStyle = rowStyle;rowInsert.Height = rowSource.Height;}for (int col = 1; col < rowSource.LastCellNum; col++){var cellsource = rowSource.GetCell(col);var cellInsert = rowInsert.CreateCell(col);var cellStyle = cellsource.CellStyle;if (cellStyle != null){cellInsert.CellStyle = cellsource.CellStyle;}}}foreach (var list in list2.Skip(6).ToList()){row = sheet.GetRow(12 + rowAdd1 + i2);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i2++;}}//3 Minor:表格填充数据var i3 = 0;foreach (var list in list3.Take(6).ToList()){row = sheet.GetRow(21 + rowAdd1 + rowAdd2 + i3);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i3++;}if (list3.Count > 6){int startRow = 27 + rowAdd1 + rowAdd2;sheet.ShiftRows(startRow, sheet.LastRowNum, list3.Count - 6, true, false);var rowSource = sheet.GetRow(22 + rowAdd1 + rowAdd2);var rowStyle = rowSource.RowStyle;for (int j = startRow; j < startRow + list3.Count - 6; j++){var rowInsert = sheet.CreateRow(j);if (rowStyle != null){rowInsert.RowStyle = rowStyle;rowInsert.Height = rowSource.Height;}for (int col = 1; col < rowSource.LastCellNum; col++){var cellsource = rowSource.GetCell(col);var cellInsert = rowInsert.CreateCell(col);var cellStyle = cellsource.CellStyle;if (cellStyle != null){cellInsert.CellStyle = cellsource.CellStyle;}}}foreach (var list in list3.Skip(6).ToList()){row = sheet.GetRow(21 + i3 + rowAdd1 + rowAdd2);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i3++;}}//4 Major表格填充数据var i4 = 0;foreach (var list in list4.Take(5).ToList()){row = sheet.GetRow(30 + rowAdd1 + rowAdd2 + rowAdd3 + i4);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i4++;}if (list4.Count > 5){int startRow = 35 + rowAdd1 + rowAdd2 + rowAdd3;sheet.ShiftRows(startRow, sheet.LastRowNum, list4.Count - 5, true, false);var rowSource = sheet.GetRow(31 + rowAdd1 + rowAdd2 + rowAdd3);var rowStyle = rowSource.RowStyle;for (int j = startRow; j < startRow + list4.Count - 5; j++){var rowInsert = sheet.CreateRow(j);if (rowStyle != null){rowInsert.RowStyle = rowStyle;rowInsert.Height = rowSource.Height;}for (int col = 1; col < rowSource.LastCellNum; col++){var cellsource = rowSource.GetCell(col);var cellInsert = rowInsert.CreateCell(col);var cellStyle = cellsource.CellStyle;if (cellStyle != null){cellInsert.CellStyle = cellsource.CellStyle;}}}foreach (var list in list4.Skip(5).ToList()){row = sheet.GetRow(30 + i4 + rowAdd1 + rowAdd2 + rowAdd3);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i4++;}}//5 Critical表格填充数据var i5 = 0;foreach (var list in list5.Take(5).ToList()){row = sheet.GetRow(38 + rowAdd1 + rowAdd2 + rowAdd3 + rowAdd4 + i5);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i5++;}if (list5.Count > 5){int startRow = 43 + rowAdd1 + rowAdd2 + rowAdd3 + rowAdd4;sheet.ShiftRows(startRow, sheet.LastRowNum, list5.Count - 5, true, false);var rowSource = sheet.GetRow(39 + rowAdd1 + rowAdd2 + rowAdd3 + rowAdd4);var rowStyle = rowSource.RowStyle;for (int j = startRow; j < startRow + list5.Count - 5; j++){var rowInsert = sheet.CreateRow(j);if (rowStyle != null){rowInsert.RowStyle = rowStyle;rowInsert.Height = rowSource.Height;}for (int col = 1; col < rowSource.LastCellNum; col++){var cellsource = rowSource.GetCell(col);var cellInsert = rowInsert.CreateCell(col);var cellStyle = cellsource.CellStyle;if (cellStyle != null){cellInsert.CellStyle = cellsource.CellStyle;}}}foreach (var list in list5.Skip(5).ToList()){row = sheet.GetRow(38 + i5 + rowAdd1 + rowAdd2 + rowAdd3 + rowAdd4);row.GetCell(1).SetCellValue(list.NO);row.GetCell(2).SetCellValue(list.DESCRIPTION);row.GetCell(3).SetCellValue(list.AUDITOR);row.GetCell(4).SetCellValue(list.CUASEANALYSIS);row.GetCell(5).SetCellValue(list.CORRECTIVEACTION);row.GetCell(6).SetCellValue(list.RESPONSER);DateTime dueday = (DateTime)list.DUEDAY;row.GetCell(7).SetCellValue(dueday.ToString("d"));row.GetCell(8).SetCellValue(list.EVIDENCE);row.GetCell(9).SetCellValue(list.REVIEWER);DateTime closeDate = (DateTime)list.CLOSEDATE;row.GetCell(10).SetCellValue(closeDate.ToString("d"));row.GetCell(11).SetCellValue(list.STATUS);row.GetCell(12).SetCellValue(list.REVIEWREMARK);i5++;}}wb.Write(ms);return ms;}}
}

导出样式如下：

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！