使用POI生成Excel下拉框

最新推荐文章于 2024-05-23 11:23:35 发布

梅比斯-维维亚米利欧

最新推荐文章于 2024-05-23 11:23:35 发布

阅读量4.5k

点赞数 8

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/q1054733797/article/details/126051548

版权

java 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

方式一：下拉框值有限

XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(true);
        XSSFSheet sheet = workbook.createSheet("sheet1");
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"选项一", "选项二", "选项三",});
        //设置生效的起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(0,100,0,0);
        DataValidation validation = helper.createValidation(constraint,addressList);
        //适配xls和xlsx
        if(validation instanceof HSSFDataValidation){
            validation.setSuppressDropDownArrow(false);
        }else{
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        }
        sheet.addValidationData(validation);
        workbook.write(new FileOutputStream("E:/test.xlsx"));

方式2：下拉框值无限

HSSFWorkbook workbook = (HSSFWorkbook)WorkbookFactory.create(false);
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //创建隐藏sheet。保存下拉框的数据
        HSSFSheet hiddenSheet = workbook.createSheet("hiddenSheet");
        List<String> list = Arrays.asList("选项一", "选项二", "选项三");
        for (int i = 0; i < list.size(); i++) {
            HSSFRow row = hiddenSheet.createRow(i);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(list.get(i));
        }
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置下拉框数据引用
        DataValidationConstraint constraint = helper.createFormulaListConstraint("hiddenSheet!$A$1:$A$" + list.size());
        //设置生效的起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(0,100,0,0);
        DataValidation validation = helper.createValidation(constraint,addressList);

        if(validation instanceof HSSFDataValidation){
            validation.setSuppressDropDownArrow(false);
        }else{
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        }
        sheet.addValidationData(validation);
        workbook.write(new FileOutputStream("E:/test.xls"));