使用POI生成Excel下拉框

 方式一：下拉框值有限

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 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"));