基于EasyExcel锁定指定列导出数据到excel

大家好，我是llp。最近在做系统报表时中有一个需求时这样的，需要查询系统数据导出excel，并要求导出的excel列中有一些时锁定的有一些时不锁定的，即使实现动态列锁定的效果。看上去应该是一个比较简单的需求，但就是这样一个需求我停滞了许久，特此做个记录顺带做个分享。

1.需求描述

要求导出的excel列中有一些时锁定的有一些时不锁定的，即使实现动态列锁定的效果。

需求图示

easyexcel基本使用： https://blog.csdn.net/qq_44981526/article/details/122052455

2.实现步骤

1.获取要导出的数据

示例代码

@GetMapping("/exportRiskDetailReport")
@ApiOperation("风险排查明细统计导出")
public void exportRiskDetailReport(DetailReportDto detailReportDto){//1.结合实际业务查询数据List list = statisticsReportDomainService.riskDetailReport(premisesIdList, detailReportDto);//2.基于EasyExcel导出excel文件EasyExcelUtil.excelLockExport(DetailReportViewDto.class, "风险排查明细统计数据"+DateUtil.format(new Date(), "yyyyMMddHHmmssS"), list, null);
}

2.编写EasyExcel工具类

@Slf4j
public class EasyExcelUtil {//导出excel指定锁定列public static void excelLockExport(Class head, String excelname, List data, String sheetName) {ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();HttpServletResponse response = requestAttributes.getResponse();response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");try {// 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode(excelname, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), head)//锁定工作簿.registerWriteHandler(new LockSheetWriteHandler())//指定单元格解锁.registerWriteHandler(new CellHandler()).sheet(sheetName == null ? "Sheet1" : sheetName).doWrite(data);} catch (Exception e) {e.printStackTrace();log.error("导出数据失败: " + e.getMessage());}}
}

3.编写UnLockCell自定义注解

/*** 用于标记锁定哪些列不需要锁定*/
@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface UnLockCell {}

使用@UnLockCell注解修饰不需要锁定的字段，这里每个字段都对应easy的表头

@HeadRowHeight(value = 15)
@ColumnWidth(value = 18)
@Data
@ApiModel(description = "风险排查明细报表DTO")
public class DetailReportViewDto extends DtoBase {@ColumnWidth(value = 0)@ExcelProperty(value = "id")@ApiModelProperty(value = "风险明细补充数据id")private Long id;@ColumnWidth(value = 0)@ExcelProperty(value = "账单明细id")@ApiModelProperty(value = "账单明细id")private Long noticeDetailId;@ColumnWidth(value = 25)@ExcelProperty(value = "欠费单位")@ApiModelProperty(value = "欠费单位")private String dataCustomerName;@ExcelProperty(value = "是否关联企业", converter = BooleanConvert.class)@ApiModelProperty(value = "是否关联企业")private Boolean relationEnterprise;@ExcelProperty(value = "客户属性")@ApiModelProperty(value = "客户属性：租户|业主")private String contractRoleType;@ExcelIgnore@ApiModelProperty(value = "所属事业部资源id")private Long orgResourceId;@ExcelProperty(value = "所属事业部")@ApiModelProperty(value = "所属事业部")private String orgName;@ExcelProperty(value = "项目部")@ApiModelProperty(value = "项目部")private String organization;@ExcelIgnore@ApiModelProperty(value = "项目部资源id")private Long organizationResourceId;@ExcelIgnore@ApiModelProperty(value = "楼盘资源id")private Long premisesId;@ColumnWidth(value = 25)@ExcelProperty(value = "楼盘")@ApiModelProperty(value = "楼盘", example = "楼盘1")private String premisesName;@ExcelProperty(value = "欠费类型")@ApiModelProperty(value = "欠费类型")private String arrearsType;@ExcelIgnore@ApiModelProperty(value = "费项id", hidden = true)private Long costItemId;@ExcelProperty(value = "欠费期间起期")@ApiModelProperty(value = "欠费期间起期")private Date costDateBegin;@ExcelProperty(value = "欠费期间止期")@ApiModelProperty(value = "欠费期间止期")private Date costDateEnd;@ExcelProperty(value = "欠费总金额", converter = MoneyConvert.class)@ApiModelProperty(value = "欠费总金额", example = "800000000")private Money arrearsAmount;//----------------导入后显示字段----------------@UnLockCell@ExcelProperty(value = "欠费原因")@ApiModelProperty(value = "欠费原因", example = "没钱")private String reasonSummary;@UnLockCell@ExcelProperty(value = "欠费可回收比例")@ApiModelProperty(value = "欠费可回收比例")private String recoverableRatio;@UnLockCell@ExcelProperty(value = "可收取金额预估", converter = MoneyConvert.class)@ApiModelProperty(value = "可收取金额预估")private Money estimateAmount;@UnLockCell@ExcelProperty(value = "不可收取金额预估", converter = MoneyConvert.class)@ApiModelProperty(value = "不可收取金额预估")private Money notChargeableAmount;@UnLockCell@ExcelProperty(value = "清收举措")@ApiModelProperty(value = "清收举措", example = "UrgeMeasuresEnum STOPMETERS")private String urgeMeasures;@UnLockCell@ExcelProperty(value = "后果预判")@ApiModelProperty(value = "后果预判")private String consequencePrediction;@UnLockCell@ExcelProperty(value = "解决建议")@ApiModelProperty(value = "解决建议")private String solutionSuggestion;@UnLockCell@ExcelProperty(value = "备注")@ApiModelProperty(value = "备注")private String remark;
}

4.编写WriteHandler

用于锁定工作簿

public class LockSheetWriteHandler implements SheetWriteHandler {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();//锁定工作簿，设置保护密码sheet.protectSheet("1qaz!QAZ");// 锁定单元格不可选中(防止别人直接复制内容到其他excel修改)((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(true);}
}

用于指定单元格样式

public class CellHandler implements CellWriteHandler {private static final String PASSWORD = "1qaz!QAZ";/*** 在创建单元格之前调用* The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook** @param writeSheetHolder* @param writeTableHolder* @param row* @param head* @param columnIndex* @param relativeRowIndex* @param isHead*/@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}/*** 在创建单元格后调用** @param writeSheetHolder* @param writeTableHolder* @param cell             * @param head* @param relativeRowIndex* @param isHead*/@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}/*** 在转换单元格数据后调用** @param writeSheetHolder* @param writeTableHolder* @param cellData* @param cell* @param head* @param relativeRowIndex* @param isHead*/@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}/*** 在完成对单元格的所有操作后调用** @param writeSheetHolder* @param writeTableHolder* @param cellDataList* @param cell* @param head* @param relativeRowIndex* @param isHead*/@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {//获取当前表头的字段名String fieldName = head.getFieldName();//获取到前面定义的DetailReportViewDto的Class对象Class clazz = writeSheetHolder.getClazz();//获取该类的所有声明的字段Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {//遍历判断字段名是否和表头的字段名相同if (field.getName().equals(fieldName)) {//相同则判断改字段是否被@UnLockCell注解修饰if (field.isAnnotationPresent(UnLockCell.class)) {//被修饰则将单元格样式设置为不锁定Map properties = new HashMap<>();properties.put(CellUtil.LOCKED, false);//这里使用CellUtil.setCellStyleProperties的方式//对单元格设置样式不会影响原来的单元格可以实现单元格复用CellUtil.setCellStyleProperties(cell, properties);}}}}
}

说明：这里分成两个writeHandler，一个用于锁定工作簿，一个用于指定单元格样式

我所猜的坑页正是在这里，最开始我是用的是CellHandler，在这个handler中去锁定工作簿，并指定锁定列遇到了如下问题：

The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook

错误的原因：创建了太多的单元格样式，而这个限制其实xlsx excel所限制的，而poi或者说easyexcel只是遵守规则。

起初查询了关于这个错误的很多文章，大多都是建议将创建样式的方法写在循环外面或者说是复用样式。

显然复用样式是比较靠谱的方案，我尝试了在CellHandler中进行复用，但在导出时会限于死循环，一直得不到响应。

解决办法：