Easyexcel生成excel并通过自定义注解实现下拉框以及动态下拉框(将数据库中的数据显示在excel下拉框中)

首先需要定义excel实体类

@Data
@ColumnWidth(22)
@HeadRowHeight(30)
public class ExcelProductDTO {


    //动态下拉框，可以查询数据库数据显示在下拉框中
    @ExcelSelected(sourceClass = MyExcelSelected.class)
    @ExcelProperty(value = "生产厂家编号")
    private String producerCode;

    @ExcelSelected(source = {"高值","低值"})
    @ExcelProperty(value = "库存价值类型")
    private String stockValType;

    @ExcelSelected(source = {"耗材","普通设备","医疗设备"})
    @ExcelProperty(value = "库存类型")
    private String stockType;

 
    @ExcelSelected(source = {"有","无"})
    @ExcelProperty(value = "是否有明细")
    private Integer hasDetail;

    @ExcelSelected(source = {"药械","资产","设备"})
    @ExcelProperty(value = "业务类型")
    private String ywType;

   
    @ExcelSelected(source = {"虚库","实库"})
    @ExcelProperty(value = "是否虚库")
    private String isVirtual;

    @ExcelSelected(source = {"是","否"})
    @ExcelProperty(value = "是否进口")
    private String isImport;
}

自定义注解

@Documented
@Target({ElementType.FIELD})//用此注解用在属性上。
@Retention(RetentionPolicy.RUNTIME)//注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    int lastRow() default 0x10000;
}

 创建动态下拉框的接口，设置动态下拉框都实现该接口

public interface ExcelDynamicSelect {
    /**
     * 获取动态生成的下拉框可选数据
     * @return 动态生成的下拉框可选数据
     */
    String[] getSource();
}

自定义注解解析类  (反射)

//自定义注解解析
@Data
@Slf4j
public class ExcelSelectedResolve {
    /**
     * 下拉内容
     */
    private String[] source;

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    private int firstRow;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    private int lastRow;

    public String[] resolveSelectedSource(ExcelSelected excelSelected) {
        if (excelSelected == null) {
            return null;
        }

        // 获取固定下拉框的内容
        String[] source = excelSelected.source();
        if (source.length > 0) {
            return source;
        }

        // 获取动态下拉框的内容
        Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
        if (classes.length > 0) {
            try {
                ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
                String[] dynamicSelectSource = excelDynamicSelect.getSource();
                if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
                    return dynamicSelectSource;
                }
            } catch (InstantiationException | IllegalAccessException e) {
                log.error("解析动态下拉框数据异常", e);
            }
        }
        return null;
    }

}

 创建Handler，设置excel中的下拉框

    
@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {
    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    /**
     * Called before create the sheet
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * Called after the sheet is created
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        selectedMap.forEach((k, v) -> {
            // 设置下拉列表的行： 首行，末行，首列，末列
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            // 设置下拉列表的值
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
    }
}

 创建导出工具类

@Slf4j
public class EasyExcelUtil {
    /**
     * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
     * @param head 导出的表头信息和配置
     * @param sheetNo sheet索引
     * @param sheetName sheet名称
     * @param <T> 泛型
     * @return sheet页
     */
    public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
        Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);

        return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                .build();
    }

    /**
     * 解析表头类中的下拉注解
     * @param head 表头类
     * @param <T> 泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();

        // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++){
            Field field = fields[i];
            // 解析注解信息
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
                String[] source = excelSelectedResolve.resolveSelectedSource(selected);
                if (source != null && source.length > 0){
                    excelSelectedResolve.setSource(source);
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property != null && property.index() >= 0){
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }

        return selectedMap;
    }

}

.创建动态下拉框配置类 

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

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

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

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

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

}

 动态下拉框配置类

//动态下拉框中的数据配置类
public class MyExcelSelected implements ExcelDynamicSelect{

    @Override
    public String[] getSource() {
        //查询下拉框中需要的数据
        ProductMapper productMapper = SpringContextUtil.getBean(ProductMapper.class);
        return productMapper.selectList(null).toArray(new String[]{});
        //当多列需要动态下拉框时，只需自定义类实现ExcelDynamicSelect中的方法，并在方法中查询数据即可。

    }
}

导出excel到桌面

@Override
    public void exportProductData(HttpServletResponse response) {

        //获取当前桌面路径  直接导出文件到桌面上
        String path = FileSystemView.getFileSystemView().getHomeDirectory().getPath();
        String fileName = "产品数据表" + System.currentTimeMillis() + ".xlsx";
        String exportFileName = path + "/" + fileName;
        //编码问题
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setDateHeader("Expires", -1);
            //设置响应头部信息，格式为附件，以及文件名
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);

            ExcelWriter excelWriter = EasyExcel.write(exportFileName).build();
            WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(ExcelProductDTO.class, 0, "产品信息表");
            //此处只导出实体类中的数据所以只new 一个空的list，如果想导出数据库数据需要从数据库中查询数据list
            excelWriter.write(new ArrayList<ExcelProductDTO>(), writeSheet);
            excelWriter.finish();
        } catch (UnsupportedEncodingException e) {
            log.error("导出Excel编码异常", e.getMessage());
        } catch (IOException e) {
            log.error("导出Excel文件异常", e.getMessage());
        }
    }

 如果需要在前端显示下载文件可参考以下代码(本人为实现仅参考)

@Override
    public void exportProductData(HttpServletResponse response) {

    
        String fileName = "产品数据表" + System.currentTimeMillis() + ".xlsx";
        
        //编码问题
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setDateHeader("Expires", -1);
            //设置响应头部信息，格式为附件，以及文件名
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);

            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(ExcelProductDTO.class, 0, "产品信息表");
            //此处只导出实体类中的数据所以只new 一个空的list，如果想导出数据库数据需要从数据库中查询数据list
            excelWriter.write(new ArrayList<ExcelProductDTO>(), writeSheet);
            excelWriter.finish();
        } catch (UnsupportedEncodingException e) {
            log.error("导出Excel编码异常", e.getMessage());
        } catch (IOException e) {
            log.error("导出Excel文件异常", e.getMessage());
        }
    }

 前端可参考

//在前端请求返回结果中
            const link = document.createElement("a");
            let blob = new Blob([res.data], { type: "multipary/form-data" });
            link.style.display = "none";
            link.href = URL.createObjectURL(blob);
            link.setAttribute("download", decodeURI(Date.now()+'导出模板.xlsx'));
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);

 如果遇到  No converter for [class java.util.LinkedHashMap] with preset Content-Type 'application/vnd.ms-excel;charset=utf-8'] 这个报错可以参考一下解决方法

@Configuration
public class MvcConfig implements WebMvcConfigurer {

    @Override
    public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
    
        convert.setSupportedMediaTypes(getSupportedMediaTypes());
        converters.add(convert);
    }

    public List<MediaType> getSupportedMediaTypes() {
        //创建fastJson消息转换器
        List<MediaType> supportedMediaTypes = new ArrayList<>();
        supportedMediaTypes.add(MediaType.APPLICATION_JSON);
        supportedMediaTypes.add(MediaType.APPLICATION_JSON_UTF8);
        supportedMediaTypes.add(MediaType.APPLICATION_ATOM_XML);
        supportedMediaTypes.add(MediaType.APPLICATION_FORM_URLENCODED);
        supportedMediaTypes.add(MediaType.APPLICATION_OCTET_STREAM);
        supportedMediaTypes.add(MediaType.APPLICATION_PDF);
        supportedMediaTypes.add(MediaType.APPLICATION_RSS_XML);
        supportedMediaTypes.add(MediaType.APPLICATION_XHTML_XML);
        supportedMediaTypes.add(MediaType.APPLICATION_XML);
        supportedMediaTypes.add(MediaType.IMAGE_GIF);
        supportedMediaTypes.add(MediaType.IMAGE_JPEG);
        supportedMediaTypes.add(MediaType.IMAGE_PNG);
        supportedMediaTypes.add(MediaType.TEXT_EVENT_STREAM);
        supportedMediaTypes.add(MediaType.TEXT_HTML);
        supportedMediaTypes.add(MediaType.TEXT_MARKDOWN);
        supportedMediaTypes.add(MediaType.TEXT_PLAIN);
        supportedMediaTypes.add(MediaType.TEXT_XML);
        supportedMediaTypes.add(MediaType.ALL);
        return supportedMediaTypes;
    }
}

 本人也是参考自己做的：使用EasyExcel导出带下拉框的Excel_√Angelの爱灬的博客-CSDN博客_easyexcel导出下拉框