Method Details

• add

  void add(ValidationType type)

  Adds data validation to the specified range.

  Uses the specified ValidationType to define the validation rule for the range associated with this IValidation object.

  
   IValidation validation = worksheet.getRange("A1:A3").getValidation();
   validation.add(ValidationType.None);
   

  Parameters:

  type - The validation type to add. Must not be null.

• add

  void add(ValidationType type, ValidationAlertStyle alertStyle, ValidationOperator validationOperator, Object formula1, Object formula2)

  Adds data validation to the specified range.

  Uses the specified ValidationType, ValidationAlertStyle, and ValidationOperator to define the validation rule for the range associated with this IValidation object.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   

  Parameters:

  type - The validation type. Must not be null.

  alertStyle - The validation alert style.

  validationOperator - The data validation operator.

  formula1 - The first part of the data validation formula.

  formula2 - The second part of the data validation formula when validationOperator is ValidationOperator.Between or ValidationOperator.NotBetween; otherwise, this argument is ignored.

  Throws:

  IllegalArgumentException - if formula1 or formula2 is not supported for the specified validation rule, or if type is ValidationType.Custom and formula1 is null.

• delete

  void delete()

  Deletes the object.

  Removes the existing data validation rule applied to the associated cell or range.

  
   IValidation validation = worksheet.getRange("A1:A2").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.delete();
   

• getAlertStyle

  ValidationAlertStyle getAlertStyle()

  Gets the ValidationAlertStyle value that specifies the data validation alert style.

  Returns the ValidationAlertStyle that specifies which icon is shown in the validation error message box when invalid data is entered for the range.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   ValidationAlertStyle alertStyle = validation.getAlertStyle();
   

  Returns:

  The ValidationAlertStyle value that specifies the data validation alert style.

• setAlertStyle

  void setAlertStyle(ValidationAlertStyle value)

  Sets the ValidationAlertStyle value that specifies the data validation alert style.

  The specified ValidationAlertStyle controls the icon shown in the validation error message box when invalid data is entered for the range.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setAlertStyle(ValidationAlertStyle.Warning);
   

  Parameters:

  value - The ValidationAlertStyle value that specifies the data validation alert style. Must not be null.

• getErrorMessage

  String getErrorMessage()

  Gets the data validation error message.

  Use this method to get the message associated with the validation rule for the range. The message can be configured with setErrorMessage(String) and is used by the data validation error alert.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setErrorMessage("Input a value between 1 and 10.");
   String errorMessage = validation.getErrorMessage();
   

  Returns:

  The data validation error message.

• setErrorMessage

  void setErrorMessage(String value)

  Sets the data validation error message.

  Use this property to configure the message associated with the validation rule for the range.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setErrorMessage("Input a value between 1 and 10.");
   String errorMessage = validation.getErrorMessage();
   

  Parameters:

  value - The data validation error message.

• getErrorTitle

  String getErrorTitle()

  Gets the title of the data-validation error dialog box.

  This title is shown in the error alert when a user enters invalid data and error alerts are enabled. Use setErrorTitle(String) to change the title text displayed in the dialog box.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setErrorTitle("Invalid Number");
   String errorTitle = validation.getErrorTitle();
   

  Returns:

  The title of the data-validation error dialog box, or null if no title has been set.

• setErrorTitle

  void setErrorTitle(String value)

  Sets the title of the data-validation error dialog box.

  Use this property to configure the title shown in the error alert when a user enters invalid data.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setErrorTitle("Invalid Number");
   String errorTitle = validation.getErrorTitle();
   

  Parameters:

  value - The title of the data-validation error dialog box, or null if no title has been set.

• getFormula1

  Object getFormula1()

  Gets the value or expression associated with the conditional format or data validation.

  The returned object represents the first part of the validation criteria and can be a constant value, a string value, a cell reference, or a formula.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "Yes,No,Maybe", null);
   Object formula1 = validation.getFormula1();
   

  Returns:

  The value or expression associated with the conditional format or data validation.

• setFormula1

  void setFormula1(Object value)

  Sets the value or expression associated with the conditional format or data validation.

  The assigned value represents the first part of the validation criteria and can be a constant value, a string value, a cell reference, or a formula.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "Yes,No,Maybe", null);
   validation.setFormula1(100);
   

  Parameters:

  value - The value or expression associated with the conditional format or data validation.

• getFormula2

  Object getFormula2()

  Gets the value or expression associated with the second part of a conditional format or data validation.

  The returned object represents the second comparison value for rules that use two operands, such as data validation rules that use ValidationOperator.Between or ValidationOperator.NotBetween. It can be a constant value, a string value, a cell reference, or a formula.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   Object formula2 = validation.getFormula2();
   

  Returns:

  The value or expression associated with the second part of the conditional format or data validation.

• setFormula2

  void setFormula2(Object value)

  Sets the value or expression associated with the second part of a conditional format or data validation.

  The assigned value represents the second comparison value for rules that use two operands, such as data validation rules that use ValidationOperator.Between or ValidationOperator.NotBetween. It can be a constant value, a string value, a cell reference, or a formula.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setFormula2(100);
   

  Parameters:

  value - The value or expression associated with the second part of the conditional format or data validation.

• getIgnoreBlank

  boolean getIgnoreBlank()

  Gets whether blank values are permitted by the data range validation.

  When this property returns true, empty cells in the validated range are allowed without violating the validation rule.

  
   IValidation validation = worksheet.getRange("A1:A3").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setIgnoreBlank(true);
   boolean ignoreBlank = validation.getIgnoreBlank();
   

  Returns:

  true if blank values are permitted by the data range validation; otherwise, false.

• setIgnoreBlank

  void setIgnoreBlank(boolean value)

  Sets whether blank values are permitted by the data range validation.

  When this property is set to true, empty cells in the validated range are allowed without violating the validation rule.

  
   IValidation validation = worksheet.getRange("A1:A3").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setIgnoreBlank(true);
   

  Parameters:

  value - true if blank values are permitted by the data range validation; otherwise, false.

• getIMEMode

  IMEModeType getIMEMode()

  Gets the input method editor (IME) mode for the data validation rule.

  This value describes the Japanese input rules applied to validated cells.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setIMEMode(IMEModeType.Hiragana);
   IMEModeType imeMode = validation.getIMEMode();
   

  Returns:

  The IME mode for the data validation rule.

• setIMEMode

  void setIMEMode(IMEModeType value)

  Sets the input method editor (IME) mode for the data validation rule.

  Use this property to specify the IMEModeType for a data validation rule.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setIMEMode(IMEModeType.Hiragana);
   

  Parameters:

  value - The IME mode to apply to the data validation rule.

• getInCellDropdown

  boolean getInCellDropdown()

  Gets whether data validation displays a drop-down list that contains acceptable values.

  Use this property to determine whether the validation rule shows an in-cell list for selecting allowed values. This setting is typically used with list validation.

  
   worksheet.getRange("A1").setValue("Open");
   worksheet.getRange("A2").setValue("Closed");
   IValidation validation = worksheet.getRange("C1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "=$A$1:$A$2", null);
   boolean inCellDropdown = validation.getInCellDropdown();
   

  Returns:

  true if data validation displays a drop-down list of acceptable values; otherwise, false.

• setInCellDropdown

  void setInCellDropdown(boolean value)

  Sets whether data validation displays a drop-down list that contains acceptable values.

  Use this property to set whether the validation rule shows an in-cell list for selecting allowed values.

  
   worksheet.getRange("A1").setValue("Open");
   worksheet.getRange("A2").setValue("Closed");
   IValidation validation = worksheet.getRange("C1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "=$A$1:$A$2", null);
   validation.setInCellDropdown(true);
   

  Parameters:

  value - true if data validation displays a drop-down list of acceptable values; otherwise, false.

• getInputMessage

  String getInputMessage()

  Gets the data validation input message.

  The input message is displayed when the user selects a cell in the validated range if input messages are enabled for the validation.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setInputMessage("Enter a whole number from 1 to 10.");
   String inputMessage = validation.getInputMessage();
   

  Returns:

  The text of the data validation input message. Returns null if no input message has been set.

• setInputMessage

  void setInputMessage(String value)

  Sets the data validation input message.

  The input message is displayed when the user selects a cell in the validated range if input messages are enabled for the validation.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setInputMessage("Enter a whole number from 1 to 10.");
   String inputMessage = validation.getInputMessage();
   

  Parameters:

  value - The text of the data validation input message. Sets null if no input message has been set.

• getInputTitle

  String getInputTitle()

  Gets the title of the data-validation input dialog box.

  Use this method to retrieve the title configured for the input prompt of a validated cell or range.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setInputTitle("Tips");
   String title = validation.getInputTitle();
   

  Returns:

  The title of the data-validation input dialog box.

• setInputTitle

  void setInputTitle(String value)

  Sets the title of the data-validation input dialog box.

  Use this property to configure the title shown in the input prompt for a validated cell or range.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setInputTitle("Tips");
   String inputTitle = validation.getInputTitle();
   

  Parameters:

  value - The title of the data-validation input dialog box.

• getOperator

  ValidationOperator getOperator()

  Gets the operator for the conditional format or data validation.

  The returned ValidationOperator determines how the validation compares the cell value with getFormula1() and, when applicable, getFormula2(). For example, ValidationOperator.Between and ValidationOperator.NotBetween compare the cell value against two formulas.

  
   IRange range = worksheet.getRange("A1");
   range.getValidation().add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   ValidationOperator operator = range.getValidation().getOperator();
   

  Returns:

  The operator used by the conditional format or data validation.

• setOperator

  void setOperator(ValidationOperator value)

  Sets the operator for the conditional format or data validation.

  The ValidationOperator determines how the validation compares the cell value with getFormula1() and, when applicable, getFormula2(). ValidationOperator.Between and ValidationOperator.NotBetween require two formulas.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   validation.setOperator(ValidationOperator.NotBetween);
   

  Parameters:

  value - The operator for the conditional format or data validation. Use ValidationOperator.Between or ValidationOperator.NotBetween only when both validation formulas are defined.

• getShowError

  boolean getShowError()

  Gets whether the data validation error message will be displayed whenever the user enters invalid data.

  Use setShowError(boolean) to control whether invalid input displays the validation error alert for the range.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "1,2,3", null);
   validation.setShowError(false);
   boolean showError = validation.getShowError();
   

  Returns:

  true if the validation error message is displayed when invalid input is entered; otherwise, false.

  Throws:

  IllegalArgumentException - if no data validation is applied to the range.

• setShowError

  void setShowError(boolean value)

  Sets whether the data validation error message will be displayed whenever the user enters invalid data.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "1,2,3", null);
   validation.setShowError(false);
   boolean showError = validation.getShowError();
   

  Parameters:

  value - true if the validation error message is displayed when invalid input is entered; otherwise, false.

  Throws:

  IllegalArgumentException - if no data validation is applied to the range.

• getShowInputMessage

  boolean getShowInputMessage()

  Gets whether the data validation input message is displayed whenever the user selects a cell in the data validation range.

  Use setShowInputMessage(boolean) to control whether the input message is shown for cells covered by this validation.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "1,2,3", null);
   validation.setShowInputMessage(true);
   boolean showInputMessage = validation.getShowInputMessage();
   

  Returns:

  true if the data validation input message is displayed when a user selects a cell in the data validation range; otherwise, false.

• setShowInputMessage

  void setShowInputMessage(boolean value)

  Sets whether the data validation input message is displayed whenever the user selects a cell in the data validation range.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "1,2,3", null);
   validation.setShowInputMessage(true);
   

  Parameters:

  value - true if the data validation input message is displayed when a user selects a cell in the data validation range; otherwise, false.

• getType

  ValidationType getType()

  Gets the data type validation for a range.

  Returns the current ValidationType that defines how values in the range are validated.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   ValidationType type = validation.getType();
   

  Returns:

  The validation type for the range.

• setType

  void setType(ValidationType value)

  Sets the data type validation for a range.

  Use this method to change the validation type of an existing IValidation rule. To fully modify a validation rule, you can use this method together with setAlertStyle(ValidationAlertStyle) and setOperator(ValidationOperator).

  
   IValidation validation = worksheet.getRange("A1:A2").getValidation();
   validation.add(ValidationType.Date, ValidationAlertStyle.Stop, ValidationOperator.Between, "13:30:00", "18:30:00");
   validation.setType(ValidationType.Time);
   

  Parameters:

  value - The ValidationType to apply to the range. Must not be null.

• getValue

  boolean getValue()

  Gets whether all the validation criteria are met (that is, if the range contains valid data).

  Returns true when the current value in the validated range satisfies the configured validation settings. If no data validation is applied to the range, this method returns true.

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   worksheet.getRange("A1").setValue(5);
   boolean isValid = validation.getValue();
   

  Returns:

  true if the current value meets all validation criteria; false otherwise.

• fromJson

  void fromJson(String json)

  Generates the data validation from the JSON string.

  Calling this method clears the existing data validation in the current range and then applies the data validation defined by the specified JSON string. If both the current API call and the JSON content provide range information, the current range is applied and the range information in the JSON string is ignored.

  
   IValidation validation = worksheet.getRange("A1:B2").getValidation();
   validation.fromJson("{\"inputTitle\":\"Tip\",\"inputMessage\":\"Value must be between 5 and 20.\"," +
           "\"type\":1,\"condition\":{\"conType\":0,\"compareType\":1,\"item1\":{\"conType\":1," +
           "\"compareType\":3,\"expected\":\"5\",\"integerValue\":true},\"item2\":{\"conType\":1," +
           "\"compareType\":5,\"expected\":\"20\",\"integerValue\":true},\"ignoreBlank\":true},\"ranges\":\"C3:D4\"}");
   

  Parameters:

  json - The JSON string that describes the data validation to apply.

• toJson

  String toJson()

  Generates a JSON string from the data validation.

  Use the returned JSON string to serialize the current validation settings for reuse with fromJson(String).

  
   IValidation validation = worksheet.getRange("A1").getValidation();
   validation.add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 10);
   String json = validation.toJson();
   

  Returns:

  The JSON string representing the data validation.