英文:
ClosedXML: Dependent dropdown remove empty options
问题
You can modify your code to dynamically update the options in the F1 dropdown menu based on the selection in E1. Here's an updated version of your code in Chinese:
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:D3").AddToNamed("Children");
var parentCell = ws.Cell("E1");
parentCell.CreateDataValidation().List(ws.Range("A1:A3"), true);
var formula = "=INDEX(Children,MATCH(E1,Parents,0),0)";
var childCell = ws.Cell("F1");
childCell.CreateDataValidation().List(formula, true);
By using the formula "=INDEX(Children,MATCH(E1,Parents,0),0)" for the F1 dropdown, it will dynamically update the options in F1 based on the selection in E1, removing empty options.
英文:
I have this table in excel :
| A | B | C | D |
|---|---|---|---|
| 1 | 4 | 5 | |
| 2 | 6 | ||
| 3 | 7 | 8 | 9 |
Where 4 and 5 are the children of 1, 6 - of 2, and 7, 8 and 9 - of 3.
I want a dependent dropdown list where in cell E1 i have a dropdown with the options 1,2 and 3, and in cell F1 I will have the options 4 and 5 if 1 is chosen in E1 and so on.
my code looks like this:
ws.Cell("A1").InsertData(new []{ 1,2,3});
ws.Cell("B1").InsertData(new []{ 4,5}, true); // this transposes the data, making it a row instead of a column
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:D3").AddToNamed("Children");
var parentCell = ws.Cell("E1");
parentCell.CreateDataValidation().List(ws.Range("A1:A3"), true);
var childCell = ws.Cell("F1");
childCell.CreateDataValidation().List("=INDEX(Children,MATCH(E1,Parents,0),0)", true);
This works fine but the problem is that when I select 1 or 2 in E1, there are empty options in the F1 dropdown menu. How do I remove these?
答案1
得分: 0
我在Excel中为每个子列表分别命名了一个范围，并在F1单元格中使用了INDIRECT来引用E1中的值。我在范围的名称前添加了下划线，因为范围名称不能以数字开头，并且在比较E1的值与范围名称时添加了下划线。代码现在如下所示：
ws.Cell("A1").InsertData(new []{ 1,2,3});
ws.Cell("B1").InsertData(new []{ 4,5}, true); // this transposes the data, making it a row instead of a column
ws.Cell("B2").InsertData(new []{ 6}, true);
ws.Cell("B3").InsertData(new []{ 7,8,9}, true);
ws.Range("B1:C1").AddToNamed("_1");
ws.Range("B2:B2").AddToNamed("_2");
ws.Range("B3:D3").AddToNamed("_3");
var parentCell = ws.Cell("E1");
parentCell.CreateDataValidation().List(ws.Range("A1:A3"), true);
var childCell = ws.Cell("F1");
childCell.CreateDataValidation().List("=INDIRECT(CONCATENATE(\"_\", $E$1))", true);
英文:
I named each of the child lists as a separate range in excel and in the F1 cell i used INDIRECT to reference the value in E1. i added _ to the names of the ranges because a range name cannot start with a number, and added an _ when comparing E1's value to the range names. Code now looks like this
ws.Cell("A1").InsertData(new []{ 1,2,3});
ws.Cell("B1").InsertData(new []{ 4,5}, true); // this transposes the data, making it a row instead of a column
ws.Cell("B2").InsertData(new []{ 6}, true);
ws.Cell("B3").InsertData(new []{ 7,8,9}, true);
ws.Range("B1:C1").AddToNamed("_1");
ws.Range("B2:B2").AddToNamed("_2");
ws.Range("B3:D3").AddToNamed("_3");
var parentCell = ws.Cell("E1");
parentCell.CreateDataValidation().List(ws.Range("A1:A3"), true);
var childCell = ws.Cell("F1");
childCell.CreateDataValidation().List("=INDIRECT(CONCATENATE(\"_\", $E$1))", true);
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论