.NET通过NPOI构建带下拉框验证的Excel模板并下载（不是级联下拉框）

环境VS2022+.NET6，Nuget引用包NPOI(2.5.6)

创建类ExcelHelper

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace Demo.Common
{
    public class ExcelHelper
    {

        /// <summary>
        /// 获取Excel模板
        /// </summary>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public XSSFWorkbook GetTemplate()
        {
            string typeSheetName = "Type";

            //列名集合
            var clmList = GetColumnTitileList();
            //下拉选项字典集合
            var ddlDic = GetDDLDic();

            //创建工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            //创建模板Sheet页
            ISheet useSheet = workbook.CreateSheet("导入模板");
            //创建下拉框Sheet页并隐藏
            ISheet typeSheet = workbook.CreateSheet(typeSheetName);
            workbook.SetSheetHidden(workbook.GetSheetIndex(typeSheet), true);

            //创建下拉框Sheet页行
            IRow row = useSheet.CreateRow(0);
            int maxRowNum = ddlDic.Max(t => t.Value.Count);
            for (int i = 0; i < maxRowNum + 1; i++)
            {
                IRow typeRow = typeSheet.CreateRow(i);
            }

            if (clmList == null || clmList.Count == 0)
                throw new Exception("列名集合不能为空");
            //循环每一列进行添加操作
            for (int i = 0; i < clmList.Count; i++)
            {
                //在模板页添加表头
                row.CreateCell(i).SetCellValue(clmList[i]);

                //如果再下拉框字典中存在这个列则需要将值放到下拉框Sheet页里面，然后在模板页绑定下拉框并加上验证
                if (ddlDic.ContainsKey(clmList[i]))
                {
                    IRow tmpRow = typeSheet.GetRow(0);
                    tmpRow.CreateCell(i).SetCellValue(clmList[i]);
                    string[] tmpArr = ddlDic[clmList[i]].ToArray();
                    for (int j = 0; j < tmpArr.Length; j++)
                    {
                        typeSheet.GetRow(j + 1).CreateCell(i).SetCellValue(tmpArr[j]);
                    }
                    string colName = GetExcelColumnName(i + 1);
                    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(useSheet as XSSFSheet);
                    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(string.Format("{2}!${0}$2:${0}${1}", colName, tmpArr.Length+1, typeSheetName));
                    CellRangeAddressList cellRegions = new CellRangeAddressList(1, 500, i, i);
                    XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, cellRegions);
                    validation.SuppressDropDownArrow = true;
                    validation.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
                    validation.ShowErrorBox = true;
                    useSheet.AddValidationData(validation);
                }
            }
            useSheet.DefaultColumnWidth = 20;

            return workbook;
        }

        /// <summary>
        /// 构建下拉选项的数据--测试数据，手动构建，可以替换成从数据库取数
        /// </summary>
        /// <returns></returns>
        private Dictionary<string, List<string>> GetDDLDic()
        {
            Dictionary<string, List<string>> keyValues = new Dictionary<string, List<string>>();
            keyValues.Add("年级", new List<string>() { "一年级", "二年级", "三年级" });
            keyValues.Add("性别", new List<string>() { "男", "女" });
            keyValues.Add("学校", new List<string>() { "学校1", "学校2", "学校3", "学校4" });
            return keyValues;
        }

        /// <summary>
        /// 获取Excel标题行名称集合
        /// </summary>
        /// <returns></returns>
        private List<string> GetColumnTitileList()
        {
            return new List<string>() { "学号", "姓名", "性别", "学校", "年级" };
        }

        /// <summary>
        /// 获取Excel列名
        /// </summary>
        /// <param name="columnNumber">列的序号，如：A、B、C、AA、BB</param>
        /// <returns></returns>
        private static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;
            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }
            return columnName;
        }

    }
}

创建控制器TemplateController

using Demo.Common;
using Microsoft.AspNetCore.Mvc;

namespace Demo.Controllers
{
    public class TemplateController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 下载模板
        /// </summary>
        /// <returns></returns>
        public FileResult Download()
        {
            var workbook = new ExcelHelper().GetTemplate();
            var ms = new NpoiMemoryStream();
            ms.AllowClose = false;
            workbook.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin);
            ms.AllowClose = true;
            string filename = "模板" + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            return File(ms, "application/vnd.ms-excel", filename);
        }
    }
}

添加类NpoiMemoryStream

namespace Demo.Common
{
    public class NpoiMemoryStream : MemoryStream
    {
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }
}