顾乔芝士网

持续更新的前后端开发技术栈

2025年04月07日

EasyExcel自定义合并单元格多行合并根据自定义字段

第一种方式实现

通过定义注解+实现RowWriteHandler接口中的afterRowDispose方法来动态合并行根据指定的key可以是单个字段也可以是多个字段也可以根据注解指定。注解方式使用参考原作者。

1 自定义注解

package com.test.utils;
 
import java.lang.annotation.*;
 
/**
 * 自定义注解，用于判断是否需要合并以及合并的主键
 *
 */
@Documented
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {
 
    /**
     * 是否是主键,即该字段相同的行合并
     */
    boolean isPk() default false;
 
    /**
     * 需要合并单元格字段标识、标识一致的会分为同一组
     */
    String[] value();
}

2 策略类

package com.test.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 自定义单元格合并策略
 *
 */
public class CustomMergeStrategy implements RowWriteHandler {
    /**
     * 主键下标集合
     */
    private Map<String, List> pkColumnIndexMap = new LinkedHashMap<>();

    /**
     * 需要合并的列的下标集合
     */
    private Map<String, List> mergeValueColumnIndexMap = new LinkedHashMap<>();

    /**
     * 需要合并的列
     */
    private final Map cellRangeAddressMap = new HashMap<>();

    /**
     * 去重后的主键下标集合
     */
    private final Set pkColumnIndexSet = new HashSet<>();

    /**
     * sheet数据总行数
     */
    private final int totalDataRowCount;

    /**
     * DTO数据类型
     */
    private final Class elementType;
   // 增加自定义合并key如果自定义合并key为空按照原有策略走
    private int[] mergeIndexs;

    public CustomMergeStrategy(Class elementType, int totalDataRowCount, int[] mergeIndexs) {
        this.elementType = elementType;
        this.totalDataRowCount = totalDataRowCount;
        this.mergeIndexs = mergeIndexs;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是标题，则直接返回
        if (isHead) {
            return;
        }

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        if (pkColumnIndexMap.isEmpty()) {
            this.lazyInit();
        }

        // 判断是否需要和上一行进行合并，不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1 return row lastrow='sheet.getRow(row.getRowNum()' - 1 pkcolumnindexmap.foreachk v -> {
            // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
            boolean mergeBol = true;
            for (Integer pkIndex : v) {
                if (lastRow.getCell(pkIndex) == null || row.getCell(pkIndex) == null) {
                    continue;
                }
                String lastKey = null;
                String currentKey = null;
                if (ObjectUtils.isEmpty(mergeIndexs)) {
                    lastKey = getStringCellValue(lastRow.getCell(pkIndex));
                    currentKey = getStringCellValue(row.getCell(pkIndex));

                } else {
                    lastKey = Arrays.stream(mergeIndexs).mapToObj(index -> getStringCellValue(lastRow.getCell(index))).collect(Collectors.joining());
                    currentKey = Arrays.stream(mergeIndexs).mapToObj(index -> getStringCellValue(row.getCell(index))).collect(Collectors.joining());
                }

                if (!StringUtils.equalsIgnoreCase(lastKey, currentKey)) {
                    mergeBol = false;
                    break;
                }
            }
            List mergeValueColumnIndex = mergeValueColumnIndexMap.get(k);
            if (mergeBol) {
                // 如果需要合并，则处理合并逻辑
                for (Integer needMerIndex : mergeValueColumnIndex) {
                    String key = String.format("%s-%s", k, needMerIndex);
                    // 处理已存在合并范围的情况，避免重复合并
                    if (pkColumnIndexSet.contains(needMerIndex)) {
                        long count = cellRangeAddressMap.entrySet().stream().filter(entry -> entry.getKey().contains("-" + needMerIndex)
                                && !entry.getKey().contains(key)).count();
                        if (count > 0) {
                            continue;
                        }
                    }
                    // 创建或更新合并范围对象
                    CellRangeAddress cellAddresses = cellRangeAddressMap.get(key);
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
                            needMerIndex, needMerIndex);
                    if (cellAddresses == null) {
                        cellRangeAddressMap.put(key, cellRangeAddress);
                    } else {
                        cellRangeAddress.setFirstRow(cellAddresses.getFirstRow());
                        cellRangeAddressMap.put(key, cellRangeAddress);
                    }
                    // 如果是最后一行，则将合并范围添加到sheet中
                    if (row.getRowNum() == totalDataRowCount) {
                        sheet.addMergedRegionUnsafe(cellRangeAddressMap.get(key));
                    }
                }
            } else {
                // 如果不需要合并，则移除相应的合并范围对象
                for (Integer needMerIndex : mergeValueColumnIndex) {
                    String key = String.format("%s-%s", k, needMerIndex);
                    CellRangeAddress cellAddresses = cellRangeAddressMap.get(key);
                    if (cellAddresses != null) {
                        sheet.addMergedRegionUnsafe(cellRangeAddressMap.get(key));
                        cellRangeAddressMap.remove(key);
                    }
                }
            }
        });
    }

    /**
     * 获取单元格的字符串内容
     *
     * @param cell 单元格对象
     * @return 单元格的字符串内容
     */
    private String getStringCellValue(Cell cell) {
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else {
            return String.valueOf(cell.getNumericCellValue());
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     */
    private void lazyInit() {
        // 获取DTO所有的属性
        Field[] fields = this.elementType.getDeclaredFields();
        boolean pkInitialized = false;

        // 遍历所有的字段
        for (Field theField : fields) {
            // 获取@ExcelProperty注解
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnno) {
                continue;
            }
            // 获取自定义的注解
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

            // 没有@CustomMerge注解的默认不合并
            if (null == customMerge) {
                continue;
            }
            String[] pks = customMerge.value();
            // 判断是否有主键标识
            if (customMerge.isPk()) {
                if (ObjectUtils.isEmpty(pks)) {
                    throw new IllegalStateException(String.format("字段[%s]使用@CustomMerge注解isPk为true但未指定合并值", theField.getName()));
                }
                for (String pk : pks) {
                    List pkColumnIndex = pkColumnIndexMap.get(pk);
                    if (pkColumnIndex == null) {
                        pkColumnIndex = new ArrayList<>();
                    }
                    pkColumnIndex.add(easyExcelAnno.index());
                    pkColumnIndexMap.put(pk, pkColumnIndex);
                    pkInitialized = true; // 标记主键已初始化
                }
                if (pks.length > 0) {
                    pkColumnIndexSet.add(easyExcelAnno.index());
                }
            }

            // 判断是否需要合并
            if (ObjectUtils.isNotEmpty(pks)) {
                for (String pk : pks) {
                    List needMergeColumnIndex = mergeValueColumnIndexMap.get(pk);
                    if (needMergeColumnIndex == null) {
                        needMergeColumnIndex = new ArrayList<>();
                    }
                    needMergeColumnIndex.add(easyExcelAnno.index());
                    mergeValueColumnIndexMap.put(pk, needMergeColumnIndex);
                }
            }
        }

        // 没有指定主键，则异常
        if (!pkInitialized) {
            throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
        }
    }
}

3 使用方式

          WriteSheet sheet2 = EasyExcel.writerSheet("卡明细补助").registerWriteHandler(new CustomMergeStrategy(LdYcTrafficAllowanceExportDetailVo.class, detailVos.size(), new int[]{2, 12, 15}))
                    .head(LdYcTrafficAllowanceExportDetailVo.class)
                    .build();

4 实体类

package com.test.domain.vo;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.yc.utils.CustomMerge;
import com.yfld.common.core.annotation.Excel;
import lombok.Data;

import java.util.Date;

/**
 * 公共交通补助明细列表
 */
@Data
public class TrafficAllowanceExportDetailVo {
...
   
//
    /** 打卡日期 */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @Excel(name = "打卡日期", width = 30, dateFormat = "yyyy-MM-dd")
    @ExcelProperty(value = "打卡日期", index = 15)
    @CustomMerge(value = {"serverRoomCode", "nickName", "checkInDate"}, isPk = true)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
    private String checkInDate;
    /** 交通补助（元） */
    @Excel(name = "交通补助")
    @ExcelProperty(value = "交通补助", index = 16)
    @CustomMerge(value = {"serverRoomCode", "nickName", "checkInDate"}, isPk = true)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
    private String transportAllowance;
}

第二种实现方式

通过定义注解+实现CellWriteHandler接口中的afterRowDispose根据指定的字段索引去做自定义key传入的数据需要做成map结构方便后续合并数据。

package com.test.utils.excel;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.yc.domain.vo.LdYcTrafficAllowanceExportDetailVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.*;

public class TrafficAllowanceCellWriteHandler implements CellWriteHandler {

    private final Map<String, List> data;

    private final Map groupRowMap; // 记录每个分组的起始行

    private final WriteCellStyle centerStyle; // 居中样式

    private final List groupKeyIndex;


    public TrafficAllowanceCellWriteHandler(Map<String, List> data, List groupKeyIndex) {
        this.data = data;
        this.groupRowMap = new HashMap<>();
        this.centerStyle = createCenterStyle();
        this.groupKeyIndex = groupKeyIndex;
    }

    /**
     * 创建居中样式
     */
    private WriteCellStyle createCenterStyle() {
        WriteCellStyle style = new WriteCellStyle();
        style.setHorizontalAlignment(HorizontalAlignment.CENTER); // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        return style;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead || cell.getRowIndex() == 0) {
            return;
        }

        Sheet sheet = writeSheetHolder.getSheet();
        int currentRowIndex = cell.getRow().getRowNum();
        String key = getRowKey(cell.getRow());

        // 遍历分组后的数据
//        for (Map.Entry<String, List> entry : data.entrySet()) {
////            String key = entry.getKey();
//             // 移动到下一个分组的起始行
//        }
        List orders = data.get(key);
        if (CollectionUtil.isNotEmpty(orders) && orders.size() > 1) {
            // 如果当前行是该分组的起始行
            if (!groupRowMap.containsKey(key)) {
                groupRowMap.put(key, currentRowIndex); // 记录分组的起始行
                if (orders.size() > 1) {
                    int lastRow = currentRowIndex + orders.size() - 1;
                    // 合并用户名和用户编码列
                    CellRangeAddress checkInDateRange = new CellRangeAddress(currentRowIndex, lastRow, 15, 15); // 用户名列
                    CellRangeAddress priceRange = new CellRangeAddress(currentRowIndex, lastRow, 16, 16); // 用户编码列
                    sheet.addMergedRegion(checkInDateRange);
                    sheet.addMergedRegion(priceRange);
                    // 设置合并区域的样式
                    setMergedRegionStyle(sheet, checkInDateRange, centerStyle);
                    setMergedRegionStyle(sheet, priceRange, centerStyle);
                }
//                currentRowIndex += orders.size();
            }

        }

    }

    /**
     * 设置合并区域的样式
     */
    private void setMergedRegionStyle(Sheet sheet, CellRangeAddress range, WriteCellStyle writeCellStyle) {
        Workbook workbook = sheet.getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();

        // 设置水平对齐方式
        if (writeCellStyle.getHorizontalAlignment() != null) {
            cellStyle.setAlignment(writeCellStyle.getHorizontalAlignment());
        }

        // 设置垂直对齐方式
        if (writeCellStyle.getVerticalAlignment() != null) {
            cellStyle.setVerticalAlignment(writeCellStyle.getVerticalAlignment());
        }

        // 设置字体
        if (writeCellStyle.getWriteFont() != null) {
            Font font = workbook.createFont();
            WriteFont writeFont = writeCellStyle.getWriteFont();
            if (writeFont.getFontName() != null) {
                font.setFontName(writeFont.getFontName());
            }
            if (writeFont.getFontHeightInPoints() != null) {
                font.setFontHeightInPoints(writeFont.getFontHeightInPoints());
            }
            if (writeFont.getBold() != null) {
                font.setBold(writeFont.getBold());
            }
            cellStyle.setFont(font);
        }

        // 设置背景颜色
        if (writeCellStyle.getFillForegroundColor() != null) {
            cellStyle.setFillForegroundColor(writeCellStyle.getFillForegroundColor());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        // 设置边框
        if (writeCellStyle.getBorderLeft() != null) {
            cellStyle.setBorderLeft(writeCellStyle.getBorderLeft());
        }
        if (writeCellStyle.getBorderRight() != null) {
            cellStyle.setBorderRight(writeCellStyle.getBorderRight());
        }
        if (writeCellStyle.getBorderTop() != null) {
            cellStyle.setBorderTop(writeCellStyle.getBorderTop());
        }
        if (writeCellStyle.getBorderBottom() != null) {
            cellStyle.setBorderBottom(writeCellStyle.getBorderBottom());
        }

        // 应用样式到合并区域的所有单元格
        for (int row = range.getFirstRow(); row <= range.getLastRow(); row++) {
            Row sheetRow = sheet.getRow(row);
            if (sheetRow == null) {
                sheetRow = sheet.createRow(row);
            }
            for (int col = range.getFirstColumn(); col <= range.getLastColumn(); col++) {
                Cell cell = sheetRow.getCell(col);
                if (cell == null) {
                    cell = sheetRow.createCell(col);
                }
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private String getRowKey(Row row) {
        StringBuilder key = new StringBuilder();
        for (int i : groupKeyIndex) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                key.append(cell.getStringCellValue());
            }
        }
        return key.toString();
//        Cell roomCodeCell = row.getCell(12);
//        if (roomCodeCell != null) {
//            key.append(roomCodeCell.getStringCellValue());
//        }
//        Cell nickNameCell = row.getCell(2);
//        if (nickNameCell != null)
//            key.append(nickNameCell.getStringCellValue());
//        Cell chenkInDateCell = row.getCell(15);
//        if (chenkInDateCell != null)
//            key.append(chenkInDateCell.getStringCellValue());
//        return key.toString();
    }
}

第二种实现方式使用方法

  // 创建并注册第二个Sheet（合并策略在此处处理）
            Map<String, List> detailMap = detailVos.stream().collect(Collectors.groupingBy(exportData -> exportData.getServerRoomCode() + exportData.getNickName() + exportData.getCheckInDate()));
  // 多个字段组合key标记excel中的位置index
            List groupKeyIndex = Arrays.asList(12, 2, 15);
            WriteSheet sheet2 = EasyExcel.writerSheet("项目打卡明细补助")
                    .head(LdYcTrafficAllowanceExportDetailVo.class)
                    .registerWriteHandler(new TrafficAllowanceCellWriteHandler(detailMap, groupKeyIndex))
                    .build();

第一种实现方式原文转载自这里在此基础上进行了调整，增加了多个字段组合key的实现方式通过参数产地

EasyExcel合并单元格，通过注解方式实现自定义合并策略，RowWriteHandler高效率实现-CSDN博客

标签：entryset 

作者:gpyoja , 分类:技术文章 , 浏览:7 , 评论:0

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言