GetValidationValue incorrect when using custom Data Validation formula

Aspose.Cells Product Family
1

Hi,

I have some custom Data Validation formulas in Excel workbook (see attached TestCase.xlsx) and tried to use cell.GetValidationValue() to check if the cell input is valid or not based on the formula. I seem to be getting incorrect values compared to Excel’s behaviour.

For example, the cell NumberSheet!B1 has the custom Data Validation formula =ISTEXT($B$1), which should result in GetValidationValue() returning false because the cell contains a number (123). However, upon testing I found cell.GetValidationValue() returned true.

Another example, the cell RefSheet!B1 has the custom Data Validation formula =$F$1, which should result in GetValidationValue() returning false because cell F1 is FALSE. However, upon testing I found cell.GetValidationValue() returned true again.

A final example, the cell BlankSheet!B4 (not B1!) has the custom Data Validation formula =TRUE, which should result in GetValidationValue() returning true because the formula is always true. However, upon testing I found cell.GetValidationValue() returned false.

The code I used to test is:


var book = new Workbook(“TestCase.xlsx”);
book.CalculateFormula();

Worksheet sheetBla = book.Worksheets[“BlankSheet”];
Worksheet sheetNum = book.Worksheets[“NumberSheet”];
Worksheet sheetRef = book.Worksheets[“RefSheet”];

bool isValid;

Cell sBb1 = sheetBla.Cells[“B1”]; isValid = sBb1.GetValidationValue(); // False Ignore Blank
Cell sBb2 = sheetBla.Cells[“B2”]; isValid = sBb2.GetValidationValue(); // False Do not ignore blank
Cell sBb3 = sheetBla.Cells[“B3”]; isValid = sBb3.GetValidationValue(); // True Ignore Blank
Cell sBb4 = sheetBla.Cells[“B4”]; isValid = sBb4.GetValidationValue(); // True Do not ignore blank

Cell sNb1 = sheetNum.Cells[“B1”]; isValid = sNb1.GetValidationValue(); // False Ignore Blank
Cell sNb2 = sheetNum.Cells[“B2”]; isValid = sNb2.GetValidationValue(); // False Do not ignore blank
Cell sNb3 = sheetNum.Cells[“B3”]; isValid = sNb3.GetValidationValue(); // True Ignore Blank
Cell sNb4 = sheetNum.Cells[“B4”]; isValid = sNb4.GetValidationValue(); // True Do not ignore blank

Cell sRb1 = sheetRef.Cells[“B1”]; isValid = sRb1.GetValidationValue(); // False Ignore Blank
Cell sRb2 = sheetRef.Cells[“B2”]; isValid = sRb2.GetValidationValue(); // False Do not ignore blank
Cell sRb3 = sheetRef.Cells[“B3”]; isValid = sRb3.GetValidationValue(); // True Ignore Blank
Cell sRb4 = sheetRef.Cells[“B4”]; isValid = sRb4.GetValidationValue(); // True Do not ignore blank


Can you please take a look?

Thanks,
Lawrence.


2

Hi,


Thanks for providing us template file, sample code and details.

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 incorrect value when using custom data validation formula for some cells. I confirm all your mentioned issues as you described.

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

Once we have an update on it, we will let you know here.

Thank you.
3

Hi,


This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-45381”). Hopefully, we will be sharing the fix in the next few days after we have completed performing QA and incorporating other enhancements and fixes.

Thank you.
4
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

Aspose.Cells for .NET v17.5.5 (.NET 4.0) compiled in .NET Framework 4.0.

and let us know your feedback.
5

Hi,

Thanks for the quick response. It looks to be fixed now.

Thanks.

6
Hi,

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.
7

The issues you have found earlier (filed as CELLSNET-45381) have been fixed in Aspose.Cells for .NET 17.6.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.