Class: Writeexcel::Worksheet::DataValidation

Inherits:
Object
  • Object
show all
Includes:
ConvertDateTime
Defined in:
lib/writeexcel/data_validations.rb

Class Method Summary collapse

Instance Method Summary collapse

Methods included from ConvertDateTime

#convert_date_time

Constructor Details

#initialize(parser = nil, param = {}) ⇒ DataValidation

Returns a new instance of DataValidation.



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/writeexcel/data_validations.rb', line 48

def initialize(parser = nil, param = {})
  @parser        = parser
  @cells         = param[:cells]
  @validate      = param[:validate]
  @criteria      = param[:criteria]
  @value         = param[:value]
  @maximum       = param[:maximum]
  @input_title   = param[:input_title]
  @input_message = param[:input_message]
  @error_title   = param[:error_title]
  @error_message = param[:error_message]
  @error_type    = param[:error_type]
  @ignore_blank  = param[:ignore_blank]
  @dropdown      = param[:dropdown]
  @show_input    = param[:show_input]
  @show_error    = param[:show_error]
end

Class Method Details

.factory(parser, date_1904, *args) ⇒ Object 



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/writeexcel/data_validations.rb', line 129

def self.factory(parser, date_1904, *args)
  # Check for a valid number of args.
  return -1 if args.size != 5 && args.size != 3

  # The final hashref contains the validation parameters.
  param = args.pop

  # 'validate' is a required parameter.
  return -3 unless param.has_key?(:validate)

  # Make the last row/col the same as the first if not defined.
  row1, col1, row2, col2 = args
  row2, col2 = row1, col1 unless row2

  # List of valid input parameters.
  obj = DataValidation.new
  valid_parameter = obj.valid_parameter_of_data_validation

  # Check for valid input parameters.
  param.each_key { |param_key| return -3 unless valid_parameter.has_key?(param_key) }

  # Map alternative parameter names 'source' or 'minimum' to 'value'.
  param[:value] = param[:source]  if param[:source]
  param[:value] = param[:minimum] if param[:minimum]

  # Check for valid validation types.
  unless obj.valid_validation_type.has_key?(param[:validate].downcase)
    return -3
  else
    param[:validate] = obj.valid_validation_type[param[:validate].downcase]
  end

  # No action is required for validation type 'any'.
  # TODO: we should perhaps store 'any' for message only validations.
  return 0 if param[:validate] == 0

  # The list and custom validations don't have a criteria so we use a default
  # of 'between'.
  if param[:validate] == 3 || param[:validate] == 7
    param[:criteria]  = 'between'
    param[:maximum]   = nil
  end

  # 'criteria' is a required parameter.
  unless param.has_key?(:criteria)
    #           carp "Parameter 'criteria' is required in data_validation()";
    return -3
  end

  # Check for valid criteria types.
  unless obj.valid_criteria_type.has_key?(param[:criteria].downcase)
    return -3
  else
    param[:criteria] = obj.valid_criteria_type[param[:criteria].downcase]
  end

  # 'Between' and 'Not between' criteria require 2 values.
  if param[:criteria] == 0 || param[:criteria] == 1
    unless param.has_key?(:maximum)
      return -3
    end
  else
    param[:maximum] = nil
  end

  # Check for valid error dialog types.
  if not param.has_key?(:error_type)
    param[:error_type] = 0
  elsif not obj.valid_error_type.has_key?(param[:error_type].downcase)
    return -3
  else
    param[:error_type] = obj.valid_error_type[param[:error_type].downcase]
  end

  # Convert date/times value if required.
  if param[:validate] == 4 || param[:validate] == 5
    if param[:value] =~ /T/
      param[:value] = obj.convert_date_time(param[:value], date_1904) || raise("invalid :value: #{param[:value]}")
    end
    if param[:maximum] && param[:maximum] =~ /T/
      param[:maximum] = obj.convert_date_time(param[:maximum], date_1904) || raise("invalid :maximum: #{param[:maximum]}")
    end
  end

  # Set some defaults if they haven't been defined by the user.
  param[:ignore_blank]  = 1 unless param[:ignore_blank]
  param[:dropdown]      = 1 unless param[:dropdown]
  param[:show_input]    = 1 unless param[:show_input]
  param[:show_error]    = 1 unless param[:show_error]

  # These are the cells to which the validation is applied.
  param[:cells] = [[row1, col1, row2, col2]]

  # A (for now) undocumented parameter to pass additional cell ranges.
  if param.has_key?(:other_cells)
    param[:cells].push(param[:other_cells])
  end

  DataValidation.new(parser, param)
end

Instance Method Details

#dv_record ⇒ Object

Calclate the DV record that specifies the data validation criteria and options for a range of cells..

cells             # Aref of cells to which DV applies.
validate          # Type of data validation.
criteria          # Validation criteria.
value             # Value/Source/Minimum formula.
maximum           # Maximum formula.
input_title       # Title of input message.
input_message     # Text of input message.
error_title       # Title of error message.
error_message     # Text of input message.
error_type        # Error dialog type.
ignore_blank      # Ignore blank cells.
dropdown          # Display dropdown with list.
input_box         # Display input box.
error_box         # Display error box.



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/writeexcel/data_validations.rb', line 84

def dv_record  # :nodoc:
  record          = 0x01BE       # Record identifier

  flags           = 0x00000000   # DV option flags.

  ime_mode        = 0            # IME input mode for far east fonts.
  str_lookup      = 0            # See below.

  # Set the string lookup flag for 'list' validations with a string array.
  str_lookup = @validate == 3 && @value.respond_to?(:to_ary) ? 1 : 0

  # The dropdown flag is stored as a negated value.
  no_dropdown = @dropdown ? 0 : 1

  # Set the required flags.
  flags |= @validate
  flags |= @error_type   << 4
  flags |= str_lookup    << 7
  flags |= @ignore_blank << 8
  flags |= no_dropdown   << 9
  flags |= ime_mode      << 10
  flags |= @show_input   << 18
  flags |= @show_error   << 19
  flags |= @criteria     << 20

  # Pack the DV cell data.
  dv_data = @cells.inject([@cells.size].pack('v')) do |result, range|
    result + [range[0], range[2], range[1], range[3]].pack('vvvv')
  end

  # Pack the record.
  data   = [flags].pack('V')     +
    pack_dv_string(@input_title,   32 ) +
    pack_dv_string(@error_title,   32 ) +
    pack_dv_string(@input_message, 255) +
    pack_dv_string(@error_message, 255) +
    pack_dv_formula(@value)             +
    pack_dv_formula(@maximum)           +
    dv_data

  header = [record, data.bytesize].pack('vv')

  header + data
end

#pack_dv_formula(formula) ⇒ Object

Pack the formula used in the DV record. This is the same as an cell formula with some additional header information. Note, DV formulas in Excel use relative addressing (R1C1 and ptgXxxN) however we use the Formula.pm’s default absolute addressing (A1 and ptgXxx).



263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
 
# File 'lib/writeexcel/data_validations.rb', line 263

def pack_dv_formula(formula)   #:nodoc:
  unused      = 0x0000

  # Return a default structure for unused formulas.
  return [0, unused].pack('vv') unless formula && formula != ''

  # Pack a list array ref as a null separated string.
  formula   = %!"#{formula.join("\0")}"! if formula.respond_to?(:to_ary)

  # Strip the = sign at the beginning of the formula string
  formula = formula.to_s unless formula.respond_to?(:to_str)

  # In order to raise formula errors from the point of view of the calling
  # program we use an eval block and re-raise the error from here.
  #
  tokens = @parser.parse_formula(formula.sub(/^=/, ''))   # ????

  #       if ($@) {
  #           $@ =~ s/\n$//;  # Strip the \n used in the Formula.pm die()
  #           croak $@;       # Re-raise the error
  #       }
  #       else {
  #           # TODO test for non valid ptgs such as Sheet2!A1
  #       }

  # Force 2d ranges to be a reference class.
  tokens.each do |t|
    t.sub!(/_range2d/, "_range2dR")
    t.sub!(/_name/, "_nameR")
  end

  # Parse the tokens into a formula string.
  formula = @parser.parse_tokens(tokens)

  [formula.length, unused].pack('vv') + formula
end

#pack_dv_string(string, max_length) ⇒ Object

Pack the strings used in the input and error dialog captions and messages. Captions are limited to 32 characters. Messages are limited to 255 chars.



234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/writeexcel/data_validations.rb', line 234

def pack_dv_string(string, max_length)   #:nodoc:
  # The default empty string is "\0".
  string = ruby_18 { "\0" } || ruby_19 { "\0".encode('BINARY') } unless string && string != ''

  # Excel limits DV captions to 32 chars and messages to 255.
  string = string[0 .. max_length-1] if string.bytesize > max_length

  ruby_19 { string = convert_to_ascii_if_ascii(string) }

  # Handle utf8 strings
  if is_utf8?(string)
    str_length = string.gsub(/[^\Wa-zA-Z_\d]/, ' ').bytesize   # jlength
    string = utf8_to_16le(string)
    encoding = 1
  else
    str_length = string.bytesize
    encoding = 0
  end

  ruby_18 { [str_length, encoding].pack('vC') + string } ||
  ruby_19 { [str_length, encoding].pack('vC') + string.force_encoding('BINARY') }
end

#valid_criteria_type ⇒ Object 



338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
 
# File 'lib/writeexcel/data_validations.rb', line 338

def valid_criteria_type
  {
    'between'                     => 0,
    'not between'                 => 1,
    'equal to'                    => 2,
    '='                           => 2,
    '=='                          => 2,
    'not equal to'                => 3,
    '!='                          => 3,
    '<>'                          => 3,
    'greater than'                => 4,
    '>'                           => 4,
    'less than'                   => 5,
    '<'                           => 5,
    'greater than or equal to'    => 6,
    '>='                          => 6,
    'less than or equal to'       => 7,
    '<='                          => 7
  }
end

#valid_error_type ⇒ Object 



359
360
361
362
363
364
365
 
# File 'lib/writeexcel/data_validations.rb', line 359

def valid_error_type
  {
    'stop'        => 0,
    'warning'     => 1,
    'information' => 2
  }
end

#valid_parameter_of_data_validation ⇒ Object 



300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
 
# File 'lib/writeexcel/data_validations.rb', line 300

def valid_parameter_of_data_validation
  {
    :validate          => 1,
    :criteria          => 1,
    :value             => 1,
    :source            => 1,
    :minimum           => 1,
    :maximum           => 1,
    :ignore_blank      => 1,
    :dropdown          => 1,
    :show_input        => 1,
    :input_title       => 1,
    :input_message     => 1,
    :show_error        => 1,
    :error_title       => 1,
    :error_message     => 1,
    :error_type        => 1,
    :other_cells       => 1
  }
end

#valid_validation_type ⇒ Object 



321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/writeexcel/data_validations.rb', line 321

def valid_validation_type
  {
    'any'             => 0,
    'any value'       => 0,
    'whole number'    => 1,
    'whole'           => 1,
    'integer'         => 1,
    'decimal'         => 2,
    'list'            => 3,
    'date'            => 4,
    'time'            => 5,
    'text length'     => 6,
    'length'          => 6,
    'custom'          => 7
  }
end