Create dropdown list using ClosedXML

Question

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.

Sheet2: 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 end of the cell but I didn't get the values in it.

Decreasing

possible duplicate of How to read excel list elements (data validation) using C# Excel Interop? – Purpose

Answer 1

Here is how defining the range should work for you.

Here, the range of cells in worksheet 2 will act as source for the items of the drop-down in worksheet 1. If items to be shown in the drop-down 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 should also hide your data validation worksheet. This will make sure that data source worksheet is hidden from the users of your worksheet. Since the users of your work sheet won't see it so they won't be able to change the master data for drop-downs. You can achieve it with below line of code:

worksheet2.Hide();

Fructification