Try an alternative: put all of the values to a range (for example: X1:X10). This special column or worksheet can be hidden. Then specify the range in the Source field of Data Validation dialog. If it works manually, then the program should work too.
How to fix the issue regarding the limit of 256 characters in data validation in excel
Hi,
I am directly inputting values from code base on to the drop-down list using data validation, where facing issues while opening the excel file.
Where have notice if the total characters of the list of dropdowns is within 256 characters, am able to open the excel file without the recovery window.
Code examples:
Workbook _workbook = new Workbook();
var dataWorksheet = _workbook.Worksheets["Sheet1"];
System.Collections.Generic.List<string> longTexts = new System.Collections.Generic.List<string>(); longTexts.Add("This is the test(IND) Rs: 10923.00 rupees.");
longTexts.Add("This is the test(US)$124.00 rupees.");
longTexts.Add("This is the test(US)$125.00 rupees.");
longTexts.Add("This is the test(US)$126.00 rupees.");
longTexts.Add("This is the test(US)$127.00 rupees.");
longTexts.Add("This is the test(US)$128.00 rupees.");
longTexts.Add("This is the test(US)$129.00 rupees.");
longTexts.Add("This is the test(US)$130.00 rupees.");
longTexts.Add("This is the test(US)$131.00 rupees.");
longTexts.Add("This is the test(US)$132.00 rupees.");
CellArea area = new CellArea();
var dropdownList = string.Join(",", longTexts.ToArray());
var validations = dataWorksheet.Validations;
var validation = validations[validations.Add(area)];
validation.Type = Aspose.Cells.ValidationType.List;
validation.Operator = OperatorType.Between;
validation.InCellDropDown = true;
validation.Formula1 = dropdownList; area.StartRow = 1;
area.EndRow = 1;
area.StartColumn = 1;
area.EndColumn = 1;
validation.AddArea(area); _workbook.Save("e:\test2\out1.xlsx");
Please help me to identify and resolve the issue.