C# NPOI导出excel绑定列的下拉数据源

using System;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

class Program
{
    static void Main(string[] args)
    {
        // 创建一个新的工作簿
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");

        // 准备下拉数据源
        string[] data = new string[] { "Option 1", "Option 2", "Option 3" };

        // 将数据源添加到工作簿中的一个隐藏工作表
        ISheet hiddenSheet = workbook.CreateSheet("HiddenSheet");
        for (int i = 0; i < data.Length; i++)
        {
            IRow row = hiddenSheet.CreateRow(i);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue(data[i]);
        }

        // 创建一个下拉列表约束
        IName namedCell = workbook.CreateName();
        namedCell.NameName = "DropDownOptions";
        namedCell.RefersToFormula = $"HiddenSheet!$A$1:$A${data.Length}";

        // 将下拉列表约束应用到目标列
        var validation = new XSSFDataValidationHelper((XSSFSheet)sheet);
        var dvConstraint = (XSSFDataValidationConstraint)validation.CreateFormulaListConstraint("DropDownOptions");
        var cellRangeAddressList = new CellRangeAddressList(0, 65535, 0, 0);
        var dataValidation = (XSSFDataValidation)validation.CreateValidation(dvConstraint, cellRangeAddressList);
        dataValidation.ShowErrorBox = true;
        dataValidation.ErrorTitle = "Invalid Selection";
        dataValidation.ErrorStyle = ExcelDataValidation.ErrorStyle.Stop;
        dataValidation.CreateErrorBox("Invalid Selection", "Please select a valid option from the dropdown list.");

        sheet.AddValidationData(dataValidation);

        // 保存 Excel 文件
        using (FileStream file = new FileStream("output.xlsx", FileMode.Create))
        {
            workbook.Write(file);
        }

        Console.WriteLine("Excel exported successfully.");
    }
}