DataValidations List Validation - String limit?

Apr 26, 2012 at 5:48 AM

Hello!!!... I'm using List Validations and if I add to the list a 100 char length items to the list the Excel throw an error and do not display the list, There is a limit on the string length for each item in the list?

 

Thanks!!!

Regards

Santiago

Nov 6, 2012 at 10:18 PM

Hello, I'm back with this issue, I'm creating a Excel Worksheet from asp.net mvc, and I added some DataValidation in this way:

	//This Works perfect
            var val = ws.DataValidations.AddListValidation("B2:B10000");
            val.ShowErrorMessage = true;
            val.Formula.Values.Add("Add");
            val.Formula.Values.Add("Delete");

	//This Is not working, Excel give error: Characteristics erased: Cell with data validation: /xl/worksheets/sheet1.xml
            var val2 = ws.DataValidations.AddListValidation("C2:C10000");
            val2.ShowErrorMessage = true;
            for (int x = 0; x < 20; x++)
            {
                Dictionary row = (Dictionary)Groups[x];
                val2.Formula.Values.Add(row["Name"].Substring(0, Math.Min(row["Name"].Length, 17)));
            }

I don't know why but if I change the content from Groups["Name"] the max length of the string can be different, sometimes bigger, sometimes smaller. And also I can't add more than 20 records to the list.

I want to know the real limits to this functionality to see if I need to do a workaround with this.

 

Thanks!

Developer
Nov 9, 2012 at 6:45 PM

Hi,

If you add a list data validation via Excel you will see that the values are entered as a comma separated string. It is stored the same way in the open office xml. In Excel there seems to be a limitation for the total length of this string, but the same limitation is not implemented in EPPlus.

Maybe you could try to render your values as a comma separated string and try to paste it into a list data validation in Excel? My guess is that the total length of your items (added to Formula.Values) exceeds Excels max limit.

Regards,

Mats

Developer
Nov 10, 2012 at 5:31 PM

An example:

val.Formula.Values.Add("First");
val.Formula.Values.Add("Second");
val.Formula.Values.Add("Third");

...will be stored like this in the workbook: "First, Second, Third". As far as I can see this string cannot be longer than 255 characters in Excel 2010.

A workaround for you could be to lookup the values via an Excel formula instead, like this:

val.Formula.ExcelFormula = "A1:A22";

I think we should add a check for this and throw an exception when the max length is exceeded.

Nov 15, 2013 at 9:46 PM
Thank you swmal, that information clears everyting! ... I took me one year to answer but istill useful info! :D