I need to generate a array of subarray from row1 and row1 of sheets 1 and 2 of excel using roo gem and ruby

Question

I have the following code

require 'rubygems'
require 'roo'

class ExcelReader
    dataarray = Array.new();
    dataarray_new = Array.new();

    workbook = Roo::Spreadsheet.open('C:\data\testexcel.xlsx')
    worksheets = workbook.sheets
    worksheets.each do |worksheet|
        num_rows = 0
        workbook.sheet(worksheet).each_row_streaming do |row|
                if(num_rows>0) #skipping header

                    row_cells = row.map do |cell|
                        cell.value
                    end         
                    dataarray.push(row_cells)
                end
                num_rows += 1
            end
            puts dataarray.to_s;

            dataarray1, dataarray2 = dataarray.partition { |element| 
                for index in (0...dataarray.size)
                    element[index].first == dataarray1[index].first if(dataarray1!=nil)
                    dataarray_new.push(element[index]) if(element[index]!=nil)
                end
            }
        end
        puts dataarray_new.to_s;
    end

Current Output

[1, "Mia", 2, "Isha", 1, "Mia", 2, "Isha", 1, "Mango", "Apple", 2, "Banana", "Cashew"]

Actually the excel looks like:

sheet 1
No Name
1  Mia
2  Isha

Sheet 2
No Fruit1 Fruit2
1  Mango   Apple
2  Banana  Cashew  

I am new to ruby I expect output

[[[1, "Mia"], [[1, "Mango", "Apple"]], [[2, "Isha"], [2, "Banana", "Cashew"]]]

I need to club the row1 of sheet 1 and row1 of sheet2 as an array i tried with partition The sheet1 and sheet2 have the column1 same.

Answer 1

Rough code to do this:

xlsx = Roo::Spreadsheet.open("C:\data\testexcel.xlsx")
result = xlsx.sheets.map.with_index do |_, index|
  CSV.parse(xlsx.sheet(index).to_csv).drop(1)
end.transpose
#=> [[["1", "Mia"], ["1", "Mango", "Apple"]], [["2", "Isha"], ["2", "Banana", "Cashew"]]]

This is basically getting the data from all spreadsheets and drops first header line. After this, I am tranposing the data I got from sheet to group.

Note: Doesn't cover the cases when unequal number of rows etc. You can modify it to your use-case. Doesn't use roo streaming.