Interface IValidation

public interface IValidation

Represents data validation for a worksheet range.

Use IRange.getValidation() to obtain an IValidation instance for a range and define the allowed data type, comparison rules, formulas, input messages, error messages, and drop-down behavior for that range. A cell can have only one validation rule applied at a time.


 worksheet.getRange("A1:A3").setValue(new Object[][] {{"Low"}, {"Medium"}, {"High"}});
 IValidation validation = worksheet.getRange("B2:B5").getValidation();
 validation.add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "=$A$1:$A$3", null);
 validation.setInCellDropdown(true);

Method Summary

Modifier and Type

Method

Description

void

add(ValidationType type)

Adds data validation to the specified range.

void

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

Adds data validation to the specified range.

void

delete()

Deletes the object.

void

fromJson(String json)

Generates the data validation from the JSON string.

ValidationAlertStyle

getAlertStyle()

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

String

getErrorMessage()

Gets the data validation error message.

String

getErrorTitle()

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

Object

getFormula1()

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

Object

getFormula2()

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

boolean

getIgnoreBlank()

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

IMEModeType

getIMEMode()

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

boolean

getInCellDropdown()

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

String

getInputMessage()

Gets the data validation input message.

String

getInputTitle()

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

ValidationOperator

getOperator()

Gets the operator for the conditional format or data validation.

boolean

getShowError()

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

boolean

getShowInputMessage()

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

ValidationType

getType()

Gets the data type validation for a range.

boolean

getValue()

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

void

setAlertStyle(ValidationAlertStyle value)

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

void

setErrorMessage(String value)

Sets the data validation error message.

void

setErrorTitle(String value)

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

void

setFormula1(Object value)

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

void

setFormula2(Object value)

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

void

setIgnoreBlank(boolean value)

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

void

setIMEMode(IMEModeType value)

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

void

setInCellDropdown(boolean value)

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

void

setInputMessage(String value)

Sets the data validation input message.

void

setInputTitle(String value)

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

void

setOperator(ValidationOperator value)

Sets the operator for the conditional format or data validation.

void

setShowError(boolean value)

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

void

setShowInputMessage(boolean value)

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

void

setType(ValidationType value)

Sets the data type validation for a range.

String

toJson()

Generates a JSON string from the data validation.

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.

Interface IValidation

Method Summary

Method Details

add

add

delete

getAlertStyle

setAlertStyle

getErrorMessage

setErrorMessage

getErrorTitle

setErrorTitle

getFormula1

setFormula1

getFormula2

setFormula2

getIgnoreBlank

setIgnoreBlank

getIMEMode

setIMEMode

getInCellDropdown

setInCellDropdown

getInputMessage

setInputMessage

getInputTitle

setInputTitle

getOperator

setOperator

getShowError

setShowError

getShowInputMessage

setShowInputMessage

getType

setType

getValue

fromJson

toJson