Verify that Cell Value Satisfies Data Validation Rules

Microsoft Excel allows users to add data validation rules to cells. For example, a decimal validation specifies that only numbers between 10 and 20 can be entered. If a user enters a different number. Microsoft Excel shows an error message and prompts them to enter a number in the correct range. If you copy and paste a number, say 3, into the cell, Excel does not run a validation check or show an error message.

Sometimes, it is necessary to verify whether a value satisfies the data validation rules applied to the cell programmatically. In the case above, for example, the entry should fail.

Introduction

Aspose.Cells for Python via .NET provides the Cell.get_validation_value() method to validate cell values programmatically. If the value in a cell does not satisfy the data validation rule applied to that cell, it returns False, else True.

The following sample code illustrates how the Cell.get_validation_value() method works. First, it enters the value 3 into C1. Because this does not satisfy the data validation rule, the Cell.get_validation_value() method returns False. Then, it enters the value 15 into C1. Because this value satisfies the data validation rule, the Cell.get_validation_value() method returns True. Similarly, it returns False for value 30.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Instantiate the workbook from sample Excel file
workbook = Workbook("sample.xlsx")
#  Access the first worksheet
worksheet = workbook.worksheets[0]
#  Access Cell C1
#  Cell C1 has the Decimal Validation applied on it.
#  It can take only the values Between 10 and 20
cell = worksheet.cells.get("C1")
#  Enter 3 inside this cell
#  Since it is not between 10 and 20, it should fail the validation
cell.put_value(3)
#  Check if number 3 satisfies the Data Validation rule applied on this cell
print("Is 3 a Valid Value for this Cell: "  + str(cell.get_validation_value()))
#  Enter 15 inside this cell
#  Since it is between 10 and 20, it should succeed the validation
cell.put_value(15)
#  Check if number 15 satisfies the Data Validation rule applied on this cell
print("Is 15 a Valid Value for this Cell: "  + str(cell.get_validation_value()))
#  Enter 30 inside this cell
#  Since it is not between 10 and 20, it should fail the validation again
cell.put_value(30)
#  Check if number 30 satisfies the Data Validation rule applied on this cell
print("Is 30 a Valid Value for this Cell: "  + str(cell.get_validation_value()))

Output

 Is 3 a Valid Value for this Cell: False

Is 15 a Valid Value for this Cell: True

Is 30 a Valid Value for this Cell: False