Create dropdown list using ClosedXML

问题:

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

I have two excel sheets

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

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

Types values are:

Employee

Student

Teacher

Now, I want to create a dropdown list.

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.

回答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();