Data Validation

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:

Data Validation with Microsoft Excel

To create validations using Microsoft Excel:

  1. In a worksheet, select the cells to which you want to apply validation.

  2. From the Data menu, select Validation. The validation dialog is displayed.

  3. Click the Settings tab and enter settings as shown below. 

    Data validation settings

todo:image_alt_text

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.

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

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:

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.

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(WholeNumberDataValidation.class) + "data/";
// Instantiating an Workbook object
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
// Accessing the Validations collection of the worksheet
Worksheet worksheet = worksheets.get(0);
// Applying the validation to a range of cells from A1 to B2 using the
// CellArea structure
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 0;
area.EndRow = 1;
area.EndColumn = 1;
ValidationCollection validations = worksheet.getValidations();
// Creating a Validation object
int index = validations.add(area);
Validation validation = validations.get(index);
// Setting the validation type to whole number
validation.setType(ValidationType.WHOLE_NUMBER);
// Setting the operator for validation to Between
validation.setOperator(OperatorType.BETWEEN);
// Setting the minimum value for the validation
validation.setFormula1("10");
// Setting the maximum value for the validation
validation.setFormula2("1000");
// Saving the Excel file
workbook.save(dataDir + "WNDValidation_out.xls");
// Print message
System.out.println("Process completed successfully");

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DecimalDataValidation.class) + "data/";
// Create a workbook object.
Workbook workbook = new Workbook();
// Create a worksheet and get the first worksheet.
Worksheet ExcelWorkSheet = workbook.getWorksheets().get(0);
// Specify the validation area of cells.
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 0;
area.EndRow = 9;
area.EndColumn = 0;
// Obtain the existing Validations collection.
ValidationCollection validations = ExcelWorkSheet.getValidations();
// Create a validation object adding to the collection list.
int index = validations.add(area);
Validation validation = validations.get(index);
// Set the validation type.
validation.setType(ValidationType.DECIMAL);
// Specify the operator.
validation.setOperator(OperatorType.BETWEEN);
// Set the lower and upper limits.
validation.setFormula1("10");
validation.setFormula2("1000");
// Set the error message.
validation.setErrorMessage("Please enter a valid integer or decimal number");
// Save the workbook.
workbook.save(dataDir + "DDValidation_out.xls");
// Print message
System.out.println("Process completed successfully");

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ListDataValidation.class) + "data/";
// Create a workbook object.
Workbook workbook = new Workbook();
// Get the first worksheet.
Worksheet ExcelWorkSheet = workbook.getWorksheets().get(0);
// Add a new worksheet and access it.
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet2 = workbook.getWorksheets().get(sheetIndex);
// Create a range with name in the second worksheet.
Range range = worksheet2.getCells().createRange(0, 4, 4, 4);
range.setName("MyRange");
// Fill different cells with data in the range.
range.get(0, 0).setValue("Blue");
range.get(1, 0).setValue("Red");
range.get(2, 0).setValue("Green");
range.get(3, 0).setValue("Yellow");
// Specify the validation area of cells.
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 0;
area.EndRow = 4;
area.EndColumn = 0;
// Obtain the existing Validations collection.
ValidationCollection validations = ExcelWorkSheet.getValidations();
// Create a validation object adding to the collection list.
int index = validations.add(area);
Validation validation = validations.get(index);
// Set the validation type.
validation.setType(ValidationType.LIST);
// Set the in cell drop down.
validation.setInCellDropDown(true);
// Set the formula1.
validation.setFormula1("=MyRange");
// Enable it to show error.
validation.setShowError(true);
// Set the alert type severity level.
validation.setAlertStyle(ValidationAlertType.STOP);
// Set the error title.
validation.setErrorTitle("Error");
// Set the error message.
validation.setErrorMessage("Please select a color from the list");
// Save the excel file.
workbook.save(dataDir + "LDValidation_out.xls");
// Print message
System.out.println("Process completed successfully");

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DateDataValidation.class) + "data/";
// Create a workbook.
Workbook workbook = new Workbook();
// Obtain the cells of the first worksheet.
Cells cells = workbook.getWorksheets().get(0).getCells();
// Put a string value into the A1 cell.
cells.get("A1").setValue("Please enter Date b/w 1/1/1970 and 12/31/1999");
// Wrap the text.
Style style = cells.get("A1").getStyle();
style.setTextWrapped(true);
cells.get("A1").setStyle(style);
// Set row height and column width for the cells.
cells.setRowHeight(0, 31);
cells.setColumnWidth(0, 35);
// Set a collection of CellArea which contains the data validation
// settings.
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 1;
area.EndRow = 0;
area.EndColumn = 1;
// Get the validations collection.
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations();
// Add a new validation.
int i = validations.add(area);
Validation validation = validations.get(i);
// Set the data validation type.
validation.setType(ValidationType.DATE);
// Set the operator for the data validation
validation.setOperator(OperatorType.BETWEEN);
// Set the value or expression associated with the data validation.
validation.setFormula1("1/1/1970");
// The value or expression associated with the second part of the data
// validation.
validation.setFormula2("12/31/1999");
// Enable the error.
validation.setShowError(true);
// Set the validation alert style.
validation.setAlertStyle(ValidationAlertType.STOP);
// Set the title of the data-validation error dialog box
validation.setErrorTitle("Date Error");
// Set the data validation error message.
validation.setErrorMessage("Enter a Valid Date");
// Set and enable the data validation input message.
validation.setInputMessage("Date Validation Type");
validation.setIgnoreBlank(true);
validation.setShowInput(true);
// Save the excel file.
workbook.save(dataDir + "DDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003);
// Print message
System.out.println("Process completed successfully");

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(TimeDataValidation.class) + "data/";
// Create a workbook.
Workbook workbook = new Workbook();
// Obtain the cells of the first worksheet.
Cells cells = workbook.getWorksheets().get(0).getCells();
// Put a string value into A1 cell.
cells.get("A1").setValue("Please enter Time b/w 09:00 and 11:30 'o Clock");
// Wrap the text.
Style style = cells.get("A1").getStyle();
style.setTextWrapped(true);
cells.get("A1").setStyle(style);
// Set row height and column width for the cells.
cells.setRowHeight(0, 31);
cells.setColumnWidth(0, 35);
// Set a collection of CellArea which contains the data validation
// settings.
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 1;
area.EndRow = 0;
area.EndColumn = 1;
// Get the validations collection.
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations();
// Add a new validation.
int i = validations.add(area);
Validation validation = validations.get(i);
// Set the data validation type.
validation.setType(ValidationType.TIME);
// Set the operator for the data validation
validation.setOperator(OperatorType.BETWEEN);
// Set the value or expression associated with the data validation.
validation.setFormula1("09:00");
// The value or expression associated with the second part of the data
// validation.
validation.setFormula2("11:30");
// Enable the error.
validation.setShowError(true);
// Set the validation alert style.
validation.setAlertStyle(ValidationAlertType.INFORMATION);
// Set the title of the data-validation error dialog box.
validation.setErrorTitle("Time Error");
// Set the data validation error message.
validation.setErrorMessage("Enter a Valid Time");
// Set and enable the data validation input message.
validation.setInputMessage("Time Validation Type");
validation.setIgnoreBlank(true);
validation.setShowInput(true);
// Save the excel file.
workbook.save(dataDir + "TDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003);
// Print message
System.out.println("Process completed successfully");

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(TextLengthDataValidation.class) + "data/";
// Create a new workbook.
Workbook workbook = new Workbook();
// Obtain the cells of the first worksheet.
Cells cells = workbook.getWorksheets().get(0).getCells();
// Put a string value into A1 cell.
cells.get("A1").setValue("Please enter a string not more than 5 chars");
// Wrap the text.
Style style = cells.get("A1").getStyle();
style.setTextWrapped(true);
cells.get("A1").setStyle(style);
// Set row height and column width for the cells.
cells.setRowHeight(0, 31);
cells.setColumnWidth(0, 35);
// Set a collection of CellArea which contains the data validation
// settings.
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 1;
area.EndRow = 0;
area.EndColumn = 1;
// Get the validations collection.
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations();
// Add a new validation.
int i = validations.add(area);
Validation validation = validations.get(i);
// Set the data validation type.
validation.setType(ValidationType.TEXT_LENGTH);
// Set the operator for the data validation.
validation.setOperator(OperatorType.LESS_OR_EQUAL);
// Set the value or expression associated with the data validation.
validation.setFormula1("5");
// Enable the error.
validation.setShowError(true);
// Set the validation alert style.
validation.setAlertStyle(ValidationAlertType.WARNING);
// Set the title of the data-validation error dialog box.
validation.setErrorTitle("Text Length Error");
// Set the data validation error message.
validation.setErrorMessage(" Enter a Valid String");
// Set and enable the data validation input message.
validation.setInputMessage("TextLength Validation Type");
validation.setIgnoreBlank(true);
validation.setShowInput(true);
// Save the excel file.
workbook.save(dataDir + "TLDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003);
// Print message
System.out.println("Process completed successfully");

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:

SampleDataValidationRules.xlsx

Sample Code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Instantiate the workbook from sample Excel file
Workbook workbook = new Workbook(srcDir + "sampleDataValidationRules.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
/*
* Access Cell C1. Cell C1 has the Decimal Validation applied on it.It can take only the values Between 10 and 20
*/
Cell cell = worksheet.getCells().get("C1");
// Enter 3 inside this cell. Since it is not between 10 and 20, it should fail the validation
cell.putValue(3);
// Check if number 3 satisfies the Data Validation rule applied on this cell
System.out.println("Is 3 a Valid Value for this Cell: " + cell.getValidationValue());
// Enter 15 inside this cell. Since it is between 10 and 20, it should succeed the validation
cell.putValue(15);
// Check if number 15 satisfies the Data Validation rule applied on this cell
System.out.println("Is 15 a Valid Value for this Cell: " + cell.getValidationValue());
// Enter 30 inside this cell. Since it is not between 10 and 20, it should fail the validation again
cell.putValue(30);
// Check if number 30 satisfies the Data Validation rule applied on this cell
System.out.println("Is 30 a Valid Value for this Cell: " + cell.getValidationValue());
// Enter large number 12345678901 inside this cell
// Since it is not between 1 and 999999999999, it should pass the validation again
Cell cell2 = worksheet.getCells().get("D1");
cell2.putValue(12345678901l);
// Check if number 12345678901 satisfies the Data Validation rule applied on this cell
System.out.println("Is 12345678901 a Valid Value for this Cell: " + cell2.getValidationValue());

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:

sampleDataValidationRules.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
Workbook book = new Workbook(srcDir + "sampleValidation.xlsx");
Worksheet sheet = book.getWorksheets().get("Sheet1");
Cells cells = sheet.getCells();
Cell a2 = cells.get("A2");
Validation va2 = a2.getValidation();
if(va2.getInCellDropDown()) {
System.out.println("A2 is a dropdown");
} else {
System.out.println("A2 is NOT a dropdown");
}
Cell b2 = cells.get("B2");
Validation vb2 = b2.getValidation();
if(vb2.getInCellDropDown()) {
System.out.println("B2 is a dropdown");
} else {
System.out.println("B2 is NOT a dropdown");
}
Cell c2 = cells.get("C2");
Validation vc2 = c2.getValidation();
if(vc2.getInCellDropDown()) {
System.out.println("C2 is a dropdown");
} else {
System.out.println("C2 is NOT a dropdown");
}

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the directories.
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
Workbook workbook = new Workbook(sourceDir + "ValidationsSample.xlsx");
// Access first worksheet.
Worksheet worksheet = workbook.getWorksheets().get(0);
// Accessing the Validations collection of the worksheet
Validation validation = worksheet.getValidations().get(0);
// Create your cell area.
CellArea cellArea = CellArea.createCellArea("D5", "E7");
// Adding the cell area to Validation
validation.addArea(cellArea, false, false);
// Save the output workbook.
workbook.save(outputDir + "ValidationsSample_out.xlsx");

The source and output excel files are attached for reference.

Source File

Output File

Advance topics

  • Get Cell Validation in ODS Files
  • Get Validation Applied on a Cell
  • Verify that Cell Value Satisfies Data Validation Rules