Adding a data validation to Excel sheet causes an exception "You must enter data for validation criteria" in .NET

Aspose.Cells Product Family
1

Hello,

after updating Aspose.cells from v17.2 to v17.8 I get an error in the validation context while saving the document:
Aspose.Cells.CellsException: “You must enter data for validation criteria.”

In my Project, I’m using aspose to create an excel-file with data validations for the purpose, that the User can download, edit and reuploar this document. For the validation I use (p.e. for numeric columns):

       var validations= worksheet.Validations;
        var area= new CellArea {
            StartColumn = columnIndex,
            EndColumn = columnIndex,
            StartRow = 3, // Header rows
            EndRow = 10000 // Data Rows ... is there a better solution?
        };
        var validation= validations[validations.Add(area)];
        validation.ShowError = true;
        validation.IgnoreBlank = true;
        validierung.Type = ValidationType.Decimal;
        validierung.ErrorMessage = errorMessage;
        validation.AlertStyle = ValidationAlertType.Stop;
        return validation;

In v17.2 it worked correctly. In the generated Excel document hasn’t been any validation errors, only if I manipulate the document in Excel with invalid data.
In v17.8 I get this error while saving the document.

What is the meaning of this Exception and how can I handle it so save the document like in v17.2.

Thx and Greetings

PS: on v17.6 its ok - on v17.7 appears this exception

2

@smuddy

Thanks for your posting and using Aspose APIs.

Please share the runnable sample console application project that replicates this issue. We will execute it at our end and help you asap.

3

Hi,

I extracted the logic from my project and added the Unittest. Aspose is included via nuget.
A valid licence is not needed - it only causes another Test to fail (commented).
The Testmethod “Test_ColumnFormatting” is failing because of the Error.
If you downgrade to v17.6 - the test is running successfully and generated the Excelfile.

ExcelTest.zip (17.4 KB)

Thx and Greetings

PS: Validation is attached in the class “ExcelValidierung”

4

@smuddy

One of your project could not be loaded. Please see this screenshot. Please provide the runnable project. Besides, we will try to make some changes to make it runnable but it is preferable if you fix it as you know the project better than us.

Screenshot:

FM9Vb3B.png1280×941 50.7 KB

https://i.imgur.com/FM9Vb3B.png

5

Hi,

the missing project is not necessary for the UnitTest, but here is the cleaned solution:
ExcelTest.zip (16.3 KB)

6

@smuddy

17.02 is generating the Test_ColumnFormatting.xlsx successfully but this file does not look good because when I check the data validation inside it, Microsoft Excel shows that you have not entered maximum and minimum values.

Please check the output Excel file as well as screenshot for your reference.

Please provide me a rectified data validation (you can rectify it manually using Microsoft Excel) so that I provide you the good code which you can then employ inside your project to deal with this issue.

Download Link(s):
Test_ColumnFormatting—17.02.zip (8.0 KB)

Screenshot:

https://i.imgur.com/AoSnpQF.png

7

Ah, thank you very much!!
Now, if I know, what the exception is trying to tell me, it makes sense.

But it looks like, that I can’t tell Excel only to validate the data type. So a lower bound should be a work around:

        validation.Operator = OperatorType.GreaterThan;
        validation.Formula1 = datatype== Datentyp.Numeric ? int.MinValue.ToString() : DateTime.MinValue.ToString();

One last question: the Formula1-Field is taking the value as a string so I can’t pass p.e. the min DateTime value as object. How do I correctly handle the culture? The app has to deal with german and english culture. Or is there a way to pass the value culture-invariant into the Excel document?

8

@smuddy

Please create your desired validation manually with Microsoft Excel and then load it inside Aspose.Cells.Workbook object and access your Validation object and check its various properties in Debug or Quick Watch. This will give you idea how to create your validation object easily and this approach can also be used to resolve other issues. If you still find any problem, then please feel free to let us know, we will help you asap.