C# Set Excel drop-down options using NPOI

This example shares the specific code for setting Excel drop-down options using NPOI for C# for your reference. The specific content is as follows

Recently, I am doing a function of exporting templates, which requires restricting the content entered by users in certain cells.

Expected effect: A drop-down selection appears in the cell, or a prompt is prompted when an input error occurs.

After reading a lot of information, I finally got the answer.

Then organize some of the following methods by yourself and record them to facilitate future use.

The first type

· Directly set the pull-down value, no more than 255 characters (advantages: simple logic; disadvantages: character limit)

· Suitable for pull-down values to be fixed values, such as: status, gender, etc.

Method block:

public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals)
{
     //Set the rows and columns that generate the drop-down box     var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);
 
     //Set the content of the drop-down box     DVConstraint constraint = (vals);
 
     //Bind the drop-down box and the action area and set the error message     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
     ("Input is not legal", "Please enter or select the value in the drop-down list.");
      = true;
 
     (dataValidate);
}

Called:

HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = ("sheet1");
(sheet, 1, 1, new List&lt;string&gt;() { "male", "female", "Confidential" }.ToArray());

The second type

Set pull-down by binding the value to the sheet

· Suitable for values with a lot of data or flexible control, such as: urban area, data table information, etc.

Method block:

public static void SetCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string name, int firstcol, int lastcol, string[] vals, int sheetindex = 1)
{
      // Create a Sheet specifically for storing the value of the drop-down item      ISheet sheet2 = (name);
      //hide      (sheetindex, true);
      int index = 0;
      foreach (var item in vals)
      {
          (index).CreateCell(0).SetCellValue(item);
          index++;
      }
      //The area of the drop-down item created:      var rangeName = name + "Range";
      IName range = ();
       = name + "!$A$1:$A$" + index;
       = rangeName;
      CellRangeAddressList regions = new CellRangeAddressList(0, 65535, firstcol, lastcol);
 
      DVConstraint constraint = (rangeName);
      HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
      ("Input is not legal", "Please enter or select the value in the drop-down list.");
       = true;
      (dataValidate);
}

Called:

HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = ("sheet1");
var roomTypeList = GetRoomTypeNameList();
(workbook, sheet, "RoomTypeDictionary", 1, 1, ());

In addition, extend the linkage pull-down (directly paste the source code)

Method block:

private void SetCityCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string dictionaryName, int citycol, int areacol, int sheetIndex)
{
    var citylist = GetCityList();
    int citycount = ;
    ISheet sheet2 = (dictionaryName);
    //hide    (sheetIndex, true);
 
    #region Urban Regional Data Construction    //City    int rowIndex = 0;
    foreach (var item in citylist)
    {
        IRow row = (rowIndex);
        (0).SetCellValue();
 
        rowIndex++;
    }
    //area    int n_rowIndex = 0;
    foreach (var item in citylist)
    {
        int areaIndex = 0;
        foreach (var area in )
        {
            IRow row = (areaIndex);
            if (row == null)
            {
                row = (areaIndex);
            }
            (n_rowIndex + 1).SetCellValue();
            areaIndex++;
        }
        n_rowIndex++;
    }
    #endregion
 
    #region Set the data field range    //Define the city    int columnIndex = 1;
    IName range_Country = ();
    range_Country.RefersToFormula = ("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), citycount);
    range_Country.NameName = "City";
 
    //Definition area    foreach (var item in citylist)
    {
        int areacount = ;
        columnIndex++;
        IName range_area = ();
        range_area.RefersToFormula = ("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), areacount);
        range_area.NameName = ;
    }
 
    //City list drop-down binding    (sheet, 1, 65535, citycol, citycol, "City");
 
    //The second column, follow the first column to link    string colName = GetExcelColumnName(areacol);
    for (int j = 1; j &lt; 500; j++)
    {
        (sheet, j, j, areacol, areacol, ("INDIRECT(${0}${1})", colName, j + 1));
    }
    #endregion
}
 
private string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = ;
    int modulo;
 
    while (dividend &gt; 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = (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 = (name);
    HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
    ("Input is not legal", "Please enter or select the value in the drop-down list.");
    (dataValidate);
}

Called:

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

The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.