easyexcel复杂模板导出(合并行列,列统计汇总)
创始人
2024-06-03 09:17:50
0

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

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

相关内容

热门资讯

白岩松评海关打假“假布布”:保... 最近一段时间,Labubu全球爆火,一“布”难求,但在哪儿,天天都要跟Labubu打交道呢?除了工厂...
打造186个以上现代农业科技试...   本报讯(洪观新闻记者 万敏)近日,江西省农业农村厅发布《2025年江西省基层农技推广体系改革与建...
2025年6月外汇局公示处罚信... 在2025年6月期间各级外汇管理部门共公示处罚案例98个,涉及48名个人,25家企业,25家银行业金...
YU7在电池包防护方面做了哪些...   炒股就看金麒麟分析师研报,权威,专业,及时,全面,助您挖掘潜力主题机会! 7月5日晚,@小米汽...
停航!停工!黄色预警!台风“丹... 转自:科普中国7 月 5 日凌晨,今年第4号台风“丹娜丝”生成。中央气象台于 5 日 18 时升级发...
南昌高温持续市民全副武装出行   昨日,记者在街头看到,高温天气来袭,行人“全副武装”出行。据天气预报显示,7日南昌晴天多云,气温...
美国得州洪灾死亡人数上升至43... 当地时间7月5日获悉,美国得克萨斯州克尔县警长表示,得州发生的洪灾已造成至少43人死亡,死亡人员包括...
鱼跃鸟飞入画来   7月盛夏,赣江浩荡奔流,鄱阳湖烟波浩渺、水天相接,河湖相连处,水清安澜、鱼翔浅底。  2020年...
越南百人采购团发来“辣评” 宁... 在2025宁波跨博会上,越南百人采购团参观宁波舟山港。(受访者供图)2025宁波跨博会闭幕已一个多月...
拓展绿色空间 绘就生态底色   市民游客在公园内享受休闲时光。  本报记者 霍亮文/摄   在哈尔滨主城区,一些曾经不起眼的角落...