Excel drop-down list is a data validation function that limits the data that can be entered in a selected cell, requiring users to select a value from a predefined set of values. In this article, you will learn how to programmatically create a drop-down list in Excel using Spire.XLS for .NET.
- Create a Drop-Down List Based on Values in a Cell Range
- Create a Drop-Down List Based on Values in a String Array
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Create a Drop-Down List Based on Values in a Cell Range
With Spire.XLS for .NET, you can add values to a range of cells and then refer to that range of cells as the data validation source to create a drop-down list. This method might be slightly time-consuming, but it allows you to easily update the items in the drop-down list by directly modifying the values in the cells of the result document. The detailed steps are as follows.
- Create a Workbook object.
- Load a sample Excel document using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Get a specified cell or cell range using Worksheet.Range[] property.
- Add values to specified cells using XlsRange.Value property.
- Get data validation of the specified cell range using XlsRange.DataValidation property.
- Create a drop-down list by referring to a specified data range as the data validation source using Validation.DataRange property.
- Save the result document using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace DropdownList { class Program { static void Main(string[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load a sample Excel document workbook.LoadFromFile(@"E:\Files\input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.Worksheets[0]; //Add values to specified cells sheet.Range["A13"].Value = "Complete"; sheet.Range["A14"].Value = "Pending"; sheet.Range["A15"].Value = "Cancelled"; //Create a drop-down list by referring to a specified data range as the data validation source sheet.Range["C2:C7"].DataValidation.DataRange = sheet.Range["A13:A15"]; //Save the result document workbook.SaveToFile("ExcelDropdownList.xlsx", ExcelVersion.Version2010); } } }
Create a Drop-Down List Based on Values in a String Array
In Excel, you can create a drop-down list by manually entering a list of values in the “Source” box of the Data Validation menu. By doing this, you don't need to add data in Excel cells, which keeps your Excel document neat and organized. The following steps shows how to use Spire.XLS for .NET to achieve the same functionality.
- Create a Workbook object.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Add text to a cell and set its font style.
- Get a specified cell or cell range using Worksheet.Range[] property.
- Get data validation of the specified cell range using XlsRange.DataValidation property.
- Set the values of the drop-down list using Validation.Values property.
- Create a drop-down list in the specified cell by setting the Validation.IsSuppressDropDownArrow property to false.
- Save the result document using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace DropdownList2 { class Program { static void Main(string[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Get the first worksheet Worksheet sheet = workbook.Worksheets[0]; //Add text to cell B2 and set its font style sheet.Range["B2"].Value = "Country"; sheet.Range["B2"].Style.Font.IsBold = true; sheet.Range["B2"].Style.KnownColor = ExcelColors.LightTurquoise; //Set the values of the drop-down list sheet.Range["C2"].DataValidation.Values = new string[] { "France", "Japan", "Canada", "China", "Germany" }; //Create a drop-down list in the specified cell sheet.Range["C2"].DataValidation.IsSuppressDropDownArrow = false; //Save the result document workbook.SaveToFile("ExcelDropdownList.xlsx", ExcelVersion.Version2010); } } }
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.