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