java导出动态下拉框excel模板

 1.原始模板

2.导出模板,下拉框为数据库中得到动态数据

public void downloadTemplate(HttpServletResponse response) throws IOException {
        // 所有部门
        List departments = expertManageMapper.selectAllDepartment();
        //所有职位
        List posts = expertManageMapper.selectAllPost();
        //所有级别
        List levels = expertManageMapper.selectAllLevel();
 
        // 创建或读取已有的Excel模板
        InputStream templateFileStream = new ClassPathResource("/templates/excel/expert_template.xlsx").getInputStream();
        Workbook workbook = new XSSFWorkbook(templateFileStream);
        Sheet sheet = workbook.getSheetAt(0);
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        // 工作表的第二行索引
        int firstRowNum = 1;
        // 工作表的最后一行索引
        int lastRowNum = 10000;
        //添加需要校验的单元格，startCol和endCol根据模板中列进行修改
        // 科室校验
        applyDataValidation(validationHelper, sheet, addressList(firstRowNum, lastRowNum, 3, 3), ArrayUtils.toArray(departments));
        // 职位校验
        applyDataValidation(validationHelper, sheet, addressList(firstRowNum, lastRowNum, 4, 4),ArrayUtils.toArray(posts));
        // 等级校验
        applyDataValidation(validationHelper, sheet, addressList(firstRowNum, lastRowNum, 5, 5), ArrayUtils.toArray(levels));
        // 设置响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String encodedFilename = UriUtils.encode("专家信息录入模板.xlsx", StandardCharsets.UTF_8);
        response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + encodedFilename);
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            // 将修改后的Excel内容写入到输出流
            workbook.write(outputStream);
        } finally {
            workbook.close();
        }
 
    }
 
  /**
     * 简化创建和应用数据验证的过程
     */
    private void applyDataValidation(DataValidationHelper validationHelper, Sheet sheet, CellRangeAddressList addressList, String[] validationData) {
        if (validationData.length > 0) {
            DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(validationData);
            DataValidation validation = validationHelper.createValidation(constraint, addressList);
            sheet.addValidationData(validation);
        }
 
    }
 
 
    /**
     * 创建CellRangeAddressList
     */
    private CellRangeAddressList addressList(int startRow, int endRow, int startCol, int endCol) {
        return new CellRangeAddressList(startRow, endRow, startCol, endCol);
    }