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);
完。


  • 6
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
使用NPOI生成Excel模板并具备下拉选项的步骤如下: 1.添加NPOI依赖包 在项目中添加NPOI的依赖包,可以通过Maven进行添加,也可以手动下载添加到项目中。 2.创建Excel模板文件 使用NPOI创建一个新的Excel文件,并创建一个Sheet。在Sheet中添加需要的表头和数据列。 3.设置下拉选项 使用NPOI提供的方法,设置需要添加下拉选项的单元格的数据校验规则。下面是示例代码: ``` //获取当前Sheet ISheet sheet = workbook.GetSheet("Sheet1"); //创建下拉列表数据 string[] data = new string[] { "选项1", "选项2", "选项3" }; var validation = sheet.GetDataValidationHelper().CreateValidation( sheet.GetDataValidationHelper().CreateExplicitListConstraint(data), new CellRangeAddressList(1, 100, 0, 0)); //设置下拉选项的单元格范围 //设置下拉选项的单元格样式 var style = workbook.CreateCellStyle(); style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //将数据校验规则应用到单元格中 sheet.AddValidationData(validation); sheet.GetRow(1).CreateCell(0).CellStyle = style; ``` 4.保存Excel模板文件 使用NPOI提供的方法,将生成的Excel模板文件保存到本地或者输出到浏览器中供用户下载。下面是示例代码: ``` //保存Excel文件 using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fileStream); } ``` 完整的示例代码如下: ``` //创建Excel文件 var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet("Sheet1"); //创建表头 var headerRow = sheet.CreateRow(0); headerRow.CreateCell(0).SetCellValue("选项"); //创建下拉列表数据 string[] data = new string[] { "选项1", "选项2", "选项3" }; var validation = sheet.GetDataValidationHelper().CreateValidation( sheet.GetDataValidationHelper().CreateExplicitListConstraint(data), new CellRangeAddressList(1, 100, 0, 0)); //设置下拉选项的单元格范围 //设置下拉选项的单元格样式 var style = workbook.CreateCellStyle(); style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //将数据校验规则应用到单元格中 sheet.AddValidationData(validation); //添加数据行 for (int i = 1; i <= 10; i++) { var dataRow = sheet.CreateRow(i); dataRow.CreateCell(0).CellStyle = style; } //保存Excel文件 using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fileStream); } ```

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值