EasyExcel导出中单元格合并遇到问题

在项目中需要对各种报表进行excel导出，主要使用了两种方式：esayExcel模板导出，poi手工导出。

esayExcel模板导出

使用模板导出相对而已比较简单，其过程如下：

1. 准备excel模板

   

   按照需求设计展示样式，同时填入对应的字段名称。字段的名称和填充的数据相关。

如果是填充的集合，则需通过“对象名.属性”的方式进行模板设计。

1. 通过模板得到ExcelWriter

public static ExcelWriter getExcelWriterWithMergeStrategy(HttpServletResponse response, String fileName, String templateFileName,
                                                              ExcelFillCellMergeStrategy excelFillCellMergeStrategy,
                                                              ExcelFillCellMergeStrategy detailMergeStrategy) throws Exception {
        OutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");//设置类型
        response.setHeader("Pragma", "No-cache");//设置头
        response.setHeader("Cache-Control", "no-cache");//设置头
        response.setDateHeader("Expires", 0);//设置日期头
        ExcelWriter excelWriter = EasyExcel.write(outputStream)
                .registerWriteHandler(excelFillCellMergeStrategy)
                .registerWriteHandler(detailMergeStrategy)
                .withTemplate(EasyExcelUtil.class.getClassLoader().getResourceAsStream("template/excel/"+templateFileName))
                .build();
        return excelWriter;
    }
           

主要是通过模板文件得到一个ExcelWriter 对象，其中xxxMergeStrategy为合并策略类。

1. 构建WriteSheet 并填充数据

WriteSheet writeSheet = EasyExcel.writerSheet("总体资源使用情况").build();
writeSheet.setAutoTrim(true);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); // 不加这个配置，第一行无法合并
//填充普通变量
excelWriter.fill(data, writeSheet);

excelWriter.fill(new FillWrapper("resourceScale", resourceScaleStatisticsVoList), fillConfig, writeSheet);
           

• 模板填充整个过程比较简单，主要是考虑单元格合并问题。单元格合并需要传入一个合并策略类，主要的逻辑是在数据填充过程中，会比较当前行和前一行的数据是否相同，如果相同则合并。

public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    private String sheet;

    private List<MergeStrategyParam> mergeStrategyParams;

    public ExcelFillCellMergeStrategy(List<MergeStrategyParam> mergeStrategyParams, String sheet) {
        this.sheet = sheet;
        this.mergeStrategyParams = mergeStrategyParams;
    }

    public static ExcelFillCellMergeStrategy createMergeStrategy(int[] mergeColumnIndex, int mergeRowIndexStart, int mergeRowIndexEnd, String sheet){
        ExcelFillCellMergeStrategy.MergeStrategyParam mergeStrategyParam = new ExcelFillCellMergeStrategy.MergeStrategyParam();
        mergeStrategyParam.setMergeColumnIndex(mergeColumnIndex);
        mergeStrategyParam.setMergeRowIndexStart(mergeRowIndexStart);
        mergeStrategyParam.setMergeRowIndexEnd(mergeRowIndexEnd);

        List<ExcelFillCellMergeStrategy.MergeStrategyParam> paramList = new ArrayList<>();
        paramList.add(mergeStrategyParam);
        return new ExcelFillCellMergeStrategy(paramList, sheet);
    }

    @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> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        if (StringUtil.isNotEmpty(sheet) && !writeSheetHolder.getSheetName().equalsIgnoreCase(sheet)){
            return;
        }
        //当前行
        int curRowIndex = cell.getRowIndex();

        //当前列
        int curColIndex = cell.getColumnIndex();

        for (MergeStrategyParam mergeStrategyParam : mergeStrategyParams) {
            int[] columnIndex = mergeStrategyParam.getMergeColumnIndex();
            if (curRowIndex > mergeStrategyParam.getMergeRowIndexStart() && curRowIndex <= mergeStrategyParam.getMergeRowIndexEnd()) {
                for (int i = 0; i < columnIndex.length; i++) {
                    if (curColIndex == columnIndex[i]) {
                        try {
                            mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                        } catch (Exception e) {
                            e.printStackTrace();
                            break;
                        }
                        break;
                    }
                }
                break;
            }
        }
    }
    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的第一列的数据和上一行的第一列数据，通过第一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }

    @Data
    public static class MergeStrategyParam{
        private int[] mergeColumnIndex;
        private int mergeRowIndexStart;
        private int mergeRowIndexEnd;
    }
}
           

这里主要增加了一个sheet变量和MergeStrategyParam对象。用来控制合并的sheet以及行数和列数。

问题

通过策略类合并单元格会出现数据的第一行无法合并。

填充时设置强制产生新行。

FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); // 不加这个配置，第一行无法合并
           

手工导出excel

CellRangeAddress blockRange = new CellRangeAddress(startRow, endRow - 1, 0, 0);
    sheet.addMergedRegionUnsafe(blockRange);
           

需要手动指定需要合并的区域，包括行数和列数。

通常我们是按照某个分类字段进行单元格合并，所以需要将某个分类进行分组。stream的groupby刚好可以满足这个要求。

这样就可以计算出每一个分类一共多少行，然后可以指定需要合并的行数。

问题

groupingBy()默认是使用的HashMap进行数据存储。对key进行hash值计算，所以产生的数据的顺序就会改变。合并单元格的数据顺序，和数据填入的顺序不一样，会导致excel数据展示紊乱。

所以在进行分组时需要指定排序规则，使用TreeMap来进行数据存储，可以保证数据按字符顺序进行排序。