关于使用NPOI生成Excel下拉项的两种使用方式（XSSF、HSSF）

XSSF：

　　

static void test() {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            var sheetIndex = 0;
            var values = new string[] { };
            var helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
            var typesRegions = new CellRangeAddressList(1, 65535, 0, 0);
            if (values.Length > 50)
            {
                var sheetTmpName = "数据源表";
                sheetIndex++;
                ISheet sheetTmp = workbook.CreateSheet(sheetTmpName);
                //隐藏
                workbook.SetSheetHidden(sheetIndex, SheetState.Hidden);
                int index = 0;
                foreach (var item in values)
                {
                    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(values), typesRegions);
                typesValidation.CreateErrorBox("输入不合法", "请输入下拉列表中的值！");
                typesValidation.ShowErrorBox = true;
                sheet.AddValidationData(typesValidation);
            }
        }

 

HSSF：

只需要修改【创建约束】的那几行代码即可

                #region 创建约束
                DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
                HSSFDataValidation typesValidation = new HSSFDataValidation(typesRegions, constraint); 
                #endregion