EasyExcel级联下拉

代码

package com.xc.excel.select;import com.alibaba.excel.EasyExcel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class EasyExcelCascadingDropdown {public static void main(String[] args) throws IOException {String fileName = "test.xlsx";// 定义国家和城市的级联关系Map<String, List<String>> countryCityMap = new HashMap<>();countryCityMap.put("中国", Arrays.asList("北京", "上海", "广州"));countryCityMap.put("美国", Arrays.asList("纽约", "洛杉矶", "芝加哥"));// 生成Excel文件Workbook workbook = new XSSFWorkbook();Sheet mainSheet = workbook.createSheet("MainSheet");// 创建标题行Row headerRow = mainSheet.createRow(0);headerRow.createCell(0).setCellValue("国家");headerRow.createCell(1).setCellValue("城市");// 创建国家下拉列表数据验证DataValidationHelper helper = mainSheet.getDataValidationHelper();DataValidationConstraint countryConstraint = helper.createExplicitListConstraint(countryCityMap.keySet().toArray(new String[0]));CellRangeAddressList countryRange = new CellRangeAddressList(1, 100, 0, 0);  // 第一列（国家）DataValidation countryValidation = helper.createValidation(countryConstraint, countryRange);mainSheet.addValidationData(countryValidation);// 设置城市的级联下拉for (int i = 1; i < 101; i++) { // B列的下拉列表依赖A列的值String formula = "INDIRECT(A" + (i + 1) + ")";DataValidationConstraint cityConstraint = helper.createFormulaListConstraint(formula);CellRangeAddressList cityRange = new CellRangeAddressList(i, i, 1, 1);  // 第二列（城市）DataValidation cityValidation = helper.createValidation(cityConstraint, cityRange);mainSheet.addValidationData(cityValidation);}// 创建DataSheet存放国家和城市映射数据Sheet dataSheet = workbook.createSheet("DataSheet");int rowIndex = 0;for (Map.Entry<String, List<String>> entry : countryCityMap.entrySet()) {String country = entry.getKey();List<String> cities = entry.getValue();// 写入国家和对应城市到DataSheetRow countryRow = dataSheet.createRow(rowIndex++);countryRow.createCell(0).setCellValue(country);for (int i = 0; i < cities.size(); i++) {Row cityRow = dataSheet.createRow(rowIndex++);cityRow.createCell(0).setCellValue(cities.get(i));}// 为每个国家创建命名区域Name name = workbook.createName();name.setNameName(country);name.setRefersToFormula("DataSheet!$A$" + (rowIndex - cities.size()) + ":$A$" + (rowIndex - 1));}// 保存Excel文件try (FileOutputStream fileOut = new FileOutputStream(fileName)) {workbook.write(fileOut);}workbook.close();System.out.println("Excel文件生成成功: " + fileName);}
}

效果