Module: Writexlsx::Utility

Included in:
Chart, Chart::Area, Chart::Axis, Chart::Bar, Chart::Chartline, Chart::Column, Chart::Errorbars, Chart::Line, Chart::Pie, Chart::Radar, Chart::Scatter, Chart::Series, Chart::Stock, ChartArea, Chartsheet, Drawing, Format, Formats, Package::App, Package::Button, Package::Comment, Package::Comments, Package::ConditionalFormat, Package::ContentTypes, Package::Core, Package::Packager, Package::Relationships, Package::SharedStrings, Package::Styles, Package::Table, Package::Theme, Package::Vml, Shape, Sheets, Sparkline, Table, Workbook, Worksheet, Worksheet::CellData, Worksheet::DataValidation, Worksheet::Hyperlink, Worksheet::PageSetup
Defined in:
lib/write_xlsx/utility.rb

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 



213
214
215
216
217
218
219
220
221
222
223
 
# File 'lib/write_xlsx/utility.rb', line 213

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 



205
206
207
 
# File 'lib/write_xlsx/utility.rb', line 205

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

#check_dimensions(row, col) ⇒ Object 



78
79
80
81
82
83
 
# File 'lib/write_xlsx/utility.rb', line 78

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.



320
321
322
323
324
325
326
 
# File 'lib/write_xlsx/utility.rb', line 320

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 



302
303
304
305
306
307
308
309
 
# File 'lib/write_xlsx/utility.rb', line 302

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.



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
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
 
# File 'lib/write_xlsx/utility.rb', line 105

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_types ⇒ Object 



534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
 
# File 'lib/write_xlsx/utility.rb', line 534

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.



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

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

#float_to_str(float) ⇒ Object 



338
339
340
341
342
343
344
345
 
# File 'lib/write_xlsx/utility.rb', line 338

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.



350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
 
# File 'lib/write_xlsx/utility.rb', line 350

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

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

  # Check for valid properties.
  allowable = Hash.new
  allowable[properties.size] = nil

  # 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 



526
527
528
529
530
531
532
 
# File 'lib/write_xlsx/utility.rb', line 526

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.



513
514
515
516
517
 
# File 'lib/write_xlsx/utility.rb', line 513

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 



555
556
557
558
559
560
561
 
# File 'lib/write_xlsx/utility.rb', line 555

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.



373
374
375
376
377
378
379
380
381
382
383
384
 
# File 'lib/write_xlsx/utility.rb', line 373

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

#ptrue?(value) ⇒ Boolean

return perl’s boolean result

Returns:

  • (Boolean) 


294
295
296
297
298
299
300
 
# File 'lib/write_xlsx/utility.rb', line 294

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

#put_deprecate_message(method) ⇒ Object 



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

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

#r_id_attributes(id) ⇒ Object 



499
500
501
 
# File 'lib/write_xlsx/utility.rb', line 499

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



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

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 



406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
 
# File 'lib/write_xlsx/utility.rb', line 406

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 



333
334
335
336
 
# File 'lib/write_xlsx/utility.rb', line 333

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 



328
329
330
331
 
# File 'lib/write_xlsx/utility.rb', line 328

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”).



244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
 
# File 'lib/write_xlsx/utility.rb', line 244

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

  cell.upcase!

  case 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 #{cell}")
  end
end

#underline_attributes(underline) ⇒ Object 



270
271
272
273
274
275
276
277
278
279
280
 
# File 'lib/write_xlsx/utility.rb', line 270

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 



386
387
388
389
390
391
392
 
# File 'lib/write_xlsx/utility.rb', line 386

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 



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

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 



550
551
552
553
 
# File 'lib/write_xlsx/utility.rb', line 550

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

#write_anchor ⇒ Object

Write the <x:Anchor> element.



444
445
446
447
448
449
 
# File 'lib/write_xlsx/utility.rb', line 444

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_fill ⇒ Object

Write the <x:AutoFill> element.



454
455
456
 
# File 'lib/write_xlsx/utility.rb', line 454

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

#write_color(writer, name, value) ⇒ Object

Write the <color> element.



285
286
287
288
289
 
# File 'lib/write_xlsx/utility.rb', line 285

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.



432
433
434
435
436
437
438
439
 
# File 'lib/write_xlsx/utility.rb', line 432

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.



461
462
463
464
465
466
467
468
469
470
471
 
# File 'lib/write_xlsx/utility.rb', line 461

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_fill ⇒ Object

Write the <v:fill> element.



425
426
427
 
# File 'lib/write_xlsx/utility.rb', line 425

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

#write_font(font) ⇒ Object

Write the <font> element.



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

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_stroke ⇒ Object

Write the <v:stroke> element.



493
494
495
496
497
 
# File 'lib/write_xlsx/utility.rb', line 493

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

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

#write_xml_declaration ⇒ Object 



503
504
505
506
507
508
 
# File 'lib/write_xlsx/utility.rb', line 503

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.



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

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 



54
55
56
57
 
# File 'lib/write_xlsx/utility.rb', line 54

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 



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

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 



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

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
19
20
 
# 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.
  row_abs = row_absolute ? '$' : ''
  col_abs = col_absolute ? '$' : ''
  col_str = xl_col_to_name(col, col_absolute)
  "#{col_str}#{absolute_char(row_absolute)}#{row}"
end

#xml_str ⇒ Object 



209
210
211
 
# File 'lib/write_xlsx/utility.rb', line 209

def xml_str
  @writer.string
end