News Category

C#/VB.NET: Create a Drop-Down List in Excel

2022-06-16 07:00:00 Written by  support iceblue
Rate this item
(0 votes)

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.

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);
        }
    }
}

C#/VB.NET: Create a Drop-Down List in Excel

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);
        }
    }
}

C#/VB.NET: Create a Drop-Down List in Excel

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.

Additional Info

  • tutorial_title:
Last modified on Thursday, 16 June 2022 08:47