Module: Writexlsx::Utility

Included in:
Chart, Chart::Area, Chart::Axis, Chart::Bar, Chart::Caption, Chart::Chartline, Chart::Column, Chart::Doughnut, Chart::Errorbars, Chart::Line, Chart::Pie, Chart::Radar, Chart::Scatter, Chart::Series, Chart::Stock, ChartArea, Chartsheet, Drawings, Format, Formats, Package::App, Package::Button, Package::Comment, Package::Comments, Package::ConditionalFormat, Package::ContentTypes, Package::Core, Package::Custom, Package::Metadata, Package::Packager, Package::Relationships, Package::RichValue, Package::RichValueRel, Package::RichValueStructure, Package::RichValueTypes, 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
CHAR_WIDTHS = 
{
  ' '  =>  3, '!' =>  5, '"' =>  6, '#' =>  7, '$' =>  7, '%' => 11,
  '&'  => 10, "'" =>  3, '(' =>  5, ')' =>  5, '*' =>  7, '+' =>  7,
  ','  =>  4, '-' =>  5, '.' =>  4, '/' =>  6, '0' =>  7, '1' =>  7,
  '2'  =>  7, '3' =>  7, '4' =>  7, '5' =>  7, '6' =>  7, '7' =>  7,
  '8'  =>  7, '9' =>  7, ':' =>  4, ';' =>  4, '<' =>  7, '=' =>  7,
  '>'  =>  7, '?' =>  7, '@' => 13, 'A' =>  9, 'B' =>  8, 'C' =>  8,
  'D'  =>  9, 'E' =>  7, 'F' =>  7, 'G' =>  9, 'H' =>  9, 'I' =>  4,
  'J'  =>  5, 'K' =>  8, 'L' =>  6, 'M' => 12, 'N' => 10, 'O' => 10,
  'P'  =>  8, 'Q' => 10, 'R' =>  8, 'S' =>  7, 'T' =>  7, 'U' =>  9,
  'V'  =>  9, 'W' => 13, 'X' =>  8, 'Y' =>  7, 'Z' =>  7, '[' =>  5,
  '\\' =>  6, ']' =>  5, '^' =>  7, '_' =>  7, '`' =>  4, 'a' =>  7,
  'b'  =>  8, 'c' =>  6, 'd' =>  8, 'e' =>  8, 'f' =>  5, 'g' =>  7,
  'h'  =>  8, 'i' =>  4, 'j' =>  4, 'k' =>  7, 'l' =>  4, 'm' => 12,
  'n'  =>  8, 'o' =>  8, 'p' =>  8, 'q' =>  8, 'r' =>  5, 's' =>  6,
  't'  =>  5, 'u' =>  8, 'v' =>  7, 'w' => 11, 'x' =>  7, 'y' =>  7,
  'z'  =>  6, '{' =>  5, '|' =>  7, '}' =>  5, '~' =>  7
}.freeze
PERL_TRUE_VALUES = 
[false, nil, 0, "0", "", [], {}].freeze

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.delete_files(path) ⇒ Object 



277
278
279
280
281
282
283
284
285
286
287
288
 
# File 'lib/write_xlsx/utility.rb', line 277

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(%r{/+$}, "") + '/' + file)
    end
    Dir.rmdir(path)
  end
end

Instance Method Details

#absolute_char(absolute) ⇒ Object 



269
270
271
 
# File 'lib/write_xlsx/utility.rb', line 269

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

#check_dimensions(row, col) ⇒ Object

Raises:



139
140
141
142
143
 
# File 'lib/write_xlsx/utility.rb', line 139

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

  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.



377
378
379
380
381
382
383
 
# File 'lib/write_xlsx/utility.rb', line 377

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 



364
365
366
367
368
369
370
371
 
# File 'lib/write_xlsx/utility.rb', line 364

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

#color(color_code) ⇒ Object

Convert the user specified colour index or string to a rgb colour.



955
956
957
958
959
960
961
962
963
964
965
 
# File 'lib/write_xlsx/utility.rb', line 955

def color(color_code) # :nodoc:
  if color_code and color_code =~ /^#[0-9a-fA-F]{6}$/
    # Convert a HTML style #RRGGBB color.
    color_code.sub(/^#/, '').upcase
  else
    index = Format.color(color_code)
    raise "Unknown color '#{color_code}' used in chart formatting." unless index

    palette_color(index)
  end
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.



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
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/write_xlsx/utility.rb', line 151

def convert_date_time(date_time_string)       # :nodoc:
  date_time = date_time_string.to_s.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   = ::Regexp.last_match(1).to_i
      min    = ::Regexp.last_match(2).to_i
      sec    = ::Regexp.last_match(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   = ::Regexp.last_match(1).to_i
    month  = ::Regexp.last_match(2).to_i
    day    = ::Regexp.last_match(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

#convert_font_args(params) ⇒ Object

Convert user defined font values into private hash values.



789
790
791
792
793
794
795
796
797
798
799
800
801
 
# File 'lib/write_xlsx/utility.rb', line 789

def convert_font_args(params)
  return unless params

  font = params_to_font(params)

  # Convert font size units.
  font[:_size] *= 100 if font[:_size] && font[:_size] != 0

  # Convert rotation into 60,000ths of a degree.
  font[:_rotation] = 60_000 * font[:_rotation].to_i if ptrue?(font[:_rotation])

  font
end

#dash_types ⇒ Object 



730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
 
# File 'lib/write_xlsx/utility.rb', line 730

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

#escape_url(url) ⇒ Object 



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

def escape_url(url)
  unless url =~ /%[0-9a-fA-F]{2}/
    # Escape the URL escape symbol.
    url = url.gsub("%", "%25")

    # Escape whitespae in URL.
    url = url.gsub(/[\s\x00]/, '%20')

    # Escape other special characters in URL.
    re = /(["<>\[\]`^{}])/
    while re =~ url
      match = $~[1]
      url = url.sub(re, sprintf("%%%x", match.ord))
    end
  end

  url
end

#fill_properties(fill) ⇒ Object

Convert user defined fill properties to the structure required internally.



632
633
634
 
# File 'lib/write_xlsx/utility.rb', line 632

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

#float_to_str(float) ⇒ Object 



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

def float_to_str(float)
  return '' unless float

  if float == float.to_i
    float.to_i.to_s
  else
    float.to_s
  end
end

#get_font_latin_attributes(font) ⇒ Object

Get the font latin attributes from a font hash.



912
913
914
915
916
917
918
919
920
921
922
 
# File 'lib/write_xlsx/utility.rb', line 912

def get_font_latin_attributes(font)
  return [] unless font
  return [] unless font.respond_to?(:[])

  attributes = []
  attributes << ['typeface', font[:_name]]            if ptrue?(font[:_name])
  attributes << ['pitchFamily', font[:_pitch_family]] if font[:_pitch_family]
  attributes << ['charset', font[:_charset]]          if font[:_charset]

  attributes
end

#get_font_style_attributes(font) ⇒ Object

Get the font style attributes from a font hash.



970
971
972
973
974
975
976
977
978
979
980
981
982
983
 
# File 'lib/write_xlsx/utility.rb', line 970

def get_font_style_attributes(font)
  return [] unless font
  return [] unless font.respond_to?(:[])

  attributes = []
  attributes << ['sz', font[:_size]]      if ptrue?(font[:_size])
  attributes << ['b',  font[:_bold]]      if font[:_bold]
  attributes << ['i',  font[:_italic]]    if font[:_italic]
  attributes << %w[u sng]             if font[:_underline]

  # Turn off baseline when testing fonts that don't have it.
  attributes << ['baseline', font[:_baseline]] if font[:_baseline] != -1
  attributes
end

#get_image_properties(filename) ⇒ Object

Extract information from the image file such as dimension, type, filename, and extension. Also keep track of previously seen images to optimise out any duplicates.



997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
 
# File 'lib/write_xlsx/utility.rb', line 997

def get_image_properties(filename)
  # Note the image_id, and previous_images mechanism isn't currently used.
  x_dpi = 96
  y_dpi = 96

  workbook = @workbook || self

  # Open the image file and import the data.
  data = File.binread(filename)
  md5  = Digest::MD5.hexdigest(data)
  if data.unpack1('x A3') == 'PNG'
    # Test for PNGs.
    type, width, height, x_dpi, y_dpi = process_png(data)
    workbook.image_types[:png] = 1
  elsif data.unpack1('n') == 0xFFD8
    # Test for JPEG files.
    type, width, height, x_dpi, y_dpi = process_jpg(data, filename)
    workbook.image_types[:jpeg] = 1
  elsif data.unpack1('A4') == 'GIF8'
    # Test for GIFs.
    type, width, height, x_dpi, y_dpi = process_gif(data, filename)
    workbook.image_types[:gif] = 1
  elsif data.unpack1('A2') == 'BM'
    # Test for BMPs.
    type, width, height = process_bmp(data, filename)
    workbook.image_types[:bmp] = 1
  else
    # TODO. Add Image::Size to support other types.
    raise "Unsupported image format for file: #{filename}\n"
  end

  # Set a default dpi for images with 0 dpi.
  x_dpi = 96 if x_dpi == 0
  y_dpi = 96 if y_dpi == 0

  [type, width, height, File.basename(filename), x_dpi, y_dpi, md5]
end

#layout_properties(args, is_text = false) ⇒ Object

Convert user defined layout properties to the format required internally.



460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
 
# File 'lib/write_xlsx/utility.rb', line 460

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

  properties = is_text ? %i[x y] : %i[x y width height]

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

  # Set the layout properties
  layout = {}
  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

#legend_properties(params) ⇒ Object

Convert user defined legend properties to the structure required internally.



408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
 
# File 'lib/write_xlsx/utility.rb', line 408

def legend_properties(params)
  legend = Writexlsx::Chart::Legend.new

  legend.position      = params[:position] || 'right'
  legend.delete_series = params[:delete_series]
  legend.font          = convert_font_args(params[:font])

  # Set the legend layout.
  legend.layout = layout_properties(params[:layout])

  # Turn off the legend.
  legend.position = 'none' if params[:none]

  # Set the line properties for the legend.
  line = line_properties(params[:line])

  # Allow 'border' as a synonym for 'line'.
  line = line_properties(params[:border]) if params[:border]

  # Set the fill properties for the legend.
  fill = fill_properties(params[:fill])

  # Set the pattern properties for the legend.
  pattern = pattern_properties(params[:pattern])

  # Set the gradient fill properties for the legend.
  gradient = gradient_properties(params[:gradient])

  # Pattern fill overrides solid fill.
  fill = nil if pattern

  # Gradient fill overrides solid and pattern fills.
  if gradient
    pattern = nil
    fill    = nil
  end

  # Set the legend layout.
  layout = layout_properties(params[:layout])

  legend.line     = line
  legend.fill     = fill
  legend.pattern  = pattern
  legend.gradient = gradient
  legend.layout   = layout

  @legend = legend
end

#line_fill_properties(params) ⇒ Object 



721
722
723
724
725
726
727
728
 
# File 'lib/write_xlsx/utility.rb', line 721

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.



623
624
625
626
627
 
# File 'lib/write_xlsx/utility.rb', line 623

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 



752
753
754
755
756
757
758
 
# File 'lib/write_xlsx/utility.rb', line 752

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

  r, g, b = @palette[idx]
  sprintf("%02X%02X%02X", r, g, b)
end

#params_to_font(params) ⇒ Object 



803
804
805
806
807
808
809
810
811
812
813
814
815
816
 
# File 'lib/write_xlsx/utility.rb', line 803

def params_to_font(params)
  {
    _name:         params[:name],
    _color:        params[:color],
    _size:         params[:size],
    _bold:         params[:bold],
    _italic:       params[:italic],
    _underline:    params[:underline],
    _pitch_family: params[:pitch_family],
    _charset:      params[:charset],
    _baseline:     params[:baseline] || 0,
    _rotation:     params[:rotation]
  }
end

#pattern_properties(args) ⇒ Object

Convert user defined pattern properties to the structure required internally.



639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
 
# File 'lib/write_xlsx/utility.rb', line 639

def pattern_properties(args) # :nodoc:
  pattern = {}

  return nil unless args

  # Check the pattern type is present.
  return nil unless args.has_key?(:pattern)

  # Check the foreground color is present.
  retuen nil unless args.has_key?(:fg_color)

  types = {
    'percent_5'                => 'pct5',
    'percent_10'               => 'pct10',
    'percent_20'               => 'pct20',
    'percent_25'               => 'pct25',
    'percent_30'               => 'pct30',
    'percent_40'               => 'pct40',

    'percent_50'               => 'pct50',
    'percent_60'               => 'pct60',
    'percent_70'               => 'pct70',
    'percent_75'               => 'pct75',
    'percent_80'               => 'pct80',
    'percent_90'               => 'pct90',

    'light_downward_diagonal'  => 'ltDnDiag',
    'light_upward_diagonal'    => 'ltUpDiag',
    'dark_downward_diagonal'   => 'dkDnDiag',
    'dark_upward_diagonal'     => 'dkUpDiag',
    'wide_downward_diagonal'   => 'wdDnDiag',
    'wide_upward_diagonal'     => 'wdUpDiag',

    'light_vertical'           => 'ltVert',
    'light_horizontal'         => 'ltHorz',
    'narrow_vertical'          => 'narVert',
    'narrow_horizontal'        => 'narHorz',
    'dark_vertical'            => 'dkVert',
    'dark_horizontal'          => 'dkHorz',

    'dashed_downward_diagonal' => 'dashDnDiag',
    'dashed_upward_diagonal'   => 'dashUpDiag',
    'dashed_horizontal'        => 'dashHorz',
    'dashed_vertical'          => 'dashVert',
    'small_confetti'           => 'smConfetti',
    'large_confetti'           => 'lgConfetti',

    'zigzag'                   => 'zigZag',
    'wave'                     => 'wave',
    'diagonal_brick'           => 'diagBrick',
    'horizontal_brick'         => 'horzBrick',
    'weave'                    => 'weave',
    'plaid'                    => 'plaid',

    'divot'                    => 'divot',
    'dotted_grid'              => 'dotGrid',
    'dotted_diamond'           => 'dotDmnd',
    'shingle'                  => 'shingle',
    'trellis'                  => 'trellis',
    'sphere'                   => 'sphere',

    'small_grid'               => 'smGrid',
    'large_grid'               => 'lgGrid',
    'small_check'              => 'smCheck',
    'large_check'              => 'lgCheck',
    'outlined_diamond'         => 'openDmnd',
    'solid_diamond'            => 'solidDmnd'
  }

  # Check for valid types.
  if types[args[:pattern]]
    pattern[:pattern] = types[args[:pattern]]
  else
    raise "Unknown pattern type '#{args[:pattern]}'"
  end

  pattern[:bg_color] = args[:bg_color] || '#FFFFFF'
  pattern[:fg_color] = args[:fg_color]

  pattern
end

#pixels_to_points(vertices) ⇒ Object

Convert vertices from pixels to points.



484
485
486
487
488
489
490
491
492
493
494
495
 
# File 'lib/write_xlsx/utility.rb', line 484

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_bmp(data, filename) ⇒ Object

Extract width and height information from a BMP file.



1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
 
# File 'lib/write_xlsx/utility.rb', line 1141

def process_bmp(data, filename)       # :nodoc:
  type     = 'bmp'

  # Check that the file is big enough to be a bitmap.
  raise "#{filename} doesn't contain enough data." if data.bytesize <= 0x36

  # Read the bitmap width and height. Verify the sizes.
  width, height = data.unpack("x18 V2")
  raise "#{filename}: largest image width #{width} supported is 65k." if width > 0xFFFF
  raise "#{filename}: largest image height supported is 65k." if height > 0xFFFF

  # Read the bitmap planes and bpp data. Verify them.
  planes, bitcount = data.unpack("x26 v2")
  raise "#{filename} isn't a 24bit true color bitmap." unless bitcount == 24
  raise "#{filename}: only 1 plane supported in bitmap image." unless planes == 1

  # Read the bitmap compression. Verify compression.
  compression = data.unpack1("x30 V")
  raise "#{filename}: compression not supported in bitmap image." unless compression == 0

  [type, width, height]
end

#process_gif(data, filename) ⇒ Object

Extract width and height information from a GIF file.



1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
 
# File 'lib/write_xlsx/utility.rb', line 1127

def process_gif(data, filename)
  type  = 'gif'
  x_dpi = 96
  y_dpi = 96

  width  = data[6, 2].unpack1("v")
  height = data[8, 2].unpack1("v")

  raise "#{filename}: no size data found in gif image.\n" if height.nil?

  [type, width, height, x_dpi, y_dpi]
end

#process_jpg(data, filename) ⇒ Object 



1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
 
# File 'lib/write_xlsx/utility.rb', line 1079

def process_jpg(data, filename)
  type     = 'jpeg'
  x_dpi    = 96
  y_dpi    = 96

  offset = 2
  data_length = data.bytesize

  # Search through the image data to read the JPEG markers.
  while offset < data_length
    marker  = data[offset + 0, 2].unpack1("n")
    length  = data[offset + 2, 2].unpack1("n")

    # Read the height and width in the 0xFFCn elements
    # (Except C4, C8 and CC which aren't SOF markers).
    if (marker & 0xFFF0) == 0xFFC0 &&
       marker != 0xFFC4 && marker != 0xFFCC
      height = data[offset + 5, 2].unpack1("n")
      width  = data[offset + 7, 2].unpack1("n")
    end

    # Read the DPI in the 0xFFE0 element.
    if marker == 0xFFE0
      units     = data[offset + 11, 1].unpack1("C")
      x_density = data[offset + 12, 2].unpack1("n")
      y_density = data[offset + 14, 2].unpack1("n")

      if units == 1
        x_dpi = x_density
        y_dpi = y_density
      elsif units == 2
        x_dpi = x_density * 2.54
        y_dpi = y_density * 2.54
      end
    end

    offset += length + 2
    break if marker == 0xFFDA
  end

  raise "#{filename}: no size data found in jpeg image.\n" unless height

  [type, width, height, x_dpi, y_dpi]
end

#process_png(data) ⇒ Object

Extract width and height information from a PNG file.



1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
 
# File 'lib/write_xlsx/utility.rb', line 1038

def process_png(data)
  type   = 'png'
  width  = 0
  height = 0
  x_dpi  = 96
  y_dpi  = 96

  offset = 8
  data_length = data.size

  # Search through the image data to read the height and width in th the
  # IHDR element. Also read the DPI in the pHYs element.
  while offset < data_length

    length = data[offset + 0, 4].unpack1("N")
    png_type   = data[offset + 4, 4].unpack1("A4")

    case png_type
    when "IHDR"
      width  = data[offset + 8, 4].unpack1("N")
      height = data[offset + 12, 4].unpack1("N")
    when "pHYs"
      x_ppu = data[offset +  8, 4].unpack1("N")
      y_ppu = data[offset + 12, 4].unpack1("N")
      units = data[offset + 16, 1].unpack1("C")

      if units == 1
        x_dpi = x_ppu * 0.0254
        y_dpi = y_ppu * 0.0254
      end
    end

    offset = offset + length + 12

    break if png_type == "IEND"
  end
  raise "#{filename}: no size data found in png image.\n" unless height

  [type, width, height, x_dpi, y_dpi]
end

#process_workbook_options(*params) ⇒ Object

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



763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
 
# File 'lib/write_xlsx/utility.rb', line 763

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

    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

Returns:

  • (Boolean) 


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

def ptrue?(value)
  if PERL_TRUE_VALUES.include?(value)
    false
  else
    true
  end
end

#put_deprecate_message(method) ⇒ Object 



290
291
292
 
# File 'lib/write_xlsx/utility.rb', line 290

def put_deprecate_message(method)
  warn("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.



127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/write_xlsx/utility.rb', line 127

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 



609
610
611
 
# File 'lib/write_xlsx/utility.rb', line 609

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

#row_col_notation(row_or_a1) ⇒ Object

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



295
296
297
 
# File 'lib/write_xlsx/utility.rb', line 295

def row_col_notation(row_or_a1)   # :nodoc:
  substitute_cellref(row_or_a1) if row_or_a1.respond_to?(:match) && row_or_a1.to_s =~ /^\D/
end

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



516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
 
# File 'lib/write_xlsx/utility.rb', line 516

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 



390
391
392
393
 
# File 'lib/write_xlsx/utility.rb', line 390

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 



385
386
387
388
 
# File 'lib/write_xlsx/utility.rb', line 385

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



305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
 
# File 'lib/write_xlsx/utility.rb', line 305

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(::Regexp.last_match(1) + '1')
    row2, col2 =  xl_cell_to_rowcol(::Regexp.last_match(2) + ROW_MAX.to_s)
    [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(::Regexp.last_match(1))
    row2, col2 =  xl_cell_to_rowcol(::Regexp.last_match(2))
    [row1, col1, row2, col2, *args]
  # Convert a cell reference: 'A1' or 'AD2000'
  when /\$?([A-Z]{1,3}\$?\d+)/
    row1, col1 = xl_cell_to_rowcol(::Regexp.last_match(1))
    [row1, col1, *args]
  else
    raise("Unknown cell reference #{normalized_cell}")
  end
end

#underline_attributes(underline) ⇒ Object 



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

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

#v_shape_attributes_base(id) ⇒ Object 



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

def v_shape_attributes_base(id)
  [
    ['id',    "_x0000_s#{id}"],
    ['type',  type]
  ]
end

#v_shape_style_base(z_index, vertices) ⇒ Object 



504
505
506
507
508
509
510
511
512
513
514
 
# File 'lib/write_xlsx/utility.rb', line 504

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 



746
747
748
749
750
 
# File 'lib/write_xlsx/utility.rb', line 746

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

  hash[key.to_sym]
end

#write_a_body_pr(rot, is_y_axis = nil) ⇒ Object

Write the <a:bodyPr> element.



837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
 
# File 'lib/write_xlsx/utility.rb', line 837

def write_a_body_pr(rot, is_y_axis = nil) # :nodoc:
  rot = -5400000 if !rot && ptrue?(is_y_axis)
  attributes = []
  if rot
    if rot == 16_200_000
      # 270 deg/stacked angle.
      attributes << ['rot',  0]
      attributes << %w[vert wordArtVert]
    elsif rot == 16_260_000
      # 271 deg/stacked angle.
      attributes << ['rot',  0]
      attributes << %w[vert eaVert]
    else
      attributes << ['rot',  rot]
      attributes << %w[vert horz]
    end
  end

  @writer.empty_tag('a:bodyPr', attributes)
end

#write_a_def_rpr(font = nil) ⇒ Object

Write the <a:defRPr> element.



887
888
889
890
891
892
893
 
# File 'lib/write_xlsx/utility.rb', line 887

def write_a_def_rpr(font = nil) # :nodoc:
  write_def_rpr_r_pr_common(
    font,
    get_font_style_attributes(font),
    'a:defRPr'
  )
end

#write_a_end_para_rpr ⇒ Object

Write the <a:endParaRPr> element.



988
989
990
 
# File 'lib/write_xlsx/utility.rb', line 988

def write_a_end_para_rpr # :nodoc:
  @writer.empty_tag('a:endParaRPr', [%w[lang en-US]])
end

#write_a_lst_style ⇒ Object

Write the <a:lstStyle> element.



861
862
863
 
# File 'lib/write_xlsx/utility.rb', line 861

def write_a_lst_style # :nodoc:
  @writer.empty_tag('a:lstStyle')
end

#write_a_p_formula(font = nil) ⇒ Object

Write the <a:p> element for formula titles.



868
869
870
871
872
873
874
875
 
# File 'lib/write_xlsx/utility.rb', line 868

def write_a_p_formula(font = nil) # :nodoc:
  @writer.tag_elements('a:p') do
    # Write the a:pPr element.
    write_a_p_pr_formula(font)
    # Write the a:endParaRPr element.
    write_a_end_para_rpr
  end
end

#write_a_p_pr_formula(font) ⇒ Object

Write the <a:pPr> element for formula titles.



880
881
882
 
# File 'lib/write_xlsx/utility.rb', line 880

def write_a_p_pr_formula(font) # :nodoc:
  @writer.tag_elements('a:pPr') { write_a_def_rpr(font) }
end

#write_a_solid_fill(fill) ⇒ Object

Write the <a:solidFill> element.



927
928
929
930
931
932
933
934
 
# File 'lib/write_xlsx/utility.rb', line 927

def write_a_solid_fill(fill) # :nodoc:
  @writer.tag_elements('a:solidFill') do
    if fill[:color]
      # Write the a:srgbClr element.
      write_a_srgb_clr(color(fill[:color]), fill[:transparency])
    end
  end
end

#write_a_srgb_clr(color, transparency = nil) ⇒ Object

Write the <a:srgbClr> element.



939
940
941
942
943
944
945
946
947
948
949
950
 
# File 'lib/write_xlsx/utility.rb', line 939

def write_a_srgb_clr(color, transparency = nil) # :nodoc:
  tag        = 'a:srgbClr'
  attributes = [['val', color]]

  if ptrue?(transparency)
    @writer.tag_elements(tag, attributes) do
      write_a_alpha(transparency)
    end
  else
    @writer.empty_tag(tag, attributes)
  end
end

#write_anchor ⇒ Object

Write the <x:Anchor> element.



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

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.



564
565
566
 
# File 'lib/write_xlsx/utility.rb', line 564

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

#write_color(name, value, writer = @writer) ⇒ Object

Write the <color> element.



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

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

  writer.empty_tag('color', attributes)
end

#write_comment_path(gradientshapeok, connecttype) ⇒ Object

Write the <v:path> element.



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

def write_comment_path(gradientshapeok, connecttype)
  attributes      = []

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

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

#write_def_rpr_r_pr_common(font, style_attributes, tag) ⇒ Object

:nodoc:



895
896
897
898
899
900
901
902
903
904
905
906
907
 
# File 'lib/write_xlsx/utility.rb', line 895

def write_def_rpr_r_pr_common(font, style_attributes, tag)  # :nodoc:
  latin_attributes = get_font_latin_attributes(font)
  has_color = ptrue?(font) && ptrue?(font[:_color])

  if !latin_attributes.empty? || has_color
    @writer.tag_elements(tag, style_attributes) do
      write_a_solid_fill(color: font[:_color]) if has_color
      write_a_latin(latin_attributes) unless latin_attributes.empty?
    end
  else
    @writer.empty_tag(tag, style_attributes)
  end
end

#write_div(align, font = nil) ⇒ Object

Write the <div> element.



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

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.



535
536
537
 
# File 'lib/write_xlsx/utility.rb', line 535

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

#write_font(font) ⇒ Object

Write the <font> element.



586
587
588
589
590
591
592
593
594
595
596
597
598
 
# File 'lib/write_xlsx/utility.rb', line 586

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.



603
604
605
606
607
 
# File 'lib/write_xlsx/utility.rb', line 603

def write_stroke
  attributes = [%w[joinstyle miter]]

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

#write_tx_pr(font, is_y_axis = nil) ⇒ Object

Write the <c:txPr> element.



821
822
823
824
825
826
827
828
829
830
831
832
 
# File 'lib/write_xlsx/utility.rb', line 821

def write_tx_pr(font, is_y_axis = nil) # :nodoc:
  rotation = nil
  rotation = font[:_rotation] if font && font.respond_to?(:[]) && font[:_rotation]
  @writer.tag_elements('c:txPr') do
    # Write the a:bodyPr element.
    write_a_body_pr(rotation, is_y_axis)
    # Write the a:lstStyle element.
    write_a_lst_style
    # Write the a:p element.
    write_a_p_formula(font)
  end
end

#write_xml_declaration ⇒ Object 



613
614
615
616
617
618
 
# File 'lib/write_xlsx/utility.rb', line 613

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.



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/write_xlsx/utility.rb', line 48

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

  col_abs = ::Regexp.last_match(1) != ""
  col     = ::Regexp.last_match(2)
  row_abs = ::Regexp.last_match(3) != ""
  row     = ::Regexp.last_match(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

  [row, col, row_abs, col_abs]
end

#xl_col_to_name(col, col_absolute) ⇒ Object 



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

def xl_col_to_name(col, col_absolute)
  col_str = ColName.instance.col_str(col)
  if col_absolute
    "#{absolute_char(col_absolute)}#{col_str}"
  else
    # Do not allocate new string
    col_str
  end
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 



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

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)

  if range1 == range2
    range1
  else
    "#{range1}:#{range2}"
  end
end

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



96
97
98
99
100
101
102
103
104
105
 
# File 'lib/write_xlsx/utility.rb', line 96

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_or_name, col, row_absolute = false, col_absolute = false) ⇒ Object

xl_rowcol_to_cell($row, col, row_absolute, col_absolute)



34
35
36
37
38
39
40
 
# File 'lib/write_xlsx/utility.rb', line 34

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

#xl_string_pixel_width(string) ⇒ Object

xl_string_pixel_width($string)

Get the pixel width of a string based on individual character widths taken from Excel. UTF8 characters are given a default width of 8.

Note, Excel adds an additional 7 pixels padding to a cell.



115
116
117
118
119
120
 
# File 'lib/write_xlsx/utility.rb', line 115

def xl_string_pixel_width(string)
  length = 0
  string.to_s.split("").each { |char| length += CHAR_WIDTHS[char] || 8 }

  length
end

#xml_str ⇒ Object 



273
274
275
 
# File 'lib/write_xlsx/utility.rb', line 273

def xml_str
  @writer.string
end