Excel for dropdown box with many options, need to use hidden workbook to solve, use function values to do the options

Fewer options (usually less than 5)

private static DataValidation setFewDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) { DataValidationHelper helper = sheet.getDataValidationHelper(); / / load drop-down list DataValidationConstraint constraint = helper. CreateExplicitListConstraint (the text list); constraint.setExplicitListValues(textList); // Set the cell on which data validity is loaded. The four parameters are: CellRangeAddressList Regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol); // Data validity object return helper.createValidation(constraint, regions); }Copy the code

More options

Create a hidden workbook

Sheet sheetHidden = wb.createSheet("Sheet2");
wb.setSheetHidden(1, true);
Copy the code

Each list takes one column

You can also use one workbook per list, using only the first column. In this case, I’m using a workbook and I’m using each column with 26 letters, which is usually enough

String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
Copy the code
for (int j = 0; j < dataList.size(); J ++) {if (index == 0) {row = sheethidden.createrow (j); / / create the data line / / sheetHidden setColumnWidth (j, 4000); Row.createcell (0).setcellValue (dataList. Get (j)); // Set the width of each column row.createcell (0).setcellValue (dataList. / / set the value of the corresponding cell} else {/ / the first dropdown option int rowCount = sheetHidden. GetLastRowNum (); If (j <= rowCount) {if (j <= rowCount) { Create the columns sheetHidden. GetRow (j) createCell (index). SetCellValue (dataList. Get (j)); / / set the value of the corresponding cell} else {/ / not to create a line, line directly create, create columns / / sheetHidden setColumnWidth (j, 4000); Sheethidden.createrow (j).createcell (index).setCellValue(dataList. Get (j)); // Set the corresponding cell value}}}Copy the code

Index represents the number of drop-down boxes, that is, the number of columns in the hidden workbook, and dataList represents the contents of the drop-down box

Create a formula

String strFormula = "Sheet2! $" + arr[index] + "$1:$" + arr[index] + "$" + dataList.size(); //Sheet2 rows A1 through A5000 as drop-down list source dataCopy the code

XLS and XLSX have different options for generating drop-down boxes

private static DataValidation setMoreDataValidation(Workbook wb, Sheet sheet, String strFormula, int startRow, int endRow, int startColumn, int endColumn) { DataValidation dataValidation; // Set the cell on which the data validity is loaded. CellRangeAddressList Regions = new CellRangeAddressList(startRow, endRow, startColumn, endColumn); If (wb instanceof XSSFWorkbook) {/ / acquire new sheet page XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula); // Set the cell on which the data validity is loaded. Start row, end row, Start column, End column DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet); dataValidation = help.createValidation(constraint, regions); dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); } else {// Sets the cell on which the data validity is loaded. Four parameters: the starting line, line, the starting column, end columns DVConstraint constraint. = DVConstraint createFormulaListConstraint (strFormula); dataValidation = new HSSFDataValidation(regions, constraint); dataValidation.setSuppressDropDownArrow(false); } dataValidation.setEmptyCellAllowed(true); dataValidation.setShowPromptBox(true); DataValidation. CreateErrorBox (" Error ", "please select data in a drop-down box"); DataValidation. CreatePromptBox (" prompt ", "can only select the drop-down box data"); return dataValidation; }Copy the code

Join workbook

sheet.addValidationData()
Copy the code

The complete code

private static void setValidationDate(Workbook wb, Sheet sheet, List<DataValidationCell> dataValidationCellList) { if (dataValidationCellList.isEmpty()) { return; } String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}; int index = 0; Row row; Sheet sheetHidden = wb.createSheet("Sheet2"); wb.setSheetHidden(1, true); for (DataValidationCell dataValidationCell : dataValidationCellList) { List<String> dataList = dataValidationCell.getDataList(); if (CollectionUtils.isEmpty(dataList)) { continue; } if (dataList.size() <= 5) { sheet.addValidationData(setFewDataValidation(sheet, dataList.toArray(new String[0]), dataValidationCell.getStartRow(), dataValidationCell.getEndRow(), dataValidationCell.getStartColumn(), dataValidationCell.getEndColumn())); } else {//String strFormula = "Sheet2! $A$1:$A$5000" ; String strFormula = "Sheet2! $" + arr[index] + "$1:$" + arr[index] + "$" + dataList.size(); Sheet. AddValidationData (setMoreDataValidation(WB, sheet, strFormula, dataValidationCell.getStartRow(), dataValidationCell.getEndRow(), dataValidationCell.getStartColumn(), dataValidationCell.getEndColumn())); Sheet2 creates sheet2 content for (int j = 0; j < dataList.size(); J ++) {if (index == 0) {row = sheethidden.createrow (j); / / create the data line / / sheetHidden setColumnWidth (j, 4000); Row.createcell (0).setcellValue (dataList. Get (j)); // Set the width of each column row.createcell (0).setcellValue (dataList. / / set the value of the corresponding cell} else {/ / the first dropdown option int rowCount = sheetHidden. GetLastRowNum (); If (j <= rowCount) {if (j <= rowCount) { Create the columns sheetHidden. GetRow (j) createCell (index). SetCellValue (dataList. Get (j)); / / set the value of the corresponding cell} else {/ / not to create a line, line directly create, create columns / / sheetHidden setColumnWidth (j, 4000); Sheethidden.createrow (j).createcell (index).setCellValue(dataList. Get (j)); // set the corresponding cell value}}} index++; }}}Copy the code
public static class DataValidationCell{
    private int startRow;

    private int endRow;

    private int startColumn;

    private int endColumn;

    private List<String> dataList;

}
Copy the code