https://blog.csdn.net/csdnalexwang0610/article/details/114792583
setXSSFValidation("hiddenSelect", sheet, comSubExp, 1, 1000, column, column);
public void setXSSFValidation(String hiddenSelect, Sheet sheet, String[] comSubExp, int firstRow, int endRow, int firstCol, int endCol) { Sheet hiddenSheet = wb.createSheet(hiddenSelect); // 把下拉框列表数据放进隐藏sheet Cell cell = null; for (int i = 0; i < comSubExp.length; i++) { Row ro = hiddenSheet.createRow(i); cell = ro.createCell(0); cell.setCellValue(comSubExp[i]); } Name nameCell = wb.createName(); nameCell.setNameName(hiddenSheet.getSheetName()); nameCell.setRefersToFormula(hiddenSheet.getSheetName() + "!$A$1:$A$" + comSubExp.length); wb.setSheetHidden(wb.getSheetIndex(hiddenSheet), true); CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); DataValidationConstraint constraint = null; DataValidation validation = null; DataValidationHelper helper = sheet.getDataValidationHelper(); // 为了适配xls和xlsx不同版本的Excel(即2003和2007版本的) if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) { constraint = helper.createFormulaListConstraint(hiddenSheet.getSheetName()); validation = helper.createValidation(constraint, addressList); } else { constraint = DVConstraint.createFormulaListConstraint(hiddenSheet.getSheetName()); validation = new HSSFDataValidation(addressList, constraint); } if (validation instanceof XSSFDataValidation) { validation.setSuppressDropDownArrow(true); validation.setShowErrorBox(true); } else { validation.setSuppressDropDownArrow(false); } sheet.addValidationData(validation); }