How to add values in a validation list at once?

Question

I'm trying to add values to a IExcelDataValidationList but the way I'm doing it right now is not very efficient.

I need to create many rows in my worksheet and populate a list for a certain cell for each of these rows. When I have many values to add to the list, this is taking forever.

for (var x = headerRowIndex + 1; x < 1000; x++)
{
    var address = ExcelCellBase.TranslateFromR1C1($"R{x}C{colIndex}", 0, 0);
    IExcelDataValidationList list = mainWorksheet.DataValidations.AddListValidation(address);
                
    foreach (var e in values)
    {
        list.Formula.Values.Add(e);
    }
}

You see how this can take a long time if values contain a lot of options.

Here's what I tried:

List<string> validationValues = new List<string>();
validationValues = values.ToList();

for (var x = headerRowIndex + 1; x < 1000; x++)
{
    var address = ExcelCellBase.TranslateFromR1C1($"R{x}C{colIndex}", 0, 0);
    var list = mainWorksheet.DataValidations.AddListValidation(address);
    ((List<string>)list.Formula.Values).AddRange(validationValues);
}

So I'm trying to add all values to the list at once. This compiles fine, but I'm getting this exception:

System.InvalidCastException: 'Unable to cast object of type 'DataValidationList' to type 'System.Collections.Generic.List`1[System.String]'.'

I've tried casting directly to DataValidationList but it's defined at private and only accessible by EPPlus itself.

Any ideas ?

Answer 1

Instead of creating IExcelDataValidationList per cell, create one for the whole column (address can include range of cells):

var address = ExcelCellBase.GetAddress(headerRowIndex + 1, colIndex, headerRowIndex + 1000, colIndex, true); // whole column
IExcelDataValidationList list = mainWorksheet.DataValidations.AddListValidation(address);
            
foreach (var e in values)
{
    list.Formula.Values.Add(e);
}