Hi, I just received the response below to this topic which I think has been subsequently withdrawn (by babar.raza). The response below describes the behaviour of the Excel user interface which allows existing values to remain when contradictory data validation rules are subsequently applied to the target cells. It does not however reflect the behaviour of Excel automation, whereby the Validation.Value method in Excel will return False indicating that the existing value fails validation. Whether it's Aspose or Excel automation, the code will not know which came first - the value or the validation rule. If this is intended behaviour, then GetValidationValue will have no use because it will always return True. In fact, I have tested adding the cell value "Hello" AFTER the validation rule is applied to the cell, and GetValidationValue still returns True.
If you disagree with my arguments above then I would appreciate if you could illustrate a scenario whereby GetValidationValue returns False. I haven't managed to get a False value yet.
Thanks!
Hi Glynn,
Thank you for contacting Aspose support.
This is the default behavior of MS Excel application that when a cell has any value, and you apply a data validation rule to it, MS Excel does not complain about that value in that particular cell. Means, MS Excel accepts any value as valid entry if it already exists at the time of applying data validation rule. Please check the attached spreadsheet, A1 has "hello" string in it with data validation rule of a whole number between 0 - 9.
Aspose.Cells follow MS Excel's guideline and recommendation in it's implementation therefore the presented behavior is correct in Aspose.Cells perspective.
Hi Glynn,
Hi all,
Are you able to provide an update on this issue? If you expect the issue to remain for some time, then I will need to code a workaround. Please let me know.
Thanks,
Glynn
Hi,
Hi,
Hi Amjad,
Sorry for the delay in responding. I tested the fix this morning (in version 8.2.2) and the problem appears to be fixed. I tested integer and custom validation and both worked fine.
Thanks for your help,
Glynn
Hi,
Hi Amjad,
Unfortunately there is still a problem. A call to GetValidationValue returns NullReferenceException within the following example:
Within Excel, in a new workbook, enter these values to the cells in range Sheet2!A1:A3: A, B, C
Define a name to the range Sheet2!A1:A3, for example "List1"
Add a validation rule to cell Sheet1!A1 - type List, Source = List1.
Cell Sheet1!A1 will now only allow the values A, B, C from it's drop-down list.
In Aspose.Cells, call GetValidationValue() for Sheet1!A1. It will return a NullReferenceException, irrespective of whether the cell contains a value.
Please advise... thanks,
Glynn
Hi,
sample code with a simple template file (attached) that is created manually in MS Excel as
per your instructions.
I got the error on the last line of code:
Object reference not set to an instance of an object.
Sample code:
Workbook workbook = new Workbook("e:\\test2\\Validationcheck1.xlsx");
//Access Cell A1
Cell cell = workbook.Worksheets[0].Cells["A1"];
//Check if number satisfies the Data Validation rule applied on this cell
Console.WriteLine("Whether a Valid Value for this Cell: " + cell.GetValidationValue()); //Error
Thanks for confirming the problem. I would appreciate an update on this issue as deployment of our solution is on hold until this problem is resolved. Thanks!
Hi,
Hi,
Thanks for your using Aspose.Cells.
We have fixed this issue.
- CELLSNET-43122 - Cell.GetValidationValue() returns a NullReferenceException
Please download and try the latest fix: Aspose.Cells for .NET v8.2.2.2 and let us know your feedback.
Hi Shakeel,
I confirm that your latest version has resolved this particular issue. Thanks for your help.
Glynn
Hi Glynn,
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.
Shakeel,
Unfortunately there is still a problem. If the validation rule is of type 'list' and the list is long (in my case 600+ rows), GetValidationValue will return False for values that are towards the end of the list. You can test this by extending the previous example to contain many more rows (and extend the named range). I have done this in the attached xlsx. GteValidationValue will return false if you choose values further down the list.
I still therefore cannot deploy the solution. I would appreciate your help with this.
Regards,
Glynn
Hi,
Please could you update me on this issue. As I have said before, our deployment is on hold while we await a solution. Thanks.
Hi,
Hi,
Thanks for your using Aspose.Cells.
We have fixed this issue.
- CELLSNET-43122 - Cell.GetValidationValue() returns a NullReferenceException
Please download and try the latest fix: Aspose.Cells for .NET v8.2.2.3 and let us know your feedback.