EasyExcel对列同类项进行单元格合并

EasyExcel对列同类项进行单元格合并

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
    <scope>compile</scope>
</dependency>

1. 返回类型

public class DownDeptResDTO extends BaseModel {

    @ExcelProperty(value = "序号" ,index = 0)
    @ApiModelProperty(value = "序号")
    private Integer number;

    @ExcelIgnore
    @ApiModelProperty(value = "部门id")
    private Long deptId;

    @ExcelProperty(value = "部门名称" ,index = 1)
    @ColumnWidth(20)
    @ApiModelProperty(value = "部门名称")
    private String deptName;

    @ExcelIgnore
    @ApiModelProperty(value = "资源id")
    private Long resId;

    @ExcelProperty(value = "资源名称" ,index = 2)
    @ColumnWidth(40)
    @ApiModelProperty(value = "资源名称")
    private String resName;

    @ExcelProperty(value = "资源类型" ,index = 3)
    @ColumnWidth(20)
    @ApiModelProperty(value = "资源类型")
    private String resTypeStr;

    @ExcelProperty(value = "资源权限" ,index = 4)
    @ColumnWidth(10)
    @ApiModelProperty(value = "资源权限")
    private String resPowerStr;

    @ExcelProperty(value = "资源层级" ,index = 5)
    @ColumnWidth(10)
    @ApiModelProperty(value = "资源层级")
    private String resLevelStr;

    @ExcelIgnore
    @ApiModelProperty(value = "资源提供部门")
    private Long resSourceDeptId;

    @ExcelProperty(value = "归集情况" ,index = 6)
    @ColumnWidth(10)
    @ApiModelProperty(value = "归集情况(1.已归集 2.未归集)")
    private String collectionSituationStr;
    
}

2. 实现调用

@ApiOperation(value = "数源单位下资源下载")
    @GetMapping("/downLoadCollectDeptRes")
    @ApiImplicitParams(value = {
            @ApiImplicitParam(name = "collectionSituation", value = "资源是否上线:1-已归集 2-未归集")
    })
    public void downLoadCollectDeptRes(HttpServletResponse response, Integer collectionSituation) throws BizException {
        Assert.isNull(collectionSituation, "归集状态不可为空!");
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
            String date = sdf.format(new Date());
            // 返回需要导出的数据
            List<DownDeptResDTO> downCollectDeptRes = rdResourceDirService.getDownCollectDeptRes(collectionSituation);
            // 根据数据返回部门分类统计的条数信息，即每个部门下的数据条数，注意对应downCollectDeptRes 的排序
            List<Integer> deptGroup = rdResourceDirService.getDownCollectDeptResGroup(collectionSituation);
            // 自定义的合并方法
            MyMergeStrategy myMergeStrategy = new MyMergeStrategy(downCollectDeptRes, deptGroup);
            String fileName = "资源列表-" + date + ".xlsx";
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8").replace("+", " "));
            EasyExcel.write(response.getOutputStream(), DownDeptResDTO.class).registerWriteHandler(myMergeStrategy)
                    .sheet("资源数据导出").doWrite(downCollectDeptRes);
            log.info("资源数据导出成功");
        } catch (Exception e) {
            log.error("系统数据导出失败" + e.getMessage(), e);
        }
    }

3. 自定义的MyMergeStrategy合并方法

public class MyMergeStrategy extends AbstractMergeStrategy {

    private List<DownDeptResDTO> downCollectDeptResList;
    private List<Integer> deptGroupCount;
    private Sheet sheet;

    public MyMergeStrategy(List<DownDeptResDTO> downCollectDeptResList, List<Integer> deptGroupCount) {
        this.downCollectDeptResList = downCollectDeptResList;
        this.deptGroupCount = deptGroupCount;
    }

    // 将该列全部合并成一个单元格，此处并未使用
    private void mergeCommonColumn(Integer index) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, downCollectDeptResList.size(), index, index);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
    }

    // 按照分组将各种类别分别合并成一个单元格
    private void mergeGroupColumn(Integer index) {
        Integer rowCnt = 1;
        for (Integer count : deptGroupCount) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCnt, rowCnt + count - 1, index, index);
            // 读取到的同类相只有一个时不合并
            if (count > 1) {
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
            rowCnt += count;
        }
    }

    @Override
    protected void merge(org.apache.poi.ss.usermodel.Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 1) {
            switch (cell.getColumnIndex()) {
                case 0:
                    // this.mergeCommonColumn(0);
                    break;
                    // 对第二列进行同类项合并，即对部门名称相同的数据进行合并同类相
                case 1:
                    this.mergeGroupColumn(1);
                    break;
                case 2:
                    break;
                case 3:
                    // this.mergeCommonColumn(3);
                    break;
                default:
                    break;
            }
        }
    }
}

5. 结果