Add Validations

You can apply data validation to restrict and verify the data entered in a single cell or a range of cells in a worksheet.

Only one validation rule should be applied for a cell. One cell cannot have multiple validation rules applied to it.

In case you try to validate a cell that already has a validation rule, an exception will be thrown.

If you want to know whether a cell range already contains the validation rule, you can use the methods of the IRange interface. If all the cells in a range possess the same validation rule applied to them, you can check it using the methods of the IRange interface.

Shared below is a list of data validations operations that can be implemented in DsExcel Java.

Add whole number validation

You can validate your data and ensure users add only whole numbers in cells or a range of cells by applying the whole number validation in a worksheet.

Refer to the following example code to add whole number validation.

// Add whole number validation
worksheet.getRange("D2:E5").getValidation().add(ValidationType.Whole, ValidationAlertStyle.Stop,
        ValidationOperator.Between, 3, 8);
IValidation validation = worksheet.getRange("D2:E5").getValidation();
validation.setIgnoreBlank(true);
validation.setInputTitle("Tips");
validation.setInputMessage("Input a value between 3 and 8, please");
validation.setErrorTitle("Error");
validation.setErrorMessage("input value does not between 3 and 8");
validation.setShowInputMessage(true);
validation.setShowError(true);

Add decimal validation

You can validate your data and ensure users add only decimal numbers in cells or a range of cells by applying the decimal validation in a worksheet.

Refer to the following example code to add decimal validation.

// Add decimal validation 
worksheet.getRange("C2:E4").getValidation().add(ValidationType.Decimal, ValidationAlertStyle.Stop,
        ValidationOperator.Between, 111.5, 72.3);

Add list validation

You can also validate lists inserted in cells or a range of cells by applying the list validation in your worksheet .

Refer to the following example code to add list validation.

worksheet.getRange("A1").setValue("aaa");
worksheet.getRange("A2").setValue("bbb");
worksheet.getRange("A3").setValue("ccc");

// Use cell reference.
worksheet.getRange("C2:E4").getValidation().add(ValidationType.List, ValidationAlertStyle.Stop,
        ValidationOperator.Between, "=$a$1:$a$3", null);

// Or use string.
// this._worksheet.getRange("C2:E4").getValidation().add(ValidationType.List,ValidationAlertStyle.Stop,
// ValidationOperator.Between, "aaa, bbb, ccc",null);

IValidation validation = worksheet.getRange("C2:E4").getValidation();
validation.setInCellDropdown(true);

Add date validation

You can validate data entered in date format in cells or a range of cells by applying the date validation in a worksheet.

Refer to the following example code to add date validation.

worksheet.getRange("C2:E4").getValidation().add(ValidationType.Date, ValidationAlertStyle.Stop,
        ValidationOperator.Between, new GregorianCalendar(2015, 11, 13), new GregorianCalendar(2015, 11, 18));

Add time validation

You can validate the time entered in cells or a range of cells by applying the time validation in a worksheet.

Refer to the following example code to add time validation.

Calendar time1 = new GregorianCalendar(1899, 11, 30, 13, 30, 0);
Calendar time2 = new GregorianCalendar(1899, 11, 30, 18, 30, 0);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("hh:mm:ss");
worksheet.getRange("C2:E4").getValidation().add(ValidationType.Time, ValidationAlertStyle.Stop,
        ValidationOperator.Between, simpleDateFormat.format(time1.getTime()),
        simpleDateFormat.format(time2.getTime()));

Add text length validation

You can validate the length of the text entered in cells or a range of cells by applying the text length validation in a worksheet.

Refer to the following example code to add text length validation.

worksheet.getRange("C2:E4").getValidation().add(ValidationType.TextLength, ValidationAlertStyle.Stop,
        ValidationOperator.Between, 2, 3);

Add custom validation

You can add a custom validation rule to validate data in a worksheet by applying custom validation.

Refer to the following example code to add custom validation.

worksheet.getRange("A2").setValue(1);
worksheet.getRange("A3").setValue(2);
worksheet.getRange("C2").setValue(1);

// While using custom validation, validationOperator and formula2 parameters will be ignored even if you have provided.
worksheet.getRange("A2:A3").getValidation().add(ValidationType.Custom, ValidationAlertStyle.Information,
        ValidationOperator.Between, "=C2", null);

Add whole number validation
Add decimal validation
Add list validation
Add date validation
Add time validation
Add text length validation
Add custom validation