demand

Add a drop-down list to the specified column. The following picture:

thinking

1. NPOI Document Website can not be accessed, here refers to the POI Document.
2. There are two ways to add drop-down lists, one is to write a string of characters directly, such as new String[]{"10", "20", "30"}. This method limits the maximum length to 255.
3. The second refers to other cells. This approach is taken in this example. Create a new Sheet and fill in all the options. As shown in the figure, the first line is customer information, the second line is payment method information, etc.

Code

/// <summary>
        /// The add validation.
        /// </summary>
        /// <param name="sheet">
        /// sheet s to be added to the list
        /// </param>
        /// <param name="itemSheet">
        /// Option sheet.
        /// </param>
        /// <param name="headerCell">
        /// Title cell
        /// </param>
        /// <param name="items">
        //List items
        /// </param>
        private static void AddValidation(ISheet sheet, ISheet itemSheet, ICell headerCell, List<string> items)
        {
            // New Construction Bank
            var row = itemSheet.CreateRow(itemSheet.PhysicalNumberOfRows);

            // Write options in new lines
            for (int i = 0; i < items.Count; i++)
            {
                var cell = row.CreateCell(i);
                cell.SetCellValue(items[i]);
            }

            // To add the scope of the drop-down list
            var addressList = new CellRangeAddressList(
                headerCell.RowIndex + 1,
                65535,
                headerCell.ColumnIndex,
                headerCell.ColumnIndex);

            var dvHelper = sheet.GetDataValidationHelper();

            // Format Sheet2!$A :$E 
            var dvConstraint = dvHelper.CreateFormulaListConstraint(
                $"{itemSheet.SheetName}!$A${row.RowNum + 1}:${Common.Util.NumberToLetter(items.Count)}${row.RowNum + 1}");
            var validation = dvHelper.CreateValidation(dvConstraint, addressList);

            // Force you to fill in the values given in the drop-down list            
            // validation.ShowErrorBox = true;

            sheet.AddValidationData(validation);
        }

Invoke with customer enumeration

    // Get the customer list
    var list = new CustomerBll().GetModelList("dr = 0");
    var items = list.Select(c => c.cusname).ToList();

    // Add drop down
    AddValidation(sheet, itemsSheet, headerRow.Cells.First(c => c.StringCellValue == "Customer name"), items);

Reference material:

1. POI Official Documents
2. Author tonyqus's official NPO Github