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/images.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, Images, 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.



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
80
81
82
83
84
 
# File 'lib/roo/excelx.rb', line 36

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)
  sheet_options[:empty_cell] = (options[:empty_cell] || false)
  shared_options = {}

  shared_options[:disable_html_wrapper] = (options[:disable_html_wrapper] || 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, shared_options)
  @filename = local_filename(filename_or_stream, @tmpdir, packed)
  process_zipfile(@filename || filename_or_stream)

  @sheet_names = []
  @sheets = []
  @sheets_by_name = {}

  workbook.sheets.each_with_index do |sheet, index|
    next if options[:only_visible_sheets] && sheet['state'] == 'hidden'

    sheet_name = sheet['name']
    @sheet_names << sheet_name
    @sheets_by_name[sheet_name] = @sheets[index] = Sheet.new(sheet_name, @shared, index, sheet_options)
  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 



86
87
88
89
90
91
92
93
 
# File 'lib/roo/excelx.rb', line 86

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.



114
115
116
117
 
# File 'lib/roo/excelx.rb', line 114

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



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

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



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

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



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

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) 


285
286
287
 
# File 'lib/roo/excelx.rb', line 285

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

#comments(sheet = nil) ⇒ Object 



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

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



297
298
299
300
301
302
303
304
 
# File 'lib/roo/excelx.rb', line 297

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) 


232
233
234
235
236
237
238
 
# File 'lib/roo/excelx.rb', line 232

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



227
228
229
230
 
# File 'lib/roo/excelx.rb', line 227

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



207
208
209
210
 
# File 'lib/roo/excelx.rb', line 207

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



214
215
216
217
 
# File 'lib/roo/excelx.rb', line 214

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



143
144
145
 
# File 'lib/roo/excelx.rb', line 143

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



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

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



184
185
186
187
188
 
# File 'lib/roo/excelx.rb', line 184

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



221
222
223
224
 
# File 'lib/roo/excelx.rb', line 221

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.



163
164
165
166
 
# File 'lib/roo/excelx.rb', line 163

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) 


171
172
173
 
# File 'lib/roo/excelx.rb', line 171

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],… 


177
178
179
180
181
 
# File 'lib/roo/excelx.rb', line 177

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



272
273
274
275
 
# File 'lib/roo/excelx.rb', line 272

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

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

Returns:

  • (Boolean) 


266
267
268
 
# File 'lib/roo/excelx.rb', line 266

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

#images(sheet = nil) ⇒ Object 



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

def images(sheet = nil)
  images_names = sheet_for(sheet).images.map(&:last)
  images_names.map { |iname| image_files.find { |ifile| ifile[iname] } }
end

#label(name) ⇒ Object

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



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

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] 


257
258
259
260
261
262
263
264
 
# File 'lib/roo/excelx.rb', line 257

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



148
149
150
 
# File 'lib/roo/excelx.rb', line 148

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



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

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

#row(rownumber, sheet = nil) ⇒ Object 



119
120
121
 
# File 'lib/roo/excelx.rb', line 119

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!)



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

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 



99
100
101
102
103
 
# File 'lib/roo/excelx.rb', line 99

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

#sheets ⇒ Object 



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

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



242
243
244
 
# File 'lib/roo/excelx.rb', line 242

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