springboot+easyexcel实现下载excels模板下拉选择

定义下拉注解

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

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public 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. 写入选项数据到隐藏Sheet
                int 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());
        }
    }

    @Override
    public 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");
            // 写入Excel
            EasyExcel.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;
}