class Creek::Sheet

Attributes

book[R]
index[R]
name[R]
rid[R]
sheetid[R]
state[R]
visible[R]

Public Class Methods

new(book, name, sheetid, state, visible, rid, index) click to toggle source
# File lib/creek/sheet.rb, line 16
def initialize(book, name, sheetid, state, visible, rid, index)
  @book    = book
  @name    = name
  @sheetid = sheetid
  @visible = visible
  @rid     = rid
  @state   = state
  @index   = index

  # An XLS file has only 256 columns, however, an XLSX or XLSM file can contain up to 16384 columns.
  # This function creates a hash with all valid XLSX column names and associated indices.
  @@excel_col_names = Hash.new
  (0...16384).each do |i|
    @@excel_col_names[col_name(i)] = i
  end
end

Public Instance Methods

rows() click to toggle source

Provides an Enumerator that returns a hash representing each row. The key of the hash is the Cell id and the value is the value of the cell.

# File lib/creek/sheet.rb, line 36
def rows
  rows_generator
end
rows_array() click to toggle source
# File lib/creek/sheet.rb, line 40
def rows_array
  rows_array_generator
end
rows_with_meta_data() click to toggle source

Provides an Enumerator that returns a hash representing each row. The hash contains meta data of the row and a ‘cells’ embended hash which contains the cell contents.

# File lib/creek/sheet.rb, line 47
def rows_with_meta_data
  rows_generator true
end

Private Instance Methods

col_index_for_cell_address(cell_address) click to toggle source
# File lib/creek/sheet.rb, line 134
def col_index_for_cell_address(cell_address)
  col       = cell_address.delete('^A-Z')
  col_index = @@excel_col_names[col]
end
col_name(i) click to toggle source

Returns valid Excel column name for a given column index. For example, returns “A” for 0, “B” for 1 and “AQ” for 42.

# File lib/creek/sheet.rb, line 55
def col_name(i)
  quot = i/26
  (quot>0 ? col_name(quot-1) : "") + (i%26+65).chr
end
convert(value, type, style_idx) click to toggle source
# File lib/creek/sheet.rb, line 103
def convert(value, type, style_idx)
  style = @book.style_types[style_idx.to_i]
  Creek::Styles::Converter.call(value, type, style, converter_options)
end
converter_options() click to toggle source
# File lib/creek/sheet.rb, line 108
def converter_options
  @converter_options ||= {shared_strings: @book.shared_strings.dictionary}
end
fill_in_empty_cells(cells, row_number, last_col) click to toggle source

The unzipped XML file does not contain any node for empty cells. Empty cells are being padded in using this function

# File lib/creek/sheet.rb, line 115
def fill_in_empty_cells(cells, row_number, last_col)
  new_cells = Hash.new
  unless cells.empty?
    keys           = cells.keys.sort
    last_col       = last_col.gsub(row_number, '')
    last_col_index = @@excel_col_names[last_col]
    [*(0..last_col_index)].each do |i|
      col = col_name(i)
      id  = "#{col}#{row_number}"
      unless cells.has_key? id
        new_cells[id] = nil
      else
        new_cells[id] = cells[id]
      end
    end
  end
  new_cells
end
rows_array_generator() click to toggle source

Returns a hash per row that includes the cell ids and values. Empty cells will be also included in the hash with a nil value.

# File lib/creek/sheet.rb, line 143
def rows_array_generator
  path = "xl/worksheets/sheet#{@index}.xml"
  if @book.files.file.exist?(path)
    # SAX parsing, Each element in the stream comes through as two events:
    # one to open the element and one to close it.
    opener = Nokogiri::XML::Reader::TYPE_ELEMENT
    closer = Nokogiri::XML::Reader::TYPE_END_ELEMENT
    Enumerator.new do |y|
      row            = nil
      cell_type      = nil
      cell_style_idx = nil
      cell_address   = nil
      @book.files.file.open(path) do |xml|
        Nokogiri::XML::Reader.from_io(xml).each do |node|
          if (node.name.eql? 'row') and (node.node_type.eql? opener)
            row = []
            y << (row) if node.self_closing?

          elsif (node.name.eql? 'row') and (node.node_type.eql? closer)
            y << row

          elsif (node.name.eql? 'c') and (node.node_type.eql? opener)
            cell_type      = node.attribute('t')
            cell_style_idx = node.attribute('s')
            cell_address   = node.attribute('r')

          elsif (node.name.eql? 'c') and (node.node_type.eql? closer)
            cell_type      = nil
            cell_style_idx = nil
            cell_address   = nil

          elsif (node.name.eql? '#text')
            if !cell_address.nil? and node.value?
              idx      = col_index_for_cell_address(cell_address)
              value    = convert(node.value, cell_type, cell_style_idx)
              row[idx] = value
            end
          end
        end
      end
    end
  end
end
rows_generator(include_meta_data=false) click to toggle source

Returns a hash per row that includes the cell ids and values. Empty cells will be also included in the hash with a nil value.

# File lib/creek/sheet.rb, line 63
def rows_generator(include_meta_data=false)
  path = "xl/worksheets/sheet#{@index}.xml"
  if @book.files.file.exist?(path)
    # SAX parsing, Each element in the stream comes through as two events:
    # one to open the element and one to close it.
    opener = Nokogiri::XML::Reader::TYPE_ELEMENT
    closer = Nokogiri::XML::Reader::TYPE_END_ELEMENT
    Enumerator.new do |y|
      row            = nil
      cells          = {}
      cell           = nil
      cell_type      = nil
      cell_style_idx = nil
      @book.files.file.open(path) do |xml|
        Nokogiri::XML::Reader.from_io(xml).each do |node|
          if (node.name.eql? 'row') and (node.node_type.eql? opener)
            row          = node.attributes
            row['cells'] = Hash.new
            cells        = Hash.new
            y << (include_meta_data ? row : cells) if node.self_closing?
          elsif (node.name.eql? 'row') and (node.node_type.eql? closer)
            processed_cells = fill_in_empty_cells(cells, row['r'], cell)
            row['cells']    = processed_cells
            y << (include_meta_data ? row : processed_cells)
          elsif (node.name.eql? 'c') and (node.node_type.eql? opener)
            cell_type      = node.attribute('t')
            cell_style_idx = node.attribute('s')
            cell           = node.attribute('r')

          elsif node.value?
            if !cell.nil?
              cells[cell] = convert(node.value, cell_type, cell_style_idx)
            end
          end
        end
      end
    end
  end
end