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

See Also:

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) —
    • Title 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. Be careful: It has an inverted logic, false shows the dropdown list! You should use hideDropDown instead.
  • hideDropDown (Boolean) —
    • A boolean value indicating whether to hide the dropdown combo box for a list type data validation. Defaults to false (meaning the dropdown is visible by default).
  • 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.


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

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

#allowBlank ⇒ Boolean

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:



61
62
63
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 61

def allowBlank
  @allowBlank
end

#error ⇒ String

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

Returns:

  • (String)

See Also:



69
70
71
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 69

def error
  @error
end

#errorStyle ⇒ Symbol

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:



81
82
83
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 81

def errorStyle
  @errorStyle
end

#errorTitle ⇒ String

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

Returns:

  • (String)

See Also:



89
90
91
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 89

def errorTitle
  @errorTitle
end

#formula1 ⇒ String

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

Returns:

  • (String)

See Also:



45
46
47
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 45

def formula1
  @formula1
end

#formula2 ⇒ String

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

Returns:

  • (String)

See Also:



52
53
54
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 52

def formula2
  @formula2
end

#operator ⇒ Symbol

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:



106
107
108
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 106

def operator
  @operator
end

#prompt ⇒ String

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

Returns:

  • (String)

See Also:



114
115
116
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 114

def prompt
  @prompt
end

#promptTitle ⇒ String

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

Returns:

  • (String)

See Also:



122
123
124
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 122

def promptTitle
  @promptTitle
end

#showDropDown ⇒ Boolean Also known as: hideDropDown

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

Returns:

  • (Boolean)

See Also:



131
132
133
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 131

def showDropDown
  @showDropDown
end

#showErrorMessage ⇒ Boolean

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:



149
150
151
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 149

def showErrorMessage
  @showErrorMessage
end

#showInputMessage ⇒ Boolean

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:



157
158
159
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 157

def showInputMessage
  @showInputMessage
end

#sqref ⇒ String

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:



164
165
166
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 164

def sqref
  @sqref
end

#type ⇒ Symbol

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) 


178
179
180
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 178

def type
  @type
end

Instance Method Details

#hideDropDown=(v) ⇒ Object

See Also:



242
243
244
245
246
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 242

def hideDropDown=(v)
  Axlsx.validate_boolean(v)
  # It's just an alias for the showDropDown attribute, hideDropDown should set the value of the original showDropDown.
  @showDropDown = v
end

#to_xml_string(str = +'')) ⇒ String

Serializes the data validation

Parameters:

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

Returns:

  • (String) 


275
276
277
278
279
280
281
282
283
284
285
286
287
288
 
# File 'lib/axlsx/workbook/worksheet/data_validation.rb', line 275

def to_xml_string(str = +'')
  valid_attributes = get_valid_attributes
  h = Axlsx.instance_values_for(self).select { |key, _| valid_attributes.include?(key.to_sym) && !CHILD_ELEMENTS.include?(key.to_sym) }

  str << '<dataValidation '
  h.each_with_index do |key_value, index|
    str << ' ' unless index == 0
    str << key_value.first << '="' << Axlsx.booleanize(key_value.last).to_s << '"'
  end
  str << '>'
  str << '<formula1>' << formula1 << '</formula1>' if formula1 && valid_attributes.include?(:formula1)
  str << '<formula2>' << formula2 << '</formula2>' if formula2 && valid_attributes.include?(:formula2)
  str << '</dataValidation>'
end