C#如何使用NPOI设置Excel下拉选项

这篇文章给大家分享的是有关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("输入不合法", "请输入或选择下拉列表中的值。");    sheet.AddValidationData(dataValidate);}

调用：

HSSFWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");SetCityCellDropdownList(workbook, sheet, "CityDictionary", 1, 2, 1);

感谢各位的阅读！关于"C#如何使用NPOI设置Excel下拉选项"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！