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
 
# 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 = workbook.sheets.map do |sheet|
    unless options[:only_visible_sheets] && sheet['state'] == 'hidden'
      sheet['name']
    end
  end.compact
  @sheets = []
  @sheets_by_name = {}
  @sheet_names.each_with_index do |sheet_name, n|
    @sheets_by_name[sheet_name] = @sheets[n] = Sheet.new(sheet_name, @shared, n, 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 



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

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.



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

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



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

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



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

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



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

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) 


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

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

#comments(sheet = nil) ⇒ Object 



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

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



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

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) 


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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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.



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

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) 


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

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


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

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



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

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

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

Returns:

  • (Boolean) 


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

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

#images(sheet = nil) ⇒ Object 



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

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



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

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] 


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

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



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

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



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

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

#row(rownumber, sheet = nil) ⇒ Object 



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

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



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

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 



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

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

#sheets ⇒ Object 



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

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



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

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