How to Create a DropDown List in Excel Using C#

Introduction

This article demonstrates how to create and fill in a drop-down list in Excel using C#. It demonstrates how to apply data validation in Excel with C#. The DropDown list is filled in with XML using data validation.

Filling in a DropDown list in Excel is not a frequent requirement in our software development. You might not get much code or help in the internet on this.

What Data Validation in Excel is

You use data validation to control the type of data or the values that users enter into a cell. For example, you may want to restrict the data entry to a certain range of dates, limit choices using a list, or ensure that only positive whole numbers are entered.

Use data validation in Excel to ensure that users enter certain values into a cell.

How Data Validation is useful in Excel

Data validation is invaluable when you want to share a workbook with others in your organization and you want the data entered in the workbook to be accurate and consistent. For example, if you do not want to enter any data in Excel by the user then you can restrict data entry to values in a DropDown list.

How to add Data Validation in Excel without C# code

• Create a blank Excel workbook and select the A1 cell in the top of the Excel workbook.
• Select the Data Tab in Excel 2010 and click the Data Validation command as shown in following screen shot.
  
  
  
  
• Click on Data Validation. Select the Settings tab from Data Validation. Select “List” from the Validation criteria and enter values into the source with comma-separated values as shown in the following screen shot then click the "Ok" button.
  
  
  
  
• After adding validation click on the "Ok" button. You will see a DropDown in Excel as shown in following screen shot.
  
  
  

Let’s start by adding the DropDown in Excel using C# code with the following example. In this article, I have created a DropDown list using data validation.

1. Create a new project in Visual Studio 2010/2012/2013

Create a new project in Visual Studio 2010/2012/2013 and select Windows Forms application as shown in the following screen shot.



2. Design the form

Once your project is created, you can drag controls from the toolbox. In my example, I dragged a label and a button as shown in the following screen shot.



3. Add XML and Excel workbook to project

After adding controls to the form, add XML and Excel files in the project. You can create a XML to fill the DropDown values in Excel. You can create a blank workbook and add it to your project.

The following  is a sample XML to fill in drop down values in Excel.

<?xml version="1.0" encoding="UTF-8"?>

-<Demo>

<ExcelVal> Mark </ExcelVal>

<ExcelVal> James </ExcelVal>

<ExcelVal> David </ExcelVal>

<ExcelVal> Smith </ExcelVal>

<ExcelVal> Bill </ExcelVal>

<ExcelVal> John </ExcelVal>

</Demo>

 
4. Open Excel file on button click event

In this sample, I have added an Excel workbook to the project. Now I want to open an Excel workbook on a button click event and add data validation for filling in the DropDown list. I have opened a workbook with the "workbooks.open()" method. You can see that as shown in the following screen shot.



5. Get Values from XML file

I need to fill in my DropDown with some values. Hence, I have created a XML file. Now I want to get values from XML the same as shown in the following code. You can pass this value to the Data Validation method.



6. Populate DropDown method to fill drop down in Excel:

You can add validation using the sheet object. I have passed the “A1” cell in "sheet.range" because I want to fill in my drop down on the “A1” cell in the workbook. You need to select "xlValidationList" to fill in the drop down. I have also call a XML method (getValuesFromXML()) as source in the Validation.Add() method.

Call this method on a button click event after opening the workbook.

// Populate Dropdown
private void PopulateDropdown(Excel.Worksheet oSheet)
{           
     oSheet.Range["A1"].Validation.Add(Excel.XlDVType.xlValidateList, Type.Missing,
     Excel.XlFormatConditionOperator.xlBetween, getValuesFromXML());           
}

7. Output of the sample code

After adding Data Validation code, now run the sample code. You can see the open workbook and see the filled in drop down as output as shown in the following screen shot.