Drop-down lists provide a better way to limit users for data entry from a specified list of options. This removes the chances of making wrong data entries if input by the user manually. Creating drop-down lists in your Excel workbook is a preferred way of compelling users to choose from available dropdown entries only. When someone selects a cell, the options in drop-down lists appear from where they can make a selection. Drop-down lists make a worksheet more efficient.

In this article, we’ll show how to create a drop-down list in a worksheet in an Excel file with NPOI API using C#/VB.NET. But first, let us have a look at how to create a dropdown list using Microsoft Excel.

Create Drop-down List using Microsoft Excel

Being an Excel user, it is easy to create a drop-down list in just a few steps. All you need to know is working with Excel menu options and the overall data entry in cells. You can create dropdown lists in Excel using the following steps.

Steps to Create Drop-down List in Excel

  1. Type the entries in a new worksheet that you want to appear in your drop-down list.
  2. Select the cell in the worksheet where you want the drop-down list.
  3. Go to the **Data **tab on the Ribbon and then go to Data Validation.
  4. On the **Settings **tab, in the **Allow **box, click List.
  5. In the Source box, select the list range that contains your data to appear in the drop-down list.
  6. Check the Ignore blank box if you consider it OK for people to leave the cell empty.
Data Validation List options

That’s all you need to create a drop-down list in Excel.

Create Dropdown List using NPOI in C# and VB.NET

Now that you have gone through the steps of creating a drop-down listing using Excel, we can proceed to do the same using NPOI API for .NET in C#. You will need to install NPOI in your .NET project to get started. You can learn about this in our detailed instructions guide for Installing NPOI for .NET.

Create Drop-down List using C#

Now that your development environment is ready for working with NPOI API in C#, create a console-based porject and add the following lines of code.

//Create workbook
IWorkbook wb = new XSSFWorkbook();
XSSFSheet ws = (XSSFSheet)wb.CreateSheet("ws");

//Create dropdown list
IDataValidationHelper validationHelper = new XSSFDataValidationHelper(ws);
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidationConstraint constraint = validationHelper.CreateExplicitListConstraint(new String[] { "Austin", "Boston", "Chicago", "Dallas", "JacksOnVille","Los Angeles", "New York", "Philladephia"});
IDataValidation dataValidation = validationHelper.CreateValidation(constraint, addressList);
dataValidation.SuppressDropDownArrow = true;
ws.AddValidationData(dataValidation);

//Save the file
FileStream file = File.Create("ExcelDropdownList.xlsx");
wb.Write(file, false);
file.Close();

Conclusion

In this article, we explored how to create a drop-down list in an Excel worksheet using NPOI for .NET API. The sample code is written in C#, but can easily be converted to VB.NET. For more code samples on working with Excel spreadsheets using NPOI API for .NET, stay tuned to our blog.