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实体类

@Data
public class CtmsSubSaeExcel implements Serializable {




    /**
     * 受试者筛选号
     */
    @ExcelProperty(value = "受试者筛选号",index = 0)
    @ColumnWidth(12)
    @ExcelValid(message = "受试者筛选号不能为空")
    @ExcelNotation(value = "受试者筛选号不能为空",remarkColumnWide =(short) 1)
    private String subjectCode;


    /**
     * SAE编号
     */
    @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 "";

    /**
     * 批注行高, 一般不用设置
     * 这个参数可以设置不同字段 批注显示框的高度
     * @return
     */
    int remarkRowHigh() default 0;

    /**
     * 批注列宽, 根据导出情况调整
     * 这个参数可以设置不同字段 批注显示框的宽度
     * @return
     */
    int remarkColumnWide() default 0;

校验是否为空 工具

public class ExcelImportValid {


    /**
     * Excel导入字段校验
     * @param object 校验的JavaBean 其属性须有自定义注解
     */
    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) {
		// 操作数据库

    }
}

备注使用的实体

@Data
public class ExcelComment {

    /** 列号 */
    private Integer column;

    /** 批注值 */
    private String remarkValue;

    /** 批注行高 */
    int remarkRowHigh;

    /** 批注列宽 */
    int remarkColumnWide;

    /**
     * 批注所在行
     *
     * @return int
     */
    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 ，我这里是从对应的实体中设置获取的。

备注解析

    /**
     * 获取批注Map
     *
     * @param clazz 类class
     * @return java.util.Map<java.lang.Integer, java.lang.String>
     * @author SunLingDa
     * @date 2022/11/3 13:24
     */
    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()));
        }

    }

效果；