EasyExcel对列同类项进行单元格合并
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
<scope>compile</scope>
</dependency>
- 返回类型
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;
}
- 实现调用
@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);
}
}
- 自定义的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;
}
}
}
}
- 结果