一切福田，不離方寸，從心而覓，感無不通。

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());

 

第二种

&middot; 通过绑定值到sheet中设置下拉

&middot; 适用于数据较多，或灵活控制的值，例如：城市区域、数据表信息等。

方法块：

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

 

from:https://www.yisu.com/zixun/688759.html

Copyright © 2024 Longsheng All rights reserved