🧾 How To Create an Excel Dropdown List Using a .NET Core API

🔍 Introduction

Excel dropdown lists are a powerful way to restrict user input to predefined options, improving accuracy and data consistency.

In this article, we’ll learn how to generate an Excel file with a dropdown list using a .NET Core Web API and the ClosedXML library. This is especially useful when you want to provide downloadable Excel templates where users can choose predefined values instead of typing them manually.

🧰 Prerequisites

Before we start, make sure you have:

.NET 6 or later installed
The ClosedXML NuGet package
```
dotnet add package ClosedXML
```
Basic understanding of ASP.NET Core Web API

🧩 Step 1: Create a Web API Endpoint

Create a new controller method in your ASP.NET Core API project.

[HttpGet("generate-excel-dropdown")]
public IActionResult GenerateExcelDropdown()
{
    var ListData = "\"Option1,Option2,Option3\"";

    using (var workbook = new ClosedXML.Excel.XLWorkbook())
    {
        var ws = workbook.AddWorksheet("Dropdown Sheet");

        // Label for the dropdown cell
        ws.Cell("A1").Value = "Select an option";

        // Dropdown target cell
        ws.Cell("B1").Value = "";
        var DropDown = ws.Range("B1");

        // Configure data validation
        var validation = DropDown.CreateDataValidation();
        validation.List(ListData, true);
        validation.IgnoreBlanks = true;
        validation.InCellDropdown = true;

        // Return the Excel file
        using (var ms = new MemoryStream())
        {
            workbook.SaveAs(ms);
            return File(
                ms.ToArray(),
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                "DropdownList.xlsx"
            );
        }
    }
}

🧠 Step 2: Understanding the Code

🔹 Defining the List Data

var ListData = "\"Option1,Option2,Option3\"";

Excel expects literal list values to be wrapped in double quotes.
The escaped quotes (\") tell Excel that it’s a literal list, not a formula or cell range.

Example

✅ "\"Yes,No,Maybe\"" → works
❌ "Yes,No,Maybe" → fails, Excel misinterprets it

🔹 Creating Workbook and Worksheet

var workbook = new XLWorkbook();
var ws = workbook.AddWorksheet("Dropdown Sheet");

XLWorkbook represents the Excel file.
AddWorksheet() adds a sheet named Dropdown Sheet.

🔹 Setting Up the Dropdown Cell

ws.Cell("A1").Value = "Select an option";
ws.Cell("B1").Value = "";
var DropDown = ws.Range("B1");

A1: Label cell for clarity.
B1: The cell where the dropdown will appear.

🔹 Creating Data Validation

var validation = DropDown.CreateDataValidation();
validation.List(ListData, true);
validation.IgnoreBlanks = true;
validation.InCellDropdown = true;

This code adds a dropdown in cell B1 with the provided options:

List() → defines the options (Option1, Option2, Option3)
IgnoreBlanks → allows the cell to remain blank
InCellDropdown → enables the dropdown arrow

🔹 Returning the File

using (var ms = new MemoryStream())
{
    workbook.SaveAs(ms);
    return File(
        ms.ToArray(),
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        "DropdownList.xlsx"
    );
}

The workbook is saved to a memory stream and returned as a downloadable Excel file.

🧾 Output

When you hit the API endpoint (for example,
https://localhost:5001/api/generate-excel-dropdown), you’ll download an Excel file containing:

Cell	Value / Feature
A1	“Select an option”
B1	Dropdown with Option1, Option2, Option3

The generated file name will be DropdownList.xlsx.

💡 Optional: Dynamic Dropdown Values

You can build the dropdown options dynamically from a database or list:

var options = new List<string> { "Admin", "Manager", "Employee" };
var listString = $"\"{string.Join(",", options)}\"";
validation.List(listString, true);

This lets your dropdown reflect real-time data from your application.

🏁 Conclusion

By using ClosedXML in your .NET Core API, you can generate Excel templates with dropdown lists dynamically — a clean and efficient way to guide user input.

This technique is especially helpful in scenarios such as:

Import templates
Report generation
Form-based Excel files

It improves usability while maintaining data consistency across your systems.