如何使用Apache POI为Excel的XSSFSheet实现两列关联级联下拉列表

需求说明：国家列选择对应选项后，相邻州列自动加载对应下拉选项，关联关系存储在Map<String, String[]>结构中

实现逻辑 #

Excel的级联下拉依赖「定义名称」和INDIRECT函数实现，我们先把内存中的关联关系写入隐藏辅助sheet并定义名称，再分别给两列配置下拉规则即可。

完整实现代码 #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.Map;
import java.util.Set;

public void addCascadingDropdown(XSSFSheet sheet, Map<String, String[]> countryStateMap) {
    XSSFWorkbook workbook = sheet.getWorkbook();
    // 1. 创建隐藏辅助sheet存储关联数据
    XSSFSheet helperSheet = workbook.createSheet("级联辅助数据");
    workbook.setSheetHidden(workbook.getSheetIndex(helperSheet), true);

    int rowIndex = 0;
    Set<String> countryList = countryStateMap.keySet();
    Row countryHeadRow = helperSheet.createRow(rowIndex++);
    int colIndex = 0;

    for (String country : countryList) {
        // 写入国家名到辅助sheet首行
        countryHeadRow.createCell(colIndex).setCellValue(country);
        // 写入对应州列表
        String[] stateArr = countryStateMap.get(country);
        for (int i = 0; i < stateArr.length; i++) {
            Row stateRow = helperSheet.getRow(rowIndex + i) == null ? helperSheet.createRow(rowIndex + i) : helperSheet.getRow(rowIndex + i);
            stateRow.createCell(colIndex).setCellValue(stateArr[i]);
        }
        // 给当前国家对应的州区域定义名称，替换特殊字符适配Excel命名规则
        String safeCountryName = country.replaceAll("[^a-zA-Z0-9\u4e00-\u9fa5]", "_");
        Name stateRangeName = workbook.createName();
        stateRangeName.setNameName(safeCountryName);
        String stateRef = "级联辅助数据!$" + CellReference.convertNumToColString(colIndex) 
                        + "$" + (rowIndex + 1) + ":$" 
                        + CellReference.convertNumToColString(colIndex) 
                        + "$" + (rowIndex + stateArr.length);
        stateRangeName.setRefersToFormula(stateRef);
        colIndex++;
    }

    // 定义国家下拉的全局名称
    Name allCountryName = workbook.createName();
    allCountryName.setNameName("所有国家");
    String countryRef = "级联辅助数据!$A$1:$" + CellReference.convertNumToColString(countryList.size()-1) + "$1";
    allCountryName.setRefersToFormula(countryRef);

    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    // 2. 配置国家列（默认第0列即A列，生效范围为第2行到1000行，可按需调整）下拉
    CellRangeAddressList countryRange = new CellRangeAddressList(1, 999, 0, 0);
    DataValidationConstraint countryConstraint = dvHelper.createFormulaListConstraint("所有国家");
    DataValidation countryValidation = dvHelper.createValidation(countryConstraint, countryRange);
    countryValidation.setSuppressDropDownArrow(true);
    countryValidation.setShowErrorBox(true);
    sheet.addValidationData(countryValidation);

    // 3. 配置州列（默认第1列即B列）级联下拉
    CellRangeAddressList stateRange = new CellRangeAddressList(1, 999, 1, 1);
    // *公式里的A2必须和生效范围的起始行保持一致，若你的表头行数不同，需要对应调整单元格编号*
    DataValidationConstraint stateConstraint = dvHelper.createFormulaListConstraint("INDIRECT(SUBSTITUTE(A2,\" \",\"_\"))");
    DataValidation stateValidation = dvHelper.createValidation(stateConstraint, stateRange);
    stateValidation.setSuppressDropDownArrow(true);
    stateValidation.setShowErrorBox(true);
    sheet.addValidationData(stateValidation);
}

注意事项 #

• 你可以根据实际业务调整CellRangeAddressList的行范围参数，示例中1,999代表从第2行（Excel行号从1开始）到第1000行生效
• 特殊字符替换逻辑要保持一致：如果国家名包含空格、特殊符号，代码中定义名称时的替换规则和INDIRECT里的替换规则必须完全匹配，否则会出现下拉失效的问题
• 如果不需要保留辅助数据，可在生成Excel前调用workbook.removeSheetAt(workbook.getSheetIndex(helperSheet))删除辅助sheet，已配置的下拉规则不受影响

内容的提问来源于stack exchange，提问作者Ashish