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

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

相关内容

热门资讯

关於香港人的称呼 关於香港人的称呼陌生的中老年男子和女子正式的通常都叫先生,太太,小姐或女士。比较多人这样称呼,始终是...
天津市北辰区有那几个乡镇啊 !... 天津市北辰区有那几个乡镇啊 !那个乡镇工业多啊详细点北辰区现辖4个街道、9个镇(不是3个了):果园新...
北方盆栽桔子树怎么养 北方盆栽桔子树怎么养盆栽橘子如管理不当,往往只开花、不结果或少结果,甚至不开花。要让盆栽橘子年年开花...
马克龙施压英国承认巴勒斯坦国 据新华社电 英国媒体4日披露,法国总统埃马纽埃尔·马克龙正向英国首相基尔·斯塔默施加压力,试图让后者...
十二星座男最喜欢哪一种类型的女... 十二星座男最喜欢哪一种类型的女孩子,为什么?在我看来,对于水瓶座的男生来说,更喜欢那些娇小可爱的女孩...
马斯克宣布“美国党”成立 当地时间7月5日,美国企业家埃隆·马斯克在社交媒体平台X上发文称,“美国党”于当日成立,以还给人民自...
12生肖中哪肖权威最大? 12生肖中哪肖权威最大?在十二生肖中权威最大的动物肯定是山中的大王老虎,因为它是百兽之王。
当别人和你说,我只要你好好的就... 当别人和你说,我只要你好好的就行,怎么回答?当别人和你说,我只要你好好的就行,怎么你好,这个要看是谁...
汪伦是怎样解释十里桃花的 汪伦是怎样解释十里桃花的汪伦解释说:“十里桃花是指十里外的桃花渡;万家酒店是指桃花潭西有个姓万的人家...
成都有没有比较好的景观(园林)... 成都有没有比较好的景观(园林)设计的手绘培训?你可以去明思源问问看那儿是专门做室内设计跟园林设计培训...