Class: Axlsx::DataValidation

Inherits:
Object
  • Object
show all
Includes:
OptionsParser
Defined in:
lib/axlsx/workbook/worksheet/data_validation.rb

Overview

Note:

The recommended way to manage data validations is via Worksheet#add_data_validation

Data validation allows the validation of cell data

Constant Summary collapse

CHILD_ELEMENTS =

instance values that must be serialized as their own elements - e.g. not attributes.

[:formula1, :formula2].freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from OptionsParser

#parse_options

Constructor Details

#initialize(options = {}) ⇒ DataValidation

Creates a new Axlsx::DataValidation object

Parameters:

  • options (Hash) (defaults to: {})

    a customizable set of options

Options Hash (options):

  • formula1 (String)
  • formula2 (String)
  • allowBlank (Boolean)
    • A boolean value indicating whether the data validation allows the use of empty or blank entries.
  • error (String)
    • Message text of error alert.
  • errorStyle (Symbol)
    • The style of error alert used for this data validation.
  • errorTitle (String)
    • itle bar text of error alert.
  • operator (Symbol)
    • The relational operator used with this data validation.
  • prompt (String)
    • Message text of input prompt.
  • promptTitle (String)
    • Title bar text of input prompt.
  • showDropDown (Boolean)
    • A boolean value indicating whether to display a dropdown combo box for a list type data validation
  • showErrorMessage (Boolean)
    • A boolean value indicating whether to display the error alert message when an invalid value has been entered, according to the criteria specified.
  • showInputMessage (Boolean)
    • A boolean value indicating whether to display the input prompt message.
  • sqref (String)
    • Range over which data validation is applied, in "A1:B2" format.
  • type (Symbol)
    • The type of data validation.

25
26
27
28
29
30
31
32
33
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 25

def initialize(options={})
  # defaults
  @formula1 = @formula2 = @error = @errorTitle = @operator = @prompt = @promptTitle = @sqref = nil
  @allowBlank = @showErrorMessage = true
  @showDropDown = @showInputMessage = false
  @type = :none
  @errorStyle = :stop
  parse_options options
end

Instance Attribute Details

#allowBlankBoolean

Allow Blank A boolean value indicating whether the data validation allows the use of empty or blank entries. 1 means empty entries are OK and do not violate the validation constraints. Available for type whole, decimal, date, time, textLength, list, custom default true

Returns:

  • (Boolean)

See Also:


59
60
61
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 59

def allowBlank
  @allowBlank
end

#errorString

Error Message Message text of error alert. Available for type whole, decimal, date, time, textLength, list, custom default nil

Returns:

  • (String)

See Also:


67
68
69
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 67

def error
  @error
end

#errorStyleSymbol

Error Style (ST_DataValidationErrorStyle) The style of error alert used for this data validation. Options are:

  • information: This data validation error style uses an information icon in the error alert.
  • stop: This data validation error style uses a stop icon in the error alert.
  • warning: This data validation error style uses a warning icon in the error alert. Available for type whole, decimal, date, time, textLength, list, custom default :stop

Returns:

  • (Symbol)

See Also:


79
80
81
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 79

def errorStyle
  @errorStyle
end

#errorTitleString

Error Title Title bar text of error alert. Available for type whole, decimal, date, time, textLength, list, custom default nil

Returns:

  • (String)

See Also:


87
88
89
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 87

def errorTitle
  @errorTitle
end

#formula1String

Formula1 Available for type whole, decimal, date, time, textLength, list, custom default nil

Returns:

  • (String)

See Also:


43
44
45
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 43

def formula1
  @formula1
end

#formula2String

Formula2 Available for type whole, decimal, date, time, textLength default nil

Returns:

  • (String)

See Also:


50
51
52
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 50

def formula2
  @formula2
end

#operatorSymbol

Operator (ST_DataValidationOperator) The relational operator used with this data validation. Options are:

  • between: Data validation which checks if a value is between two other values.
  • equal: Data validation which checks if a value is equal to a specified value.
  • greater_than: Data validation which checks if a value is greater than a specified value.
  • greater_than_or_equal: Data validation which checks if a value is greater than or equal to a specified value.
  • less_than: Data validation which checks if a value is less than a specified value.
  • less_than_or_equal: Data validation which checks if a value is less than or equal to a specified value.
  • not_between: Data validation which checks if a value is not between two other values.
  • not_equal: Data validation which checks if a value is not equal to a specified value. Available for type whole, decimal, date, time, textLength default nil

Returns:

  • (Symbol)

See Also:


104
105
106
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 104

def operator
  @operator
end

#promptString

Input prompt Message text of input prompt. Available for type whole, decimal, date, time, textLength, list, custom default nil

Returns:

  • (String)

See Also:


112
113
114
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 112

def prompt
  @prompt
end

#promptTitleString

Prompt title Title bar text of input prompt. Available for type whole, decimal, date, time, textLength, list, custom default nil

Returns:

  • (String)

See Also:


120
121
122
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 120

def promptTitle
  @promptTitle
end

#showDropDownBoolean

Show drop down A boolean value indicating whether to display a dropdown combo box for a list type data validation. Be careful: false shows the dropdown list! Available for type list default false

Returns:

  • (Boolean)

See Also:


129
130
131
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 129

def showDropDown
  @showDropDown
end

#showErrorMessageBoolean

Show error message A boolean value indicating whether to display the error alert message when an invalid value has been entered, according to the criteria specified. Available for type whole, decimal, date, time, textLength, list, custom default false

Returns:

  • (Boolean)

See Also:


138
139
140
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 138

def showErrorMessage
  @showErrorMessage
end

#showInputMessageBoolean

Show input message A boolean value indicating whether to display the input prompt message. Available for type whole, decimal, date, time, textLength, list, custom default false

Returns:

  • (Boolean)

See Also:


146
147
148
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 146

def showInputMessage
  @showInputMessage
end

#sqrefString

Range over which data validation is applied, in "A1:B2" format Available for type whole, decimal, date, time, textLength, list, custom default nil

Returns:

  • (String)

See Also:


153
154
155
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 153

def sqref
  @sqref
end

#typeSymbol

The type (ST_DataValidationType) of data validation. Options are:

  • custom: Data validation which uses a custom formula to check the cell value.
  • date: Data validation which checks for date values satisfying the given condition.
  • decimal: Data validation which checks for decimal values satisfying the given condition.
  • list: Data validation which checks for a value matching one of list of values.
  • none: No data validation.
  • textLength: Data validation which checks for text values, whose length satisfies the given condition.
  • time: Data validation which checks for time values satisfying the given condition.
  • whole: Data validation which checks for whole number values satisfying the given condition. default none

Returns:

  • (Symbol)

167
168
169
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 167

def type
  @type
end

Instance Method Details

#to_xml_string(str = '') ⇒ String

Serializes the data validation

Parameters:

  • str (String) (defaults to: '')

Returns:

  • (String)

215
216
217
218
219
220
221
222
223
224
225
226
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 215

def to_xml_string(str = '')
  valid_attributes = get_valid_attributes

  str << '<dataValidation '
  str << instance_values.map do |key, value| 
    '' << key << '="' << Axlsx.booleanize(value).to_s << '"' if (valid_attributes.include?(key.to_sym) && !CHILD_ELEMENTS.include?(key.to_sym)) 
  end.join(' ')
  str << '>'
  str << ('<formula1>' << self.formula1 << '</formula1>') if @formula1 and valid_attributes.include?(:formula1)
  str << ('<formula2>' << self.formula2 << '</formula2>') if @formula2 and valid_attributes.include?(:formula2)
  str << '</dataValidation>'
end