easyexcel合并策略

1、行合并

@Slf4j
public class ExcelRowHandler implements RowWriteHandler, SheetWriteHandler {

    /**
     * 起始行索引
     */
    private Integer startRowIndex = 0;

    /**
     * 结束行索引
     */
    private Integer endRowIndex;

    private Set<Integer> mergeColSet = new HashSet<>();

    private Integer sheetNo = 0;

    private String sheetName;

    public ExcelRowHandler() {}

    public ExcelRowHandler(Integer startRowIndex, Integer endRowIndex) {
        this.startRowIndex = startRowIndex;
        this.endRowIndex = endRowIndex;
    }

    public ExcelRowHandler(Integer startRowIndex, Integer endRowIndex, Set<Integer> mergeColSet) {
        this.startRowIndex = startRowIndex;
        this.endRowIndex = endRowIndex;
        this.mergeColSet = mergeColSet;
    }

    public ExcelRowHandler(Integer startRowIndex, Integer endRowIndex, Set<Integer> mergeColSet, String sheetName, Integer sheetNo) {
        this.startRowIndex = startRowIndex;
        this.endRowIndex = endRowIndex;
        this.mergeColSet = mergeColSet;
        this.sheetName = sheetName;
        this.sheetNo = sheetNo;
    }

    /**
     * 存储列的值
     */
    private Map<Integer, Object> columnDataMap = new HashMap<>();

    /**
     * 存储该数据第一次出现行
     */
    private Map<Object, Integer> currentDataFirstRowMap = new HashMap<>();


    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 不是表头数据开始处理
        if (!isHead) {
            // 获取表格对象
            Sheet sheet = writeSheetHolder.getSheet();
            // 获取当前行索引
            int currentRow = row.getRowNum();
            // 获取前一行索引
            int preRow = currentRow - 1;
            if (currentRow >= startRowIndex && currentRow <= endRowIndex && null != mergeColSet && !mergeColSet.isEmpty()) {
                for (Integer i : mergeColSet) {
                    // 等于起始行 存储当前行的值
                    Object currentColData = "";
                    if (null != row.getCell(i)) {
                        CellType cellType = row.getCell(i).getCellType();
                        switch (cellType) {
                            case NUMERIC -> currentColData = row.getCell(i).getNumericCellValue();
                            case STRING -> currentColData = row.getCell(i).getStringCellValue();
                        }
                    }
                    if (currentRow == startRowIndex && !TextUtil.isEmpty(currentColData)) {
                        currentDataFirstRowMap.put(getKey(currentColData, i), currentRow);
                        columnDataMap.put(i, currentColData);
                    } else {
                        // 获取当前列前一行的值
                        Object preData = columnDataMap.get(i);
                        // 不为空进行数据处理
                        if (!TextUtil.isEmpty(currentColData)) {
                            // 当前列前一行为空
                            if (TextUtil.isEmpty(preData)) {
                                // 存储当前行当前列第一个出现的位置
                                currentDataFirstRowMap.put(getKey(currentColData, i), currentRow);
                                // 存储当前列正扫描到的值
                                columnDataMap.put(i, currentColData);
                            } else {
                                // 当前行前一列不为空 判断当前行当前列的值是否和前一行当前列的值相等
                                if (!preData.equals(currentColData)) {
                                    // 如果不相等 合并之前的单元格 获取前一个值第一次出现的位置
                                    Integer preDataFirstRow = currentDataFirstRowMap.get(getKey(preData, i));
                                    if (null != preDataFirstRow && preDataFirstRow != preRow) {
                                        // 当前列前一行数据第一次出现的位置不为空 且不是在前一行第一次出现 合并单元格
                                        log.info("合并数据：{}，开始行：{}，结束行：{}，列：{}", preData, preDataFirstRow, preRow, i);
                                        mergeRegion(sheet, preDataFirstRow, preRow, i);
                                    }
                                    currentDataFirstRowMap.remove(getKey(preData, i));
                                    // 清除当前列扫描到的值
                                    columnDataMap.remove(i);
                                    // 存储当前值第一次出现的位置
                                    currentDataFirstRowMap.put(getKey(currentColData, i), currentRow);
                                    // 存储当前列新扫描到的值
                                    columnDataMap.put(i, currentColData);
                                } else if (currentRow == endRowIndex) {
                                    // 当前行数据和前一行数据相等 且当前行是最后一行 获取前一行数据第一次出现的位置 进行合并
                                    Integer preDataFirstRow = currentDataFirstRowMap.get(getKey(preData, i));
                                    if (null != preDataFirstRow) {
                                        // 当前列前一行数据第一次出现的位置不为空 合并单元格
                                        log.info("合并数据：{}，开始行：{}，结束行：{}，列：{}", preData, preDataFirstRow, preRow, i);
                                        mergeRegion(sheet, preDataFirstRow, currentRow, i);
                                    }
                                }
                                // 如果相等 继续判断下一行当前列
                            }
                        } else {
                            // 当前行当前列的值为空 判断前一行当前列的值
                            if (!TextUtil.isEmpty(preData)) {
                                // 前一行当前列的值不为空 获取前一行当前列的数据第一次出现的位置
                                Integer preDataFirstRow = currentDataFirstRowMap.get(getKey(preData, i));
                                if (null != preDataFirstRow && preDataFirstRow != preRow) {
                                    // 第一次出现的位置不为空并且不是在前一行第一次出现
                                    log.info("合并数据：{}，开始行：{}，结束行：{}，列：{}", preData, preDataFirstRow, preRow, i);
                                    mergeRegion(sheet, preDataFirstRow, preRow, i);
                                }
                            }
                            currentDataFirstRowMap.remove(getKey(preData,i));
                            // 清空当前列扫描到的值
                            columnDataMap.remove(i);
                        }
                    }
                }
            }
        }
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (null == sheetName) {
            return;
        }
        writeWorkbookHolder.getCachedWorkbook().setSheetName(sheetNo, sheetName);
    }

    private String getKey(Object data, int col) {
        return data + "-" + col;
    }

    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int col) {
        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, col, col);
        sheet.addMergedRegion(cellAddresses);
    }
}

2、列合并

@Slf4j
public class ExcelColHandler implements RowWriteHandler {

    private Integer firstColIndex = 0;

    private Integer lastColIndex = 0;

    public ExcelColHandler() {}

    public ExcelColHandler(Integer firstColIndex, Integer lastColIndex) {
        this.firstColIndex = firstColIndex;
        this.lastColIndex = lastColIndex;
    }

    /**
     * 存储该数据第一次出现的列
     */
    private Map<String, Integer> dataFirstColMap = new HashMap<>();

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        if (!isHead) {
            // 获取表格对象
            Sheet sheet = writeSheetHolder.getSheet();
            // 获取当前行索引
            int currentRow = row.getRowNum();
            String preData = null;
            for (Integer i = firstColIndex; i <= lastColIndex; i++) {
                String currentData = row.getCell(i).getStringCellValue();
                if (!TextUtil.isEmpty(currentData)) {
                    // 当前单元格值不为空 是第一列存储当前单元格值
                    if (Objects.equals(firstColIndex, i)) {
                        preData = currentData;
                        dataFirstColMap.put(currentData, i);
                    } else {
                        if (null != preData) {
                            if (!currentData.equals(preData)) {
                                Integer firstCol = dataFirstColMap.get(preData);
                                if (null != firstCol && firstCol != i - 1) {
                                    // 合并数据
                                    mergeRegion(sheet, currentRow, firstCol, i - 1);
                                }
                                preData = currentData;
                                dataFirstColMap.remove(preData);
                                dataFirstColMap.put(currentData, i);
                            }
                        } else {
                            preData = currentData;
                            dataFirstColMap.put(currentData, i);
                        }
                    }
                } else if (!TextUtil.isEmpty(preData)) {
                    Integer firstCol = dataFirstColMap.get(preData);
                    if (null != firstCol && firstCol != i - 1) {
                        // 合并数据
                        mergeRegion(sheet, currentRow, firstCol, i - 1);
                    }
                    dataFirstColMap.remove(preData);
                    preData = null;
                }
            }
        }
    }

    private void mergeRegion(Sheet sheet, int row, int firstCol, int lastCol) {
        CellRangeAddress cellAddresses = new CellRangeAddress(row, row, firstCol, lastCol);
        sheet.addMergedRegion(cellAddresses);
    }
}