Easyexcel生成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@Slf4jpublic 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@AllArgsConstructorpublic class SelectedSheetWriteHandler implements SheetWriteHandler {private final Map<Integer, ExcelSelectedResolve> selectedMap;/*** Called before create the sheet*/@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** Called after the sheet is created*/@Overridepublic 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);});}}

创建导出工具类

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

.创建动态下拉框配置类

@Componentpublic class SpringContextUtil implements ApplicationContextAware {private static ApplicationContext applicationContext;@Overridepublic void setApplicationContext(ApplicationContext applicationContext) throws BeansException {SpringContextUtil.applicationContext = applicationContext;}// 获取ApplicationContextpublic static ApplicationContext getApplicationContext() {return applicationContext;}// 通过class获取Beanpublic static <T> T getBean(Class<T> clazz) {return applicationContext.getBean(clazz);}// 通过name以及class获取Beanpublic static <T> T getBean(String name, Class<T> clazz) {return applicationContext.getBean(name, clazz);}}

动态下拉框配置类

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

导出excel到桌面

@Overridepublic 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，如果想导出数据库数据需要从数据库中查询数据listexcelWriter.write(new ArrayList<ExcelProductDTO>(), writeSheet);excelWriter.finish();} catch (UnsupportedEncodingException e) {log.error("导出Excel编码异常", e.getMessage());} catch (IOException e) {log.error("导出Excel文件异常", e.getMessage());}}

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

@Overridepublic 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，如果想导出数据库数据需要从数据库中查询数据listexcelWriter.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'] 这个报错可以参考一下解决方法

@Configurationpublic class MvcConfig implements WebMvcConfigurer {@Overridepublic 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导出下拉框

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