Forum: Ruby Issue in reading the contents of XLSX using roo gem

Hi All,

I have to parse through and store the contents of each cell in an array.
I wrote the following. But when i print the contents of my resultant
array "contents" all i get is [nil,nil,nil....nil]. Please correct where
i am wrong?
In the following code i am first getting what is the max number of rows
and cols i have in the xlsx file. Then run through the cells using the
lastRow and lastColumn in the for loop.
-----------------------
require 'roo'
require 'rubygems'

#Excel file instance
workbook = Excelx.new(ARGV[0])

#Need to find the count of lastrow and last column
lastRow = workbook.last_row
lastColumn = workbook.last_column
print "Last row -"+" ", lastRow
print "Last column -"+" ",lastColumn

#parse the first sheet contents
contents = []

for i in 1..lastRow do
  for j in 1..lastColumn do
    contents.push(workbook.cell([i],[j],workbook.sheets[0]))
    j=j+1
  end
  i=i+1
end
print contents
-------------------------

thanks

Rochit Sen wrote in post #1083936:
> Hi All,
>
> I have to parse through and store the contents of each cell in an array.
> I wrote the following. But when i print the contents of my resultant
> array "contents" all i get is [nil,nil,nil....nil]. Please correct where
> i am wrong?
> In the following code i am first getting what is the max number of rows
> and cols i have in the xlsx file. Then run through the cells using the
> lastRow and lastColumn in the for loop.
> -----------------------
> require 'roo'
> require 'rubygems'
>
> #Excel file instance
> workbook = Excelx.new(ARGV[0])
>
> #Need to find the count of lastrow and last column
> lastRow = workbook.last_row
> lastColumn = workbook.last_column
> print "Last row -"+" ", lastRow
> print "Last column -"+" ",lastColumn
>
> #parse the first sheet contents
> contents = []
>
> for i in 1..lastRow do
>   for j in 1..lastColumn do
>     contents.push(workbook.cell([i],[j],workbook.sheets[0]))
>     j=j+1
>   end
>   i=i+1
> end
> print contents
> -------------------------
>
> thanks

 First of all, it's not necessary to pass the third argument to cell() 
because by default the sheets where roo looks is the first one.
 Second what are you doing here? => j=j+1 <= there's no meaning since 
the block iterates by himself, passing the next number of 1..X feature.
 Third the malfunction can be because you pass #Array items to cell() 
when they should be just #Fixnum items. Just cell(i, j), try that way. 
The code should stay like this:


 require 'roo'
 require 'rubygems'

 #Excel file instance
 workbook = Excelx.new(ARGV[0])

 #Need to find the count of lastrow and last column
 lastRow = workbook.last_row
 lastColumn = workbook.last_column
 print "Last row -"+" ", lastRow
 print "Last column -"+" ",lastColumn

 #parse the first sheet contents
 contents = []

 for i in 1..lastRow do
   for j in 1..lastColumn do
     contents.push(workbook.cell(i, j))
   end
 end
 print contents

>      contents.push(workbook.cell(i, j))

Thanks a lot igorjorobus. This worked :)