EasyExcel设置下拉值，兼容java不同版本

keep the

已于 2025-02-23 16:37:51 修改

阅读量1.3k

点赞数 14

CC 4.0 BY-SA版权

文章标签： java excel

于 2024-12-05 14:19:24 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_68266674/article/details/144263073

工具类在底部，先上代码。

静态下拉值设置

public class ActiveGifts {

private String lpbarcode;

@ExcelProperty(value = "礼品名称")
private String lpname;

@ExcelProperty(value = "是否可用")
@ExcelDropdown(value = {"Y", "N"}, isAllowOtherValue = true)
private String isvalid;

}

动态下拉值设置

java8版本

实体

public class ActiveGifts {

private String lpbarcode;

@ExcelProperty(value = "礼品名称")
private String lpname;

@ExcelProperty(value = "是否可用")
@ExcelDropdown(value = {"Y", "N"}, isAllowOtherValue = true)
private String isvalid;

}

示例方法：动态修改为：是，否

public void downloadTemplate(HttpServletResponse response) {
    try {
        ExcelImportUtils<ActiveGifts> activeGiftsExcelImportUtils = new ExcelImportUtils<>(ActiveGifts.class);
        String[] strings = new String[1];
        strings[0] = "isvalid";
        Object[] values = new Object[strings.length];
        values[0] = new String[]{"是", "否"};
        //动态修改ExcelDropdown注解的值
        activeGiftsExcelImportUtils.setExcelDropdownValue(strings, values);
        activeGiftsExcelImportUtils.downLoad(response,new ArrayList<>(),"文件名", "sheet名");
    } catch (Exception e) {
        e.printStackTrace();
        throw new ServiceException("下载失败");
    }

}

java9及以上版本

实体

去掉这行：@ExcelDropdown(value = {"Y", "N"}, isAllowOtherValue = true)

public class ActiveGifts {

private String lpbarcode;

@ExcelProperty(value = "礼品名称")
private String lpname;

@ExcelProperty(value = "是否可用")
private String isvalid;

}

示例方法

public void downloadTemplate(HttpServletResponse response) {
    try {
        ExcelImportUtils<ActiveGifts> activeGiftsExcelImportUtils = new ExcelImportUtils<>(ActiveGifts.class);
        //动态设置下拉的值
        activeGiftsExcelImportUtils.setDropDownValue("isvalid", new String[]{"是", "否"});
        activeGiftsExcelImportUtils.downLoad(response, new ArrayList<>(), "文件名", "sheet名");
    } catch (Exception e) {
        e.printStackTrace();
        throw new ServiceException("下载失败");
    }
}

工具类

AnnotationUtil

@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;
    }


    /**
     * !!!!!!!!!! java版本过高用不了，须在启动类中添加配置 参考：https://blog.youkuaiyun.com/wenxuankeji/article/details/140672099
     * 动态修改对象属性上某个注解的属性值，通过getClazz()方法可以得到修改后的class
     * @author ljd
     * @date 2024/12/4
     * @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;
    }

}

ExcelDropdown

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

ExcelImportUtils

@Slf4j
public class ExcelImportUtils<T extends Object> {

    private Class<T> clazz;
    // 对应列的下拉列表
    Map<Integer, Map<String, Object>> mapDropDown = new HashMap<>();

    //对应日期位置列表
    List<Integer> dateList = new ArrayList<>();


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

    //导入
    public List<T> excelImport(InputStream stream) throws Exception {

        List<T> list = new ArrayList<>();

        EasyExcel.read(stream, Object.class, new AnalysisEventListener<T>() {
            @SneakyThrows
            @Override
            public void invoke(T o, AnalysisContext analysisContext) {
                list.add(o);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                log.debug("导入完成！");
            }
        }).sheet().doRead();

        return list;
    }

    /**
     * 下载
     *
     * @param res       响应
     * @param data      下载的数据
     * @param fileName  文件名
     * @param sheetName 表名
     * @throws Exception
     */
    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);
    }

    //响应头封装
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }

    //自定义处理器：单元格下拉列表格式
    class DropdownWriteHandler implements SheetWriteHandler {

        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

            Sheet sheet = writeSheetHolder.getSheet();
            //开始设置下拉框
            DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
            for (Map.Entry<Integer, Map<String, Object>> entry : mapDropDown.entrySet()) {
                /***起始行、终止行、起始列、终止列**/
                CellRangeAddressList addressList = new CellRangeAddressList(1, 100000, entry.getKey(), entry.getKey());  // 检查的区域
                /***设置下拉框数据**/
                DataValidationConstraint constraint = helper.createExplicitListConstraint((String[]) entry.getValue().get("val"));
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
                /***处理Excel兼容性问题**/
                if (dataValidation instanceof XSSFDataValidation) {
                    dataValidation.setSuppressDropDownArrow(true);  // 验证输入数据是否真确
                    dataValidation.setShowErrorBox(true);  // 输入无效值时是否显示错误框
                    dataValidation.setShowPromptBox(true);  // 设置无效值时 是否弹出提示框
                    dataValidation.createPromptBox("温馨提示", "只能选择列表中的值!!!");   // 设置无效值时的提示框内容
                    sheet.addValidationData(dataValidation);
                } else {
                    dataValidation.setSuppressDropDownArrow(false);
                }
                sheet.addValidationData(dataValidation);
            }

            /***时间格式校验**/
            for (int i : dateList) {
                DataValidationConstraint constraint2 = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "Date(1900, 1, 1)", "Date(2100, 12, 31)", "yyyy/MM/dd");
                DataValidation dataValidation2 = helper.createValidation(constraint2, new CellRangeAddressList(1, 100000, i, i));

                //校验时间
                dataValidation2.setSuppressDropDownArrow(true);  // 验证输入数据是否真确
                dataValidation2.setShowErrorBox(true);  // 输入无效值时是否显示错误框
                dataValidation2.setShowPromptBox(true);  // 设置无效值时 是否弹出提示框
                dataValidation2.createPromptBox("温馨提示", "请输入[yyyy-MM-dd]格式日期!!!");   // 设置无效值时的提示框内容
                sheet.addValidationData(dataValidation2);
            }

            //下面定时样式的
            Row row = sheet.getRow(0);
            if (row != null) {
                Workbook workbook = writeWorkbookHolder.getWorkbook();
                row.setHeight((short) 500);
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    sheet.setColumnWidth(i, 5000);
                    Cell cell = row.getCell(i);
                    cell.setCellStyle(setStyle(workbook));
                }
                row.setHeight((short) (205 * 7));
            }
        }

        //设置单元格样式
        public CellStyle setStyle(Workbook wb) {
            Font dataFont = wb.createFont();
            dataFont.setColor(new HSSFColor().getIndex());
            dataFont.setFontName("宋体");
            dataFont.setFontHeight((short) 240);
            dataFont.setBold(true);
            dataFont.setFontHeightInPoints((short) 10);
            CellStyle dataStyle = wb.createCellStyle();
            dataStyle.setFont(dataFont);
            dataStyle.setWrapText(true);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            return dataStyle;
        }

    }

    /**
     * 设置下拉值
     * （提供该方法，主要解决java版本过高不能动态修改ExcelDropdown的value值的问题）
     *
     * @param field   字段名
     * @param strings 列表值
     * @author ljd
     * @date 2024/12/5
     */
    public void setDropDownValue(String field, String[] strings) {
        Map<Integer, Map<String, Object>> mapDropDown = new HashMap<>();
        HashMap<String, Object> stringObjectHashMap = new HashMap<>();
        stringObjectHashMap.put("val", strings);
        Field[] fields = clazz.getDeclaredFields();
        //用于标志是excel的哪个列
        int flag = -1;
        for (int i = 0; i < fields.length; i++) {
            ExcelProperty annotation = fields[i].getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                flag = flag + 1;
            }
            if (fields[i].getName().equals(field)) {
                mapDropDown.put(flag, stringObjectHashMap);
            }
        }
        this.mapDropDown = mapDropDown;
    }

    //下拉列表
    private void setMapDropDown(Class clazz) throws Exception {
        Field[] fields = clazz.getDeclaredFields();
        if (fields == null) {
            throw new Exception("属性为空");
        }
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            //DateTimeFormat注解
            if (field.isAnnotationPresent(DateTimeFormat.class)) {
                int index = field.getAnnotation(ExcelProperty.class).index(); //位置
                dateList.add(index);
            }
            //判断这个字段上是否有相应的注解信息（ExcelDropdown.class）
            if (field.isAnnotationPresent(ExcelDropdown.class) && field.isAnnotationPresent(ExcelProperty.class)) {
//                int index = field.getAnnotation(ExcelProperty.class).index(); //位置
                String[] values = field.getAnnotation(ExcelDropdown.class).value(); //下拉列表的value内容
                boolean allowOtherValue = field.getAnnotation(ExcelDropdown.class).isAllowOtherValue();  //下拉列表的isAllowOtherValue值

                Map<String, Object> map = new HashMap<>();
                map.put("val", values);
                map.put("isAllow", allowOtherValue);
                mapDropDown.put(i, map);
            }
        }
    }

    //设置对应属性的@ExcelDropdown注解的对应属性的对应值。
    // 如：fieldNames[0] 对应 attrNames[0] 对应 attrValues[0]， 否则出错。
    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);
    }
}

前端接收并下载

发送请求并处理res

//下载模板
const downloadTemp = () => {
   activeGiftsApi.activeGiftsDownloadTemp().then((res) => {
      let blob = new Blob([res.data], { type: 'application/vnd.ms-excel;charset=utf-8' })
      let filename = '活动礼品导入模板'
      let url = window.URL.createObjectURL(blob)
      let aLink = document.createElement('a')
      aLink.style.display = 'none'
      aLink.href = url
      aLink.setAttribute('download', filename)
      document.body.appendChild(aLink)
      aLink.click()
      document.body.removeChild(aLink)
      window.URL.revokeObjectURL(url)
   })
}