Package io.keikai.axyra.sheets.content

Class Validation

java.lang.Object
io.keikai.axyra.sheets.content.Validation
public final class Validation extends Object
A data-validation rule (immutable). Apply with Range.setValidation(...); read back with Sheet.validations(). Crosses the boundary as flat JSON.

Types: whole, decimal, list, date, time, textLength, custom. Operators: between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual.

Operands (formula1, formula2) are formulas without a leading =, saved as given: a number ("10"), a cell reference ("$A$1") or a function call ("DATE(2020,1,1)"). Unknown tokens are not rejected; see each builder method for what they become. 


 sheet.range("B2:B100").setValidation(
         Validation.wholeNumber("between", "1", "10"));

  • Nested Class Summary

    Nested Classes
    Modifier and Type
    Class
    Description
    static final class 
    Validation.Builder
    A fluent builder for a Validation.

  • Method Summary

    Modifier and Type
    Method
    Description
    boolean
    allowBlank()
    Whether a blank cell passes the rule.
    static Validation.Builder
    builder()
    A builder for any validation type.
    static Validation
    custom(String formula)
    A custom-formula constraint: an entry is valid when the formula returns TRUE.
    static Validation
    date(String operator, String formula1, String formula2)
    A date constraint, for example date("between", "DATE(2020,1,1)", "DATE(2020,12,31)").
    static Validation
    decimal(String operator, String formula1, String formula2)
    A decimal-number constraint, for example decimal("greaterThan", "0", null).
    String
    errorMessage()
    The text of the error alert; an empty string when none.
    String
    errorStyle()
    How firmly a failure is reported: stop (rejects the entry), warning or information; an empty string when unset.
    String
    errorTitle()
    The title of the error alert; an empty string when none.
    int
    firstColumn()
    The first column of the rule's first range (0-based, inclusive); 0 for a rule that was built, not read back.
    int
    firstRow()
    The first row of the rule's first range (0-based, inclusive).
    String
    formula1()
    The first operand as a formula without a leading = or _xlfn. prefixes, or null when none is set.
    String
    formula2()
    The second operand of a between / notBetween rule, or null when none is set.
    static Validation
    fromJson(String json)
    Builds a rule from the engine's JSON, the form behind Sheet.validations().
    String
    inputMessage()
    The text of the input hint; an empty string when none.
    String
    inputTitle()
    The title of the input hint; an empty string when none.
    int
    lastColumn()
    The last column of the rule's first range (0-based, inclusive); 0 for a rule that was built, not read back.
    int
    lastRow()
    The last row of the rule's first range (0-based, inclusive); 0 for a rule that was built, not read back.
    static Validation
    list(String... items)
    A drop-down list of the given allowed values, with its in-cell arrow shown.
    String
    operator()
    The comparison token.
    boolean
    showDropdown()
    Whether a list rule offers its items through an in-cell drop-down arrow.
    boolean
    showErrorMessage()
    Whether an invalid entry raises the error alert.
    boolean
    showInputMessage()
    Whether the input hint shows when the cell is selected.
    static Validation
    textLength(String operator, String formula1, String formula2)
    A text-length constraint on the number of characters, for example textLength("lessThanOrEqual", "50", null).
    static Validation
    time(String operator, String formula1, String formula2)
    A time constraint, for example time("between", "TIME(9,0,0)", "TIME(17,0,0)").
    Validation.Builder
    toBuilder()
    A builder pre-loaded with this rule, for read–modify–write round-trips.
    String
    toJson()
    The rule as the JSON object sent to the engine.
    String
    type()
    The validation type token; "none" for a read-back rule that allows any value, an empty string when unset.
    static Validation
    wholeNumber(String operator, String formula1, String formula2)
    A whole-number constraint, for example wholeNumber("between", "1", "10").

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

  • Method Details

    • list

      public static Validation list(String... items)
      A drop-down list of the given allowed values, with its in-cell arrow shown.

      The values are written as one quoted list literal, as Excel writes it ("Draft,Approved"); an unquoted list would be read as a name. A double quote inside a value is escaped by doubling it.

      Throws:
      IllegalArgumentException - if no value is given, or a value is null or contains a comma — the list literal separates its values with commas, so such a value would silently become several. List values like that from a range with builder().type("list").formula1("$X$1:$X$9").

    • wholeNumber

      public static Validation wholeNumber(String operator, String formula1, String formula2)
      A whole-number constraint, for example wholeNumber("between", "1", "10").
      Parameters:
      operator - a comparison token; see Validation.Builder.operator(String)
      formula1 - the first operand, a formula without a leading =
      formula2 - the second operand, used only by between and notBetween; null otherwise

    • decimal

      public static Validation decimal(String operator, String formula1, String formula2)
      A decimal-number constraint, for example decimal("greaterThan", "0", null).
      Parameters:
      operator - a comparison token; see Validation.Builder.operator(String)
      formula1 - the first operand, a formula without a leading =
      formula2 - the second operand, used only by between and notBetween; null otherwise

    • date

      public static Validation date(String operator, String formula1, String formula2)
      A date constraint, for example date("between", "DATE(2020,1,1)", "DATE(2020,12,31)").

      The operands are formulas, not date text. Pass a DATE call, a date serial number ("43831" is 2020-01-01 in the 1900 date system) or a cell reference. A string such as "2020-01-01" is taken as a formula: Excel reads it as the subtraction 2020 - 1 - 1, the serial number 2018, which is not the intended date.

      Parameters:
      operator - a comparison token; see Validation.Builder.operator(String)
      formula1 - the first operand, a formula without a leading =
      formula2 - the second operand, used only by between and notBetween; null otherwise

    • time

      public static Validation time(String operator, String formula1, String formula2)
      A time constraint, for example time("between", "TIME(9,0,0)", "TIME(17,0,0)"). The operands are formulas: a TIME call, a day fraction ("0.5" is noon) or a cell reference.
      Parameters:
      operator - a comparison token; see Validation.Builder.operator(String)
      formula1 - the first operand, a formula without a leading =
      formula2 - the second operand, used only by between and notBetween; null otherwise

    • textLength

      public static Validation textLength(String operator, String formula1, String formula2)
      A text-length constraint on the number of characters, for example textLength("lessThanOrEqual", "50", null).
      Parameters:
      operator - a comparison token; see Validation.Builder.operator(String)
      formula1 - the first operand, a formula without a leading =
      formula2 - the second operand, used only by between and notBetween; null otherwise

    • custom

      public static Validation custom(String formula)
      A custom-formula constraint: an entry is valid when the formula returns TRUE. Write the formula for the top-left cell of the range, without a leading =, for example custom("ISNUMBER(A1)"); relative references shift for the other cells, as in Excel.

    • builder

      public static Validation.Builder builder()
      A builder for any validation type. It starts with allowBlank = true, showErrorMessage = true and errorStyle = "stop"; a rule with no operator uses between.

    • toBuilder

      public Validation.Builder toBuilder()
      A builder pre-loaded with this rule, for read–modify–write round-trips.

    • toJson

      public String toJson()
      The rule as the JSON object sent to the engine. Used by the SDK.

    • fromJson

      public static Validation fromJson(String json)
      Builds a rule from the engine's JSON, the form behind Sheet.validations(). Used by the SDK.

    • type

      public String type()
      The validation type token; "none" for a read-back rule that allows any value, an empty string when unset.

    • operator

      public String operator()
      The comparison token. A read-back rule always carries one (between when the file had none); an empty string when unset.

    • formula1

      public String formula1()
      The first operand as a formula without a leading = or _xlfn. prefixes, or null when none is set.

    • formula2

      public String formula2()
      The second operand of a between / notBetween rule, or null when none is set.

    • allowBlank

      public boolean allowBlank()
      Whether a blank cell passes the rule.

    • showDropdown

      public boolean showDropdown()
      Whether a list rule offers its items through an in-cell drop-down arrow. Always false for a rule that is not a list.

    • errorStyle

      public String errorStyle()
      How firmly a failure is reported: stop (rejects the entry), warning or information; an empty string when unset.

    • errorTitle

      public String errorTitle()
      The title of the error alert; an empty string when none.

    • errorMessage

      public String errorMessage()
      The text of the error alert; an empty string when none.

    • showErrorMessage

      public boolean showErrorMessage()
      Whether an invalid entry raises the error alert.

    • inputTitle

      public String inputTitle()
      The title of the input hint; an empty string when none.

    • inputMessage

      public String inputMessage()
      The text of the input hint; an empty string when none.

    • showInputMessage

      public boolean showInputMessage()
      Whether the input hint shows when the cell is selected.

    • firstRow

      public int firstRow()
      The first row of the rule's first range (0-based, inclusive). A rule that covers several ranges reports only its first range. 0 for a rule that was built, not read back.

    • firstColumn

      public int firstColumn()
      The first column of the rule's first range (0-based, inclusive); 0 for a rule that was built, not read back.

    • lastRow

      public int lastRow()
      The last row of the rule's first range (0-based, inclusive); 0 for a rule that was built, not read back.

    • lastColumn

      public int lastColumn()
      The last column of the rule's first range (0-based, inclusive); 0 for a rule that was built, not read back.