Module: Writexlsx::Utility

Constant Summary collapse

ROW_MAX =

:nodoc:

1048576
COL_MAX =

:nodoc:

16384
STR_MAX =

:nodoc:

32767
SHEETNAME_MAX =

:nodoc:

31

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.delete_files(path) ⇒ Object


228
229
230
231
232
233
234
235
236
237
238
# File 'lib/write_xlsx/utility.rb', line 228

def self.delete_files(path)
  if FileTest.file?(path)
    File.delete(path)
  elsif FileTest.directory?(path)
    Dir.foreach(path) do |file|
      next if file =~ /^\.\.?$/  # '.' or '..'
      delete_files(path.sub(/\/+$/,"") + '/' + file)
    end
    Dir.rmdir(path)
  end
end

Instance Method Details

#absolute_char(absolute) ⇒ Object


220
221
222
# File 'lib/write_xlsx/utility.rb', line 220

def absolute_char(absolute)
  absolute ? '$' : ''
end

#check_dimensions(row, col) ⇒ Object


93
94
95
96
97
98
# File 'lib/write_xlsx/utility.rb', line 93

def check_dimensions(row, col)
  if !row || row >= ROW_MAX || !col || col >= COL_MAX
    raise WriteXLSXDimensionError
  end
  0
end

#check_dimensions_and_update_max_min_values(row, col, ignore_row = 0, ignore_col = 0) ⇒ Object

Check that row and col are valid and store max and min values for use in other methods/elements.

The ignore_row/ignore_col flags is used to indicate that we wish to perform the dimension check without storing the value.

The ignore flags are use by set_row() and data_validate.


335
336
337
338
339
340
341
# File 'lib/write_xlsx/utility.rb', line 335

def check_dimensions_and_update_max_min_values(row, col, ignore_row = 0, ignore_col = 0)       #:nodoc:
  check_dimensions(row, col)
  store_row_max_min_values(row) if ignore_row == 0
  store_col_max_min_values(col) if ignore_col == 0

  0
end

#check_parameter(params, valid_keys, method) ⇒ Object


317
318
319
320
321
322
323
324
# File 'lib/write_xlsx/utility.rb', line 317

def check_parameter(params, valid_keys, method)
  invalids = params.keys - valid_keys
  unless invalids.empty?
    raise WriteXLSXOptionParameterError,
      "Unknown parameter '#{invalids.join(', ')}' in #{method}."
  end
  true
end

#convert_date_time(date_time_string) ⇒ Object

convert_date_time(date_time_string)

The function takes a date and time in ISO8601 “yyyy-mm-ddThh:mm:ss.ss” format and converts it to a decimal number representing a valid Excel date.

Dates and times in Excel are represented by real numbers. The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day in seconds. The epoch can be either 1900 or 1904.

Parameter: Date and time string in one of the following formats:

yyyy-mm-ddThh:mm:ss.ss  # Standard
yyyy-mm-ddT             # Date only
          Thh:mm:ss.ss  # Time only

Returns:

A decimal number representing a valid Excel date, or
nil if the date is invalid.

120
121
122
123
124
125
126
127
128
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
# File 'lib/write_xlsx/utility.rb', line 120

def convert_date_time(date_time_string)       #:nodoc:
  date_time = date_time_string.sub(/^\s+/, '').sub(/\s+$/, '').sub(/Z$/, '')

  # Check for invalid date char.
  return nil if date_time =~ /[^0-9T:\-\.Z]/

  # Check for "T" after date or before time.
  return nil unless date_time =~ /\dT|T\d/

  days      = 0 # Number of days since epoch
  seconds   = 0 # Time expressed as fraction of 24h hours in seconds

  # Split into date and time.
  date, time = date_time.split(/T/)

  # We allow the time portion of the input DateTime to be optional.
  if time
    # Match hh:mm:ss.sss+ where the seconds are optional
    if time =~ /^(\d\d):(\d\d)(:(\d\d(\.\d+)?))?/
      hour   = $1.to_i
      min    = $2.to_i
      sec    = $4.to_f || 0
    else
      return nil # Not a valid time format.
    end

    # Some boundary checks
    return nil if hour >= 24
    return nil if min  >= 60
    return nil if sec  >= 60

    # Excel expresses seconds as a fraction of the number in 24 hours.
    seconds = (hour * 60* 60 + min * 60 + sec) / (24.0 * 60 * 60)
  end

  # We allow the date portion of the input DateTime to be optional.
  return seconds if date == ''

  # Match date as yyyy-mm-dd.
  if date =~ /^(\d\d\d\d)-(\d\d)-(\d\d)$/
    year   = $1.to_i
    month  = $2.to_i
    day    = $3.to_i
  else
    return nil  # Not a valid date format.
  end

  # Set the epoch as 1900 or 1904. Defaults to 1900.
  # Special cases for Excel.
  unless date_1904?
    return      seconds if date == '1899-12-31' # Excel 1900 epoch
    return      seconds if date == '1900-01-00' # Excel 1900 epoch
    return 60 + seconds if date == '1900-02-29' # Excel false leapday
  end


  # We calculate the date by calculating the number of days since the epoch
  # and adjust for the number of leap days. We calculate the number of leap
  # days by normalising the year in relation to the epoch. Thus the year 2000
  # becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays.
  #
  epoch   = date_1904? ? 1904 : 1900
  offset  = date_1904? ?    4 :    0
  norm    = 300
  range   = year - epoch

  # Set month days and check for leap year.
  mdays   = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
  leap    = 0
  leap    = 1  if year % 4 == 0 && year % 100 != 0 || year % 400 == 0
  mdays[1]   = 29 if leap != 0

  # Some boundary checks
  return nil if year  < epoch or year  > 9999
  return nil if month < 1     or month > 12
  return nil if day   < 1     or day   > mdays[month - 1]

  # Accumulate the number of days since the epoch.
  days = day                               # Add days for current month
  (0 .. month-2).each do |m|
    days += mdays[m]                      # Add days for past months
  end
  days += range * 365                      # Add days for past years
  days += ((range)                /  4)    # Add leapdays
  days -= ((range + offset)       /100)    # Subtract 100 year leapdays
  days += ((range + offset + norm)/400)    # Add 400 year leapdays
  days -= leap                             # Already counted above

  # Adjust for Excel erroneously treating 1900 as a leap year.
  days += 1 if !date_1904? and days > 59

  date_time = sprintf("%0.10f", days + seconds)
  date_time = date_time.sub(/\.?0+$/, '') if date_time =~ /\./
  if date_time =~ /\./
    date_time.to_f
  else
    date_time.to_i
  end
end

#dash_typesObject


552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
# File 'lib/write_xlsx/utility.rb', line 552

def dash_types
  {
    :solid               => 'solid',
    :round_dot           => 'sysDot',
    :square_dot          => 'sysDash',
    :dash                => 'dash',
    :dash_dot            => 'dashDot',
    :long_dash           => 'lgDash',
    :long_dash_dot       => 'lgDashDot',
    :long_dash_dot_dot   => 'lgDashDotDot',
    :dot                 => 'dot',
    :system_dash_dot     => 'sysDashDot',
    :system_dash_dot_dot => 'sysDashDotDot'
  }
end

#fill_properties(fill) ⇒ Object

Convert user defined fill properties to the structure required internally.


540
541
542
# File 'lib/write_xlsx/utility.rb', line 540

def fill_properties(fill) # :nodoc:
  line_fill_properties(fill)
end

#float_to_str(float) ⇒ Object


353
354
355
356
357
358
359
360
# File 'lib/write_xlsx/utility.rb', line 353

def float_to_str(float)
  return '' unless float
  if float == float.to_i
    float.to_i.to_s
  else
    float.to_s
  end
end

#layout_properties(args, is_text = false) ⇒ Object

Convert user defined layout properties to the format required internally.


365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
# File 'lib/write_xlsx/utility.rb', line 365

def layout_properties(args, is_text = false)
  return unless ptrue?(args)

  properties = is_text ? [:x, :y] : [:x, :y, :width, :height]

  # Check for valid properties.
  args.keys.each do |key|
    unless properties.include?(key.to_sym)
        raise "Property '#{key}' not allowed in layout options\n"
    end
  end

  # Set the layout properties
  layout = Hash.new
  properties.each do |property|
    value = args[property]
    # Convert to the format used by Excel for easier testing.
    layout[property] = sprintf("%.17g", value)
  end

  layout
end

#line_fill_properties(params) ⇒ Object


544
545
546
547
548
549
550
# File 'lib/write_xlsx/utility.rb', line 544

def line_fill_properties(params)
  return { :_defined => 0 } unless params
  ret = params.dup
  ret[:dash_type] = yield if block_given? && ret[:dash_type]
  ret[:_defined] = 1
  ret
end

#line_properties(line) ⇒ Object

Convert user defined line properties to the structure required internally.


531
532
533
534
535
# File 'lib/write_xlsx/utility.rb', line 531

def line_properties(line) # :nodoc:
  line_fill_properties(line) do
    value_or_raise(dash_types, line[:dash_type], 'dash type')
  end
end

#palette_color(index) ⇒ Object


573
574
575
576
577
578
579
# File 'lib/write_xlsx/utility.rb', line 573

def palette_color(index)
  # Adjust the colour index.
  idx = index - 8

  rgb = @palette[idx]
  sprintf("%02X%02X%02X", *rgb)
end

#pixels_to_points(vertices) ⇒ Object

Convert vertices from pixels to points.


391
392
393
394
395
396
397
398
399
400
401
402
# File 'lib/write_xlsx/utility.rb', line 391

def pixels_to_points(vertices)
  col_start, row_start, x1,    y1,
  col_end,   row_end,   x2,    y2,
  left,      top,       width, height  = vertices.flatten

  left   *= 0.75
  top    *= 0.75
  width  *= 0.75
  height *= 0.75

  [left, top, width, height]
end

#process_workbook_options(*params) ⇒ Object

Workbook の生成時のオプションハッシュを解析する


584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
# File 'lib/write_xlsx/utility.rb', line 584

def process_workbook_options(*params)
  case params.size
  when 0
    [{}, {}]
  when 1 # one hash
    options_keys = [:tempdir, :date_1904, :optimization, :excel2003_style]

    hash = params.first
    options = hash.reject{|k,v| !options_keys.include?(k)}

    default_format_properties =
      hash[:default_format_properties] ||
      hash.reject{|k,v| options_keys.include?(k)}

    [options, default_format_properties.dup]
  when 2 # array which includes options and default_format_properties
    options, default_format_properties = params
    default_format_properties ||= {}

    [options.dup, default_format_properties.dup]
  end
end

#ptrue?(value) ⇒ Boolean

return perl's boolean result


309
310
311
312
313
314
315
# File 'lib/write_xlsx/utility.rb', line 309

def ptrue?(value)
  if [false, nil, 0, "0", "", [], {}].include?(value)
    false
  else
    true
  end
end

#put_deprecate_message(method) ⇒ Object


240
241
242
# File 'lib/write_xlsx/utility.rb', line 240

def put_deprecate_message(method)
  $stderr.puts("Warning: calling deprecated method #{method}. This method will be removed in a future release.")
end

#quote_sheetname(sheetname) ⇒ Object

Sheetnames used in references should be quoted if they contain any spaces, special characters or if the look like something that isn't a sheet name. TODO. We need to handle more special cases.


81
82
83
84
85
86
87
88
89
90
91
# File 'lib/write_xlsx/utility.rb', line 81

def quote_sheetname(sheetname) #:nodoc:
  # Use Excel's conventions and quote the sheet name if it comtains any
  # non-word character or if it isn't already quoted.
  name = sheetname.dup
  if name =~ /\W/ && !(name =~ /^'/)
    # Double quote and single quoted strings.
    name = name.gsub(/'/, "''")
    name = "'#{name}'"
  end
  name
end

#r_id_attributes(id) ⇒ Object


517
518
519
# File 'lib/write_xlsx/utility.rb', line 517

def r_id_attributes(id)
  ['r:id', "rId#{id}"]
end

#row_col_notation(args) ⇒ Object

Check for a cell reference in A1 notation and substitute row and column


245
246
247
248
249
250
251
# File 'lib/write_xlsx/utility.rb', line 245

def row_col_notation(args)   # :nodoc:
  if args[0] =~ /^\D/
    substitute_cellref(*args)
  else
    args
  end
end

#shape_style_base(left_str, top_str, width_str, height_str, z_index_str) ⇒ Object


424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
# File 'lib/write_xlsx/utility.rb', line 424

def shape_style_base(left_str, top_str, width_str, height_str, z_index_str)
  [
   'position:absolute;',
   'margin-left:',
   left_str, 'pt;',
   'margin-top:',
   top_str, 'pt;',
   'width:',
   width_str, 'pt;',
   'height:',
   height_str, 'pt;',
   'z-index:',
   z_index_str, ';'
  ]
end

#store_col_max_min_values(col) ⇒ Object


348
349
350
351
# File 'lib/write_xlsx/utility.rb', line 348

def store_col_max_min_values(col)
  @dim_colmin = col if !@dim_colmin || (col < @dim_colmin)
  @dim_colmax = col if !@dim_colmax || (col > @dim_colmax)
end

#store_row_max_min_values(row) ⇒ Object


343
344
345
346
# File 'lib/write_xlsx/utility.rb', line 343

def store_row_max_min_values(row)
  @dim_rowmin = row if !@dim_rowmin || (row < @dim_rowmin)
  @dim_rowmax = row if !@dim_rowmax || (row > @dim_rowmax)
end

#substitute_cellref(cell, *args) ⇒ Object

Substitute an Excel cell reference in A1 notation for zero based row and column values in an argument list.

Ex: (“A4”, “Hello”) is converted to (3, 0, “Hello”).


259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
# File 'lib/write_xlsx/utility.rb', line 259

def substitute_cellref(cell, *args)       #:nodoc:
  return [*args] if cell.respond_to?(:coerce) # Numeric

  normalized_cell = cell.upcase

  case normalized_cell
  # Convert a column range: 'A:A' or 'B:G'.
  # A range such as A:A is equivalent to A1:65536, so add rows as required
  when /\$?([A-Z]{1,3}):\$?([A-Z]{1,3})/
    row1, col1 =  xl_cell_to_rowcol($1 + '1')
    row2, col2 =  xl_cell_to_rowcol($2 + ROW_MAX.to_s)
    return [row1, col1, row2, col2, *args]
  # Convert a cell range: 'A1:B7'
  when /\$?([A-Z]{1,3}\$?\d+):\$?([A-Z]{1,3}\$?\d+)/
    row1, col1 =  xl_cell_to_rowcol($1)
    row2, col2 =  xl_cell_to_rowcol($2)
    return [row1, col1, row2, col2, *args]
  # Convert a cell reference: 'A1' or 'AD2000'
  when /\$?([A-Z]{1,3}\$?\d+)/
    row1, col1 =  xl_cell_to_rowcol($1)
    return [row1, col1, *args]
  else
    raise("Unknown cell reference #{normalized_cell}")
  end
end

#underline_attributes(underline) ⇒ Object


285
286
287
288
289
290
291
292
293
294
295
# File 'lib/write_xlsx/utility.rb', line 285

def underline_attributes(underline)
  if underline == 2
    [['val', 'double']]
  elsif underline == 33
    [['val', 'singleAccounting']]
  elsif underline == 34
    [['val', 'doubleAccounting']]
  else
    []    # Default to single underline.
  end
end

#v_shape_attributes_base(id, z_index) ⇒ Object


404
405
406
407
408
409
410
# File 'lib/write_xlsx/utility.rb', line 404

def v_shape_attributes_base(id, z_index)
  [
   ['id',          "_x0000_s#{id}"],
   ['type',        type],
   ['style',       (v_shape_style_base(z_index, vertices) + style_addition).join]
  ]
end

#v_shape_style_base(z_index, vertices) ⇒ Object


412
413
414
415
416
417
418
419
420
421
422
# File 'lib/write_xlsx/utility.rb', line 412

def v_shape_style_base(z_index, vertices)
  left, top, width, height = pixels_to_points(vertices)

  left_str    = float_to_str(left)
  top_str     = float_to_str(top)
  width_str   = float_to_str(width)
  height_str  = float_to_str(height)
  z_index_str = float_to_str(z_index)

  shape_style_base(left_str, top_str, width_str, height_str, z_index_str)
end

#value_or_raise(hash, key, msg) ⇒ Object


568
569
570
571
# File 'lib/write_xlsx/utility.rb', line 568

def value_or_raise(hash, key, msg)
  raise "Unknown #{msg} '#{key}'" if hash[key.to_sym].nil?
  hash[key.to_sym]
end

#write_anchorObject

Write the <x:Anchor> element.


462
463
464
465
466
467
# File 'lib/write_xlsx/utility.rb', line 462

def write_anchor
  col_start, row_start, x1, y1, col_end, row_end, x2, y2 = vertices
  data = [col_start, x1, row_start, y1, col_end, x2, row_end, y2].join(', ')

  @writer.data_element('x:Anchor', data)
end

#write_auto_fillObject

Write the <x:AutoFill> element.


472
473
474
# File 'lib/write_xlsx/utility.rb', line 472

def write_auto_fill
  @writer.data_element('x:AutoFill', 'False')
end

#write_color(writer, name, value) ⇒ Object

Write the <color> element.


300
301
302
303
304
# File 'lib/write_xlsx/utility.rb', line 300

def write_color(writer, name, value) #:nodoc:
  attributes = [[name, value]]

  writer.empty_tag('color', attributes)
end

#write_comment_path(gradientshapeok, connecttype) ⇒ Object

Write the <v:path> element.


450
451
452
453
454
455
456
457
# File 'lib/write_xlsx/utility.rb', line 450

def write_comment_path(gradientshapeok, connecttype)
  attributes      = []

  attributes << ['gradientshapeok', 't'] if gradientshapeok
  attributes << ['o:connecttype', connecttype]

  @writer.empty_tag('v:path', attributes)
end

#write_div(align, font = nil) ⇒ Object

Write the <div> element.


479
480
481
482
483
484
485
486
487
488
489
# File 'lib/write_xlsx/utility.rb', line 479

def write_div(align, font = nil)
  style = "text-align:#{align}"
  attributes = [['style', style]]

  @writer.tag_elements('div', attributes) do
    if font
      # Write the font element.
      write_font(font)
    end
  end
end

#write_fillObject

Write the <v:fill> element.


443
444
445
# File 'lib/write_xlsx/utility.rb', line 443

def write_fill
  @writer.empty_tag('v:fill', fill_attributes)
end

#write_font(font) ⇒ Object

Write the <font> element.


494
495
496
497
498
499
500
501
502
503
504
505
506
# File 'lib/write_xlsx/utility.rb', line 494

def write_font(font)
  caption = font[:_caption]
  face    = 'Calibri'
  size    = 220
  color   = '#000000'

  attributes = [
                ['face',  face],
                ['size',  size],
                ['color', color]
               ]
  @writer.data_element('font', caption, attributes)
end

#write_strokeObject

Write the <v:stroke> element.


511
512
513
514
515
# File 'lib/write_xlsx/utility.rb', line 511

def write_stroke
  attributes = [['joinstyle', 'miter']]

  @writer.empty_tag('v:stroke', attributes)
end

#write_xml_declarationObject


521
522
523
524
525
526
# File 'lib/write_xlsx/utility.rb', line 521

def write_xml_declaration
  @writer.xml_decl
  yield
  @writer.crlf
  @writer.close
end

#xl_cell_to_rowcol(cell) ⇒ Object

Returns: [row, col, row_absolute, col_absolute]

The row_absolute and col_absolute parameters aren't documented because they mainly used internally and aren't very useful to the user.


26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# File 'lib/write_xlsx/utility.rb', line 26

def xl_cell_to_rowcol(cell)
  cell =~ /(\$?)([A-Z]{1,3})(\$?)(\d+)/

  col_abs = $1 != ""
  col     = $2
  row_abs = $3 != ""
  row     = $4.to_i

  # Convert base26 column string to number
  # All your Base are belong to us.
  chars = col.split(//)
  expn = 0
  col = 0

  chars.reverse.each do |char|
    col += (char.ord - 'A'.ord + 1) * (26 ** expn)
    expn += 1
  end

  # Convert 1-index to zero-index
  row -= 1
  col -= 1

  return [row, col, row_abs, col_abs]
end

#xl_col_to_name(col, col_absolute) ⇒ Object


52
53
54
55
# File 'lib/write_xlsx/utility.rb', line 52

def xl_col_to_name(col, col_absolute)
  col_str = ColName.instance.col_str(col)
  "#{absolute_char(col_absolute)}#{col_str}"
end

#xl_range(row_1, row_2, col_1, col_2, row_abs_1 = false, row_abs_2 = false, col_abs_1 = false, col_abs_2 = false) ⇒ Object


57
58
59
60
61
62
63
# File 'lib/write_xlsx/utility.rb', line 57

def xl_range(row_1, row_2, col_1, col_2,
             row_abs_1 = false, row_abs_2 = false, col_abs_1 = false, col_abs_2 = false)
  range1 = xl_rowcol_to_cell(row_1, col_1, row_abs_1, col_abs_1)
  range2 = xl_rowcol_to_cell(row_2, col_2, row_abs_2, col_abs_2)

  "#{range1}:#{range2}"
end

#xl_range_formula(sheetname, row_1, row_2, col_1, col_2) ⇒ Object


65
66
67
68
69
70
71
72
73
74
# File 'lib/write_xlsx/utility.rb', line 65

def xl_range_formula(sheetname, row_1, row_2, col_1, col_2)
  # Use Excel's conventions and quote the sheet name if it contains any
  # non-word character or if it isn't already quoted.
  sheetname = "'#{sheetname}'" if sheetname =~ /\W/ && !(sheetname =~ /^'/)

  range1 = xl_rowcol_to_cell( row_1, col_1, 1, 1 )
  range2 = xl_rowcol_to_cell( row_2, col_2, 1, 1 )

  "=#{sheetname}!#{range1}:#{range2}"
end

#xl_rowcol_to_cell(row, col, row_absolute = false, col_absolute = false) ⇒ Object

xl_rowcol_to_cell($row, col, row_absolute, col_absolute)


14
15
16
17
18
# File 'lib/write_xlsx/utility.rb', line 14

def xl_rowcol_to_cell(row, col, row_absolute = false, col_absolute = false)
  row += 1      # Change from 0-indexed to 1 indexed.
  col_str = xl_col_to_name(col, col_absolute)
  "#{col_str}#{absolute_char(row_absolute)}#{row}"
end

#xml_strObject


224
225
226
# File 'lib/write_xlsx/utility.rb', line 224

def xml_str
  @writer.string
end