Class: Roo::Excelx

Inherits:
Base
  • Object
show all
Extended by:
Forwardable, Tempdir
Defined in:
lib/roo/excelx.rb,
lib/roo/excelx/cell.rb,
lib/roo/excelx/sheet.rb,
lib/roo/excelx/format.rb,
lib/roo/excelx/shared.rb,
lib/roo/excelx/styles.rb,
lib/roo/excelx/comments.rb,
lib/roo/excelx/workbook.rb,
lib/roo/excelx/cell/base.rb,
lib/roo/excelx/cell/date.rb,
lib/roo/excelx/cell/time.rb,
lib/roo/excelx/extractor.rb,
lib/roo/excelx/sheet_doc.rb,
lib/roo/excelx/cell/empty.rb,
lib/roo/excelx/coordinate.rb,
lib/roo/excelx/cell/number.rb,
lib/roo/excelx/cell/string.rb,
lib/roo/excelx/cell/boolean.rb,
lib/roo/excelx/cell/datetime.rb,
lib/roo/excelx/relationships.rb,
lib/roo/excelx/shared_strings.rb

Defined Under Namespace

Modules: Format Classes: Cell, Comments, Coordinate, Extractor, Relationships, Shared, SharedStrings, Sheet, SheetDoc, Styles, Workbook

Constant Summary collapse

ERROR_VALUES = 
%w(#N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM!).to_set
ExceedsMaxError = 
Class.new(StandardError)

Constants inherited from Base

Base::MAX_ROW_COL, Base::MIN_ROW_COL

Instance Attribute Summary

Attributes inherited from Base

#header_line, #headers

Instance Method Summary collapse

Methods included from Tempdir

finalize_tempdirs, make_tempdir

Methods inherited from Base

TEMP_PREFIX, #cell_type_by_value, #close, #collect_last_row_col_for_sheet, #default_sheet, #default_sheet=, #each, #each_with_pagename, finalize, #find, #first_column_as_letter, #first_last_row_col_for_sheet, #info, #inspect, #last_column_as_letter, #parse, #reload, #row_with, #sheet

Methods included from Formatters::YAML

#to_yaml

Methods included from Formatters::XML

#to_xml

Methods included from Formatters::Matrix

#to_matrix

Methods included from Formatters::CSV

#to_csv

Methods included from Formatters::Base

#integer_to_timestring

Constructor Details

#initialize(filename_or_stream, options = {}) ⇒ Excelx

initialization and opening of a spreadsheet file values for packed: :zip optional cell_max (int) parameter for early aborting attempts to parse enormous documents.



35
36
37
38
39
40
41
42
43
44
45
46
47
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
73
74
75
76
77
78
79
 
# File 'lib/roo/excelx.rb', line 35

def initialize(filename_or_stream, options = {})
  packed = options[:packed]
  file_warning = options.fetch(:file_warning, :error)
  cell_max = options.delete(:cell_max)
  sheet_options = {}
  sheet_options[:expand_merged_ranges] = (options[:expand_merged_ranges] || false)
  sheet_options[:no_hyperlinks] = (options[:no_hyperlinks] || false)

  unless is_stream?(filename_or_stream)
    file_type_check(filename_or_stream, %w[.xlsx .xlsm], 'an Excel 2007', file_warning, packed)
    basename = find_basename(filename_or_stream)
  end

  # NOTE: Create temp directory and allow Ruby to cleanup the temp directory
  #       when the object is garbage collected. Initially, the finalizer was
  #       created in the Roo::Tempdir module, but that led to a segfault
  #       when testing in Ruby 2.4.0.
  @tmpdir = self.class.make_tempdir(self, basename, options[:tmpdir_root])
  ObjectSpace.define_finalizer(self, self.class.finalize(object_id))

  @shared = Shared.new(@tmpdir)
  @filename = local_filename(filename_or_stream, @tmpdir, packed)
  process_zipfile(@filename || filename_or_stream)

  @sheet_names = workbook.sheets.map do |sheet|
    unless options[:only_visible_sheets] && sheet['state'] == 'hidden'
      sheet['name']
    end
  end.compact
  @sheets = []
  @sheets_by_name = Hash[@sheet_names.map.with_index do |sheet_name, n|
    @sheets[n] = Sheet.new(sheet_name, @shared, n, sheet_options)
    [sheet_name, @sheets[n]]
  end]

  if cell_max
    cell_count = ::Roo::Utils.num_cells_in_range(sheet_for(options.delete(:sheet)).dimensions)
    raise ExceedsMaxError.new("Excel file exceeds cell maximum: #{cell_count} > #{cell_max}") if cell_count > cell_max
  end

  super
rescue
  self.class.finalize_tempdirs(object_id)
  raise
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(method, *args) ⇒ Object 



81
82
83
84
85
86
87
88
 
# File 'lib/roo/excelx.rb', line 81

def method_missing(method, *args)
  if (label = workbook.defined_names[method.to_s])
    safe_send(sheet_for(label.sheet).cells[label.key], :value)
  else
    # call super for methods like #a1
    super
  end
end

Instance Method Details

#cell(row, col, sheet = nil) ⇒ Object

Returns the content of a spreadsheet-cell. (1,1) is the upper left corner. (1,1), (1,‘A’), (‘A’,1), (‘a’,1) all refers to the cell at the first line and first row.



104
105
106
107
 
# File 'lib/roo/excelx.rb', line 104

def cell(row, col, sheet = nil)
  key = normalize(row, col)
  safe_send(sheet_for(sheet).cells[key], :value)
end

#celltype(row, col, sheet = nil) ⇒ Object

returns the type of a cell:

  • :float

  • :string,

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



188
189
190
191
 
# File 'lib/roo/excelx.rb', line 188

def celltype(row, col, sheet = nil)
  key = normalize(row, col)
  safe_send(sheet_for(sheet).cells[key], :type)
end

#column(column_number, sheet = nil) ⇒ Object

returns all values in this column as an array column numbers are 1,2,3,… like in the spreadsheet



115
116
117
118
119
120
 
# File 'lib/roo/excelx.rb', line 115

def column(column_number, sheet = nil)
  if column_number.is_a?(::String)
    column_number = ::Roo::Utils.letter_to_number(column_number)
  end
  sheet_for(sheet).column(column_number)
end

#comment(row, col, sheet = nil) ⇒ Object

returns the comment at (row/col) nil if there is no comment



269
270
271
272
 
# File 'lib/roo/excelx.rb', line 269

def comment(row, col, sheet = nil)
  key = normalize(row, col)
  sheet_for(sheet).comments[key]
end

#comment?(row, col, sheet = nil) ⇒ Boolean

true, if there is a comment

Returns:

  • (Boolean) 


275
276
277
 
# File 'lib/roo/excelx.rb', line 275

def comment?(row, col, sheet = nil)
  !!comment(row, col, sheet)
end

#comments(sheet = nil) ⇒ Object 



279
280
281
282
283
 
# File 'lib/roo/excelx.rb', line 279

def comments(sheet = nil)
  sheet_for(sheet).comments.map do |(x, y), comment|
    [x, y, comment]
  end
end

#each_row_streaming(options = {}) ⇒ Object

Yield an array of Excelx::Cell Takes options for sheet, pad_cells, and max_rows



287
288
289
290
291
292
293
294
 
# File 'lib/roo/excelx.rb', line 287

def each_row_streaming(options = {})
  sheet = sheet_for(options.delete(:sheet))
  if block_given?
    sheet.each_row(options) { |row| yield row }
  else
    sheet.to_enum(:each_row, options)
  end
end

#empty?(row, col, sheet = nil) ⇒ Boolean

Returns:

  • (Boolean) 


222
223
224
225
226
227
228
 
# File 'lib/roo/excelx.rb', line 222

def empty?(row, col, sheet = nil)
  sheet = sheet_for(sheet)
  key = normalize(row, col)
  cell = sheet.cells[key]
  !cell || cell.empty? ||
    (row < sheet.first_row || row > sheet.last_row || col < sheet.first_column || col > sheet.last_column)
end

#excelx_format(row, col, sheet = nil) ⇒ Object

returns the internal format of an excel cell



217
218
219
220
 
# File 'lib/roo/excelx.rb', line 217

def excelx_format(row, col, sheet = nil)
  key = normalize(row, col)
  sheet_for(sheet).excelx_format(key)
end

#excelx_type(row, col, sheet = nil) ⇒ Object

returns the internal type of an excel cell

  • :numeric_or_formula

  • :string

Note: this is only available within the Excelx class



197
198
199
200
 
# File 'lib/roo/excelx.rb', line 197

def excelx_type(row, col, sheet = nil)
  key = normalize(row, col)
  safe_send(sheet_for(sheet).cells[key], :cell_type)
end

#excelx_value(row, col, sheet = nil) ⇒ Object

returns the internal value of an excelx cell Note: this is only available within the Excelx class



204
205
206
207
 
# File 'lib/roo/excelx.rb', line 204

def excelx_value(row, col, sheet = nil)
  key = normalize(row, col)
  safe_send(sheet_for(sheet).cells[key], :cell_value)
end

#first_column(sheet = nil) ⇒ Object

returns the number of the first non-empty column



133
134
135
 
# File 'lib/roo/excelx.rb', line 133

def first_column(sheet = nil)
  sheet_for(sheet).first_column
end

#first_row(sheet = nil) ⇒ Object

returns the number of the first non-empty row



123
124
125
 
# File 'lib/roo/excelx.rb', line 123

def first_row(sheet = nil)
  sheet_for(sheet).first_row
end

#font(row, col, sheet = nil) ⇒ Object

Given a cell, return the cell’s style



174
175
176
177
178
 
# File 'lib/roo/excelx.rb', line 174

def font(row, col, sheet = nil)
  key = normalize(row, col)
  definition_index = safe_send(sheet_for(sheet).cells[key], :style)
  styles.definitions[definition_index] if definition_index
end

#formatted_value(row, col, sheet = nil) ⇒ Object

returns the internal value of an excelx cell Note: this is only available within the Excelx class



211
212
213
214
 
# File 'lib/roo/excelx.rb', line 211

def formatted_value(row, col, sheet = nil)
  key = normalize(row, col)
  safe_send(sheet_for(sheet).cells[key], :formatted_value)
end

#formula(row, col, sheet = nil) ⇒ Object

Returns the formula at (row,col). Returns nil if there is no formula. The method #formula? checks if there is a formula.



153
154
155
156
 
# File 'lib/roo/excelx.rb', line 153

def formula(row, col, sheet = nil)
  key = normalize(row, col)
  safe_send(sheet_for(sheet).cells[key], :formula)
end

#formula?(*args) ⇒ Boolean

Predicate methods really should return a boolean value. Hopefully no one was relying on the fact that this previously returned either nil/formula

Returns:

  • (Boolean) 


161
162
163
 
# File 'lib/roo/excelx.rb', line 161

def formula?(*args)
  !!formula(*args)
end

#formulas(sheet = nil) ⇒ Object

returns each formula in the selected sheet as an array of tuples in following format

[row, col, formula], [row, col, formula],… 


167
168
169
170
171
 
# File 'lib/roo/excelx.rb', line 167

def formulas(sheet = nil)
  sheet_for(sheet).cells.select { |_, cell| cell.formula }.map do |(x, y), cell|
    [x, y, cell.formula]
  end
end

#hyperlink(row, col, sheet = nil) ⇒ Object

returns the hyperlink at (row/col) nil if there is no hyperlink



262
263
264
265
 
# File 'lib/roo/excelx.rb', line 262

def hyperlink(row, col, sheet = nil)
  key = normalize(row, col)
  sheet_for(sheet).hyperlinks[key]
end

#hyperlink?(row, col, sheet = nil) ⇒ Boolean

Returns:

  • (Boolean) 


256
257
258
 
# File 'lib/roo/excelx.rb', line 256

def hyperlink?(row, col, sheet = nil)
  !!hyperlink(row, col, sheet)
end

#label(name) ⇒ Object

returns the row,col values of the labelled cell (nil,nil) if label is not defined



238
239
240
241
242
243
 
# File 'lib/roo/excelx.rb', line 238

def label(name)
  labels = workbook.defined_names
  return [nil, nil, nil] if labels.empty? || !labels.key?(name)

  [labels[name].row, labels[name].col, labels[name].sheet]
end

#labels ⇒ Object

Returns an array which all labels. Each element is an array with

labelname, [row,col,sheetname] 


247
248
249
250
251
252
253
254
 
# File 'lib/roo/excelx.rb', line 247

def labels
  @labels ||= workbook.defined_names.map do |name, label|
    [
      name,
      [label.row, label.col, label.sheet]
    ]
  end
end

#last_column(sheet = nil) ⇒ Object

returns the number of the last non-empty column



138
139
140
 
# File 'lib/roo/excelx.rb', line 138

def last_column(sheet = nil)
  sheet_for(sheet).last_column
end

#last_row(sheet = nil) ⇒ Object

returns the number of the last non-empty row



128
129
130
 
# File 'lib/roo/excelx.rb', line 128

def last_row(sheet = nil)
  sheet_for(sheet).last_row
end

#row(rownumber, sheet = nil) ⇒ Object 



109
110
111
 
# File 'lib/roo/excelx.rb', line 109

def row(rownumber, sheet = nil)
  sheet_for(sheet).row(rownumber)
end

#set(row, col, value, sheet = nil) ⇒ Object

set a cell to a certain value (this will not be saved back to the spreadsheet file!)



144
145
146
147
148
 
# File 'lib/roo/excelx.rb', line 144

def set(row, col, value, sheet = nil) #:nodoc:
  key = normalize(row, col)
  cell_type = cell_type_by_value(value)
  sheet_for(sheet).cells[key] = Cell.new(value, cell_type, nil, cell_type, value, nil, nil, nil, Coordinate.new(row, col))
end

#sheet_for(sheet) ⇒ Object 



94
95
96
97
98
 
# File 'lib/roo/excelx.rb', line 94

def sheet_for(sheet)
  sheet ||= default_sheet
  validate_sheet!(sheet)
  @sheets_by_name[sheet]
end

#sheets ⇒ Object 



90
91
92
 
# File 'lib/roo/excelx.rb', line 90

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



232
233
234
 
# File 'lib/roo/excelx.rb', line 232

def to_s(sheet = nil)
  sheet_for(sheet).cells.inspect
end