Verify that cell value satisfies data validation rules in .NET

Hi,

I observed the issue as you mentioned. I used the following sample code to verify the cell value's data validation but it returns true.
e.g
Sample code:

//Instantiate a new workbook
Workbook workbook = new Workbook();

//Accessing the ValidationCollection collection of the worksheet
ValidationCollection validations = workbook.Worksheets[0].Validations;
//Creating a Validation object
Validation validation = validations[validations.Add()];
//Setting the validation type to whole number
validation.Type = Aspose.Cells.ValidationType.WholeNumber;
//Setting the operator for validation to Between
validation.Operator = OperatorType.Between;
//Setting the minimum value for the validation
validation.Formula1 = "0";
//Setting the maximum value for the validation
validation.Formula2 = "10";
validation.ErrorMessage = "Error....";
//Applying the validation to a range of cells from A1 to B2 using the
//CellArea structure
CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;
//Adding the cell area to Validation
validation.AreaList.Add(area);


//Access Cell A1
//It can take only the values Between 0 and 10
Cell cell = workbook.Worksheets[0].Cells["A1"];

//Enter "hello" inside this cell
//Since it is not between 0 and 10, it should fail the validation
cell.PutValue("hello");

//Check if number satisfies the Data Validation rule applied on this cell
Console.WriteLine("Is hello a Valid Value for this Cell: " + cell.GetValidationValue()); //True - Not Ok

I have reopened your issue "CELLSNET-42935" now. We will look into it soon.

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

Thank you.

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,


I have withdrawn my response upon discussing the matter internally. We have to look further into the situation to provide you with a valid justification for the said behavior. As narrated by Amjad, the relevant ticket has been re-opened, and we will shortly get back to you with updates in this regard.

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,


I am afraid, there is no update on your issue at the moment. Our concerned developers are on their holidays (they will come back after 3-5 days or so). Once they come back, we will schedule it and figure your issue out, hopefully, it will be fixed soon.

Keep in touch.

Thank you.

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.2.1.4

We have fixed your issue now.

Let us know your feedback.

Thanks,

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,


Thanks for your feedback.

Good to know that your issue is resolved by the fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.

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,


Thanks for providing details.

After an initial test, I observed the issue as you mentioned by using the following
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.


e.g


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

I have logged a separate ticket with an id "CELLSNET-43122" for your issue. We will look into it soon.

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

Thank you.

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,


I have checked status of your issue “CELLSNET-43122”, it is still Open. We have logged your concerns against your issue into our database. We have also asked the concerned developer to update on it or provide an eta for it.

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

Thank you.

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,


Thanks for providing us template file.

After an initial test, as I have tested the issue with your newly attached file and found the issue as you pointed out. The issue is still there if we have a long List for data validation and we choose a bottom value (initial values work fine though). I have reopened the issue “CELLSNET-43122”. We will look into it to figure it out soon.

We are sorry for any inconvenience caused!

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,


I am afraid, your issue is still in process, it is not fixed yet. I have asked the relevant developer to update on it or provide an eta (if possible).

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

Thank you.

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.