- 学习地址：https://d9bp4nr5ye.feishu.cn/wiki/O3obweIbgi2Rk1ksXJncpClTnAf
- B站视频：https://www.bilibili.com/video/BV1H34y1T7Lm
先来看看最终实现效果，如果效果是你想要的，再看看实现逻辑。
EasyExcel本身是支持设置下拉校验的，但有个问题，一旦下拉数据超过50个左右的时候就无法正常展示，当然，现在这个问题得到了解决。
来看整体的项目目录
如果你之前使用过EasyExcel那上面主流程代码你将会很熟悉，下拉选相较于之前的导出只是多注册了一个 Handler。
一、主流程代码
@RestController
@RequestMapping("/one")
public class OneController {// 测试数据构建private static List<String> countryList = Arrays.asList("中国","美国","俄罗斯","德国","日本");private static List<String> cityList = Arrays.asList("深圳","广州","上海","北京","纽约","莫斯科","东京");private static Map<String, List> dropDownData = new HashMap<>(2);private static List<SheetOneVO> cityEntityList = new ArrayList<>(cityList.size());static {dropDownData.put("country", countryList);dropDownData.put("city", cityList);for (String item : cityList) {cityEntityList.add(new SheetOneVO(item));}}@GetMapping("/kk-one")public void one(HttpServletResponse response) throws Exception {// 通用内容设置String fileName = URLEncoder.encode("templateOne.xlsx",CharEncoding.UTF_8);response.setContentType("application/octet-stream");response.setCharacterEncoding(CharEncoding.UTF_8);response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName);// 构建模板数据ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();WriteSheet writeSheet = EasyExcel.writerSheet(0, "one").registerWriteHandler(new XdxCellWriteHandler(dropDownData)).head(TemplateOneVO.class).build();WriteSheet citySheet = EasyExcel.writerSheet(1, "city").head(SheetOneVO.class).needHead(false).build();excelWriter.write(Arrays.asList(new TemplateOneVO("张三","美国", "胡佛")), writeSheet).write(cityEntityList, citySheet).finish();}
}
二、处理器代码
- 这个handler重写了单元格拦截器，在遍历表头的时候，设置下拉选。
- 正常是超过50个下拉才会出问题，这里懒得造那么多数据所以设置了一个LIMIT，超过它就会换成联动sheet方式，从而就解决了个数限制的问题。
public class XdxCellWriteHandler implements CellWriteHandler {/*** 超过 LIMIT 的大小就使用 sheet关联下拉，否则直接设置下拉*/private static final Integer LIMIT = 6;private Map<String, List> dropDownData;public XdxCellWriteHandler(Map<String, List> dropDownData) {this.dropDownData = dropDownData;}/*** 设置下拉框数据* @param writeSheetHolder* @param key 当前列名* @param rowIndex 行号* @param columnIndex 列号*/private void setSelectDataList(WriteSheetHolder writeSheetHolder, String key, int rowIndex, int columnIndex) {if (dropDownData.get(key) == null) {return;}Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();// 设置下拉列表的行： 首行，末行，首列，末列CellRangeAddressList rangeList = new CellRangeAddressList(rowIndex, 50000, columnIndex, columnIndex);// 设置下拉列表的值DataValidationConstraint constraint;if (dropDownData.get(key).size() < LIMIT) {// 直接设置下拉选constraint = helper.createExplicitListConstraint((String[]) dropDownData.get(key).toArray(new String[0]));} else {// 联动到另外一个 sheetconstraint = helper.createFormulaListConstraint(key+ "!$A$1:$A$" + dropDownData.get(key).size());}// 设置约束DataValidation validation = helper.createValidation(constraint, rangeList);// 阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox("提示", "请输入下拉选项中的内容");sheet.addValidationData(validation);}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<WriteCellData<?>> cellDataList, Cell cell, Head head,Integer relativeRowIndex, Boolean isHead) {// 设置隐藏 sheetWriteSheet writeSheet = writeSheetHolder.getWriteSheet();if (writeSheet.getSheetNo() > 0) {Workbook workbook = writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook();workbook.setSheetHidden(writeSheet.getSheetNo(), true);return;} if (!isHead) {//设置value下拉框setSelectDataList(writeSheetHolder, head.getFieldName(), cell.getRowIndex(), cell.getColumnIndex());}}
}
三、两个VO
TemplateOneVO
@Data
@AllArgsConstructor
public class TemplateOneVO {@ExcelProperty("姓名")private String name;@ExcelProperty("国家")private String country;@ExcelProperty("城市")private String city;
}
SheetOneVO
@Data
@AllArgsConstructor
public class SheetOneVO {private String key;
}
