Class: Roo::Excelx

Inherits:
Base
  • Object
show all
Extended by:
Forwardable
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, Base::TEMP_PREFIX

Instance Attribute Summary

Attributes inherited from Base

#header_line, #headers

Instance Method Summary collapse

Methods inherited from Base

#cell_type_by_value, #close, #collect_last_row_col_for_sheet, #default_sheet, #default_sheet=, #each, #each_with_pagename, #find, #first_column_as_letter, #first_last_row_col_for_sheet, #info, #inspect, #last_column_as_letter, #parse, #reload, #row_with, #sheet, #to_csv, #to_matrix, #to_xml, #to_yaml

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.



33
34
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
 
# File 'lib/roo/excelx.rb', line 33

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)

  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

  @tmpdir = make_tmpdir(basename, options[:tmpdir_root])
  @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 => e # clean up any temp files, but only if an error was raised
  close
  raise e
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(method, *args) ⇒ Object 



72
73
74
75
76
77
78
79
 
# File 'lib/roo/excelx.rb', line 72

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.



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

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



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

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



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

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



260
261
262
263
 
# File 'lib/roo/excelx.rb', line 260

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) 


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

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

#comments(sheet = nil) ⇒ Object 



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

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



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

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) 


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

def empty?(row, col, sheet = nil)
  sheet = sheet_for(sheet)
  key = normalize(row, col)
  cell = sheet.cells[key]
  !cell || cell.empty? || (cell.type == :string && cell.value.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



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

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



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

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



195
196
197
198
 
# File 'lib/roo/excelx.rb', line 195

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



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

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



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

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



165
166
167
168
169
 
# File 'lib/roo/excelx.rb', line 165

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



202
203
204
205
 
# File 'lib/roo/excelx.rb', line 202

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.



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

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) 


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

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


158
159
160
161
162
 
# File 'lib/roo/excelx.rb', line 158

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



253
254
255
256
 
# File 'lib/roo/excelx.rb', line 253

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

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

Returns:

  • (Boolean) 


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

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



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

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] 


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

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



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

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



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

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

#row(rownumber, sheet = nil) ⇒ Object 



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

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



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

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 



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

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

#sheets ⇒ Object 



81
82
83
 
# File 'lib/roo/excelx.rb', line 81

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



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

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