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?

Answer 1

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.

Answer 2

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