Failed to open excel file while adding dropdown list with long text

Hi,
I Have a requirement where need to add multiple long text with special characters in excel dropdown, while doing that am getting an error “Removed Feature: Data validation from /xl/worksheets/sheet1.xml part”.

Example:

Workbook _workbook = new Workbook(filePath + “SourceFile.xlsx”);
var dataWorksheet = _workbook.Worksheets[“Sheet1”]

List longTexts = new List();
longTexts.Add(“This is the test(IND) Rs: 10923.00 rupees.”)
longTexts.Add(“This is the test(US)$124.00 .”)
longTexts.Add(“This is the test(US)$125.00 .”)

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(filePath + “out.xlsx”);

Issue : if we add the text of length more than 32 characters, file is being corrupted and return file recovery popup and if i click on Yes, its shows below error
“Removed Feature: Data validation from /xl/worksheets/sheet1.xml part”

Please help me, if this can be resolved,

@Deepak_S8956,

I tested the following sample code (I created the Excel file from the scratch), it works fine and the output XLSX file (attached) is fine tuned. I am using latest version/fix: Aspose.Cells for .NET v24.6.
e.g.,
Sample code:

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 repees.");
longTexts.Add("This is the test(US)$124.00 repees.");
longTexts.Add("This is the test(US)$125.00 repees.");

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");

out1.zip (6.0 KB)

If you still find the issue with latest version/fix (Aspose.Cells for .NET v24.6), could you please zip and attach your template Excel file “SourceFile.xlsx”. We will check your issue soon.

Hi @amjad.sahi ,

Have notice another issue, which is the total length of “dropdownList” on the above example. If its exceeding 255 characters then its failing to load the excel as well.

Do you have any suggestion on this to set any property which accept more than
255 characters.

@Deepak_S8956,

I understand your issue now. Please note that you can’t exceed the limit of 256 characters in data validation (e.g., list) if you are directly inputting values. This is an MS Excel limitation and has nothing to do with Aspose.Cells. However, you may work around it or cope with it by inputting data into the worksheet (range) cells and then specifying the data range for the “Formula1” attribute of Validation type. This means you have to insert the data into the worksheet first and then specify the data range for the validation (source) formula. See the sample code that works fine as I tested.
e.g.,
Sample code:

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 repees.");
longTexts.Add("This is the test(US)$124.00 repees.");
longTexts.Add("This is the test(US)$125.00 repees.");
longTexts.Add("This is the test(US)$126.00 repees.");
longTexts.Add("This is the test(US)$127.00 repees.");
longTexts.Add("This is the test(US)$128.00 repees.");
longTexts.Add("This is the test(US)$129.00 repees.");
longTexts.Add("This is the test(US)$130.00 repees.");
longTexts.Add("This is the test(US)$131.00 repees.");
longTexts.Add("This is the test(US)$132.00 repees.");

System.Collections.ArrayList arrayList = new System.Collections.ArrayList(longTexts);
_workbook.Worksheets["Sheet1"].Cells.ImportArrayList(arrayList,0,1,true);

CellArea area = new CellArea();
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 = "=Sheet1!B1:B10";
area.StartRow = 1;
area.EndRow = 1;
area.StartColumn = 1;
area.EndColumn = 1;

validation.AddArea(area);
_workbook.Save("e:\\test2\\out1.xlsx");

Please find attached the output XLSX file for your reference. The output file can be opened fine (without error) in MS Excel.
out1.zip (6.2 KB)