Class: Roo::Excelx

Inherits:
Base
  • Object
show all
Defined in:
lib/roo/excelx.rb,
lib/roo/excelx/cell.rb,
lib/roo/excelx/sheet.rb,
lib/roo/excelx/styles.rb,
lib/roo/excelx/comments.rb,
lib/roo/excelx/workbook.rb,
lib/roo/excelx/extractor.rb,
lib/roo/excelx/sheet_doc.rb,
lib/roo/excelx/relationships.rb,
lib/roo/excelx/shared_strings.rb

Defined Under Namespace

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

Constant Summary collapse

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.



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/roo/excelx.rb', line 84

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 = File.basename(filename_or_stream)
  end

  @tmpdir = make_tmpdir(basename, options[:tmpdir_root])
  @filename = local_filename(filename_or_stream, @tmpdir, packed)
  @comments_files = []
  @rels_files = []
  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, @rels_files[n], @sheet_files[n], @comments_files[n], styles, shared_strings, workbook, 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 



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

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.



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

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



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

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



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

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



305
306
307
308
 
# File 'lib/roo/excelx.rb', line 305

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) 


311
312
313
 
# File 'lib/roo/excelx.rb', line 311

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

#comments(sheet = nil) ⇒ Object 



315
316
317
318
319
 
# File 'lib/roo/excelx.rb', line 315

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



323
324
325
326
327
328
329
330
 
# File 'lib/roo/excelx.rb', line 323

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) 


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

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



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

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



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

def excelx_type(row, col, sheet = nil)
  key = normalize(row, col)
  safe_send(sheet_for(sheet).cells[key], :excelx_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



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

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

#first_column(sheet = nil) ⇒ Object

returns the number of the first non-empty column



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

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



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

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



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

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

#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.



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

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) 


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

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


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

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



298
299
300
301
 
# File 'lib/roo/excelx.rb', line 298

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

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

Returns:

  • (Boolean) 


292
293
294
 
# File 'lib/roo/excelx.rb', line 292

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



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

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] 


283
284
285
286
287
288
289
290
 
# File 'lib/roo/excelx.rb', line 283

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



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

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



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

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

#row(rownumber, sheet = nil) ⇒ Object 



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

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



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

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, Cell::Coordinate.new(row, col))
end

#sheet_for(sheet) ⇒ Object 



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

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

#sheets ⇒ Object 



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

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



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

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