class Creek::Sheet
Attributes
Public Class Methods
# 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
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
# File lib/creek/sheet.rb, line 40 def rows_array rows_array_generator end
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
# 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
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
# 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
# File lib/creek/sheet.rb, line 108 def converter_options @converter_options ||= {shared_strings: @book.shared_strings.dictionary} end
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
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
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