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