ListDataValidationRule.Argument1 Property Length Causing Issue

[Nicholas]

Hello,

I am using the RadSpreadProcessing library to create Excel reports: https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/overview

I'm using the DataValidation (i.e. ListDataValidationRule) to create dropdowns with a set of values: https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/data-validation

Here's the issue I'm seeing:

I go to try to create a dropdown with the ListDataValidationRule object, but when my string length is >255 characters for the Argument1 property and I export out the report, I receive an error when trying to launch the exported file:

Sample code:

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0); 
 
ListDataValidationRuleContext context = new ListDataValidationRuleContext(worksheet, dataValidationRuleCellIndex); 
context.ErrorStyle = ErrorStyle.Stop; 
context.ErrorAlertTitle = "Wrong value"; 
context.ErrorAlertContent = "The entered value is not valid"; 
context.InCellDropdown = true; 
context.IgnoreBlank= false;
// Insert string length is > 255 characters
context.Argument1 = <INSERT STRING OF LENGTH > 255 CHARACTERS>; 
 
ListDataValidationRule rule = new ListDataValidationRule(context); 
 
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule); 
 

[Nicholas]

For example, string with 256 characters:

context.Argument1 = "B6 Drill Chuck Connection BFA 0060, B10 Drill Chuck Connection BFA 0100, B12 Drill Chuck Connection BFA 0120, B16 Drill Chuck Connection BFA 0160, B18 Drill Chuck Connection BFA 0180, B22 Drill Chuck Connection BFA 0220, B24 Drill Chuck Connection BFA 0240"

[Dimitar]

Hi Nicholas,

Yes, that is a limitation that exists in Excel. I would suggest adding the data to cells and then referencing the range in the validation rule: 

ListDataValidationRuleContext context = new ListDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid";
context.InCellDropdown = true;
context.IgnoreBlank = false;
 
for (int i = 0; i < 100; i++)
{
    worksheet.Cells[i, 1].SetValue("item" + i);
}

context.Argument1 = "=($B$1:$B$100)";

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik