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



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

def initialize(filename, options = {})
  packed = options[:packed]
  file_warning = options.fetch(:file_warning, :error)
  cell_max = options.delete(:cell_max)

  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 { |sheet| sheet['name'] }
  @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_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 



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

def method_missing(method,*args)
  if label = workbook.defined_names[method.to_s]
    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.



302
303
304
305
306
 
# File 'lib/roo/excelx.rb', line 302

def cell(row, col, sheet=nil)
  key = normalize(row,col)
  cell = sheet_for(sheet).cells[key]
  cell.value if cell
end

#celltype(row, col, sheet = nil) ⇒ Object

returns the type of a cell:

  • :float

  • :string,

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



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

def celltype(row,col,sheet=nil)
  key = normalize(row, col)
  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



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

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



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

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) 


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

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

#comments(sheet = nil) ⇒ Object 



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

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



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

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) 


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

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



409
410
411
412
 
# File 'lib/roo/excelx.rb', line 409

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



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

def excelx_type(row,col,sheet=nil)
  key = normalize(row,col)
  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



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

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

#first_column(sheet = nil) ⇒ Object

returns the number of the first non-empty column



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

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



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

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



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

def font(row, col, sheet=nil)
  key = normalize(row,col)
  styles.definitions[sheet_for(sheet).cells[key].style]
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.



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

def formula(row,col,sheet=nil)
  key = normalize(row,col)
  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) 


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

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


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

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



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

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

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

Returns:

  • (Boolean) 


453
454
455
 
# File 'lib/roo/excelx.rb', line 453

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



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

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]


443
444
445
446
447
448
449
450
451
 
# File 'lib/roo/excelx.rb', line 443

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



337
338
339
 
# File 'lib/roo/excelx.rb', line 337

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



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

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

#row(rownumber, sheet = nil) ⇒ Object 



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

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



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

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 



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

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

#sheets ⇒ Object 



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

def sheets
  @sheet_names
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



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

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