Class: Roo::Excelx

Inherits:
Base
  • Object
show all
Defined in:
lib/roo/excelx.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, #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, 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.



253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/roo/excelx.rb', line 253

def initialize(filename, 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)

  file_type_check(filename,'.xlsx','an Excel-xlsx', file_warning, packed)

  @tmpdir = make_tmpdir(filename.split('/').last, options[:tmpdir_root])
  @filename = local_filename(filename, @tmpdir, packed)
  @comments_files = []
  @rels_files = []
  process_zipfile(@tmpdir, @filename)

  @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
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(method, *args) ⇒ Object 



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

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.



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

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



395
396
397
398
 
# File 'lib/roo/excelx.rb', line 395

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



321
322
323
324
325
326
 
# File 'lib/roo/excelx.rb', line 321

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



474
475
476
477
 
# File 'lib/roo/excelx.rb', line 474

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) 


480
481
482
 
# File 'lib/roo/excelx.rb', line 480

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

#comments(sheet = nil) ⇒ Object 



484
485
486
487
488
 
# File 'lib/roo/excelx.rb', line 484

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



492
493
494
 
# File 'lib/roo/excelx.rb', line 492

def each_row_streaming(options={})
  sheet_for(options.delete(:sheet)).each_row(options) { |row| yield row }
end

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

Returns:

  • (Boolean) 


422
423
424
425
426
427
428
 
# File 'lib/roo/excelx.rb', line 422

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



417
418
419
420
 
# File 'lib/roo/excelx.rb', line 417

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



404
405
406
407
 
# File 'lib/roo/excelx.rb', line 404

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



411
412
413
414
 
# File 'lib/roo/excelx.rb', line 411

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



339
340
341
 
# File 'lib/roo/excelx.rb', line 339

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



329
330
331
 
# File 'lib/roo/excelx.rb', line 329

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



381
382
383
384
385
 
# File 'lib/roo/excelx.rb', line 381

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.



360
361
362
363
 
# File 'lib/roo/excelx.rb', line 360

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) 


368
369
370
 
# File 'lib/roo/excelx.rb', line 368

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


374
375
376
377
378
 
# File 'lib/roo/excelx.rb', line 374

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



467
468
469
470
 
# File 'lib/roo/excelx.rb', line 467

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

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

Returns:

  • (Boolean) 


461
462
463
 
# File 'lib/roo/excelx.rb', line 461

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



438
439
440
441
442
443
444
445
446
447
 
# File 'lib/roo/excelx.rb', line 438

def label(name)
  labels = workbook.defined_names
  if labels.empty? || !labels.key?(name)
    [nil,nil,nil]
  else
    [labels[name].row,
      labels[name].col,
      labels[name].sheet]
  end
end

#labels ⇒ Object

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

labelname, [row,col,sheetname] 


451
452
453
454
455
456
457
458
459
 
# File 'lib/roo/excelx.rb', line 451

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



344
345
346
 
# File 'lib/roo/excelx.rb', line 344

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



334
335
336
 
# File 'lib/roo/excelx.rb', line 334

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

#row(rownumber, sheet = nil) ⇒ Object 



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

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



350
351
352
353
354
 
# File 'lib/roo/excelx.rb', line 350

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 



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

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

#sheets ⇒ Object 



296
297
298
 
# File 'lib/roo/excelx.rb', line 296

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



432
433
434
 
# File 'lib/roo/excelx.rb', line 432

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