当前位置:   article > 正文

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

easyexcel锁定列

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

大家好,我是llp。最近在做系统报表时,遇到一个需求,需要查询系统数据导出excel,并要求导出的excel列中有一些是锁定的有一些是不锁定的,其实就是实现动态列锁定的效果。

1.需求描述

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

  • 需求图示

image-20221129185658805

2.实现步骤

1.获取要导出的数据

示例代码

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

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());
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

3.编写UnLockCell自定义注解

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


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

使用@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;
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115

4.编写WriteHandler

用于锁定工作簿

public class LockSheetWriteHandler implements SheetWriteHandler {

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //锁定工作簿,设置保护密码
        sheet.protectSheet("1qaz!QAZ");
        // 锁定单元格不可选中(防止别人直接复制内容到其他excel修改)
        ((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(true);
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

用于指定单元格样式

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
     */
    @Override
    public 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
     */
    @Override
    public 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
     */
    @Override
    public 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
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        String fieldName = head.getFieldName();
        Class clazz = writeSheetHolder.getClazz();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.getName().equals(fieldName)) {
                if (field.isAnnotationPresent(UnLockCell.class) && field.isAnnotationPresent(ExcelProperty.class)) {
                    Map<String, Object> properties = new HashMap<>(1);
                    properties.put(CellUtil.LOCKED, false);
                    CellUtil.setCellStyleProperties(cell, properties);
                }
            }
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78

说明:这里分成两个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中进行复用,但在导出时会限于死循环,一直得不到响应。

解决办法:

  • 将锁定工作簿操作放在实现SheetWriteHandler接口的实现类中实现
  • 单元格样式复用操作,则在实现CellWriteHandler接口的实现类中实现

image-20221129190948467

3.最终效果

image-20221129193526121

image-20221129185658805

4.小总结

问题本身并不难,还是要多养成看API的习惯。有时候问题在网上找不到或者说应用场景不一样,看API文档确实会给到很大的帮助。

声明:本文内容由网友自发贡献,转载请注明出处:【wpsshop】
推荐阅读
相关标签
  

闽ICP备14008679号