Module: Writexlsx::Utility

Included in:
Chart, Chart::Area, Chart::Axis, Chart::Bar, Chart::Column, Chart::Line, Chart::Pie, Chart::Radar, Chart::Scatter, Chart::Stock, 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, 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 



236
237
238
239
240
241
242
243
244
245
246
 
# File 'lib/write_xlsx/utility.rb', line 236

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 



228
229
230
 
# File 'lib/write_xlsx/utility.rb', line 228

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

#check_dimensions(row, col) ⇒ Object 



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

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.



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

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 



325
326
327
328
329
330
331
332
 
# File 'lib/write_xlsx/utility.rb', line 325

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.



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
219
220
221
222
223
224
225
226
 
# File 'lib/write_xlsx/utility.rb', line 121

def convert_date_time(date_time_string)       #:nodoc:
  date_time = date_time_string

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

  # Strip leading and trailing whitespace.
  date_time.sub!(/^\s+/, '')
  date_time.sub!(/\s+$/, '')

  # 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/

  # Strip trailing Z in ISO8601 date.
  date_time.sub!(/Z$/, '')

  # 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

#float_to_str(float) ⇒ Object 



361
362
363
364
365
366
367
368
 
# File 'lib/write_xlsx/utility.rb', line 361

def float_to_str(float)
  return '' unless float
  if float == float.to_i
    float.to_i.to_s
  else
    float.to_s
  end
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) 


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

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

#put_deprecate_message(method) ⇒ Object 



248
249
250
 
# File 'lib/write_xlsx/utility.rb', line 248

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

#row_col_notation(args) ⇒ Object

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



253
254
255
256
257
258
259
 
# File 'lib/write_xlsx/utility.rb', line 253

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 



356
357
358
359
 
# File 'lib/write_xlsx/utility.rb', line 356

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 



351
352
353
354
 
# File 'lib/write_xlsx/utility.rb', line 351

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



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
 
# File 'lib/write_xlsx/utility.rb', line 267

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 



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

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

#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.



308
309
310
311
312
 
# File 'lib/write_xlsx/utility.rb', line 308

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

#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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/write_xlsx/utility.rb', line 52

def xl_col_to_name(col, col_absolute)
  # Change from 0-indexed to 1 indexed.
  col += 1
  col_str = ''

  while col > 0
    # Set remainder from 1 .. 26
    remainder = col % 26
    remainder = 26 if remainder == 0

    # Convert the remainder to a character. C-ishly.
    col_letter = ("A".ord + remainder - 1).chr

    # Accumulate the column letters, right to left.
    col_str = col_letter + col_str

    # Get the next order of magnitude.
    col = (col - 1) / 26
  end

  "#{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 



75
76
77
78
79
80
81
 
# File 'lib/write_xlsx/utility.rb', line 75

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 



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

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)



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

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 



232
233
234
 
# File 'lib/write_xlsx/utility.rb', line 232

def xml_str
  @writer.string
end