class Roo::Excelx

Constants

ExceedsMaxError

Public Class Methods

new(filename, options = {}) click to toggle source

initialization and opening of a spreadsheet file values for packed: :zip optional cell_max (int) parameter for early aborting attempts to parse enormous documents.

Calls superclass method Roo::Base::new
# 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

Public Instance Methods

cell(row, col, sheet=nil) click to toggle source

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.

# 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) click to toggle source

returns the type of a cell:

  • :float

  • :string,

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime

# 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) click to toggle source

returns all values in this column as an array column numbers are 1,2,3,… like in the spreadsheet

# 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) click to toggle source

returns the comment at (row/col) nil if there is no comment

# 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) click to toggle source

true, if there is a comment

# File lib/roo/excelx.rb, line 472
def comment?(row,col,sheet=nil)
  !!comment(row,col,sheet)
end
comments(sheet=nil) click to toggle source
# 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={}) { |row| ... } click to toggle source

Yield an array of Excelx::Cell Takes options for sheet, pad_cells, and max_rows

# 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) click to toggle source
# 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) click to toggle source

returns the internal format of an excel cell

# 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) click to toggle source

returns the internal type of an excel cell

  • :numeric_or_formula

  • :string

Note: this is only available within the Excelx class

# 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) click to toggle source

returns the internal value of an excelx cell Note: this is only available within the Excelx class

# 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) click to toggle source

returns the number of the first non-empty column

# File lib/roo/excelx.rb, line 332
def first_column(sheet=nil)
  sheet_for(sheet).first_column
end
first_row(sheet=nil) click to toggle source

returns the number of the first non-empty row

# File lib/roo/excelx.rb, line 322
def first_row(sheet=nil)
  sheet_for(sheet).first_row
end
font(row, col, sheet=nil) click to toggle source

Given a cell, return the cell’s style

# 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) click to toggle source

Returns the formula at (row,col). Returns nil if there is no formula. The method formula? checks if there is a formula.

# 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) click to toggle source

Predicate methods really should return a boolean value. Hopefully no one was relying on the fact that this previously returned either nil/formula

# File lib/roo/excelx.rb, line 361
def formula?(*args)
  !!formula(*args)
end
formulas(sheet=nil) click to toggle source

returns each formula in the selected sheet as an array of tuples in following format

[row, col, formula], [row, col, formula],…
# 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
label(name) click to toggle source

returns the row,col values of the labelled cell (nil,nil) if label is not defined

# 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() click to toggle source

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

labelname, [row,col,sheetname]
# 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) click to toggle source

returns the number of the last non-empty column

# File lib/roo/excelx.rb, line 337
def last_column(sheet=nil)
  sheet_for(sheet).last_column
end
last_row(sheet=nil) click to toggle source

returns the number of the last non-empty row

# File lib/roo/excelx.rb, line 327
def last_row(sheet=nil)
  sheet_for(sheet).last_row
end
method_missing(method,*args) click to toggle source
Calls superclass method Roo::Base#method_missing
# 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
row(rownumber,sheet=nil) click to toggle source
# File lib/roo/excelx.rb, line 308
def row(rownumber,sheet=nil)
  sheet_for(sheet).row(rownumber)
end
sheet_for(sheet) click to toggle source
# File lib/roo/excelx.rb, line 292
def sheet_for(sheet)
  sheet ||= default_sheet
  validate_sheet!(sheet)
  @sheets_by_name[sheet]
end
sheets() click to toggle source
# File lib/roo/excelx.rb, line 288
def sheets
  @sheet_names
end
to_s(sheet=nil) click to toggle source

shows the internal representation of all cells for debugging purposes

# File lib/roo/excelx.rb, line 424
def to_s(sheet=nil)
  sheet_for(sheet).cells.inspect
end

Private Instance Methods

process_zipfile(tmpdir, zipfilename) click to toggle source

Extracts all needed files from the zip file

# File lib/roo/excelx.rb, line 491
def process_zipfile(tmpdir, zipfilename)
  @sheet_files = []
  Zip::File.foreach(zipfilename) do |entry|
    path =
      case entry.name.downcase
      when /workbook.xml$/
        "#{tmpdir}/roo_workbook.xml"
      when /sharedstrings.xml$/
        "#{tmpdir}/roo_sharedStrings.xml"
      when /styles.xml$/
        "#{tmpdir}/roo_styles.xml"
      when /sheet.xml$/
        path = "#{tmpdir}/roo_sheet"
        @sheet_files.unshift(path)
        path
      when /sheet([0-9]+).xml$/
        # Numbers 3.1 exports first sheet without sheet number. Such sheets
        # are always added to the beginning of the array which, naturally,
        # causes other sheets to be pushed to the next index which could
        # lead to sheet references getting overwritten, so we need to
        # handle that case specifically.
        nr = $1
        sheet_files_index = nr.to_i - 1
        sheet_files_index += 1 if @sheet_files[sheet_files_index]
        @sheet_files[sheet_files_index] = "#{tmpdir}/roo_sheet#{nr.to_i}"
      when /comments([0-9]+).xml$/
        nr = $1
        @comments_files[nr.to_i-1] = "#{tmpdir}/roo_comments#{nr}"
      when /sheet([0-9]+).xml.rels$/
        nr = $1
        @rels_files[nr.to_i-1] = "#{tmpdir}/roo_rels#{nr}"
      end
    if path
      entry.extract(path)
    end
  end
end
shared_strings() click to toggle source
# File lib/roo/excelx.rb, line 533
def shared_strings
  @shared_strings ||= SharedStrings.new(File.join(@tmpdir, 'roo_sharedStrings.xml'))
end
styles() click to toggle source
# File lib/roo/excelx.rb, line 529
def styles
  @styles ||= Styles.new(File.join(@tmpdir, 'roo_styles.xml'))
end
workbook() click to toggle source
# File lib/roo/excelx.rb, line 537
def workbook
  @workbook ||= Workbook.new(File.join(@tmpdir, "roo_workbook.xml"))
end