EasyExcel 导出合并层级单元格

EasyExcel 导出合并层级单元格

一、案例

案例一

• 1.相同订单号单元格进行合并

合并结果

案例二

• 1.相同订单号的单元格进行合并
• 2.相同订单号的总数和总金额进行合并

合并结果

案例三

• 1.相同订单号的单元格进行合并
• 2.相同订单号的商品分类进行合并
• 3.相同订单号的总数和总金额进行合并
• 4.相同订单号和相同商品分类的分类总数、分类总金额进行合并

合并结果

二、代码实现

相关依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.1</version>
</dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.16</version>
</dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.26</version>
</dependency>

2.1 AbstractMergeStrategy

import com.alibaba.excel.write.handler.CellWriteHandler;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
public abstract class AbstractMergeStrategy implements CellWriteHandler {/*** 最大行索引*/public final static int EXCEL_LAST_INDEX = 1048575;/*** 默认合并起始行*/public final static int DEFAULT_START_ROW_INDEX = 1;/*** 合并抽象方法* @param sheet* @param cell*/public abstract void merge(Sheet sheet, Cell cell);/*** 获取单元格值* @param cell */public Object getCellValue(Cell cell) {return cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();}
}

2.2 ColumnMergeStrategy

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.easy.excel.demo.model.MergeRowColumn;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.*;
import java.util.stream.Collectors;/*** 合并单元格策略：适用于列合并*/
@Slf4j
public class ColumnMergeStrategy extends AbstractMergeStrategy {/*** 合并起始行索引*/private int mergeStartRowIndex;/*** 合并结束行索引*/private int mergeEndRowIndex;/*** 待合并的列（如果没有指定，则所有的列都会进行合并）*/private List<Integer> mergeColumnIndexList;/*** 待合并的列父级依赖关系 <需要合并的列索引, 依赖的父级列索引>* key 需要合并的列, value 所依赖的父级列的列表    */private Map<Integer, List<Integer>> mergeColumnIndexMap = new HashMap<>();/*** 合并的行列索引数据(存储每列的数据合并的行列索引范围)*/private Map<Integer, MergeRowColumn> mergeRowColumnMap = new HashMap<>();private Sheet sheet;public ColumnMergeStrategy() {this(DEFAULT_START_ROW_INDEX, EXCEL_LAST_INDEX);}public ColumnMergeStrategy(List<Integer> mergeColumnIndexList) {this(DEFAULT_START_ROW_INDEX, EXCEL_LAST_INDEX, mergeColumnIndexList);}public ColumnMergeStrategy(Map<Integer, List<Integer>> mergeColumnIndexMap) {this.mergeColumnIndexMap = mergeColumnIndexMap;this.mergeColumnIndexList = mergeColumnIndexMap.keySet().stream().collect(Collectors.toList());this.mergeStartRowIndex = DEFAULT_START_ROW_INDEX;this.mergeEndRowIndex = EXCEL_LAST_INDEX;}public ColumnMergeStrategy(int mergeStartRowIndex) {this(mergeStartRowIndex, EXCEL_LAST_INDEX);}public ColumnMergeStrategy(int mergeStartRowIndex, int mergeEndRowIndex) {this(mergeStartRowIndex, mergeEndRowIndex, new ArrayList<>());}public ColumnMergeStrategy(int mergeStartRowIndex, int mergeEndRowIndex, List<Integer> mergeColumnIndexList) {this.mergeStartRowIndex = mergeStartRowIndex;this.mergeEndRowIndex = mergeEndRowIndex;this.mergeColumnIndexList = mergeColumnIndexList;}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean isHead) {// 头不参与合并if (isHead) {return;}// 初始化 sheetif (sheet == null) {this.sheet = writeSheetHolder.getSheet();}// 如果当前行大于合并起始行则进行合并if (cell.getRowIndex() >= mergeStartRowIndex && cell.getRowIndex() <= mergeEndRowIndex) {// 判断是否是全列合并或者当前列在需要合并列中if (CollUtil.isEmpty(mergeColumnIndexList) || (CollUtil.isNotEmpty(mergeColumnIndexList) && mergeColumnIndexList.contains(cell.getColumnIndex()))) {// 合并单元格this.merge(writeSheetHolder.getSheet(), cell);}}}@Overridepublic void merge(Sheet sheet, Cell cell) {// 当前单元格行、列索引int curRowIndex = cell.getRowIndex();int curColumnIndex = cell.getColumnIndex();// 当前单元格的值为Object curCellValue = this.getCellValue(cell);// 上一行的行索引int aboveRowIndex = curRowIndex - 1;if (aboveRowIndex < 0 || aboveRowIndex < mergeStartRowIndex) {// 初始化当前列的 合并区域范围MergeRowColumn mergeRowColumn = new MergeRowColumn(curRowIndex, curRowIndex, curColumnIndex, curColumnIndex);mergeRowColumnMap.put(curColumnIndex, mergeRowColumn);return;}// 获取上一个单元格Cell aboveCell = sheet.getRow(aboveRowIndex).getCell(curColumnIndex);// 上一个单元格的值Object aboveCellValue = this.getCellValue(aboveCell);// 判断上一个单元格是否能合并if (Objects.equals(curCellValue, aboveCellValue)) {boolean needMerge = true;// 父级列 列表List<Integer> parentColumnIndexList = mergeColumnIndexMap.get(curColumnIndex);if (parentColumnIndexList != null && !parentColumnIndexList.isEmpty()) {for (Integer parentColumnIndex : parentColumnIndexList) {Cell mainCell = sheet.getRow(curRowIndex).getCell(parentColumnIndex);Cell aboveMainCell = sheet.getRow(aboveRowIndex).getCell(parentColumnIndex);Object mainCellValue = this.getCellValue(mainCell);Object aboveMainCellValue = this.getCellValue(aboveMainCell);// 所有主列都需要满足合并条件才能合并副列if (!Objects.equals(mainCellValue, aboveMainCellValue)) {needMerge = false;break;}}}// 允许合并if (needMerge){// 修改当前列的行合并索引范围MergeRowColumn mergeRowColumn = mergeRowColumnMap.get(curColumnIndex);mergeRowColumn.setEndRowIndex(curRowIndex);} else {// 合并已有的单元格,修改行索引指向mergeRowColumnCell(sheet, curRowIndex,curColumnIndex);}} else {// 合并已有的单元格,修改行索引指向mergeRowColumnCell(sheet, curRowIndex,curColumnIndex);}}/*** 检查给定的单元格是否在一个或多个合并区域中。** @return 如果指定单元格是合并区域的一部分，则返回 true；否则返回 false。*/private boolean isMergedRegion(Sheet sheet, Integer rowIndex, Integer columnIndex) {// 获取当前工作表中的所有合并区域数量int numMergedRegions = sheet.getNumMergedRegions();// 遍历所有合并区域for (int i = 0; i < numMergedRegions; i++) {CellRangeAddress region = sheet.getMergedRegion(i);// 检查指定的单元格是否在当前合并区域内if (region.isInRange(rowIndex, columnIndex)) {return true;}}return false;}/*** 合并区域单元格** @param sheet* @param curRowIndex* @param curColumnIndex*/private void mergeRowColumnCell(Sheet sheet, Integer curRowIndex, Integer curColumnIndex) {// 获取当前的列的合并区域索引对象MergeRowColumn mergeRowColumn = mergeRowColumnMap.get(curColumnIndex);// 合并单元格mergeCell(sheet, mergeRowColumn, curRowIndex, curColumnIndex);}/*** 手动合并最后的单元格* (最后一段单元格需要手动合并)*/public void finalMergeCell() {// 遍历所有列的合并索引,合并最后的单元格for (Map.Entry<Integer, MergeRowColumn> entry : mergeRowColumnMap.entrySet()) {Integer columnIndex = entry.getKey();MergeRowColumn mergeRowColumn = entry.getValue();Integer endRowIndex = mergeRowColumn.getEndRowIndex();mergeCell(sheet, mergeRowColumn, endRowIndex, columnIndex);}}/*** 合并单元格** @param sheet* @param mergeRowColumn* @param curRowIndex* @param curColumnIndex*/private void mergeCell(Sheet sheet, MergeRowColumn mergeRowColumn,Integer curRowIndex, Integer curColumnIndex) {// 获取合并的行起始索引Integer startRowIndex = mergeRowColumn.getStartRowIndex();// 获取合并的行结束索引Integer endRowIndex = mergeRowColumn.getEndRowIndex();// 合并单元格(至少有两个单元格以上才能进行合并)if (startRowIndex < endRowIndex) {CellRangeAddress cellAddresses = new CellRangeAddress(startRowIndex, endRowIndex, curColumnIndex, curColumnIndex);// 判断起始单元格是否已经合并过了boolean mergedRegion = isMergedRegion(sheet, startRowIndex, curColumnIndex);if (!mergedRegion) {// 合并指定区域的单元格sheet.addMergedRegion(cellAddresses);}}// 重置合并索引(当前列的行指针下移)mergeRowColumn.setStartRowIndex(curRowIndex);mergeRowColumn.setEndRowIndex(curRowIndex);}
}

源码分析：

• mergeRowColumnMap 是进行合并的关键所在，存储了所有需要合并的列的行合并区域索引，在遍历数据过程中，根据情况进行单元格合并然后偏移指针，或者只修改指针，这样就不需要频繁的进行合并操作。

2.3 MergeRowColumn

@NoArgsConstructor
@AllArgsConstructor
@Data
public class MergeRowColumn {/*** 开始行索引*/private Integer startRowIndex;/*** 结束行索引*/private Integer endRowIndex;/*** 开始列索引*/private Integer startColumnIndex;/*** 结束列索引*/private Integer endColumnIndex;
}

三、测试

案例测试代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.easy.excel.demo.handler.ColumnMergeStrategy;
import com.easy.excel.demo.model.OrderDetailEntity;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;import java.io.File;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;@Slf4j
@SpringBootTest
public class EasyExcelDemoTest2 {/*** 案例一*/@Testpublic void testMerge1(){File file = new File("order1.xlsx");WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").head(OrderDetailEntity.class).registerWriteHandler(new ColumnMergeStrategy(Arrays.asList(0)))// 订单号.build();ExcelWriter excelWriter = EasyExcel.write(file).build();// 写入数据excelWriter.write(data(), writeSheet);// 手动合并最后的单元格(最后的单元格没有办法合并,需要手动进行合并)writeSheet.getCustomWriteHandlerList().stream().filter(handler -> handler instanceof ColumnMergeStrategy).map(handler -> (ColumnMergeStrategy) handler).forEach(handler -> handler.finalMergeCell());excelWriter.finish();}/*** 案例二*/@Testpublic void testMerge2(){// 输出文件路径File file = new File("order2.xlsx");// 初始化列合并列父级依赖关系Map<Integer, List<Integer>> mergeColumnIndexMap = new HashMap<>();mergeColumnIndexMap.put(0, new ArrayList<>());//订单号mergeColumnIndexMap.put(10, Arrays.asList(0));//总数 ==> 订单号mergeColumnIndexMap.put(11, Arrays.asList(0));//总金额 ==> 订单号WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").head(OrderDetailEntity.class).registerWriteHandler(new ColumnMergeStrategy(mergeColumnIndexMap)).build();ExcelWriter excelWriter = EasyExcel.write(file).build();excelWriter.write(data(), writeSheet);// 手动合并最后的单元格(分段数据注入时最后的单元格没有办法合并,需要手动进行合并)writeSheet.getCustomWriteHandlerList().stream().filter(handler -> handler instanceof ColumnMergeStrategy).map(handler -> (ColumnMergeStrategy) handler).forEach(handler -> handler.finalMergeCell());excelWriter.finish();}/*** 案例三*/@Testpublic void testMerge3(){// 输出文件路径File file = new File("order3.xlsx");// 初始化列合并上级依赖关系Map<Integer, List<Integer>> mergeColumnIndexMap = new HashMap<>();mergeColumnIndexMap.put(0, new ArrayList<>());//订单号mergeColumnIndexMap.put(2, Arrays.asList(0));//商品分类 ==> 订单号mergeColumnIndexMap.put(8, Arrays.asList(0, 2));//分类总数 ==> 订单号,商品分类mergeColumnIndexMap.put(9, Arrays.asList(0, 2));//分类总金额 ==> 订单号,商品分类mergeColumnIndexMap.put(10, Arrays.asList(0));//总数 ==> 订单号mergeColumnIndexMap.put(11, Arrays.asList(0));//总金额 ==> 订单号WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").head(OrderDetailEntity.class).registerWriteHandler(new ColumnMergeStrategy(mergeColumnIndexMap)).build();ExcelWriter excelWriter = EasyExcel.write(file).build();// 模拟分页查询数据for (int i = 0; i < 3; i++) {excelWriter.write(data(), writeSheet);}// 手动合并最后的单元格(分段数据注入时最后的单元格没有办法合并,需要手动进行合并)writeSheet.getCustomWriteHandlerList().stream().filter(handler -> handler instanceof ColumnMergeStrategy).map(handler -> (ColumnMergeStrategy) handler).forEach(handler -> handler.finalMergeCell());excelWriter.finish();}/*** 随机生成测试数据* @return*/private Collection<?> data() {Map<String, List<String>> productMap = getProductMap();List<String> statusList = Arrays.asList("待发货", "已发货", "运输中", "待取货", "已完成");List<OrderDetailEntity> dataList = new ArrayList<>();Random random = new Random();int orderCount = random.nextInt(2) + 5;for (int i = 0; i < orderCount; i++) {String orderCode = "PL" + DateUtils.format(new Date(), "yyyyMMddHHmm") + "000" + i;int orderDetailCount = random.nextInt(10) + 1;List<OrderDetailEntity> detailEntities = new ArrayList<>();Map<String, BigDecimal> categoryTotalQuantityMap = new HashMap<>();Map<String, BigDecimal> categoryTotalPriceMap = new HashMap<>();BigDecimal totalQuantity = BigDecimal.ZERO;BigDecimal totalPrice = BigDecimal.ZERO;for (int j = 0; j < orderDetailCount; j++) {String orderDetailCode = UUID.randomUUID().toString();String productCategory = new ArrayList<String>(productMap.keySet()).get(random.nextInt(productMap.size()));List<String> productList = productMap.get(productCategory);String productCode = "SKU" + (random.nextInt(1000)+1000);String productName = productList.get(random.nextInt(productList.size())) + "-A" + random.nextInt(50);BigDecimal price = new BigDecimal(random.nextInt(2000) + 800);BigDecimal quantity = new BigDecimal(random.nextInt(5) + 1);String status = statusList.get(random.nextInt(statusList.size()));String key = orderCode + "-" + productCategory;BigDecimal categoryTotalQuantity = categoryTotalQuantityMap.get(key);if (categoryTotalQuantity == null) {categoryTotalQuantity = quantity;} else {categoryTotalQuantity = categoryTotalQuantity.add(quantity);}categoryTotalQuantityMap.put(key, categoryTotalQuantity);BigDecimal categoryTotalPrice = categoryTotalPriceMap.get(key);if (categoryTotalPrice == null) {categoryTotalPrice = price.multiply(quantity);} else {categoryTotalPrice = categoryTotalPrice.add(price.multiply(quantity));}categoryTotalPriceMap.put(key, categoryTotalPrice);totalQuantity = totalQuantity.add(quantity);totalPrice = totalPrice.add(price.multiply(quantity));detailEntities.add(OrderDetailEntity.builder().orderCode(orderCode).orderDetailCode(orderDetailCode).productCategory(productCategory).productCode(productCode).productName(productName).price(price).quantity(quantity).status(status).build());}for (OrderDetailEntity item : detailEntities) {String key = item.getOrderCode() + "-" + item.getProductCategory();item.setCategoryTotalQuantity(categoryTotalQuantityMap.get(key));item.setCategoryTotalPrice(categoryTotalPriceMap.get(key));item.setTotalQuantity(totalQuantity);item.setTotalPrice(totalPrice);}detailEntities = detailEntities.stream().sorted(Comparator.comparing(OrderDetailEntity::getOrderCode).thenComparing(OrderDetailEntity::getProductCategory)).collect(Collectors.toList());dataList.addAll(detailEntities);}return dataList;}private Map<String, List<String>> getProductMap() {Map<String, List<String>> productMap = new HashMap<>();// 家电List<String> householdList = new ArrayList<>();householdList.add("电视机");householdList.add("冰箱");householdList.add("洗衣机");householdList.add("空调");productMap.put("家电", householdList);// 数码产品List<String> digitalList = new ArrayList<>();digitalList.add("手机");digitalList.add("摄影机");digitalList.add("电脑");digitalList.add("照相机");digitalList.add("投影仪");digitalList.add("智能手表");productMap.put("数码产品", digitalList);// 健身器材List<String> gymEquipmentList = new ArrayList<>();gymEquipmentList.add("动感单车");gymEquipmentList.add("健身椅");gymEquipmentList.add("跑步机");productMap.put("健身器材", gymEquipmentList);return productMap;}
}

OrderDetailEntity

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;import java.math.BigDecimal;@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
// 头背景设置
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
//标题高度
@HeadRowHeight(30)
//内容高度
@ContentRowHeight(20)
//内容居中,左、上、右、下的边框显示
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
public class OrderDetailEntity {@ExcelProperty(value = "订单号")@ColumnWidth(25)private String orderCode;@ExcelProperty(value = "订单明细")@ColumnWidth(40)private String orderDetailCode;@ExcelProperty(value = "商品分类")@ColumnWidth(20)private String productCategory;@ExcelProperty(value = "商品编码")@ColumnWidth(20)private String productCode;@ExcelProperty(value = "商品名称")@ColumnWidth(20)private String productName;@ExcelProperty(value = "单价")@ColumnWidth(10)private BigDecimal price;@ExcelProperty(value = "数量")@ColumnWidth(10)private BigDecimal quantity;@ExcelProperty(value = "状态")@ColumnWidth(10)private String status;@ExcelProperty(value = "分类总数")@ColumnWidth(20)private BigDecimal categoryTotalQuantity;@ExcelProperty(value = "分类总金额")@ColumnWidth(20)private BigDecimal categoryTotalPrice;@ExcelProperty(value = "总数")@ColumnWidth(10)private BigDecimal totalQuantity;@ExcelProperty(value = "总金额")@ColumnWidth(10)private BigDecimal totalPrice;
}

参考文章

https://blog.csdn.net/xhmico/article/details/141814528