reading only the second row of an xlsx file using roo

Question

I am using roo to read an xlsx file.

book = Roo::Spreadsheet.open("codes.xlsx")
sheet = book.sheet('Sheet1')

This reads all the rows in the file.

I can iterate through this sheet using

plans_sheet.each(column_1: 'column_1', column_2: 'column_2') do |hash|

But this iteration happens from the first row which has all the column names as well. I need to take only the second row data.

Update - when you do row(2) it returns you an array. And when you are iterating using .each it returns you a hash which has column names as your key .

How to do that.

Answer 1

Just do

whatever_sheet.row(2)

It is always good to have a look at the documentation, it is explained in the README.md of the Gem, see https://github.com/roo-rb/roo#accessing-rows-and-columns

Answer 2

If you want to iterate over rows starting from the second one, just use Enumerable#drop

plans_sheet.each(column_1: 'column_1', column_2: 'column_2').drop(1) do |hash|
  # ...
end

Answer 3

Roo::Excelx#each method returns standard Ruby Enumerator:

enum = sheet.each(column_1: 'column_1', column_2: 'column_2')
enum.class # => Enumerator

So there are two ways to achieve your goal:

1) Use drop method:

enum.drop(1).each do |hash| 
  # do something with the hash    
end

If you need only the second row:

  hash = enum.drop(1).first

2) Move the internal position of the iterator:

enum.next # move 1 step forward to skip the first row

# continue moving
loop do
  hash = enum.next
  # do something with the hash
end

If you need only the second row:

  enum.next # skip the first row
  hash = enum.next # second row

Also take into account:

There isRoo::Excelx::Sheet class which also represents a worksheet and has the each_row method that receives :offset parameter. But unfortunately it doesn't have an option to transform a row into a hash with given keys.

book = Roo::Spreadsheet.open("codes.xlsx")
sheet = book.sheet_for('Sheet1')

sheet.each_row(offset: 1) do |row| # skip first row
  # do something with the row
end