Shulou(Shulou.com)06/01 Report--

这篇文章给大家分享的是有关C#如何使用NPOI设置Excel下拉选项的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

第一种

· 直接设置下拉值，不超过255个字符（优点：逻辑简单 ;缺点：有字符限制）

· 适用于下拉值为固定值，例如：状态、性别等

方法块：

public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals){ //设置生成下拉框的行和列 var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol); //设置 下拉框内容 DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals); //绑定下拉框和作用区域，并设置错误提示信息 HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate);}

调用：

HSSFWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");ExcelHelper.SetCellDropdownList(sheet, 1, 1, new List() { "男", "女", "保密" }.ToArray());第二种

· 通过绑定值到sheet中设置下拉

· 适用于数据较多，或灵活控制的值，例如：城市区域、数据表信息等。

方法块：

public static void SetCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string name, int firstcol, int lastcol, string[] vals, int sheetindex = 1){ //先创建一个Sheet专门用于存储下拉项的值 ISheet sheet2 = workbook.CreateSheet(name); //隐藏 workbook.SetSheetHidden(sheetindex, true); int index = 0; foreach (var item in vals) { sheet2.CreateRow(index).CreateCell(0).SetCellValue(item); index++; } //创建的下拉项的区域： var rangeName = name + "Range"; IName range = workbook.CreateName(); range.RefersToFormula = name + "!$A$1:$A$" + index; range.NameName = rangeName; CellRangeAddressList regions = new CellRangeAddressList(0, 65535, firstcol, lastcol); DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate);}

调用：

HSSFWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");var roomTypeList = GetRoomTypeNameList();ExcelHelper.SetCellDropdownList(workbook, sheet, "RoomTypeDictionary", 1, 1, roomTypeList.ToArray());

另外，延伸联动下拉（直接贴源码了）

方法块：

private void SetCityCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string dictionaryName, int citycol, int areacol, int sheetIndex){ var citylist = GetCityList(); int citycount = citylist.Count; ISheet sheet2 = workbook.CreateSheet(dictionaryName); //隐藏 workbook.SetSheetHidden(sheetIndex, true); #region 城市区域数据构造 //城市 int rowIndex = 0; foreach (var item in citylist) { IRow row = sheet2.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(item.Name); rowIndex++; } //区域 int n_rowIndex = 0; foreach (var item in citylist) { int areaIndex = 0; foreach (var area in item.AreaList) { IRow row = sheet2.GetRow(areaIndex); if (row == null) { row = sheet2.CreateRow(areaIndex); } row.CreateCell(n_rowIndex + 1).SetCellValue(area.Name); areaIndex++; } n_rowIndex++; } #endregion #region 设置数据字段范围 //定义城市 int columnIndex = 1; IName range_Country = workbook.CreateName(); range_Country.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), citycount); range_Country.NameName = "城市"; //定义区 foreach (var item in citylist) { int areacount = item.AreaList.Count; columnIndex++; IName range_area = workbook.CreateName(); range_area.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), areacount); range_area.NameName = item.Name; } //城市列表下拉绑定 ExcelHelper.SetCellDropdownList(sheet, 1, 65535, citycol, citycol, "城市"); //第二列，跟随第一列联动 string colName = GetExcelColumnName(areacol); for (int j = 1; j

< 500; j++) { ExcelHelper.SetCellDropdownList(sheet, j, j, areacol, areacol, string.Format("INDIRECT(${0}${1})", colName, j + 1)); } #endregion} private 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;}public static void SetCellDropdownList(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, string name){ CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(name); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); dataValidate.CreateErrorBox("Input is illegal", "Enter or select a value from the drop-down list. "); sheet.AddValidationData(dataValidate);}

Call:

HSSFWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");SetCityCellDropdownList(workbook, sheet, "CityDictionary", 1, 2, 1); Thank you for reading! About "C#how to use NPOI to set Excel drop-down options" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.