esayExcel自定义注解导出表头批注

注解

package com.baidu.activitidemo.annotation;

import com.baidu.activitidemo.handler.ExcelRemarkHandler;

import java.lang.annotation.*;

/**
 * 设置表头的批注, 需要配合{@link ExcelRemarkHandler}使用
 *
 * @author li
 * @date 2022/09/24
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelRemark {
    /**
     * 一般使用此属性的值进行批注的填充
     *
     * @return {@link String[]}
     */
    String value() default "";

    /**
     * 慎用此属性，建议使用value
     * 此处值应为一个SpEL，返回值需要是一个String
     * 此表达式若有值(无论是否调用是否成功)，则会忽略value属性
     * 使用示例：#root.getBean('userService')?.getSelectedList('2')
     * 此处的使用示例中的 #root指的是applicationContext
     *
     * @return {@link String}
     */
    String dynamicAccess() default "";

    /**
     * 批注所在行,一般不用设置，代码自动判断
     * -1表示自动获取头的最后一个行
     *
     * @return int
     */
    int remarkRow() default -1;

    /**
     * 批注行高, 一般不用设置
     *
     * @return
     */
    int remarkRowHigh() default 0;

    /**
     * 批注列宽, 根据导出情况调整
     *
     * @return
     */
    int remarkColumnWide() default 0;

}

Handler

package com.baidu.activitidemo.handler;


import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.baidu.activitidemo.annotation.ExcelRemark;
import com.baidu.activitidemo.config.SpringContextUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.expression.Expression;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * 设置表头的批注, 需要配合注解{@link ExcelRemark}使用
 * eg：EasyExcel.write(fileName, Person.class).registerWriteHandler(new ExcelRemarkHandler(Person.class))
 *
 * @author lxh
 * @date 2022/9/24 17:30
 */
public class ExcelRemarkHandler<T> implements RowWriteHandler {

    /** 解析后的下拉数据 */
    private Map<Integer, ExcelRemarkResolve> excelRemarkResolveMap;

    /** spring表达式解析器 */
    private SpelExpressionParser spelExpressionParser;

    /** 标准上下文 */
    private StandardEvaluationContext standardEvaluationContext;

    /** 需要解析的类 */
    private Class<T> clazz;

    public ExcelRemarkHandler(Class<T> clazz) {
        this.clazz = clazz;
        //解析注解
        this.analysisAnnotation();
    }


    @Override
    public void afterRowCreate(RowWriteHandlerContext context) {
        if (CollectionUtils.isEmpty(excelRemarkResolveMap)) {
            return;
        }

        if (context.getHead()) {
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
            excelRemarkResolveMap.forEach((column, v) -> {
                //若当前行与解析数据后的批注行不一样，则跳过
                if (v.row != context.getRow().getRowNum()) {
                    return;
                }
                //v.column, v.row决定批注放在哪个单元格, v.remarkColumnWide, v.remarkRowHigh决定单元个的长宽
                Comment comment =
                        drawingPatriarch.createCellComment(
                                new XSSFClientAnchor(0, 0, 0, 0,
                                        v.column, v.row, v.remarkColumnWide, v.remarkRowHigh));

                // 输入批注信息
                comment.setString(new XSSFRichTextString(v.getRemarkValue()));
                // 将批注添加到单元格对象中,这后面的的代码不知何用，删除的话，批注也能添加上
                Row row = sheet.getRow(v.row);
                if (row == null) {
                    row = sheet.createRow(v.row);
                }
                Cell cell = row.getCell(v.column);
                if (cell == null) {
                    cell = row.createCell(v.column);
                }
                cell.setCellComment(comment);

            });


        }

    }

    /**
     * 解析注释
     *
     * @return {@link List}<{@link ExcelRemarkResolve}>
     */
    private Map<Integer, ExcelRemarkResolve> analysisAnnotation() {
        Map<Integer, ExcelRemarkResolve> map = new TreeMap<>();

        //SpEL解析器
        //getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            //解析注解信息
            ExcelIgnore ignore = field.getAnnotation(ExcelIgnore.class);
            if (ignore != null) {
                continue;
            }

            //解析注解信息
            ExcelRemark remark = field.getAnnotation(ExcelRemark.class);
            if (remark != null) {
                ExcelRemarkResolve resolve = new ExcelRemarkResolve();
                String dynamicAccess = remark.dynamicAccess();
                if (dynamicAccess == null || dynamicAccess.isEmpty()) {
                    //直接获取注解中的值
                    resolve.setRemarkValue(remark.value());
                } else {
                    //调用SpEL解析器
                    resolve.setRemarkValue(this.getrRemarkValueFormApplicationContext(dynamicAccess));
                }
                if (resolve.getRemarkValue() == null || resolve.getRemarkValue().length() <= 0) {
                    continue;
                }

                //解析注解信息
                ExcelProperty property = field.getAnnotation(ExcelProperty.class);
                if (property != null && property.index() >= 0) {
                    resolve.setColumn(property.index());
                } else {
                    resolve.setColumn(i);
                }

                resolve.setRemarkRowHigh(remark.remarkRowHigh());
                resolve.setRemarkColumnWide(remark.remarkColumnWide());
                //设置批注所在行,若都没执行，***则使用int初始化的默认值0***
                if (remark.remarkRow() != -1) {
                    resolve.setRow(remark.remarkRow());
                } else if (property != null) {
                    //length属性为元素个数,此处需转换算成index
                    resolve.setRow(property.value().length - 1);
                }

                //将参数放入集合
                map.put(resolve.getColumn(), resolve);
            }
        }

        this.excelRemarkResolveMap = map;

        return map;

    }

    /**
     * 调用上下文中的方法，动态获取下拉值
     *
     * @param dynamicAccess 动态访问
     * @return {@link String}
     */
    private String getrRemarkValueFormApplicationContext(String dynamicAccess) {
        if (spelExpressionParser == null) {
            this.spelExpressionParser = new SpelExpressionParser();
        }

        if (standardEvaluationContext == null) {
            standardEvaluationContext = new StandardEvaluationContext(SpringContextUtil.getApplicationContext());
        }

        Expression expression = spelExpressionParser.parseExpression(dynamicAccess);
        Object value = expression.getValue(standardEvaluationContext);

        return value.toString();
    }

    @Data
    private class ExcelRemarkResolve {

        /** 列号 */
        private Integer column;

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

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

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

        /**
         * 批注所在行
         *
         * @return int
         */
        int row;
    }
}

spring上下文工具

package com.baidu.activitidemo.config;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * spring上下文工具
 * @author lxh
 * @date 2022/9/24 13:17
 */

@Component
public class SpringContextUtil implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringContextUtil.applicationContext = applicationContext;
    }


    /**
     * 获取ApplicationContext
     *
     * @return {@link ApplicationContext}
     */
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    /**
     * 通过class获取Bean
     *
     * @param clazz clazz
     * @return {@link T}
     */
    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }


    /**
     * 通过name以及class获取Bean
     *
     * @param name  名字
     * @param clazz clazz
     * @return {@link T}
     */
    public static <T> T getBean(String name, Class<T> clazz) {
        return applicationContext.getBean(name, clazz);
    }

}

使用示例

package com.baidu.activitidemo.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baidu.activitidemo.annotation.ExcelRemark;
import com.baidu.activitidemo.annotation.ExcelSelected;
import lombok.Data;

import java.time.LocalDateTime;

/**
 * @author lxh
 * @date 2022/7/24 10:32
 */
@Data
public class Person {
    @ExcelProperty("用户名")
    private String userName;

    @ExcelProperty(value = "年龄")
    private String userAge;

    @ExcelRemark(value = "编号只能从下拉框中选择\n否则无法通过校验")
    @ExcelSelected(value = {"code_001", "code_002"})
    @ExcelProperty(value = "编号")
    private String code;

    @ExcelProperty("创建时间")
    private LocalDateTime createTime;
}


@Test
void contextLoads() {
    List<Person> list = new ArrayList<>();
    list.add(new Person());
    String fileName = "D:\\temp\\" + System.currentTimeMillis() + ".xlsx";

    EasyExcel.write(fileName, Person.class)
            .registerWriteHandler(new ExcelRemarkHandler(Person.class))
            .sheet("模板")
            .doWrite(list);

}

大部分从网上搬的

主要来源：

Easyexcel生成excel并通过自定义注解实现下拉框以及动态下拉框(将数据库中的数据显示在excel下拉框中)EasyExcel在项目中的应用-在web中导出带下拉框和批注的excel文件