本文实例为大家分享了C#使用NPOI设置Excel下拉选项的具体代码，供大家参考，具体内容如下
最近在做一个导出模板的功能，需要限制用户的某些单元格输入的内容。
期望达到的效果：单元格中出现下拉选择，或输入错误时提示。
翻阅了许多资料，终于得到了答案。
然后自己整理下边一些方法，记录下来方便以后使用。
第一种
· 直接设置下拉值，不超过255个字符（优点：逻辑简单 ；缺点：有字符限制）
· 适用于下拉值为固定值，例如：状态、性别等
方法块：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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);} |
调用：
|
1
2
3
|
HSSFWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");ExcelHelper.SetCellDropdownList(sheet, 1, 1, new List<string>() { "男", "女", "保密" }.ToArray()); |
第二种
· 通过绑定值到sheet中设置下拉
· 适用于数据较多，或灵活控制的值，例如：城市区域、数据表信息等。
方法块：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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);} |
调用：
|
1
2
3
4
|
HSSFWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");var roomTypeList = GetRoomTypeNameList();ExcelHelper.SetCellDropdownList(workbook, sheet, "RoomTypeDictionary", 1, 1, roomTypeList.ToArray()); |
另外，延伸联动下拉（直接贴源码了）
方法块：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
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;} |
|
1
2
3
4
5
6
7
8
|
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("输入不合法", "请输入或选择下拉列表中的值。"); sheet.AddValidationData(dataValidate);} |
调用：
|
1
2
3
|
HSSFWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");SetCityCellDropdownList(workbook, sheet, "CityDictionary", 1, 2, 1); |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/u011958513/article/details/78624036
