CODEWITHSUNDEEP
rubyarraysexcelroo-gem
Mike
Mike

Reputation: 29

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

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!

Upvotes: 2

Views: 1639

Answers (2)

javalearner_heaven
javalearner_heaven

Reputation: 523

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

Upvotes: 1

Amit Joki
Amit Joki

Reputation: 59282

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

Upvotes: 2

Related Questions