下载Excel导出模板时设置下拉框
一个需要导出的数据
@DropDownSetField#source注入字符串，作为下拉框数据
@DropDownSetField#sourceClass 注入一个类，获取数据库等持久化数据，作为下拉框数据
@Data public class QuestionBankImportVO { @ExcelProperty("题型") @DropDownSetField(source = {"单选", "多选", "判断"}) private String questionType; @ExcelProperty("试题内容") private String subject; @ExcelProperty("选项A") private String optionA; @ExcelProperty("选项B") private String optionB; @ExcelProperty("选项C") private String optionC; @ExcelProperty("选项D") private String optionD; @ExcelProperty("答案") @DropDownSetField(source = {"对", "错", "A", "B", "C", "D", "AB", "AC", "AD", "BC", "BD", "CD", "ABC", "ABD", "BCD", "ABCD"}) private String correctOptionId; @ExcelProperty("知识点") @DropDownSetField(sourceClass = KnowlidgePointBO.class) private String knowledgeId; @ExcelProperty("解析") private String analysis; @ExcelProperty("试题类目") @DropDownSetField(sourceClass = QuestionCategoryBO.class) private String questionCategory; }
1. 注解（用于设置下拉框数据）
@Documented @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface DropDownSetField { /** * 固定下拉内容 */ String[] source() default {}; /** * 动态下拉内容 */ Class[] sourceClass() default {}; }
2. 一个获取数据的类（用于填充@DropDownSetField的source属性 ）
public interface DropDownSetInterface { /** * 动态获取下拉框数据源配置 */ String[] getSource(); }
3.实现DropDownSetInterface 接口，动态注入下拉框的数据（sourceClass注入了对应的实现类，通过getSource()注入动态获取的数据）
public class KnowlidgePointBO implements DropDownSetInterface { @Autowired private KnowledgePointsService knowledgePointsService = (KnowledgePointsService) SpringBeanUtil.getBeanByClass(KnowledgePointsService.class); @Override public String[] getSource() { HashSet<String> knowledgePointSet = new HashSet<>(); List<KnowledgePoints> knowledgePoints1k = knowledgePointsService.selectList(new QueryWrapper<KnowledgePoints>() .eq(TENANT_ID, JwtUtil.getCurrentUser().getTenantId())); if (CollUtil.isNotEmpty(knowledgePoints1k)) { knowledgePoints1k.forEach(knowledgePoints -> knowledgePointSet.add(knowledgePoints.getName())); } return knowledgePointSet.toArray(new String[knowledgePointSet.size()]); } }
4.加载DropDownSetField 注入的下拉框数据
public class ResolveDropAnnotationUtil { public static String[] resolve(DropDownSetField dropDownSetField){ if(!Optional.ofNullable(dropDownSetField).isPresent()){ return null; } // 获取固定下拉信息 String[] source = dropDownSetField.source(); if(null != source && source.length > 0){ return source; } // 获取动态的下拉数据 Class<? extends DropDownSetInterface>[] classes = dropDownSetField.sourceClass(); if(null != classes && classes.length > 0){ try { DropDownSetInterface dropDownSetInterface = Arrays.stream(classes).findFirst().get().newInstance(); String[] dynamicSource = dropDownSetInterface.getSource(); if(null != dynamicSource && dynamicSource.length > 0){ return dynamicSource; } } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } } return null; } }
5.将加载的数据进行封装（导出数据和下拉框数据）
public class ProductCellWriteHandler implements SheetWriteHandler { private Map<Integer,String[]> map = null; public ProductCellWriteHandler(Map<Integer,String[]> map){ this.map = map; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { // 这里可以对cell进行任何操作 Sheet sheet = writeSheetHolder.getSheet(); DataValidationHelper helper = sheet.getDataValidationHelper(); // k 为存在下拉数据集的单元格下表 v为下拉数据集 map.forEach((k, v) -> { // 下拉列表约束数据 DataValidationConstraint constraint = helper.createExplicitListConstraint(v); // 设置下拉单元格的首行 末行 首列 末列 CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k); // 设置约束 DataValidation validation = helper.createValidation(constraint, rangeList); // 阻止输入非下拉选项的值 validation.setErrorStyle(DataValidation.ErrorStyle.STOP); validation.setShowErrorBox(true); validation.setSuppressDropDownArrow(true); validation.createErrorBox("提示","此值与单元格定义格式不一致"); // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓"); sheet.addValidationData(validation); }); } }
6.获取导出数据的utils
/** * Spring上下文工具类 */ @Component public class SpringBeanUtil implements ApplicationContextAware { public static ApplicationContext applicationContext; @Override public void setApplicationContext(ApplicationContext context) throws BeansException { applicationContext = context; } /** * 这里使用的是根据class类型来获取bean 当然你可以根据名称或者其他之类的方法 主要是有applicationContext你想怎么弄都可以 */ public static Object getBeanByClass(Class clazz) { return applicationContext.getBean(clazz); } }
@Slf4j public class DownloadTemplateUtil { public static Map<Integer, String[]> getIntegerMap(Class clazz) { // 获取改类声明的所有字段 Field[] fields = clazz.getDeclaredFields(); // 响应字段对应的下拉集合 Map<Integer, String[]> map = new HashMap<>(); Field field = null; // 循环判断哪些字段有下拉数据集，并获取 for(int i =0;i<fields.length;i++){ field = fields[i]; // 解析注解信息 DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class); if(null != dropDownSetField){ String[] sources = ResolveDropAnnotationUtil.resolve(dropDownSetField); if(null != sources && sources.length > 0){ map.put(i,sources); } } } return map; } private DownloadTemplateUtil() { } }
7.导出测试
public RestResult<Void> downLoadTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException { String fileName = "试题导入模板" + DateUtil.format(new Date(), DatePattern.NORM_DATE_PATTERN) + ".xlsx"; ExcelFileResponseHeaderConfig.setResponseHeader(request, response, fileName); //下拉框转换 Map<Integer, String[]> map = DownloadTemplateUtil.getIntegerMap(QuestionBankImportVO.class); ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream(), QuestionBankImportVO.class) .registerWriteHandler(new ProductCellWriteHandler(map)).build(); WriteSheet sheet = EasyExcelFactory.writerSheet(0, "试题导入模板").build(); excelWriter.write(new ArrayList<>(), sheet); excelWriter.finish(); return RestResult.success(); }
