定義下拉注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDropDown {/*** 固定下拉選項*/String[] source() default {};/*** 動態數據源key（從上下文中獲取）*/String sourceMethod() default "";/*** 下拉框起始行（默認從第2行開始）*/int firstRow() default 1;/*** 下拉框結束行（默認到10000行）*/int lastRow() default 10000;
}

實現CellWriteHandler 接口

@Slf4j
public class DynamicDropDownHandler implements CellWriteHandler {private final ApplicationContext applicationContext;public DynamicDropDownHandler(ApplicationContext applicationContext) {this.applicationContext = applicationContext;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {Class clazz = writeSheetHolder.getClazz();try {String fieldName = head.getFieldName();Field field = clazz.getDeclaredField(fieldName);ExcelDropDown dropDown = field.getAnnotation(ExcelDropDown.class);if (dropDown != null) {String[] source = dropDown.source();if (source.length == 0) {//判斷是否有從庫中查詢數據的方法String method = dropDown.sourceMethod();if (null != method && !"".equals(method)) {source = applicationContext.getBean(method, String[].class);}if (source.length == 0) {return;}}Sheet sheet = writeSheetHolder.getSheet();Workbook workbook = sheet.getWorkbook();// 1. 創建隱藏Sheet存儲下拉選項String optionSheetName = fieldName+"_options";Sheet optionsSheet = workbook.getSheet(optionSheetName);if (optionsSheet == null) {optionsSheet = workbook.createSheet(optionSheetName);workbook.setSheetHidden(workbook.getSheetIndex(optionsSheet), true); // 隱藏Sheet}// 2. 寫入選項數據到隱藏Sheetint optionCol = 0; // 使用第一列存儲選項for (int i = 0; i < source.length; i++) {Row row = optionsSheet.getRow(i);if (row == null) {row = optionsSheet.createRow(i);}row.createCell(optionCol).setCellValue(source[i]);}// 3. 創建名稱引用String rangeName = "DROP_DOWN_" + fieldName.toUpperCase();Name namedRange = workbook.getName(rangeName);if (namedRange == null) {namedRange = workbook.createName();namedRange.setNameName(rangeName);}namedRange.setRefersToFormula(String.format(optionSheetName+"!$A$1:$A$%d", source.length));// 4. 設置數據驗證（使用公式引用）DataValidationHelper helper = sheet.getDataValidationHelper();DataValidationConstraint constraint =helper.createFormulaListConstraint(rangeName);// 設置下拉范圍：從第二行開始到最大行，當前列int columnIndex = cell.getColumnIndex();CellRangeAddressList addressList = new CellRangeAddressList(1, // 從第二行開始2, // Excel最大行號columnIndex,columnIndex);DataValidation validation = helper.createValidation(constraint, addressList);validation.setSuppressDropDownArrow(true);validation.setShowErrorBox(true);sheet.addValidationData(validation);}} catch (Exception e) {log.error(e.getMessage());}}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}
}

使用下載模板接口

    public void downloadTemplate(HttpServletResponse response) {try {// 設置響應頭response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode("模板", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 寫入ExcelEasyExcel.write(response.getOutputStream(), ImportVo.class).autoCloseStream(true).registerWriteHandler(new DynamicDropDownHandler(applicationContext)).sheet("Sheet").doWrite(Collections.singletonList(Collections.emptyList())); } catch (Exception e) {throw new RuntimeException(e);}}

實體類

public class ImportVo {@ExcelProperty(value = "動態數據下拉")@ExcelDropDown(sourceMethod = "getNameList")private String name;@ExcelProperty(value = "性別")@ExcelDropDown(source = {"男","女"})private String sex;
}