EasyExcel模板填充导出时合并单元格

一、FillConfig

使用EasyExcel的填充导出时，需要注意设置填充配置 FillConfig 。

// 使用模板填充时， forceNewRow(true) 这个是必须设置的，不然在 writeHandle中无法获取到整个sheet中的内容。
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();

二、注册 WriteHandle

实现 CellWriteHandler 。

public class RelationMergeHandler implements CellWriteHandler {

    private final Logger logger = LoggerFactory.getLogger(RelationMergeHandler.class);

    private Map<Integer, Boolean> rowMergeMap = new LinkedHashMap<>();

    @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 || cell.getRowIndex() < 2) {
            logger.info("\r\n当前为表头, 不执行操作");
            return;
        }

        /*
         * 判断当前 Cell 所在列索引不是0
         * 说明：
         *   1、列索引从0开始
         *   2、不添加当前代码判断，整个表都会进行跨行合并
         */
        if (cell.getColumnIndex() != 0 && cell.getColumnIndex() != 1 && cell.getColumnIndex() != 2) {
            logger.info("\r\n当前不是 [0,1,2] 其中一列, 不执行操作");
            return;
        }


        //当前 Sheet
        Sheet sheet = cell.getSheet();
        //当前 Cell 所在行索引
        int rowIndexCurr = cell.getRowIndex();
        //当前 Cell 所在行的上一行索引
        int rowIndexPrev = rowIndexCurr - 1;
        //当前 Cell 所在行的 Row 对象
        Row rowCurr = cell.getRow();
        //当前 Cell 所在行的上一行 Row 对象
        Row rowPrev = sheet.getRow(rowIndexPrev);
        //当前单元格的上一行同列单元格
        Cell cellPrev = rowPrev.getCell(cell.getColumnIndex());

        //当前单元格的值
        Object cellValueCurr = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        //上面单元格的值
        Object cellValuePrev = cellPrev.getCellType() == CellType.STRING ? cellPrev.getStringCellValue() : cellPrev.getNumericCellValue();

        //判断当前单元格与上面单元格是否相等，不相等不执行操作
        if (!cellValueCurr.equals(cellValuePrev)) {
            logger.info("\r\n两个单元格值不相等, 不执行操作");
            return;
        }

        if (cell.getColumnIndex() == 0) {
            rowMergeMap.put(cell.getRowIndex(), true);
        } else if (cell.getColumnIndex() != 0 && !rowMergeMap.containsKey(cell.getRowIndex())) {
            logger.info("\r\n当前列数据相同,但首列数据不同, 不执行操作");
            return;
        }

        /*
         * 当第一列上下两个单元格不一样时，说明不是一个数据，其他列单元格值相等也不能进行合并
         * 由于可以通过数据知道第一列为字符型数据，可以直接获取
         */
        if (!rowPrev.getCell(0).getStringCellValue().equals(rowCurr.getCell(0).getStringCellValue())) {
            logger.info("\r\n开始 " + rowCurr.getCell(0).getStringCellValue() + "数据合并操作");
            return;
        }

        //从 Sheet 中，获取所有合并区域
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        //是否合并过
        boolean merged = false;
        //遍历合并区域集合
        for (int i = 0; i < mergedRegions.size(); i++) {
            CellRangeAddress cellAddresses = mergedRegions.get(i);
            //判断 cellAddress 的范围是否是从 rowIndexPrev 到 cell.getColumnIndex()
            if (cellAddresses.isInRange(rowIndexPrev, cell.getColumnIndex())) {
                //从集合中移除
                sheet.removeMergedRegion(i);
                //设置范围最后一行，为当前行
                cellAddresses.setLastRow(rowIndexCurr);
                //重新添加到 Sheet 中
                sheet.addMergedRegion(cellAddresses);
                //已完成合并
                merged = true;
                break;
            }
        }

        //merged=false，表示当前单元格为第一次合并
        if (!merged) {
            CellRangeAddress cellAddresses = new CellRangeAddress(rowIndexPrev, rowIndexCurr, cell.getColumnIndex(), cell.getColumnIndex());
            sheet.addMergedRegion(cellAddresses);
        }
        logger.info("\r\n合并单元格完成");
    }

三、导出

String fileName = "导出文件_" + DateUtil.format(new Date(), "yyyyMMdd_HHmmss") + ".xlsx";
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName));
                response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
                try (ServletOutputStream outputStream = response.getOutputStream();
                     InputStream inputStream = new ClassPathResource("/template/exportOutwardInvestment.xlsx").getInputStream()) {
                    ExcelWriter excelWriter = EasyExcel.write(outputStream)
                            .withTemplate(inputStream)
                            .registerWriteHandler(new RelationMergeHandler())
                            .build();
                    FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
                    excelWriter.fill(dataList, fillConfig, EasyExcel.writerSheet("导出").build());
                    excelWriter.finish();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }