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依賴導入

<!-- excel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.1</version></dependency><!-- 解決jxls中poi版本沖突問題引入 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version></dependency>

版本說明

合并策略類
目前只支持行合并，列合并對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<Integer> exportFieldGroupCountList;/*** 目標合并列index*/private Integer targetColumnIndex;// 需要開始合并單元格的首行indexprivate Integer rowIndex;public ExcelFillCellMergeStrategy(){}// exportDataList為待合并目標列的值public ExcelFillCellMergeStrategy(List<Integer> 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){rowCount += 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<Void> downloadInstallmentMaterial(String code, String name, String shortName) {// 基礎數據List<PledgeInvoiceITransferVO> transferVOList = new ArrayList();transferVOList.add(data);// 合并行標記List<Integer> exportFieldGroupCountList = new ArrayList<>();List<PledgeInvoiceITransferVO> batchTransferVOS = assembleInvoice(transferVOList,exportFieldGroupCountList);String title = "中登附件表格-" + shortName + bizBatchGroupBO.getRefactoringContractSerialNumber() + "-" + bizBatchGroupBO.getGroupSerialNumber() + ".xlsx";Map<String, Object> 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<PledgeInvoiceITransferVO> assembleInvoice(List<PledgeInvoiceITransferVO> transferVOList,List<Integer> exportFieldGroupCountList){List<PledgeInvoiceITransferVO> batchTransferVOS = new ArrayList<>();List<String> assetCodes = transferVOList.stream().map(transferVO -> transferVO.getAssetCode()).collect(Collectors.toList());List<SelectInvoiceListDTO> invoiceListDTOList = mock(assetCodes);Map<String,List<SelectInvoiceListDTO>> invoiceMap = invoiceListDTOList.stream().collect(Collectors.groupingBy(SelectInvoiceListDTO::getAssetCode,LinkedHashMap::new,Collectors.toList()));AtomicInteger index = new AtomicInteger(1);transferVOList.forEach(transferVO -> {List<SelectInvoiceListDTO> 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<SelectInvoiceListDTO> mock(List<String> assetCodes){List<SelectInvoiceListDTO> list = new ArrayList<>();for (String assetCode:assetCodes) {int num = RandomUtil.randomInt(10) + 1;for(int i=0;i<num;i++){SelectInvoiceListDTO 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<String, Object> dataMap, String fileName, HttpServletResponse response,List<Integer> exportFieldGroupCountList,List<Integer> 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

解決：根據上文的版本說明進行匹配即可解決

總結

以上是生活随笔為你收集整理的easyexcel复杂模板导出（合并行列，列统计汇总）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。