Data Validation
Data Validation Types and Execution
Data validation is the ability to set rules pertaining to data entered on a worksheet. For example, use validation to ensure that a column labeled DATE contains only dates, or that another column contains only numbers. You could even ensure that a column labeled DATE contains only dates within a certain range. With data validation, you can control what is entered into cells in the worksheet.
Microsoft Excel supports a number of different types of data validation. Each type is used to control what type of data is entered into a cell, or cell range. Below, code snippets illustrate how to validate that:
- Numbers are whole, that is, that they don’t have a decimal part.
- Decimal numbers follow the right structure. The code example defines that a range of cells should have two decimal spaces.
- Values are restricted to a list of values. List validation defines a separate list of values that can be applied to a cell, or cell range.
- Dates fall within a specific range.
- A time is within a specific range.
- A text is within a given character length.
Data Validation with Microsoft Excel
To create validations using Microsoft Excel:
- In a worksheet, select the cells to which you want to apply validation.
- From the Data menu, select Validation. The validation dialog will be displayed.
- Click the Settings tab and enter settings.
Data Validation with Aspose.Cells for Python Excel Library
Data validation is a powerful feature for validating the information entered into worksheets. With data validation, developers can provide users with a list of choices, restrict data entries to a specific type or size, etc. In Aspose.Cells for Python via .NET, each Worksheet class has a validations property which represents a collection of Validation objects. To set up validation, set some of the Validation class' properties as follows:
- type – represents the validation type, which may be specified by using one of the predefined values in the ValidationType enumeration.
- Operator – represents the operator to be used in the validation, which may be specified by using one of the predefined values in the OperatorType enumeration.
- formula1 – represents the value or expression associated with the first part of the data validation.
- formula2 – represents the value or expression associated with the second part of the data validation.
When the Validation object’s properties have been configured, developers can use the CellArea structure to store information about the cell range that will be validated using the created validation.
Types of Data Validation
The ValidationType enumeration has the following members:
| Member Name | Description |
|---|---|
| ANY_VALUE | Denotes a value of any type. |
| WHOLE_NUMBER | Denotes validation type for whole numbers. |
| DECIMAL | Denotes validation type for decimal numbers. |
| LIST | Denotes validation type for drop-down list. |
| DATE | Denotes validation type for dates. |
| TIME | Denotes validation type for time. |
| TEXT_LENGTH | Denotes validation type for the length of the text. |
| CUSTOM | Denotes custom validation type. |
Whole Number Data Validation
With this type of validation, users can enter only whole numbers within a specified range into the validated cells. The code examples that follow show how to implement the WholeNumber validation type. The example creates the same data validation using Aspose.Cells for Python via .NET that we created using Microsoft Excel above.
List Data Validation
This type of validation allows the user to enter values from a drop-down list. It provides a list: a series of rows that contain data. In the example, a second worksheet is added to hold the list source. Users can only select values from the list. The validation area is the cell range A1:A5 in the first worksheet.
It is important here that you set the Validation.in_cell_drop_down property to true.
Date Data Validation
With this type of validation, users enter date values within a specified range, or meeting specific criteria, into the validated cells. In the example, the user is restricted to enter dates between 1970 to 1999. Here, the validation area is the B1 cell.
Time Data Validation
With this type of validation, users can enter times within a specified range, or meeting some criteria, into the validated cells. In the example, the user is restricted to enter times between 09:00 to 11:30 AM. Here, the validation area is the B1 cell.
Text Length Data Validation
With this type of validation, users can enter text values of a specified length into the validated cells. In the example, the user is restricted to enter string values with no more than 5 characters. The validation area is the B1 cell.
Data Validation Rules
When data validations are implemented, then validation can be checked by assigning different values in the cells. Cell.get_validation_value() can be used to fetch the validation result. The following example demonstrates this feature with different values. The sample file can be downloaded from the following link for testing:
sampleDataValidationRules.xlsx
Check if validation in cell is dropdown
As we have seen there are many types of validations that can be implemented within a cell. If you want to check whether validation is dropdown or not, Validation.in_cell_drop_down property can be used to test this. The following sample code demonstrates the usage of this property. A sample file for testing can be downloaded from the following link:
Add CellArea to existing Validation
There might be cases where you might want to add CellArea to existing Validation. When you add CellArea using Validation.add_area(cell_area), Aspose.Cells checks all existing areas to see if the new area already exists. If the file has a large number of validations, this takes a performance hit. To overcome this, the API provides the Validation.add_area(cell_area, check_intersection, check_edge) method. The checkIntersection parameter indicates whether to check the intersection of a given area with existing validation areas. Setting it to false will disable the checking of other areas. The checkEdge parameter indicates whether to check the applied areas. If the new area becomes the top-left area, internal settings are rebuilt. If you are sure that the new area is not the top-left area, you may set this parameter as false.
The following code snippet demonstrates the use of the Validation.add_area(cell_area, check_intersection, check_edge) method to add new CellArea to existing Validation.
The source and output excel files are attached for reference.