C#导出execl带下拉项数据巨多方案

这个方法可以解决大数据下拉问题

/// <summary>
        /// 这个是正对整个execel 不是针对某列，所有必须有 IWorkbook workbook, 解决下拉数据量过多，导出之后点击没有反应
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="firstRow"></param>
        /// <param name="lastRow"></param>
        /// <param name="firstCol"></param>
        /// <param name="lastCol"></param>
        /// <param name="vals"></param>
        private static void SetCellDropdownListHasError2(IWorkbook workbook,ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, string[] vals)
        {
            var helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
            var typesRegions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            var sheetIndex = 0;
            if (vals.Length > 50)
            {
                var sheetTmpName = "数据源表";
                sheetIndex++;
                ISheet sheetTmp = workbook.CreateSheet(sheetTmpName);
                //隐藏
                workbook.SetSheetHidden(sheetIndex, SheetState.Hidden);
                int index = 0;
                foreach (var item in vals)
                {
                    sheetTmp.CreateRow(index).CreateCell(0).SetCellValue(item);
                    index++;
                }
                //创建的下拉项的区域：
                var rangeName = sheetTmpName + "Range";
                IName range = workbook.CreateName();
                range.RefersToFormula = sheetTmpName + "!$A$1:$A$" + index;
                range.NameName = rangeName;

                #region 创建约束
                var typesValidation = helper.CreateValidation(helper.CreateFormulaListConstraint(rangeName), typesRegions);
                typesValidation.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
                #endregion

                typesValidation.ShowPromptBox = true;
                sheet.AddValidationData(typesValidation);
            }
            else
            {
                var typesValidation = helper.CreateValidation(helper.CreateExplicitListConstraint(vals), typesRegions);
                typesValidation.CreateErrorBox("输入不合法", "请输入下拉列表中的值！");
                typesValidation.ShowErrorBox = true;
                sheet.AddValidationData(typesValidation);
            }
        }

调用

SetCellDropdownListHasError2(workbook, sheet, 1, dtSource.Rows.Count, 3, 3, flav.ToArray());

效果图

 

参考：https://www.cnblogs.com/wshisboy/p/7887363.html