Module: Writexlsx::Utility

Included in:
Chart, Chart::Area, Chart::Bar, Chart::Column, Chart::Line, Chart::Pie, Chart::Scatter, Chart::Stock, Package::App, Package::Comment, Package::Comments, Package::ContentTypes, Package::Core, Package::Packager, Package::Relationships, Package::SharedStrings, Package::Styles, Package::Theme, Package::Vml, Workbook, Worksheet, Worksheet::CellData
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 



102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/write_xlsx/utility.rb', line 102

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 



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

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

#put_deprecate_message(method) ⇒ Object 



114
115
116
 
# File 'lib/write_xlsx/utility.rb', line 114

def put_deprecate_message(method)
  $stderr.puts("Warning: calling deprecated method #{method}. This method will be removed in a future release.")
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”).



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

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 



150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/write_xlsx/utility.rb', line 150

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

#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 



98
99
100
 
# File 'lib/write_xlsx/utility.rb', line 98

def xml_str
  @writer.string
end