Exception:The total length of a DataValidation list cannot exceed 255 characters

Question

Am trying to create a formula field dynamically in epplus. If formula field contains less than 255 character then it is creating properly. If it exceeds 255 then it is throwing a exception as Exception:The total length of a DataValidation list cannot exceed 255 characters.

Can any one please help me to solve this issue? or please tell me some alternatives.

Answer 1

The problem is you are using the Formulacontainer of that cell to store all of the available list options - basically a CSV list. That has a hard limit of 255 characters in Excel. You can see this by going into excel and manually entering values separated by commas right in the "Source" box when creating a new Validation List.

Your best option may be to populate the values in cells and give the range of the values to the Formula instead. Like this:

using (var pack = new ExcelPackage(existingFile))
{

    var ws = pack.Workbook.Worksheets.Add("Content");

    //var val = ws.DataValidations.AddListValidation("A1"); 
    //val.Formula.Values.Add("Here we have to add long text");
    //val.Formula.Values.Add("All list values combined have to have more then 255 chars");
    //val.Formula.Values.Add("more text 1 more text more text more text"); 
    //val.Formula.Values.Add("more text 2 more text more text more text"); 

    ws.Cells["B1"].Value = "Here we have to add long text";
    ws.Cells["B2"].Value = "All list values combined have to have more then 255 chars";
    ws.Cells["B3"].Value = "more text 1 more text more text more text";
    ws.Cells["B4"].Value = "more text 2 more text more text more text";
    ws.Cells["B5"].Value = "more text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore";

    var val = ws.DataValidations.AddListValidation("A1");
    val.Formula.ExcelFormula = "B1:B5";

    pack.SaveAs(existingFile);
}

Answer 2

Ernie's solution works perfectly! Except for the range of values which kept changing with each row. (i.e the first row added items from B1:B5, the second row B2:B6...)

This code change solved the issue:

val.Formula.ExcelFormula = "$B$1:$B$5";

[Adding as a solution because I was not allowed to comment :) ]