easyexcel 实现表头批注

墨蓝 2024-03-22 17:26 66阅读 0赞

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

#### 以下是表头代码实现； ####

##### 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 ，我这里是从对应的实体中设置获取的。  
![在这里插入图片描述][121ed1c71f874c4f860d8e76c823aae4.png]

##### 备注解析 #####

/**
         * 获取批注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()));
            }
    
        }

#### 效果； ####

![在这里插入图片描述][b738bd213f794e54b5bdfced22822260.png]

[121ed1c71f874c4f860d8e76c823aae4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/3ca9b2dd1a774d508d87a3368c222840.png
[b738bd213f794e54b5bdfced22822260.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/553adfad5c1241b9b8cd210e2ced89c2.png