使用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和xlsxif(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"));