Category / Section
How to avoid exception while setting more number of characters in list validation of WinForms Spreadsheet (SfSpreadsheet)?
1 min read
Data validation
Maximum number of characters accepted in ListOfValues property for DataValidation is 256 (as per MS Excel). If the array list which is assigned to ListOfValues property holds more than 256 characters, then it may lead to an exception.
This issue can be resolved by assigning the list of values to the unused ranges in Spreadsheet and refer that range as validation range(DataRange)
C#
string[] values = new string[]
{
"A sample text in Spreadsheet1",
"A sample text in Spreadsheet2",
"A sample text in Spreadsheet3",
"A sample text in Spreadsheet4",
"A sample text in Spreadsheet5"
"A sample text in Spreadsheet6",
"A sample text in Spreadsheet7",
"A sample text in Spreadsheet8",
"A sample text in Spreadsheet9",
"A sample text in Spreadsheet10",
"A sample text in Spreadsheet11",
"A sample text in Spreadsheet12",
"A sample text in Spreadsheet13"
};
//Setting the values to unused range,
for (int i = 0, j = 500; i < values.Length; i++, j++)
spreadsheet.ActiveSheet[j, 1000].Value = values[i];
//Setting the data validation to Cell A3,
var validation = spreadsheet.ActiveSheet.Range["A3"].DataValidation;
IRange valueRange = spreadsheet.ActiveSheet[500, 1000, 500 + values.Length - 1, 1000];
validation.DataRange = valueRange;
//Hiding the unused range,
spreadsheet.ActiveSheet.HideColumn(1000);
spreadsheet.ActiveGrid.ColumnWidths.SetHidden(1000, 1000, true);
Note:
You can assign the array list of values to an unused range and hide that range.
Samples: WF
Reference link: https://help.syncfusion.com/windowsforms/spreadsheet/editing#data-validation
