EasyExcel合并单元格

EasyExcel合并单元格

写excel不可避免的会使用到合并单元格，之前也说过EasyExcel提供了对于excel文件、sheet页、行以及单元格的钩子，合并单元格用到的就是单元格的钩子，也就是实现CellWriteHandler接口。

而且EasyExcel提供了一个合并单元格的抽象类AbstractMergeStrategy，可以继承该类来实现merge方法。

这里简单实现了一个自定义的合并单元格，与该列的上一行比较值是否相同，相同的话就进行合并

static class CustomMergeCellHandler extends AbstractMergeStrategy {

    // 需要合并的字段
    private List<String> mergeFields;
    // 总条数
    private int count;
    public CustomMergeCellHandler(List<String> mergeFields,int count){
        this.mergeFields = mergeFields;
        this.count = count;
    }

    // 存储上次的值  Pair left为值，right为上次出现为第几行
    private Map<String, Pair<Object,Integer>> previousValueMap = new HashMap<>();

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {

        int curRowNum = relativeRowIndex+1;
        if(mergeFields != null && mergeFields.size() > 0){
            if(mergeFields.contains(head.getFieldName())){ // 是否是需要合并的字段
                if(previousValueMap.containsKey(head.getFieldName())){
                    // 比较是否相等
                    Pair<Object, Integer> pair = previousValueMap.get(head.getFieldName());
                    Object curValue = getCellValue(cell);

                    if(!pair.getLeft().equals(curValue)){ // 不相等，需要进行合并处理
                        if(curRowNum - pair.getRight() > 1){ // 只有一行时不进行处理
                            sheet.addMergedRegion(new CellRangeAddress(pair.getRight(), curRowNum - 1, cell.getColumnIndex(), cell.getColumnIndex()));
                            
                        }
                        // 置为最新的值
                        previousValueMap.put(head.getFieldName(),Pair.of(curValue,curRowNum));
                    } else { // 如果是最后一行，也要进行合并
                        if(count == curRowNum){
                            if(curRowNum - pair.getRight() > 1){ // 只有一行时不进行处理
                                sheet.addMergedRegion(new CellRangeAddress(pair.getRight(), curRowNum, cell.getColumnIndex(), cell.getColumnIndex()));

                                // 置为最新的值
                                previousValueMap.put(head.getFieldName(),Pair.of(curValue,curRowNum));
                            }
                        }

                    }

                } else {
                    Object curValue = getCellValue(cell);
                    previousValueMap.put(head.getFieldName(),Pair.of(curValue,curRowNum));
                }
            }
        }
    }

    protected 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 "";
        }
    }
}

测试一下

@Test
public void testMerge(){
    List<String> fieldList = new ArrayList<>();
    fieldList.add("parentId");


    List<Merge> merges = new ArrayList<>();
    Merge merge = new Merge(1,1);
    Merge merge1 = new Merge(1,2);
    Merge merge2 = new Merge(2,3);
    Merge merge3 = new Merge(3,4);
    Merge merge4 = new Merge(3,5);
    Merge merge5 = new Merge(3,6);
    Merge merge6 = new Merge(3,7);
    Merge merge7 = new Merge(3,8);
    Merge merge8 = new Merge(4,9);
    merges.add(merge);
    merges.add(merge1);
    merges.add(merge2);
    merges.add(merge3);
    merges.add(merge4);
    merges.add(merge5);
    merges.add(merge6);
    merges.add(merge7);
    merges.add(merge8);

    CustomMergeCellHandler customMergeCellHandler = new CustomMergeCellHandler(fieldList,merges.size());


    EasyExcel.write("/Users/zhanghe/Desktop/user/fengshang/ads/ad-console/temp/merge.xls")
            .head(Merge.class)
            .registerWriteHandler(customMergeCellHandler)
            .sheet()
            .doWrite(merges);



}

效果