java导出动态下拉框excel模板

工地精神

已于 2024-02-20 14:17:53 修改

阅读量1k

点赞数 12

文章标签： java windows 开发语言 excel

于 2024-02-20 14:09:27 首次发布

本文链接：https://blog.csdn.net/txyllyyj/article/details/136188886

版权

文章描述了一个基于JavaServlet的方法，用于下载包含动态数据库数据的Excel模板，对科室、职位和等级字段进行数据验证，确保用户输入的有效性。

摘要由CSDN通过智能技术生成

1.原始模板

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

public void downloadTemplate(HttpServletResponse response) throws IOException {
        // 所有部门
        List<String, String> departments = expertManageMapper.selectAllDepartment();
        //所有职位
        List<String, String> posts = expertManageMapper.selectAllPost();
        //所有级别
        List<String, String> 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);
    }