easyexcel 动态合并单元格

目前操作excel文档的底层都是用poi来进行的，在早期工作开发过程中，是基于poi，然后对每一个数据单元格进行操作代码编写，后面有一些比较好的开源项目，像easyexcel、easypoi、hutool等，由于身处电商之都，就选择了阿里开源的easyexcel来做二次开发，源码相对来说并没有那么枯燥，有兴趣可以细细研读一番，对抽象能力，编码规范等一些重要的编程功底都有所帮助的。

单元格的动态合并

在实际项目开发过程中，不可避免地会有生成excel文档的需求，同时也不可避免地会有对excel里面内容的一些微小操作，所以，我们在创建workbook、sheet、row、cell会对其相应的有一些样式的要求。

easyexcel有提供writehandler接口提供自由发挥的空间。

workbook创建前后，所有操作完成后过程中提供了改writehandler(相当于操作workbook过程的3个切面处理)。

public interface WorkbookWriteHandler extends WriteHandler {

    /**
     * Called before create the workbook
     */
    void beforeWorkbookCreate();

    /**
     * Called after the workbook is created
     *
     * @param writeWorkbookHolder
     */
    void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder);

    /**
     * Called after all operations on the workbook have been completed
     *
     * @param writeWorkbookHolder
     */
    void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder);
}

sheet创建前后有SheetWriteHandler：

public interface SheetWriteHandler extends WriteHandler {

    /**
     * Called before create the sheet
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder);

    /**
     * Called after the sheet is created
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder);
}

Row创建前后有RowWriteHandler：

public interface RowWriteHandler extends WriteHandler {

    /**
     * Called before create the row
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     *            Nullable.It is null without using table writes.
     * @param rowIndex
     * @param relativeRowIndex
     *            Nullable.It is null in the case of fill data.
     * @param isHead
     *            Nullable.It is null in the case of fill data.
     */
    void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,
        Integer relativeRowIndex, Boolean isHead);

    /**
     * Called after the row is created
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     *            Nullable.It is null without using table writes.
     * @param row
     * @param relativeRowIndex
     *            Nullable.It is null in the case of fill data.
     * @param isHead
     *            Nullable.It is null in the case of fill data.
     */
    void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Integer relativeRowIndex, Boolean isHead);

    /**
     * Called after all operations on the row have been completed.This method is not called when fill the data.
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     *            Nullable.It is null without using table writes.
     * @param row
     * @param relativeRowIndex
     *            Nullable.It is null in the case of fill data.
     * @param isHead
     *            Nullable.It is null in the case of fill data.
     */
    void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Integer relativeRowIndex, Boolean isHead);
}

Cell操作过程有CellWriteHandler：

public interface CellWriteHandler extends WriteHandler {

    /**
     * Called before create the cell
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     *            Nullable.It is null without using table writes.
     * @param row
     * @param head
     *            Nullable.It is null in the case of fill data and without head.
     * @param columnIndex
     * @param relativeRowIndex
     *            Nullable.It is null in the case of fill data.
     * @param isHead
     *            It will always be false when fill data.
     */
    void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head,
        Integer columnIndex, Integer relativeRowIndex, Boolean isHead);

    /**
     * Called after the cell is created
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     *            Nullable.It is null without using table writes.
     * @param cell
     * @param head
     *            Nullable.It is null in the case of fill data and without head.
     * @param relativeRowIndex
     *            Nullable.It is null in the case of fill data.
     * @param isHead
     *            It will always be false when fill data.
     */
    void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head,
        Integer relativeRowIndex, Boolean isHead);

    /**
     * Called after the cell data is converted
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     *            Nullable.It is null without using table writes.
     * @param cell
     * @param head
     *            Nullable.It is null in the case of fill data and without head.
     * @param cellData
     *            Nullable.It is null in the case of add header.
     * @param relativeRowIndex
     *            Nullable.It is null in the case of fill data.
     * @param isHead
     *            It will always be false when fill data.
     */
    void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData,
        Cell cell, Head head, Integer relativeRowIndex, Boolean isHead);

    /**
     * Called after all operations on the cell have been completed
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     *            Nullable.It is null without using table writes.
     * @param cell
     * @param head
     *            Nullable.It is null in the case of fill data and without head.
     * @param cellDataList
     *            Nullable.It is null in the case of add header.There may be several when fill the data.
     * @param relativeRowIndex
     *            Nullable.It is null in the case of fill data.
     * @param isHead
     *            It will always be false when fill data.
     */
    void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
        List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead);
}

因为是动态合并单元格，只用到了CellWriteHandler，不过项目提供了一个AbstractMergeStrategy，比较友好，让你只关心合并的工作，不需要对其他的想太多。

public abstract class AbstractMergeStrategy implements CellWriteHandler {
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
        Head head, Integer relativeRowIndex, Boolean isHead) {}

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder,
        WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex,
        Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
        List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            return;
        }
        merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
    }

    /**
     * merge
     *
     * @param sheet
     * @param cell
     * @param head
     * @param relativeRowIndex
     */
    protected abstract void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex);
}

自定义单元格合并策略

public class CellMergeWriterHandler {

    public static CellWriteHandler getCellWriteHandler(CellMergeEnum cellMergeEnum) {
        if(CellMergeEnum.ROW.equals(cellMergeEnum)) {
            return new RowMergeWriterHandler();
        }

        if(CellMergeEnum.COLUMN.equals(cellMergeEnum)) {
            return new ColumnMergeWriterHandler();
        }

        return null;
    }

    public static CellWriteHandler getCellWriteHandler(CellMergeEnum cellMergeEnum, List<Integer> cols) {
        if(CellMergeEnum.ROW.equals(cellMergeEnum)) {
            return new RowMergeWriterHandler(cols);
        }

        if(CellMergeEnum.COLUMN.equals(cellMergeEnum)) {
            return new ColumnMergeWriterHandler(cols);
        }

        return null;
    }

    public static class ColumnMergeWriterHandler extends AbstractMergeStrategy {

        private List<Integer> mergeCols;

        public ColumnMergeWriterHandler() {}

        public ColumnMergeWriterHandler(List<Integer> mergeCols) {
            this.mergeCols = mergeCols;
        }

        @Override
        protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
            if(CollectionUtils.isNotEmpty(this.mergeCols) && !this.mergeCols.contains(cell.getColumnIndex())) {
                return;
            }
            mergeCell(sheet, cell, CellMergeEnum.COLUMN);
        }

    }


    public static class RowMergeWriterHandler extends AbstractMergeStrategy {

        private List<Integer> mergeCols;

        public RowMergeWriterHandler() {}

        public RowMergeWriterHandler(List<Integer> mergeCols) {
            this.mergeCols = mergeCols;
        }

        @Override
        protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
            if(CollectionUtils.isNotEmpty(mergeCols) && !mergeCols.contains(cell.getColumnIndex())) {
                return;
            }
            mergeCell(sheet, cell, CellMergeEnum.ROW);
        }
    }


    private static void mergeCell(Sheet sheet, Cell cell, CellMergeEnum cellMergeEnum) {
        if (Objects.isNull(cell)) {
            return;
        }

        int rowIndex = cell.getRowIndex(), colIndex = cell.getColumnIndex();
        Row preRow = null;
        Cell preCell = null;

        if(CellMergeEnum.ROW.equals(cellMergeEnum)) {
            preRow = sheet.getRow(rowIndex - 1);
            preCell = preRow.getCell(colIndex);
        }

        if(CellMergeEnum.COLUMN.equals(cellMergeEnum)) {
            if(colIndex == 0) {
                return;
            }
            preRow = cell.getRow();
            preCell = preRow.getCell(colIndex - 1);
        }

        if (Objects.isNull(preRow)||Objects.isNull(preCell)) {
            return;
        }
        mergeRows(sheet, preCell, cell);
    }


    /**
     * 行单元格合并
     *
     * @param sheet
     * @param preCell
     * @param curCell
     */
    private static void mergeRows(Sheet sheet, Cell preCell, Cell curCell) {
        Object preCellValue = getCellValue(preCell), curCellValue = getCellValue(curCell);
        if(Objects.isNull(curCellValue)) {
            return;
        }
        
        if(!"".equals(preCellValue)) {
            if(!preCellValue.equals(curCellValue)) {
                return;
            }
            curCell.setCellType(CellType.BLANK);
            sheet.addMergedRegion(new CellRangeAddress(preCell.getRowIndex(), curCell.getRowIndex(), preCell.getColumnIndex(), curCell.getColumnIndex()));
            return;
        }

        List<CellRangeAddress> list = sheet.getMergedRegions();
        CellRangeAddress rangeAddress = list.stream().filter(e -> compareColAndRow(e, preCell)).findFirst().orElse(null);
        if(Objects.isNull(rangeAddress)) {
            if("".equals(curCellValue)) {
                curCell.setCellType(CellType.BLANK);
                sheet.addMergedRegion(new CellRangeAddress(preCell.getRowIndex(), curCell.getRowIndex(), preCell.getColumnIndex(), curCell.getColumnIndex()));
                return;
            }
            return;
        }
        int firstRow = rangeAddress.getFirstRow(), firstColumn = rangeAddress.getFirstColumn();
        String value = String.valueOf(getCellValue(sheet.getRow(firstRow).getCell(firstColumn)));
        if(!value.equals(curCellValue)) {
            return;
        }

        int lastRow = curCell.getRowIndex(), lastColumn = curCell.getColumnIndex();
        for (int i = 0; i < list.size(); i++) {
            if(rangeAddress.equals(list.get(i))) {
                sheet.removeMergedRegion(i);
                curCell.setCellType(CellType.BLANK);
                sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
                return;
            }
        }
    }

    private static boolean compareColAndRow(CellRangeAddress cellRangeAddress, Cell cell) {
        return cellRangeAddress.getFirstColumn()<=cell.getColumnIndex() && cellRangeAddress.getLastColumn()>=cell.getColumnIndex()
            &&cellRangeAddress.getFirstRow()<=cell.getRowIndex()&&cellRangeAddress.getLastRow()>=cell.getRowIndex();
    }


    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    protected static Object getCellValue(Cell cell) {
        if (Objects.isNull(cell)) {
            return "";
        }

        CellType cellTypeEnum = cell.getCellTypeEnum();
        switch (cellTypeEnum) {
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case NUMERIC:
                return cell.getNumericCellValue();
            default:
                return "";
        }
    }

    public enum CellMergeEnum {
        ROW, COLUMN;
    }
}

通过模板实现数据填充

模板内容：

填充数据代码：

public class ExcelMergeTest {

  public static void main(String[] args) {

    Map<String, List<ExcelDto>> map = new HashMap<>(2);
    map.put("a", getListDtos());
    map.put("b", getListDtos());

      ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(new File("F:\\template2.xlsx"))
          .withTemplate("F:\\SourceCode\\easyexcel\\src\\main\\resources\\template2.xlsx");
      excelWriterBuilder.inMemory(Boolean.TRUE);
      ExcelWriter excelWriter = excelWriterBuilder.build();

      WriteSheet writeSheet = EasyExcel.writerSheet(0)
          .registerWriteHandler(CellMergeWriterHandler.getCellWriteHandler(CellMergeWriterHandler.CellMergeEnum.COLUMN, Arrays.asList(4)))
          .registerWriteHandler(CellMergeWriterHandler.getCellWriteHandler(CellMergeWriterHandler.CellMergeEnum.ROW, Arrays.asList(0,1,2)))
          .build();
      FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
      excelWriter.fill(new FillWrapper("a", getListDtos()), fillConfig, writeSheet);


      excelWriter.fill(new FillWrapper("b", getListDtos()), fillConfig, writeSheet);
      excelWriter.finish();

  }


  public static List<ExcelDto> getListDtos() {
   List<ExcelDto> list = new ArrayList<>();
   int t = 1000, i = 0;
   while (i<1) {
       list.add(ExcelDto.builder().name("张三").age(2).sex(2).work("打工仔").profession("打工仔").build());
       list.add(ExcelDto.builder().name("李四").age(2).sex(2).work("打工仔").profession("打工仔").build());
       list.add(ExcelDto.builder().name("李四").age(1).sex(1).work("打工仔").profession("打工仔").build());
       list.add(ExcelDto.builder().name("李四").age(2).sex(2).work("打工仔").profession("打工仔").build());
       list.add(ExcelDto.builder().name("赵六").age(2).sex(2).work("老板").profession("老板").build());
       i ++;
   }
    return list;
  }
}

完成效果：

合并单元格的本质还是要知道要合并单元格的坐标，然后通过sheet合并渲染。