Document Solutions for Excel, Java Edition | Document Solutions
Features / Data Validations / Add Validations
In This Topic
    Add Validations
    In This Topic

    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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    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.

    Java
    Copy Code 
    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.

    Java
    Copy Code 
    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.

    Java
    Copy Code 
    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.

    Java
    Copy Code 
    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);