4,374 questions
Creating a searchable dropdown list for excel generated using EPPlus
Benjamin Chew
41
Reputation points
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
3,597 questions
Sign in to answer