Creating a searchable dropdown list for excel generated using EPPlus

Benjamin Chew 41 Reputation points
2022-01-07T02:28:59.763+00:00

my site will allow user to download an excel template for them to fill up and upload to the site to processed and save to DB.

As the header for the table is dynamically determined at runtime, the excel is generated using EPPlus.

some of the template will be having dropdownlist and the items in the dropdown list might be over hundreds. so I am wondering if it is possible to create a dropdown list with searchable options?

below is my code to create the dropdown list

            try
            {

                //create a WorkSheet

                string sheetname = columnname.Replace("/", "").Replace(",", "").Replace("@", "").Replace("|", "").Replace(" ", "").Trim();
                if (sheetname.Length > 16)
                {
                    sheetname = sheetname.Substring(0, 15);
                }
                sheetname += "_DO_NOT_DELETE";
                ExcelWorksheet worksheetOpt = package.Workbook.Worksheets.Add(sheetname);

                //add all the content from the List<Book> collection, starting at cell A1
                worksheetOpt.Cells["A1"].LoadFromCollection(options);
                worksheetOpt.Hidden = eWorkSheetHidden.Hidden;
                var validation = worksheet.DataValidations.AddListValidation(worksheet.Cells[3, column, row - 1, column].Address);
                validation.ShowErrorMessage = true;
                validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
                validation.ErrorTitle = "An invalid value was entered";
                validation.Error = "Select a value from the list";
                if (excelfomula)
                {
                    validation.Formula.ExcelFormula = "=" + sheetname + "!A:A";
                }
                else
                {
                    foreach (string opt in options)
                    {
                        validation.Formula.Values.Add(opt);
                    }
                }
                validation.AllowBlank = optional;
                validation.Validate();
            }
            catch (Exception ex)
            { LoggingService.LogInfo(ex); }
Microsoft 365 and Office Development Other
Developer technologies ASP.NET Other
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.