创建级联下拉框

1.Workbook wb= new XSSFWorkbook();

2.获取下拉数据所在的sheet

Sheet pbTypeSheet = wb.getSheet("国家");

若是没有excel模板，需要先将数据写入下拉数据所在的sheet

// 定义国家和城市的关系

Map<String, String[]> countryCityMap = new HashMap<>();

countryCityMap.put("USA", new String[]{"New York", "Los Angeles", "Chicago"});

countryCityMap.put("Canada", new String[]{"Toronto", "Vancouver", "Montreal"});

countryCityMap.put("China", new String[]{"Beijing", "Shanghai", "Guangzhou"});

Sheet hiddenSheet = workbook.createSheet("Hidden");

workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

// 在隐藏工作表中创建国家和城市的数据

Row row = hiddenSheet.createRow(rowIndex);

Cell countryCellHidden = row.createCell(rowIndex++);

countryCellHidden.setCellValue(country);

for (int i = 0; i < cities.length; i++) {

Cell cityCellHidden = row.createCell(i + 1);

cityCellHidden.setCellValue(cities[i]);

}

3.获取要生成下拉框的sheet

Sheet mainSheet = wb.getSheet("位置信息");

4.生成父级下拉框

poi的下拉框需要指明下拉框名和下拉框选项所在位置

Name namedCell = wb.createName();
namedCell.setNameName("国家");
namedCell.setRefersToFormula("国家!$A$5:$A$10");

5.生成子级下拉框（同父级一样）

6.设置数据验证规则

设置约束条件 

DataValidationConstraint  constraint =helper.createFormulaListConstraint("国家");

也可直接设置数据

String[] countries = countryCityMap.keySet().toArray(new String[0]);

DataValidationConstraint constraint = helper.createExplicitListConstraint(countries);

设置下拉框内容所在的单元格范围

CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

生成验证规则

DataValidation validation = helper.createValidation(constraint, addressList);

放入sheet中

sheet.addValidationData(validation);

7. 创建国家和城市之间的联动

DataValidationHelper dvHelper = mainSheet.getDataValidationHelper();

DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT($A$2)");

CellRangeAddressList addressList = new CellRangeAddressList(1, 1, 1, 1);

DataValidation validation = dvHelper.createValidation(dvConstraint, addressList); mainSheet.addValidationData(validation);

完整代码：

package jp.trial.md.main;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

public class ExcelCreate {

protected static final String[] egChar = new String[] { "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" };

    public static void main(String[] args) {
        String filePath = "countries.xlsx";

// 定义国家和城市的关系
Map<String, String[]> countryCityMap = new HashMap<>();
countryCityMap.put("USA", new String[]{"New York", "Los Angeles", "Chicago"});
countryCityMap.put("Canada", new String[]{"Toronto", "Vancouver", "Montreal"});
countryCityMap.put("China", new String[]{"Beijing", "Shanghai", "Guangzhou"});
        // 创建工作簿
        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建主工作表
            Sheet mainSheet = workbook.createSheet("Main");

            // 创建国家列
            Row headerRow = mainSheet.createRow(0);
            Cell countryHeaderCell = headerRow.createCell(0);
            countryHeaderCell.setCellValue("Country");

            Row countryRow = mainSheet.createRow(1);
            Cell countryCell = countryRow.createCell(0);
            countryCell.setCellValue("Select Country");

            // 获取所有国家
            String[] countries = countryCityMap.keySet().toArray(new String[0]);
            createDropdownList(mainSheet, countries, 1, 1, 0, 0);

            // 创建城市列
            Cell cityHeaderCell = headerRow.createCell(1);
            cityHeaderCell.setCellValue("City");

            Row cityRow = mainSheet.createRow(1);
            Cell cityCell = cityRow.createCell(1);
            cityCell.setCellValue("Select City");

            // 创建隐藏工作表，用于存储国家和城市关系
            Sheet hiddenSheet = workbook.createSheet("Hidden");
            workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

            int rowIndex = 0;
            for (Map.Entry<String, String[]> entry : countryCityMap.entrySet()) {
                String country = entry.getKey();
                String[] cities = entry.getValue();

                // 在隐藏工作表中创建国家和城市的数据
                Row row = hiddenSheet.createRow(rowIndex);
                Cell countryCellHidden = row.createCell(rowIndex++);
                countryCellHidden.setCellValue(country);
                for (int i = 0; i < cities.length; i++) {
                    Cell cityCellHidden = row.createCell(i + 1);
                    cityCellHidden.setCellValue(cities[i]);
                }
              
                // 创建命名范围
                Name namedRange = workbook.createName();
                namedRange.setNameName(country);
                String reference = "Hidden!" + getExcelColumnName(rowIndex) + (1) + ":" + getExcelColumnName(rowIndex) + (cities.length);
                System.out.println(reference);
                namedRange.setRefersToFormula(reference);
            }

            // 创建国家和城市之间的联动
            DataValidationHelper dvHelper = mainSheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT($A$2)");
            CellRangeAddressList addressList = new CellRangeAddressList(1, 1, 1, 1);
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            mainSheet.addValidationData(validation);

            // 保存Excel文件
            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                workbook.write(fos);
            }
            System.out.println("Excel 文件已生成: " + filePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void createDropdownList(Sheet sheet, String[] list, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidation validation = helper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);
    }

    private static String getExcelColumnName(int hCol) {
        int egCharCnt=egChar.length;
        if(hCol<egCharCnt){
           return egChar[hCol];
        }else{
           int firstNum=hCol/egCharCnt-1;
           int secondNum=hCol%egCharCnt;
           return egChar[firstNum]+egChar[secondNum];
        }
}