Microsoft Excel provides some good features to auto-filter or validate worksheet data.
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. Aspose.Cells fully supports Microsoft Excel’s data validation and autofilter features. This article explains how to use the features in Microsoft Excel, and how to code them using Aspose.Cells.
Data Validation Types and Execution
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.
- 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 is displayed.
Click the Settings tab and enter settings as shown below.
Data validation settings
Data Validation with Aspose.Cells
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, each Worksheet class has a Validations object which represents a collection of Validation objects. To set up validation, set some of the Validation class' properties:
- 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.
Types of Data Validation
Data validation allows you to build business rules into each cell so that incorrect entries result in error messages. Business rules are the policies and procedures that govern how a business operates. Aspose.Cells supports all the important types of data validation.
The ValidationType enumeration has the following members:
|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.|
Programming Sample: 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 WHOLE_NUMBER validation type. The example creates the same data validation using Aspose.Cells that we created using Microsoft Excel above.
Programming Sample: Decimal Data Validation
With this type of validation, the user can enter decimal numbers into the validated cells. In the example, the user is restricted to enter decimal value only and the validation area is A1:A10.
Programming Sample: 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. 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.setInCellDropDown property to true.
Programming Sample: 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.
Programming Samples: 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.
Programming Samples: 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.GetValidationValue() 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:
Check if validation in a 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.InCellDropDown property can be used to test this. Following sample code demonstrates the usage of this property. The 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.AddArea(CellArea cellArea), 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.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge) 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.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge) method to add new CellArea to existing Validation.
The source and output excel files are attached for reference.