Java 导出Excel增加下拉框选项

excel对于下拉框较多选项的，需要使用隐藏工作簿来解决，使用函数取值来做选项

选项较少（一般少于5个）：

private static DataValidation setFewDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
    DataValidationHelper helper = sheet.getDataValidationHelper();
    //加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
    constraint.setExplicitListValues(textList);
    //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
    //数据有效性对象
    return helper.createValidation(constraint, regions);
}

选项较多

创建隐藏工作簿:

Sheet sheetHidden = wb.createSheet(\"Sheet2\");
wb.setSheetHidden(1, true);

每一个列表占用一列

当然也可以每个列表使用一张工作簿，只用第一列。 这里是使用一个工作簿使用每个列，先26个字母，一般够用了

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\"};

for (int j = 0; j < dataList.size(); j++) {
    if (index == 0) { //第1个下拉选项，直接创建行、列
        row = sheetHidden.createRow(j); //创建数据行
        //      sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
        row.createCell(0).setCellValue(dataList.get(j)); //设置对应单元格的值
    } else { //非第1个下拉选项
        int rowCount = sheetHidden.getLastRowNum();
        if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
            //获取行，创建列
            sheetHidden.getRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值

        } else { //未创建过的行，直接创建行、创建列
            //  sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
            //创建行、创建列
            sheetHidden.createRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值
        }
    }
}

index 代表第几个下拉框，也就是在隐藏工作簿的第几列，dataList表示下拉框的内容

创建公式：

String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$" + dataList.size();

Sheet2第A1到A5000作为下拉列表来源数据

xls和xlsx生成下拉框的选项不一样

private static DataValidation setMoreDataValidation(Workbook wb, Sheet sheet, String strFormula, int startRow, int endRow, int startColumn, int endColumn) {

    DataValidation dataValidation;
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(startRow, endRow, startColumn, endColumn);
    if (wb instanceof XSSFWorkbook) {
        //获取新sheet页内容
        XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        // 数据有效性对象
        DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
        dataValidation = help.createValidation(constraint, regions);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    } else {
        // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
        dataValidation = new HSSFDataValidation(regions, constraint);
        dataValidation.setSuppressDropDownArrow(false);
    }
    dataValidation.setEmptyCellAllowed(true);
    dataValidation.setShowPromptBox(true);
    dataValidation.createErrorBox(\"Error\", \"请选择下拉框中的数据\");
    dataValidation.createPromptBox(\"提示\", \"只能选择下拉框里面的数据\");
    return dataValidation;

}

加入工作簿:

sheet.addValidationData()

完整代码:

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())); //超过255个报错
        } else {
            //String strFormula = \"Sheet2!$A$1:$A$5000\" ; //Sheet2第A1到A5000作为下拉列表来源数据
            String strFormula = \"Sheet2!$\" + arr[index] + \"$1:$\" + arr[index] + \"$\" + dataList.size(); //Sheet2第A1到A5000作为下拉列表来源数据
            sheet.addValidationData(setMoreDataValidation(wb, sheet, strFormula,
                    dataValidationCell.getStartRow(), dataValidationCell.getEndRow(),
                    dataValidationCell.getStartColumn(), dataValidationCell.getEndColumn())); //下拉列表元素很多的情况
            //2、生成sheet2内容
            for (int j = 0; j < dataList.size(); j++) {
                if (index == 0) { //第1个下拉选项，直接创建行、列
                    row = sheetHidden.createRow(j); //创建数据行
                    //      sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
                    row.createCell(0).setCellValue(dataList.get(j)); //设置对应单元格的值
                } else { //非第1个下拉选项
                    int rowCount = sheetHidden.getLastRowNum();
                    if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
                        //获取行，创建列
                        sheetHidden.getRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值
                    } else { //未创建过的行，直接创建行、创建列
                        //  sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
                        //创建行、创建列
                        sheetHidden.createRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值
                    }
                }
            }
            index++;
        }
    }
}

public static class DataValidationCell{
    private int startRow;
    private int endRow;
    private int startColumn;
    private int endColumn;
    private List<String> dataList;
}