如何用EasyExcel实现下拉表格导入，导出

快速上手

1. 自定义注解

@Documented
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelDropdown {
    String[] value() default {};  //下拉列表
    boolean isAllowOtherValue() default false;   //是否允许设置其他的值。false：只能是下拉列表的值；true：允许列表之外的值
}

2. 添加工具类

/**
 * * @projectName
 * * @title AnnotationUtil
 * * @package
 * * @description注解工具类
 * * @author IT_CREAT
 * * @date2019 2019/9/14 21:16
 * * @version V1.0.0
 */
@Data
@Accessors(chain = true)
@ToString
public class AnnotationUtil<T> {

    public Class<T> clazz;

    public AnnotationUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * 动态修改对象属性上某个注解的属性值，通过getClazz()方法可以得到修改后的class
     *
     * @param fieldName       对象属性名称
     * @param annotationClass 注解class
     * @param attrName        注解属性名称
     * @param attrValue       注解属性值
     * @return 本工具类实例
     * @throws Exception 异常
     */
    public AnnotationUtil updateAnnoAttrValue(String fieldName, Class<? extends Annotation> annotationClass, String attrName, Object attrValue) throws Exception {
        Field[] declaredFields = this.clazz.getDeclaredFields();
        if (null != declaredFields && declaredFields.length != 0) {
            for (int i = 0; i < declaredFields.length; i++) {
                Field declaredField = declaredFields[i];
                if (fieldName.equals(declaredField.getName())) {
                    InvocationHandler invocationHandler = Proxy.getInvocationHandler(declaredField.getAnnotation(annotationClass));
                    Field hField = invocationHandler.getClass().getDeclaredField("memberValues");
                    hField.setAccessible(true);
                    Map memberValues = (Map) hField.get(invocationHandler);
                    memberValues.put(attrName, attrValue);
                    break;
                }
            }
        }
        return this;
    }

    /**
     * 动态修改对象属性上某个注解的属性值，通过getClazz()方法可以得到修改后的class
     *
     * @param fieldNames      字段数组
     * @param annotationClass 注解
     * @param attrNames       属性名数组-和字段依次匹配
     * @param attrValues      属性值数组-和字段依次匹配
     * @return
     * @throws Exception
     */
    public AnnotationUtil updateAnnoAttrValue(String[] fieldNames, Class<? extends Annotation> annotationClass, String[] attrNames, Object[] attrValues) throws Exception {
     /*   if (fieldNames == null || fieldNames.length == 0 || attrNames == null || attrValues == null) {
            throw new Exception("参数错误！");
        }
*/
        Field[] declaredFields = this.clazz.getDeclaredFields();
        if (null != declaredFields && declaredFields.length != 0) {
            HashMap<String, Field> map = new HashMap<>();
            for (int i = 0; i < declaredFields.length; i++) {
                Field declaredField = declaredFields[i];
                map.put(declaredField.getName(), declaredField);
            }

            for (int j = 0; j < fieldNames.length; j++) {
                if (!map.containsKey(fieldNames[j])) {
                    throw new Exception("字段名错误");
                }
                if(map.get(fieldNames[j]).getAnnotation(annotationClass) == null){
                    throw new Exception("该属性上无此注解");
                }
                InvocationHandler invocationHandler = Proxy.getInvocationHandler(map.get(fieldNames[j]).getAnnotation(annotationClass));
                Field hField = invocationHandler.getClass().getDeclaredField("memberValues");
                hField.setAccessible(true);
                Map memberValues = (Map) hField.get(invocationHandler);
                memberValues.put(attrNames[j], attrValues[j]);
            }
        }
        return this;
    }

}

3. 调用下面这两个方法

/** 
* 先调用这个方法
* fieldNames 表示导入bean中，需要添加下拉菜单的列的属性名。比如：ObjectBean有一个sex属性需要添加下拉列表时，fileNames里面就要添加"sex"
* attrValues 二维数组 每一维数组表示，对应同意下标位置，filedName中可能出现的值。例如：上面sex对应位置的值应该是["男","女"]。
**/
public void setExcelDropdownValue(String[] fieldNames, Object[] attrValues) throws Exception {
        AnnotationUtil<T> s = new AnnotationUtil<T>(this.clazz);
        String[] attrNames = new String[fieldNames.length]; //ExcelDropdown注解只有value属性
        for (int i = 0; i < attrNames.length; i++) {
            attrNames[i] = "value";
        }
        //更新字段上ExcelDropdown注解的value属性的值
        s.updateAnnoAttrValue(fieldNames, ExcelDropdown.class, attrNames, attrValues);
    }

/**
* 再调用这个方法
* 
**/
 public void downLoad(HttpServletResponse res, List<T> data, String fileName, String sheetName) throws Exception {
        setMapDropDown(this.clazz);
        DropdownWriteHandler dropdownWriteHandler = new DropdownWriteHandler();
        EasyExcelFactory.write(getOutputStream(fileName, res), this.clazz).sheet(sheetName).registerWriteHandler(dropdownWriteHandler).doWrite(data);
    }

读写基本数据参考 EasyExcel官网