使用ClosedXML创建下拉列表 [英] Create dropdown list using ClosedXML

问题描述

我已经在项目中实现了Excel模板下载.我使用了ClosedXML.

I have implemented Excel template download in my project. I used ClosedXML.

我有两个Excel工作表

I have two excel sheets

Sheet1: 在此工作表中，我有一列名为"类型"，可以在其中创建下拉列表，这是第三列.

Sheet1: In this sheet, I have a column called "Type" in which I can create Dropdown List and it is the third column.

第2张: 在此工作表中，我有一列名为"类型"，其范围地址为:"B2:B4" ，它是第二列.

Sheet 2: In this sheet, I have a column called "Types" and its Range Address is: "B2:B4" and it is the second column.

类型值是:

员工

学生

老师

现在，我想创建一个下拉列表.

Now, I want to create a dropdown list.

我在ClosedXML中创建了一个下拉列表，代码为:

I created a dropdownlist in ClosedXML and the code is:

//Getting the range of sheet 2

var range = workbook.Worksheet(2).Range(workbook.Worksheet(2).Cell(2,2).Address,workbook.Worksheet(2).Cell(4,2).Address);

//Applying sheet 2's range with sheet 1 
workbook.Worksheet(1).Column(3).SetDataValidation().List(range:range);

wb.Worksheet(1).Column(3).SetDataValidation().IgnoreBlanks = true;
wb.Worksheet(1).Column(3).SetDataValidation().InCellDropdown = true;

我在单元格的右侧有一个下拉符号，但是我没有在其中得到值.

I got the dropdown symbol at the right side of the cell, But I didn't get the values in it.

推荐答案

以下是定义范围应如何为您工作的方式.由于已经知道工作表2中将用作工作表1下拉菜单项的单元格范围，因此您可以这样操作:

Here is how the defining the range should work for you. Since the range of cells in worksheet 2 which will act as the items of the drop down in worksheet 1 is already known then you can do it like this:

//get a reference to worksheet 2 containing various type values - Employee, Student, Teacher:
var worksheet2 = workbook.Worksheet(2);

//Applying sheet 2's range validation in sheet 1  where drop down list is to be shown
workbook.Worksheet(1).Column(3).SetDataValidation().List(worksheet2.Range("B2:B4"), true);

您还可以选择隐藏数据验证工作表，以使电子表格的用户不了解或无法操作包含您的主数据的第二张工作表:

You can also choose to hide your data validation worksheet so that the user's of your spread sheet won't get to know or can manipulate the second sheet which contains your master data for drop downs:

worksheet2.Hide();

这篇关于使用ClosedXML创建下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！