Incorrect data validation rule value for large number

If we add data validation to a cell in Excel to check that the entered value is below 10000000000 (menu Data, Data Validation, Allow: Whole number, Data: less than, Maximum: 10000000000), then the corresponding Aspose.Cells.Validation object has Value1 -2147483648 instead of 10000000000.

Here is the test file: DataValidationIssue.xlsx.zip (6.3 KB)

And here is the test code:

var workbook = new Workbook("DataValidationIssue.xlsx");

var validation = workbook.Worksheets[0].Validations[0];
var validationValue1 = validation.Value1; // expected: 10000000000, actual: -2147483648

Can you investigate and fix this issue? We’re using Aspose.Cells v25.1.2 for .NET.

@perfectxl

It appears that you are encountering an issue with the data validation rule in Aspose.Cells where the maximum value for a whole number validation is not being set correctly for large numbers. Specifically, when you set the maximum value to 10,000,000,000, the corresponding Aspose.Cells.Validation object is returning -2,147,483,648 instead.

This issue arises because the maximum value for a whole number validation in Aspose.Cells is limited to the range of a 32-bit signed integer, which is from -2,147,483,648 to 2,147,483,647. Since 10,000,000,000 exceeds this limit, it cannot be represented correctly, resulting in the incorrect value you are seeing.

To work around this limitation, you can consider using a different approach for data validation. For example, you could implement a custom validation logic in your application to check if the entered value is below 10,000,000,000, rather than relying solely on the built-in data validation feature.

Here is a sample code snippet that demonstrates how you might implement this custom validation:

var workbook = new Workbook("DataValidationIssue.xlsx");
var worksheet = workbook.Worksheets[0];
var cell = worksheet.Cells["A1"]; // Assuming you want to validate cell A1

// Example of setting a value
cell.PutValue(10000000001); // Enter a value greater than 10 billion

// Custom validation logic
if (cell.Value is double value && value >= 10000000000)
{
    Console.WriteLine("Value exceeds the maximum limit of 10,000,000,000.");
}
else
{
    Console.WriteLine("Value is within the acceptable range.");
}

This way, you can ensure that the values entered into the cell adhere to your specified limits, even if the built-in validation does not support such large numbers.

If you need further assistance or have additional questions, feel free to ask!

@perfectxl
We have reproduced the issue and opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-57799

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

@perfectxl,

This is to inform you that your issue (Ticket ID: “CELLSNET-57799”) has been resolved. The fix will be included in the next release (Aspose.Cells v25.2) scheduled for release this week. We will let you know when the next version is released.

The issues you have found earlier (filed as CELLSNET-57799) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi