ClosedXML：移除依赖下拉列表中的空选项

问题原因 #

你当前使用的公式=INDEX(Children,MATCH(E1,Parents,0),0)会返回对应父项所在的整行数据，包括该行的空单元格，因此下拉菜单中会出现空选项。

解决方案 #

方法1：通过动态范围截取非空子项

修改F1单元格的数据验证公式，利用INDEX结合COUNTA定位到该行的非空单元格范围，避免包含空值。

对应的C#代码修改如下：

var childCell = ws.Cell("F1");
// 公式解释：先找到对应父项的行，再用COUNTA统计该行非空单元格数量，最终只取非空的子项范围
childCell.CreateDataValidation().List("=INDEX(Children,MATCH(E1,Parents,0),1):INDEX(Children,MATCH(E1,Parents,0),COUNTA(INDEX(Children,MATCH(E1,Parents,0),0)))", true);

方法2：使用Excel动态数组函数过滤空值（适用于Excel 365/2021及以上版本）

如果你的Excel版本支持动态数组函数，可以用FILTER直接过滤掉该行的空单元格：

对应的C#代码：

var childCell = ws.Cell("F1");
// 公式解释：先获取对应父项的行数据，再过滤掉空值
childCell.CreateDataValidation().List("=FILTER(INDEX(Children,MATCH(E1,Parents,0),0),INDEX(Children,MATCH(E1,Parents,0),0)<>\"\")", true);

方法3：为每个父项单独定义子项命名范围

这种方法更直观，提前为每个父项的子项创建独立的命名范围，避免整行范围包含空值：

// 插入数据部分保持不变
ws.Cell("A1").InsertData(new []{ 1,2,3});
ws.Cell("B1").InsertData(new []{ 4,5}, true);
ws.Cell("B2").InsertData(new []{ 6}, true);
ws.Cell("B3").InsertData(new []{ 7,8,9}, true);

// 定义父项命名范围
ws.Range("A1:A3").AddToNamed("Parents");

// 为每个父项的子项单独定义命名范围
ws.Range("B1:C1").AddToNamed("Child_1");
ws.Range("B2:B2").AddToNamed("Child_2");
ws.Range("B3:D3").AddToNamed("Child_3");

// 父项下拉列表保持不变
var parentCell = ws.Cell("E1");
parentCell.CreateDataValidation().List(ws.Range("A1:A3"), true);

// 子项下拉列表使用INDIRECT调用对应命名范围
var childCell = ws.Cell("F1");
childCell.CreateDataValidation().List("=INDIRECT(\"Child_\"&E1)", true);

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