C#之NPOI设置Excel下拉

 背景

        导出Excel其实是很常见的，但以前就是很简单将数据导出到Excel，做的最多的就是文字居中、栏位自适应、设置数据类型，其实很少会有设置Excel下拉这种，但用户的需求，而且这也算是Excel比较常用的，所以只能百度啦！

环境

•   VS2019  .NET Core API
  • NPOI(2.5.3)

问题

        由于我们导出的Excel是.xlsx格式，但参考的文章是.xls的，于是继续百度，后来找到了一个导出.xlsx的，然后把这两个文章的的精华综合了一下，于是我的两个格式的都可以用的方法出炉了！

核心代码

        #region Excel辅助导出

        public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals)
        {

            //設置生成下拉框的行和列
            var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);
            IDataValidation validation = null;

            if (sheet.GetType().Name.Contains("XSSF")) // .xlsx
            {
                XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);//获得一个数据验证Helper  
                                                                                                 //IDataValidation
                validation = helper.CreateValidation(
                helper.CreateExplicitListConstraint(vals), cellRegions);//创建约束
            }
            else // HSSF .xls
            {
                //設置 下拉框內容
                DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);
                validation = new HSSFDataValidation(cellRegions, constraint);


                /*綁定下拉框和作用區域，並設置錯誤提示信息
                HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
                dataValidate.CreateErrorBox("輸入不合法", "請輸入或選擇下拉列表中的值。");
                dataValidate.ShowPromptBox = true;
*/
            }

            validation.CreateErrorBox("輸入不合法", "請輸入或選擇下拉列表中的值。");
            validation.ShowPromptBox = true;

            sheet.AddValidationData(validation);
        }

        #endregion

代码调用

        我是对导出Excel的导出代码进行了自己的设置，所以我会构造一个Dictionary<int[], string[]> arrs 传入我的方法中，然后对它进行设置，你若是自己完整写的话，只需要用我屏蔽代码的那种写法就可以。

// 生成要传入的数据源
Dictionary<int[], string[]> arrs  = new Dictionary<int[], string[]>() {
                        { new[] { 2, 2 },  new[] { "深圳户籍", "广东省内非深户", "广东省外户籍" } },
                        { new[] { 5, 5 },  new[] { "布依族", "朝鲜族", "满族", "侗族", "瑶族", "白族", "土家族", "哈尼族", "哈萨克族", "傣族", "黎族", "傈僳族", "佤族", "畲族", "高山族", "拉祜族", "水族", "东乡族", "纳西族", "景颇族", "柯尔克孜族", "土族", "达斡尔族", "仫佬族", "羌族", "布朗族", "撒拉族", "毛南族", "仡佬族", "锡伯族", "阿昌族", "普米族", "塔吉克族", "怒族", "乌孜别克族", "俄罗斯族", "鄂温克族", "德昂族", "保安族", "裕固族", "京族", "塔塔尔族", "独龙族", "鄂伦春族", "赫哲族", "门巴族", "珞巴族", "基诺族", "其他" } }, 
                        { new[] { 13, 13 },  new[] { "一档医疗在职", "二档医疗", "三档医疗" } }
                    };


// 对数据源进行循环
if (arrs != null)
            {
                foreach (var item in arrs)
                {
                    SetCellDropdownList(sheet, item.Key[0], item.Key[1], item.Value);
                    //SetCellDropdownList(sheet, 1, 1, new[] { "深圳户籍", "广东省内非深户", "广东省外户籍" });
                    //SetCellDropdownList(sheet, 1, 1, new List<string>() { "男", "女", "保密" }.ToArray());
                }
            }

Excel导出代码参考

        其实之前打算优化一下导出的，后来还是没做。。。

        #region  导出Excel
        /// <summary>
        /// 将DataTable转换成  Two dimensional Arrays二维数组[,];Jagged array交错数组[][]
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="filePath">@"D:\\TEMP\\EXCEL\\" + shhetName + ".xlsx";</param>
        public static string[,] DtToTwoDimensArr(DataTable dt)
        {
            #region 填充数据
            string[,] dataArray = new string[1 + dt.Rows.Count, dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count; i++)//填写列名
            {
                dataArray[0, i] = dt.Columns[i].ColumnName;

                for (int j = 0; j < dt.Rows.Count; j++)//填入数据
                {
                    dataArray[j + 1, i] = dt.Rows[j][i].ToString();
                }
            }
            return dataArray;
            #endregion

            通过NPOI组件写入Excel数据
            //TwoDimensArrToExcel(dataArray, "dataa.xlsx", dt.Rows.Count, dt.Columns.Count);
        }

        /// <summary>
        /// 将数据写入Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        /// <param name="rowNum"></param>
        /// <param name="colNum"></param>
        /// <param name="arrs">设置下拉菜单</param>
        /// <returns></returns>
        public static byte[] TwoDimensArrToExcel(DataTable dt, string filePath, int rowNum, int colNum, Dictionary<int[], string[]> arrs = null)
        {
            string[,] str = DtToTwoDimensArr(dt);

            //string sheetName = Path.GetFileNameWithoutExtension(filePath);

            IWorkbook workbook = null; //新建IWorkbook對象  

            if (filePath.IndexOf(".xlsx") > 0) // 2007版本 
            {
                workbook = new XSSFWorkbook(); //xlsx數據讀入workbook 
            }
            else if (filePath.IndexOf(".xls") > 0) // 2003版本 
            {
                workbook = new HSSFWorkbook(); //xls數據讀入workbook 
            }

            ISheet sheet = workbook.CreateSheet("Sheet1"); //创建第一個工作表 

            if (arrs != null)
            {
                foreach (var item in arrs)
                {
                    SetCellDropdownList(sheet, item.Key[0], item.Key[1], item.Value);
                    //SetCellDropdownList(sheet, 1, 1, new[] { "深圳户籍", "广东省内非深户", "广东省外户籍" });
                    //SetCellDropdownList(sheet, 1, 1, new List<string>() { "男", "女", "保密" }.ToArray());
                }
            }


            for (int i = 0; i < str.GetLength(0); i++)// 行循环
            {
                IRow row = sheet.CreateRow(i);//创建一行数据
                for (int j = 0; j < str.GetLength(1); j++)// 列循环
                {
                    row.CreateCell(j).SetCellValue(str[i, j]);
                }
            }
            byte[] buffer;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.ToArray();
                ms.Close();
            }
            return buffer;

        }

        #endregion

参考文章

C#使用NPOI設置Excel下拉選項 – WalkonNet

Npoi导出Excel并设置下拉选择限制_unicorn_47的博客-CSDN博客_npoi.mapper 导出模板下拉列表