Error on list validation

Aspose.Cells Product Family
1

Hi Team,

We are creating an excel through aspose and adding list validation to a column, it works fine for us normally but in one particular case when i am using it, while opening the excel, it says excel needs to be repaired.

Below is the code being used:

var validations = WorkSheet.Validations;
var FlatList= “Verkäufer,Umweltschutz-Techniker,Umweltschutz-Ingenieur,Tischler,Technischer Planer Werkzeug,Technischer Planer,Techniker Planung Werk/Technik/Einrichtung m.E.,Techniker Planung Werk/Technik/Einrichtung,Techniker Planung Werk/Einrichtung (Elektronik) komplex,Techniker Planung Werk/Einrichtung (Elektronik) einfach”;

        CellArea area;
        area.StartRow = 1;
        area.EndRow = 100;
        area.StartColumn = 0;
        area.EndColumn = 0;

        var validation = validations[validations.Add(area)];
        validation.Type = ValidationType.List;
        validation.Operator = OperatorType.Between;
        validation.InCellDropDown = true;

        validation.Formula1 = FlatList;

        validation.ShowError = true;

        validation.AlertStyle = ValidationAlertType.Stop;

        // Set the error title
        validation.ErrorTitle = "Test";

        //Set the error message
        validation.ErrorMessage = "Test message";

This fails only in this specific flat list string.

Please let me know how to handle this.

Thanks.

2

@siddhanntarora1992,

Thanks for the sample code segment and details.

I tried adding List data validation based on your string (list) value(s) as data source in FlatList variable in MS Excel manually but I could not succeed. It looks like your provide list (string) is either lengthy or invalid. Anyways, could you provide us a template file containing your desired list data validation (applied), you may create the file in MS Excel manually, we will check on how to do it via Aspose.Cells APIs.

3

Hi Amjad,
I think it is happening because the length of the string exceeds 255 characters, is there any other way to create data list validation?

Thanks.

4

@siddhanntarora1992,

I am afraid, if you could not accomplish the task in MS Excel manually, you cannot do it via Aspose.Cells APIs as it is the limitation of MS Excel file formats (e.g XLS, XLSX, etc.). In short Aspose.Cells follows MS Excel standards and specifications when performing certain features or tasks or rendering Excel files. If you could perform the task in MS Excel manually, you may do the same with Aspose.Cells. If you still face any issue where it is not performing task as opposed to MS Excel (where you could do that), let us know with details and sample files, we will check it soon.

5

@siddhanntarora1992,
We have investigated it a bit more and would suggest you to put those values into cells in a range, and then use the range as source of the validation.

Let us know your feedback.