How to put arrays from Roo excel sheet in two dimensional array

Question

I am using the roo-gem in ruby to get excel sheet cell values.

I have a file 'ruby.rb' with:

require 'spreadsheet'
require 'roo'

xls = Roo::Spreadsheet.open('test_work.xls')
xls.each do |row|
  p row
end

my output in the terminal when I run ruby 'ruby.rb' is:

["id", "header2", "header3", "header4"]
["val1", "val2", "val3", "val4"]
["val1", "val2", "val3", "val4"]

when I add:

require 'spreadsheet'
require 'roo'

xls = Roo::Spreadsheet.open('test_work.xls')
xls.each do |row|
  two_dimensional = []
  two_dimensional << row
  p two_dimensional
end

I get:

[["id", "header2", "header3", "header4"]]
[["val1", "val2", "val3", "val4"]]
[["val1", "val2", "val3", "val4"]]

What I want is:

[["id", "header2", "header3", "header4"],
["val1", "val2", "val3", "val4"],
["val1", "val2", "val3", "val4"]]

How would I go about doing this.

Thanks!

you realize that `xls` already has this structure before the loop right? if `xls.each { |row| p row }` comes out as you have described then `xls` is already the multidimensional array that you want. if you really wanted to add this unneeded complexity then `xls.map { |row| row}` will do this too. — engineersmnky, Mar 25 '15 at 15:54

score 2 · Accepted Answer · answered Mar 25 '15 at 14:53

2

Just declare the array outside the each block. You're resetting it to [] every time the block is run. In that case, you will only append to one array.

two_dimensional = []
xls = Roo::Spreadsheet.open('test_work.xls')
xls.each do |row|
  two_dimensional << row
  p two_dimensional
end

answered Mar 25 '15 at 14:53

Amit Joki

58,320
7
77
95

Thanks Amit! Silly mistake on my part. – Mike Mar 25 '15 at 15:04
@Mike glad to have helped :) – Amit Joki Mar 25 '15 at 15:04
Just to clarify you are taking a multidimensional `Array` and making a new one that looks exactly the same? Why would you need to do this? If your code works the way he wanted then `xls` is already what he wanted from the start. – engineersmnky Mar 25 '15 at 15:56

score 1 · Answer 2 · answered Nov 02 '17 at 15:04

You can also try

require 'rubygems'
require 'roo'

class InputExcelReader
$INPUTPATH = 'C:\test_input_excel.xlsx'
excel_data_array = Array.new()

workbook = Roo::Spreadsheet.open($INPUTPATH)
worksheets = workbook.sheets
puts worksheets
puts "Found #{worksheets.count} worksheets"

worksheets.each do |worksheet|
    puts "Reading: #{worksheet}"
    num_rows = 0
    workbook.sheet(worksheet).each_row_streaming do |row|
        if(num_rows>0)
            puts "Reading the row no: #{num_rows}"
            row_cells = row.map { |cell| 
                puts "Reading cells"
                cell.value              
            }
            excel_data_array.push(row_cells)            
        end
        num_rows += 1
    end
    puts excel_data_array.to_s
end
end

How to put arrays from Roo excel sheet in two dimensional array

2 Answers2