EasyExcel实现级联下拉框

EasyExcel版本: 3.3.4
JDK: 17

最近项目有个需求导出excel需要在省和市两列实现级联下拉选择框,特地记录一下

EasyExcel 实现级联下拉框需要创建一个类实现SheetWriteHandler 接口, 下面给出Demo,
这个类无需修改可以直接使用, 但是需要注意由于实现级联下拉需要创建一个隐藏的sheet页,
所以在读取这个excel的时候需要从第二个sheet页开始读取, 跳过第一个sheet页

public class CascadeSheetWriteHandler implements SheetWriteHandler {

	// sheetName
	private final String sheetName;
	// 下拉框从哪一行开始
	private final int firstRow;
	// 下拉框从哪一行结束
	private final int lastRow;
	// 父级下拉框在哪一列 注意: 从0开始
	private final int parentColumnIndex;
	// 子级下拉框在哪一列 注意: 从0开始
	private final int childColumnIndex;
	// 父级与子级的映射关系, key为父级 value为子级
	private final Map<String, List<String>> cascadeMap;

	public CascadeSheetWriteHandler(String sheetName, int provinceColumnIndex, int cityColumnIndex, Map<String, List<String>> provinceCityMap) {
		this.sheetName = sheetName;
		this.childColumnIndex = cityColumnIndex;
		this.parentColumnIndex = provinceColumnIndex;
		this.cascadeMap = provinceCityMap;
		this.firstRow = 1;
		this.lastRow = 10000;
	}

	public CascadeSheetWriteHandler(String sheetName, int provinceColumnIndex, int cityColumnIndex, int firstRow, int lastRow, Map<String, List<String>> provinceCityMap) {

		if (firstRow > lastRow) {
			throw new IllegalStateException("The first row must be less than the last row");
		}

		this.sheetName = sheetName;
		this.childColumnIndex = cityColumnIndex;
		this.parentColumnIndex = provinceColumnIndex;
		this.cascadeMap = provinceCityMap;
		this.firstRow = firstRow;
		this.lastRow = lastRow;
	}


	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		// 创建级联映射的sheet页
		Workbook workbook = writeWorkbookHolder.getWorkbook();
		Sheet hiddenSheet = workbook.createSheet(sheetName);
		// 隐藏辅助 sheet
		workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

		int rowIndex = 0;
		for (Map.Entry<String, List<String>> entry : cascadeMap.entrySet()) {
			Row row = hiddenSheet.createRow(rowIndex);
			// 父级放在 A 列
			row.createCell(0).setCellValue(entry.getKey());

			// 子级从 B 列开始
			for (int i = 0; i < entry.getValue().size(); i++) {
				row.createCell(i + 1).setCellValue(entry.getValue().get(i));
			}
			rowIndex++;
		}
	}

	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		Sheet sheet = writeSheetHolder.getSheet();
		Workbook workbook = writeWorkbookHolder.getWorkbook();
		DataValidationHelper helper = sheet.getDataValidationHelper();

		// 1. 父级下拉框
		String[] parents = cascadeMap.keySet().toArray(new String[0]);
		Name parentRange = workbook.createName();
		String parentName = "Parents" + UUID.randomUUID().toString().replace("-","").substring(0,3);
		parentRange.setNameName(parentName);
		parentRange.setRefersToFormula(sheetName + "!$A$1:$A$" + parents.length);

		DataValidationConstraint parentConstraint = helper.createFormulaListConstraint(parentName);
		// 设置父级下拉框范围
		CellRangeAddressList parentRangeAddressList = new CellRangeAddressList(firstRow, lastRow, parentColumnIndex, parentColumnIndex);
		DataValidation parentValidation = helper.createValidation(parentConstraint, parentRangeAddressList);
		sheet.addValidationData(parentValidation);

		// 2. 子级级联下拉框
		for (int i = 0; i < parents.length; i++) {
			String parent = parents[i];
			int rowIndex = i + 1; // Excel 索引从 1 开始 这里要加1
			int cityStartColumn = 1; // 城市数据从 B 列开始

			Name childRange = workbook.createName();
			childRange.setNameName(parent.replace(" ", "_")); // 确保 Excel 名称合法
			childRange.setRefersToFormula(sheetName + "!$B$" + rowIndex + ":$" + (char) ('A' + cityStartColumn + cascadeMap.get(parent).size() - 1) + "$" + rowIndex);
		}

		// 将父级索引转换成 26位英文字母格式
		String parentColumnLetter = getColumnLetter(parentColumnIndex);
		String childFormula = "INDIRECT($" + parentColumnLetter + "2)";

		DataValidationConstraint cityConstraint = helper.createFormulaListConstraint(childFormula);
		// 设置子级级联下拉框范围
		CellRangeAddressList cityRangeAddressList = new CellRangeAddressList(firstRow, lastRow, childColumnIndex, childColumnIndex);
		DataValidation cityValidation = helper.createValidation(cityConstraint, cityRangeAddressList);
		sheet.addValidationData(cityValidation);
	}

	/**
	 * 将列索引转换为 Excel 的列字母，例如：
	 * 0 -> A, 1 -> B, 2 -> C, ..., 25 -> Z, 26 -> AA, ...
	 */
	private String getColumnLetter(int columnIndex) {
		StringBuilder columnName = new StringBuilder();
		while (columnIndex >= 0) {
			columnName.insert(0, (char) ('A' + (columnIndex % 26)));
			columnIndex = (columnIndex / 26) - 1;
		}
		return columnName.toString();
	}

}

	public static void main(String[] args) {

		Map<String, List<String>> provinceCityMap = new LinkedHashMap<>();
		provinceCityMap.put("湖北省", Arrays.asList("武汉市", "黄石市", "鄂州市"));
		provinceCityMap.put("广东省", Arrays.asList("广州市", "深圳市", "佛山市"));
		provinceCityMap.put("江苏省", Arrays.asList("苏州市", "南京市", "无锡市"));

		EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demo.xlsx", DemoExcel.class)
			.registerWriteHandler(new CascadeSheetWriteHandler("省市级联映射", 2, 3, provinceCityMap))
			.sheet("sheet1")
			.doWrite(new ArrayList<>());

	}

最终效果:

实际上级联下拉就是在生成excel之前创建一个额外的sheet页, 其中A列存放父级选项, 从B列开始存放这个父级的子级选项, 隐藏是为了防止被误操作, 导致级联下拉失败