easyexcel 实现表头批注

场景；在业务逻辑中，导出的时候需要给客户提供下载的模版，上传的文件有些字段值需要验证，如果不按照验证的规则，后端解析的时候就会失败，所以在导出模版需要给客户一个提示，一种方法是下载的模版中加入示例数据，另外一种就是在表头添加上备注。

以下是表头代码实现；

easyexcel 依赖以及对应的poi

  <dependency>            <groupId>com.alibaba</groupId>            <artifactId>easyexcel</artifactId>            <version>3.1.1</version>            <exclusions>                <exclusion>                    <groupId>javax.servlet</groupId>                    <artifactId>servlet-api</artifactId>                </exclusion>                <exclusion>                    <groupId>org.apache.poi</groupId>                    <artifactId>poi</artifactId>                </exclusion>                <exclusion>                    <groupId>org.apache.poi</groupId>                    <artifactId>poi-ooxml</artifactId>                </exclusion>                <exclusion>                    <groupId>org.apache.poi</groupId>                    <artifactId>poi-ooxml-schemas</artifactId>                </exclusion>            </exclusions>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>4.1.2</version>        </dependency>

easyexcel实体类

@Datapublic class CtmsSubSaeExcel implements Serializable {        @ExcelProperty(value = "受试者筛选号",index = 0)    @ColumnWidth(12)    @ExcelValid(message = "受试者筛选号不能为空")    @ExcelNotation(value = "受试者筛选号不能为空",remarkColumnWide =(short) 1)    private String subjectCode;        @ExcelProperty(value = "SAE编号唯一标识",index = 1)    @ExcelValid(message = "SAE编号不能为空")    @ColumnWidth(12)    private String saeNum;        @ExcelProperty(value = "SAE的医学术语",index = 2)    @ColumnWidth(12)    private String saeMedicalTerm;    }

@ExcelValid ；校验必填字段

@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelValid {    String message() default "导入有未填入的字段";}

@ExcelNotation ；自定义备注注解 。注意 @ExcelProperty必须加上index 不然获取的时候会有问题

@Target(FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelNotation {        String value() default "";        int remarkRowHigh() default 0;        int remarkColumnWide() default 0;

校验是否为空 工具

public class ExcelImportValid {        public static void valid(Object object) {        Field[] fields = object.getClass().getDeclaredFields();        for (Field field : fields) {            //设置可访问            field.setAccessible(true);            //属性的值            Object fieldValue = null;            try {                fieldValue = field.get(object);            } catch (IllegalAccessException e) {                throw ServiceExceptionUtil.exception(new ErrorCode(9999,                        field.getAnnotation(ExcelValid.class).message()));            }            //是否包含必填校验注解            boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);            if (isExcelValid && Objects.isNull(fieldValue)) {// 返回自定义的异常 提示                throw ServiceExceptionUtil.exception(new ErrorCode(9999,field.getAnnotation(ExcelValid.class).message()));            }        }    }}

导入数据的监听器

public class SubSAEListener extends AnalysisEventListener<CtmsSubSaeExcel> {    @Override    public void invoke(CtmsSubSaeExcel data, AnalysisContext context) {    // 获取行号        Integer rowIndex = context.readRowHolder().getRowIndex();        // 校验必填        ExcelImportValid.valid(data);  // 操作数据值         }    @Override    public void onException(Exception exception, AnalysisContext context) throws Exception {        batchAddList.clear();        batchUpdateList.clear();        super.onException(exception, context);    }    @Override    public void doAfterAllAnalysed(AnalysisContext context) {// 操作数据库    }}

备注使用的实体

@Datapublic class ExcelComment {        private Integer column;        private String remarkValue;        int remarkRowHigh;        int remarkColumnWide;        int row;}

备注注册器

public class CommentCellWriteHandler implements CellWriteHandler {        private final Map<Integer, ExcelComment> notationMap;    public CommentCellWriteHandler(Map<Integer, ExcelComment> notationMap) {        this.notationMap = notationMap;    }    @Override    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,     List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// 这里使用isHead判断 是否是表头的时候为true ,填入数据的时候是false , 之前使用head判断，调整表头备注只能有一个，切记        if(isHead){             Sheet sheet = writeSheetHolder.getSheet();            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();            if (CollUtil.isNotEmpty(notationMap) && notationMap.containsKey(cell.getColumnIndex())) {                // 批注内容                ExcelComment excelComment = notationMap.get(cell.getColumnIndex());                if(Objects.nonNull(excelComment)){                    // 创建绘图对象                    Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), 0,                        (short)excelComment.getRemarkColumnWide(), 1));                    comment.setString(new XSSFRichTextString(excelComment.getRemarkValue()));                    cell.setCellComment(comment);                }            }        }    }}

红线部分。是用来这是批注的宽度和高度，默认是0 ，我这里是从对应的实体中设置获取的。

备注解析

        public static Map<Integer, ExcelComment> getNotationMap(Class<?> clazz) {        Map<Integer, ExcelComment> notationMap = new HashMap<>();        Field[] fields = clazz.getDeclaredFields();        for (Field field : fields) {            if (!field.isAnnotationPresent(ExcelNotation.class) ) {                continue;            }            ExcelComment excelComment = new ExcelComment();            ExcelNotation excelNotation = field.getAnnotation(ExcelNotation.class);            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);            excelComment.setRemarkValue(excelNotation.value());            excelComment.setRemarkColumnWide(excelNotation.remarkColumnWide());            notationMap.put(excelProperty.index(), excelComment);        }        return notationMap;    }

业务实现

 @PostMapping("/test")    public void exportSubSAEExcel(HttpServletResponse response){        try {                   response.setContentType("application/vnd.ms-excel");            response.setCharacterEncoding("utf-8");            // 防止中文乱码            String fileName = URLEncoder.encode("SAE", "UTF-8")                    .replaceAll("\\+","%20");            response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + fileName + ExcelTypeEnum.XLSX.getValue());            //响应的输入流            ServletOutputStream outputStream = response.getOutputStream();            // workbook            ExcelWriterBuilder writeWorkBook = EasyExcel.write(outputStream, CtmsSubSaeExcel.class).inMemory(Boolean.TRUE).inMemory(Boolean.TRUE)                    .head(CtmsSubSaeExcel.class)                    .useDefaultStyle(false)                    .registerWriteHandler(new CommentCellWriteHandler(getNotationMap(CtmsSubSaeExcel.class))));// 添加备注的监听器                       // sheet            writeWorkBook.sheet().sheetName("SAE").sheetNo(0).doWrite(ctmsSubSaeExcels);            outputStream.flush();            outputStream.close();        }catch (IOException e){            throw ServiceExceptionUtil.exception(new ErrorCode(9999,"导出失败"));        }catch (IllegalArgumentException e){            throw ServiceExceptionUtil.exception(new ErrorCode(9999,e.getMessage()));        }    }

效果；

来源地址：https://blog.csdn.net/qq_43049310/article/details/130697234