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.
The problem is you are using the Formula
container 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);
}
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 :) ]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With