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



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
286
287
288
289
290
291
292
293
294
295
296
 
# File 'lib/roo/excelx.rb', line 258

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,'.xlsx','an Excel-xlsx', 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 



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

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.



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

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



406
407
408
409
 
# File 'lib/roo/excelx.rb', line 406

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



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

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



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

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) 


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

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

#comments(sheet = nil) ⇒ Object 



495
496
497
498
499
 
# File 'lib/roo/excelx.rb', line 495

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



503
504
505
 
# File 'lib/roo/excelx.rb', line 503

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) 


433
434
435
436
437
438
439
 
# File 'lib/roo/excelx.rb', line 433

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



428
429
430
431
 
# File 'lib/roo/excelx.rb', line 428

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



415
416
417
418
 
# File 'lib/roo/excelx.rb', line 415

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



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

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



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

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



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

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



392
393
394
395
396
 
# File 'lib/roo/excelx.rb', line 392

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.



371
372
373
374
 
# File 'lib/roo/excelx.rb', line 371

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) 


379
380
381
 
# File 'lib/roo/excelx.rb', line 379

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


385
386
387
388
389
 
# File 'lib/roo/excelx.rb', line 385

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



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

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

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

Returns:

  • (Boolean) 


472
473
474
 
# File 'lib/roo/excelx.rb', line 472

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



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

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] 


462
463
464
465
466
467
468
469
470
 
# File 'lib/roo/excelx.rb', line 462

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



355
356
357
 
# File 'lib/roo/excelx.rb', line 355

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



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

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

#row(rownumber, sheet = nil) ⇒ Object 



326
327
328
 
# File 'lib/roo/excelx.rb', line 326

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



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

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 



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

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

#sheets ⇒ Object 



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

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



443
444
445
 
# File 'lib/roo/excelx.rb', line 443

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