easyexcel复杂模板导出（合并行列，列统计汇总） |
您所在的位置：网站首页 › easyExcel › easyexcel复杂模板导出（合并行列，列统计汇总） |
easyexcel复杂模板导出（合并行列，列统计汇总）
|
easyexcel复杂模板导出（合并行列，统计汇总）
为什么使用easyexcel1. easyexcel可以通过模板导出（符合项目使用习惯）2. easyexcel支持大数据量导出，性能较好（满足业务导出需求）
切换时一个业务导出需求定义easyexcel模板实现效果代码实现
使用easyexcel遇到的问题poi依赖冲突
为什么使用easyexcel
easyexcel官网地址: https://easyexcel.opensource.alibaba.com/docs/current/ 项目之前一直使用Jxls进行excel导出，通过定义模板，导出时传入对应数据即可导出excel，使用起来还比较方便，项目上线1年之后，数据量越来越多，导出excel越来越慢，数据量再多点还会导致内存溢出服务重启，亟需优化。 调研之后，发现easyexcel满足如下两点 1. easyexcel可以通过模板导出（符合项目使用习惯） 2. easyexcel支持大数据量导出，性能较好（满足业务导出需求） 切换时一个业务导出需求
pom依赖导入 com.alibaba easyexcel 3.2.1 org.apache.poi poi-ooxml 4.1.2 org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml-schemas 4.1.2版本说明 实体对象： package com.servingcloud.factoring.dto.response.vo.installment; import com.alibaba.excel.annotation.format.DateTimeFormat; import com.alibaba.excel.annotation.format.NumberFormat; import com.xintech.spacexcockroach.common.exception.BizException; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.math.BigDecimal; import java.util.Date; /** * 中登登记发票转让清单 * @author pengdy */ @Data public class PledgeInvoiceITransferVO implements Cloneable { @ApiModelProperty("序号") private Integer index; @ApiModelProperty("资产编号") private String assetCode; @ApiModelProperty("应收账款转让编号") private String transferNumber; @ApiModelProperty("基础交易合同名称") private String contractName; @ApiModelProperty("基础交易合同编号") private String contractCode; @ApiModelProperty("项目公司") private String projectOrgName; @ApiModelProperty("债权人") private String supplierName; @ApiModelProperty("发票编号") private String invoiceNo; @NumberFormat("#.##%") @ApiModelProperty("发票金额") private BigDecimal invoiceAmount; @NumberFormat("#.##%") @ApiModelProperty("发票转让金额") private BigDecimal invoiceTransAmount; @NumberFormat("#.##%") @ApiModelProperty("应收账款金额") private BigDecimal financeMoney; @DateTimeFormat("yyyy/MM/dd") @ApiModelProperty("账款到期日") private Date applyDueDate; public PledgeInvoiceITransferVO clone(){ PledgeInvoiceITransferVO transferVO; try { transferVO = (PledgeInvoiceITransferVO)super.clone(); } catch (CloneNotSupportedException e) { throw new BizException("对象复制失败。"); } return transferVO; } }service实现 @Override public ResponseDTO downloadInstallmentMaterial(String code, String name, String shortName) { // 基础数据 List transferVOList = new ArrayList(); transferVOList.add(data); // 合并行标记 List exportFieldGroupCountList = new ArrayList(); List batchTransferVOS = assembleInvoice(transferVOList,exportFieldGroupCountList); String title = "中登附件表格-" + shortName + bizBatchGroupBO.getRefactoringContractSerialNumber() + "-" + bizBatchGroupBO.getGroupSerialNumber() + ".xlsx"; Map map = new HashMap(); // 统计对象 PledgeInvoiceITransferVO count = new PledgeInvoiceITransferVO(); count.setInvoiceNo("合计"); // 发票金额合计 count.setInvoiceAmount(batchTransferVOS.stream().map(PledgeInvoiceITransferVO::getInvoiceAmount).reduce(BigDecimal.ZERO,BigDecimal::add)); // 发票转让金额合计 count.setInvoiceTransAmount(batchTransferVOS.stream().map(PledgeInvoiceITransferVO::getInvoiceTransAmount).reduce(BigDecimal.ZERO,BigDecimal::add)); // 应收账款金额合计 count.setFinanceMoney(batchTransferVOS.stream().map(PledgeInvoiceITransferVO::getFinanceMoney).reduce(BigDecimal.ZERO,BigDecimal::add)); batchTransferVOS.add(count); map.put("list", batchTransferVOS); String templatePath = "template/group/installmentInvoiceTransferList.xlsx"; EasyExcelUtil.commonExportTest(templatePath, map, title, response,exportFieldGroupCountList,Arrays.asList(0,1,2,3,4,5,9,10)); return new ResponseDTO(ResponseCode.OK); } /** * 组装资产发票参数 * @param transferVOList */ private List assembleInvoice(List transferVOList,List exportFieldGroupCountList){ List batchTransferVOS = new ArrayList(); List assetCodes = transferVOList.stream().map(transferVO -> transferVO.getAssetCode()).collect(Collectors.toList()); List invoiceListDTOList = mock(assetCodes); Map invoiceMap = invoiceListDTOList.stream().collect(Collectors.groupingBy(SelectInvoiceListDTO::getAssetCode, LinkedHashMap::new,Collectors.toList())); AtomicInteger index = new AtomicInteger(1); transferVOList.forEach(transferVO -> { List invoiceList = invoiceMap.get(transferVO.getAssetCode()); for (SelectInvoiceListDTO selectInvoiceListDTO : invoiceList) { PledgeInvoiceITransferVO newVO = transferVO.clone(); newVO.setIndex(index.get()); newVO.setInvoiceNo(selectInvoiceListDTO.getInvoiceNo()); newVO.setInvoiceAmount(selectInvoiceListDTO.getAmountWithTax()); newVO.setInvoiceTransAmount(selectInvoiceListDTO.getAmountTransferred()); batchTransferVOS.add(newVO); } index.getAndIncrement(); exportFieldGroupCountList.add(invoiceList.size()); }); return batchTransferVOS; } /** * mock数据 **/ private List mock(List assetCodes){ List list = new ArrayList(); for (String assetCode:assetCodes) { int num = RandomUtil.randomInt(10) + 1; for(int i=0;i response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", String.format("attachment;filename=%s",fileName)); InputStream in = EasyExcelUtil.class.getClassLoader().getResourceAsStream(templatePath); try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(in).build()){ ExcelWriterSheetBuilder builder = new ExcelWriterSheetBuilder(); // 设置合并的列 for(Integer col:mergeColumn){ builder.registerWriteHandler(new ExcelFillCellMergeStrategy(exportFieldGroupCountList,col)); } WriteSheet writeSheet = builder.build(); excelWriter.fill(dataMap.get("list"),writeSheet); } catch (IOException e) { log.error("获取文件流失败",e); throw new BizException("文件下载失败。"); } } 使用easyexcel遇到的问题 poi依赖冲突使用时出现：NoSuchMethodException ， ClassNotFoundException, NoClassDefFoundError 解决：根据上文的版本说明进行匹配即可解决 |
合并策略类 目前只支持行合并，列合并对merge方法进行扩展即可【本文地址】