若依导出模板时设置动态excel下拉框（表连接的）

一、问题

若依导出只能；使用dictType、combo、comboReadDict、readConverterExp这些来控制字典的导出下拉，如果不是字典了该咋弄，不能一个一个录把，多了少了不愁人，愁人的是要万一一直修改，岂不是每次修改完这导出也需要修改了，直接

二、解决

1、实体类

@Excel(name = "生产经营区域", readConverterExp = "", combo = {})
private Long deptId;

2.1、临时使用

实现类代码，这里直接临时写入impl，看起来方便，如果很多，那就很麻烦了

public void importTemplate(HttpServletResponse response) throws Exception  {ExcelUtil<SysCompany> util = new ExcelUtil<SysCompany>(SysCompany.class);Class<SysCompany> clazz = util.clazz;Field field = clazz.getDeclaredField("deptId");Excel excel = field.getAnnotation(Excel.class);InvocationHandler h = Proxy.getInvocationHandler(excel);Field hField = h.getClass().getDeclaredField("memberValues");hField.setAccessible(true);Map<String, Object> memberValues = (Map<String, Object>) hField.get(h);// 查询数据库List<SysDept> deptList = deptMapper.selectDeptListAll();String[] comboArray = new String[deptList.size()];StringBuilder readConverterExpStr = new StringBuilder();for (int i = 0; i < deptList.size(); i++) {comboArray[i] = deptList.get(i).getDeptName();if (i == 0){readConverterExpStr.append(deptList.get(i).getDeptId()+"="+deptList.get(i).getDeptName());}else {readConverterExpStr.append(","+deptList.get(i).getDeptId()+"="+deptList.get(i).getDeptName());}}memberValues.put("combo", comboArray);memberValues.put("readConverterExp", readConverterExpStr.toString());util.importTemplateExcel(response, "企业信息模板");}

2.2、统一工具类

这里为了后续使用，直接写入工具类了，在ExcelUtil.java下定义了exportDropdownExcel方法，设置导出下拉框

/*** 设置导出下拉框** @param list 关联集合，查询的列和后续对应；select key,text from table* @param name 需要下拉的字段* @param key 需要映射的值* @param text 需要映射的文本* @throws Exception**/public < E> void exportDropdownExcel(List<E> list, String name, String key, String text) throws Exception {Field field = clazz.getDeclaredField(name);// 获取字段上的Excel注解Excel excel = field.getAnnotation(Excel.class);InvocationHandler h = Proxy.getInvocationHandler(excel);Field hField = h.getClass().getDeclaredField("memberValues");hField.setAccessible(true);Map<String, Object> memberValues = (Map<String, Object>) hField.get(h);// 动态获取字典值Map<String, String> dictMap = new HashMap<>();for (E item : list) {Class<?> dictClass = item.getClass();// 获取字典项的值和文本字段Field keyField = dictClass.getDeclaredField(key);Field textField = dictClass.getDeclaredField(text);// 设置可访问keyField.setAccessible(true);textField.setAccessible(true);String keyStr = String.valueOf(keyField.get(item));String valueStr = String.valueOf(textField.get(item));dictMap.put(keyStr, valueStr);}// 设置下拉框选项String[] comboArray = dictMap.values().toArray(new String[0]);StringBuilder readConverterExpStr = new StringBuilder();int i = 0;for (Map.Entry<String, String> entry : dictMap.entrySet()) {if (i == 0) {readConverterExpStr.append(entry.getKey()).append("=").append(entry.getValue());} else {readConverterExpStr.append(",").append(entry.getKey()).append("=").append(entry.getValue());}i++;}// 修改注解属性memberValues.put("combo", comboArray);memberValues.put("readConverterExp", readConverterExpStr.toString());}

3、调用

临时使用的直接就是调用了，这里调用工具类的

public void importTemplate(HttpServletResponse response) throws Exception  {ExcelUtil<SysCompany> util = new ExcelUtil<SysCompany>(SysCompany.class);util.exportDropdownExcel(deptMapper.selectDeptListAll(),"deptId","deptId","deptName");util.exportDropdownExcel( manageMapper.selectManageListAll(),"superAgencyId","manageId","manageName");util.importTemplateExcel(response, "企业信息模板");}

实现图示如下

完事直接使用就OK了，有更好的改进和意见可以评论区交流一下