Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

asp.net

epplus

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.

like image 208
Keerthi Kumar Avatar asked Jan 28 '15 16:01

Keerthi Kumar


2 Answers

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);
}
like image 84
Ernie S Avatar answered Nov 14 '22 21:11

Ernie S


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 :) ]

like image 36
Linda Avatar answered Nov 14 '22 22:11

Linda