easyexcel复杂模板导出（合并行列，统计汇总）

• 为什么使用easyexcel
• • 1. 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支持大数据量导出，性能较好（满足业务导出需求）

切换时一个业务导出需求

定义easyexcel模板

实现效果

代码实现

pom依赖导入

	   com.alibabaeasyexcel3.2.1org.apache.poipoi-ooxml4.1.2org.apache.poipoi4.1.2org.apache.poipoi-ooxml-schemas4.1.2

版本说明

合并策略类
目前只支持行合并，列合并对merge方法进行扩展即可

package com.servingcloud.factoring.utils.excel;import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/*** @author pengdy* @desc easyexcel合并行列导出*/
@Data
public class ExcelFillCellMergeStrategy extends AbstractMergeStrategy {/*** 分组，每几行合并一次*/private List exportFieldGroupCountList;/*** 目标合并列index*/private Integer targetColumnIndex;// 需要开始合并单元格的首行indexprivate Integer rowIndex;public ExcelFillCellMergeStrategy(){}// exportDataList为待合并目标列的值public ExcelFillCellMergeStrategy(List exportFieldGroupCountList, Integer targetColumnIndex) {this.exportFieldGroupCountList = exportFieldGroupCountList;this.targetColumnIndex = targetColumnIndex;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {if (null == rowIndex) {rowIndex = cell.getRowIndex();}// 只有合并的行与标记行相对应时才进行合并if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {mergeGroupColumn(sheet);}}private void mergeGroupColumn(Sheet sheet) {int rowCount = rowIndex;for(Integer count:exportFieldGroupCountList){// 1行调用合并方法会报错if(count == 1){continue;}// 合并单元格CellRangeAddress cellAddresses = new CellRangeAddress(rowCount,rowCount+count-1,targetColumnIndex,targetColumnIndex);sheet.addMergedRegion(cellAddresses);rowCount += count;}}
}

实体对象：

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实现

    @Overridepublic 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;iSelectInvoiceListDTO dto = new SelectInvoiceListDTO();dto.setAssetCode(assetCode);dto.setInvoiceNo(assetCode + i);dto.setAmountWithTax(new BigDecimal(i));dto.setAmountTransferred(new BigDecimal(i));list.add(dto);}}return list;}

easyexcel导出工具类

	/*** 合并导出* @param templatePath 模板地址* @param dataMap 数据* @param fileName 文件名* @param exportFieldGroupCountList 合并行长度集合* @param mergeColumn 合并列标记*/public static void mergeExport(String templatePath, Map dataMap, String fileName, HttpServletResponse response,List exportFieldGroupCountList,List mergeColumn){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

解决：根据上文的版本说明进行匹配即可解决