🔍 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:

🧩 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

🔹 Creating Workbook and Worksheet

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

🔹 Setting Up the Dropdown Cell

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

🔹 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:

🔹 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:

CellValue / Feature
A1“Select an option”
B1Dropdown 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:

It improves usability while maintaining data consistency across your systems.