Using Column headers to parse excel sheets using roo - Ruby

Question

Can we use column headers to specify the column number from which we are parsing the excel sheet using roo gem? My current code is like this now:

oo = Openoffice.new("simple_spreadsheet.ods")
oo.default_sheet = oo.sheets.first
(2..oo.last_row).each do |line|
  date       = oo.cell(line,'A')
  start_time = oo.cell(line,'B')
  end_time   = oo.cell(line,'C')
  pause      = oo.cell(line,'D')
  ...
end

I would like to parse from column headers instead of specifying columns as 'A' 'B' 'C' ... Can I acheive this using Roo?

as I know only digits or alphabet can be used to access a cell. — Anatoly
– Anatoly, Commented Jul 20, 2011 at 18:05
@mu is too short..yeah..u r right..marvellous colors they got there :P — rubyprince
– rubyprince, Commented Jul 20, 2011 at 18:46

Rajkaran Mishra · Accepted Answer · 2019-11-19 14:56:31Z

3

You can grab the entire header row as an array and hash the entire row key'd on the header row.

oo = Openoffice.new("simple_spreadsheet.ods") 
oo.default_sheet = oo.sheets.first 
header = oo.row(1) 
2.upto(oo.last_row) do |line|  
  row_data =  Hash[header.zip oo.row(line)]
  ...
end

You could also use row_data[line] to nest the hashes for later use.

edited Nov 19, 2019 at 14:56

Rajkaran Mishra

4,9962 gold badges40 silver badges65 bronze badges

answered Jul 22, 2011 at 15:11

SamuraiJack

505 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bhushan Lodha Over a year ago

could you tell me how to write to xlsx and xls file using roo

konung Over a year ago

You can't, at the moment roo only supports read for Excel. You can use axlsx gem to do writing. I know - it's not ideal, but so far I cant' find a gem that does both things well.

Code Maverick · Accepted Answer · 2012-04-13 20:37:38Z

3

A cleaner/clearer version of the above is

oo = Openoffice.new("simple_spreadsheet.ods") 
oo.default_sheet = file.sheets.first 
header = oo.first_row 
2.upto(oo.last_row) do |line|  
  row_data =  Hash[*header.zip(row).flatten]
  ...
end

the original took me a bit to understand because especially as i thought hash was a local variable named hash instead of the class Hash

edited Apr 13, 2012 at 20:37

Code Maverick

20.5k12 gold badges65 silver badges117 bronze badges

answered Apr 13, 2012 at 19:37

dabobert

94912 silver badges10 bronze badges

Comments

Aaron Henderson · Accepted Answer · 2013-07-23 07:00:23Z

This will use the header row as the keys. The helpful parts are transpose and strip.

def self.excel_to_hash(folder_name, file_name, tab_name)
    # Takes an excel file name and a tab name, and returns an array of stripped, transposed rows
    # Sample call:  my_data = excel_to_hash File.join(Rails.root,'db/data/data_to_import.xlsx'), 'models'
    rows = []
    file = File.open(File.join(folder_name, file_name), mode = 'r')
    excel = Excelx.new(file.path, nil, :ignore)
    excel.default_sheet = excel.sheets.index(tab_name) + 1
    header = excel.row(1)
    (2..excel.last_row).each do |i|
      next unless excel.row(i)[0]
      row = Hash[[header, excel.row(i)].transpose]      
      row.each_key{|x| row[x] = row[x].to_s.strip if row[x]}
      rows << row
    end
    return rows
  end

valid through Roo gem 1.10.2

Ahtasham Naeem · Accepted Answer · 2022-10-18 13:27:12Z

0

This works for me

 require 'roo'
    # open excel file
    excel_file = Roo::Spreadsheet.open(file_path)
      # iterate on each sheet
      excel_file.each_with_pagename do |name, sheet|
        # iterate on each sheet
        sheet.parse(headers: true, pad_cells: true) do |row|
          # data should be access by column header if we have column header Name we can access like this
          row['Name']
        end
      end
    end

answered Oct 18, 2022 at 13:27

Ahtasham Naeem

112 bronze badges

Collectives™ on Stack Overflow

Using Column headers to parse excel sheets using roo - Ruby

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related