C#使用NPOI设置Excel下拉选项
时间：2022-11-08 09:50:18|栏目：.NET代码|点击： 次
本文实例为大家分享了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<string>() { "男", "女", "保密" }.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("输入不合法", "请输入或选择下拉列表中的值。");
sheet.AddValidationData(dataValidate);
}
调用：
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
SetCityCellDropdownList(workbook, sheet, "CityDictionary", 1, 2, 1);
