C#使用NPOI设置Excel下拉选项
C#使用NPOI设置Excel下拉选项
更新时间：2022年02月21日 16:46:17 作者：风萧萧萧兮 这篇文章主要为大家详细介绍了C#使用NPOI设置Excel下拉选项，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下本文实例为大家分享了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);
|
- C#通过NPOI导入导出数据EXCEL
- C#用NPOI导出导入Excel帮助类
- C#使用Npoi导出Excel并合并行列
- C#使用NPOI库读写Excel文件
- C#利用NPOI操作Excel(单元格设置)
- C#使用NPOI实现Excel导入导出功能
- C#使用NPOI导出Excel类封装
- C# 基于NPOI操作Excel
- C#使用NPOI导入Excel的方法详解
- C#基于NPOI操作Excel