Allow whole number Data Validation with formula in Excel not working in .NET

Aspose.Cells Product Family
1

Hi,

I’ve set up a data validation (cell B3) like this:

Allow: Whole number
Data: between
Minimum: =D3
Maximum: 100

As you can see, the minimum is a formula reference. When I call cell.GetValidationValue(), it always returns true, even if I enter an invalid value. I’ve attached an example:

Test.zip (6.3 KB)

If you type -60 into cell B3 in Excel you get an error, but if I enter the same value using the code below, cell.GetValidationValue() returns true instead of false.

var book = new Workbook("Test.xlsx");
book.CalculateFormula();

Worksheet sheetBla = book.Worksheets["Sheet1"];

Cell age = sheetBla.Cells["B3"];
age.PutValue("-60", true, false);
book.CalculateFormula();

bool isValid = age.GetValidationValue(); // isValid is true.

Can you please take a look.

Thanks.

2

@llawryy,

Thanks for the template file and sample code.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample code with your template file. I found that Cell.GetValidationValue() returns true, even if we enter an invalid value into the cell.

I have logged a ticket with an id “CELLSNET-45532” for your issue. We will look into it to figure it out soon.

Thank you.

3

@llawryy

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.

4

Hi,

There is still a problem even with the update. If the Data Validation formula references a different worksheet, then the cell.GetValidationValue() method returns invalid values.

For example, in the Test1.xslx attached, please check cell Sheet1!B4. The valid values are between 16 and 100.
Cell.GetValidationValue() returns

  • false for numbers < 0 (e.g. -1, -60)
  • true for numbers between 0 and 100
  • false for numbers > 100

I have another Test2.xslx attached, please check Sheet2!B1. The valid values are between 50 and 100.
Cell.GetValidationValue() returns

  • true for numbers < 0
  • true for numbers between 0 and 100
  • true for numbers > 100

NewTests.zip (14.6 KB)

5

@llawryy,

Thanks for the providing template files and details.

I confirmed the issue is still there if the data validation formula references a different worksheet, the cell.GetValidationValue() method may return invalid values. I evaluated the issues (using your newly attached files in the archive (NewTests.zip)) with the following cases:
1)
e.g
Sample code:

     var book = new Workbook("e:\\test2\\test1.xlsx");
    book.CalculateFormula();

    Worksheet sheetBla = book.Worksheets["Sheet1"];

    Cell age = sheetBla.Cells["B4"];
    //age.PutValue("-60", true, false);// - Ok
    //age.PutValue(-60);// - Ok
    **age.PutValue(15);//Not Ok**
    //age.PutValue(101);// - Ok

    book.CalculateFormula();

    bool isValid = age.GetValidationValue(); // isValid is true

e.g
Sample code:

    var book = new Workbook("e:\\test2\\Test2.xlsx");
    book.CalculateFormula();

    Worksheet sheetBla = book.Worksheets["Sheet2"];

    Cell age = sheetBla.Cells["B1"];
    **//age.PutValue("-60", true, false);//Not Ok**

** // age.PutValue(-60);//Not Ok**
** // age.PutValue(15);//Not Ok**
** age.PutValue(101);//Not Ok**

    book.CalculateFormula();

    bool isValid = age.GetValidationValue(); // isValid is true

I have reopened your issue “CELLSNET-45532” now. Please spare us little time as we will look into the issues and fix these issues.

Thank you.

6

@llawryy,

We have fixed your issue now as we did evaluate your mentioned cases. We will soon provide you the fix after performing QA and incorporating other enhancements and fixes.

Thank you.

7

@llawryy,

Please try the latest version/fix: Aspose.Cells for .NET v17.7.7:

Your issue should be fixed in it.

Let us know your feedback.

Thank you.

8

Hi support,

I’ve tested the latest version and the issue is now fixed.

Thanks for the quick response.

Lawry.

9

@llawryy

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

10

The issues you have found earlier (filed as CELLSNET-45532) have been fixed in latest version of Aspose.Cells for .NET (Download | NuGet).