Ruby and Excel

[Analogy Analogy]

I'm reading an excel file (text and numbers) with the intent of storing
the data in a 2D array.


Here's my code to read in the data (after opening the excel file):


line = '1'

array = []

while worksheet.Range("c"+line) ['Value'] do

row = []

for column in 'c'..'z' do

row <<worksheet.cells(line, column).text

end

line.succ!

array << row


My problem is that this works if the first two columns of the excel file
are empty (i.e. data starts in cell C1), but doesn't read the excel data
if it starts in the first or second row (i.e. cell A1). Anyone know how
I might be able to pull all my data into a 2D array no matter where the
data starts? Thanks!

 

[mully]

I'm reading an excel file (text and numbers) with the intent of storing
the data in a 2D array.

Anyone know how
I might be able to pull all my data into a 2D array no matter where the
data starts? Thanks!

Here's a method I frequently use to get a 2-dimensional array of all
values from a worksheet...

data = worksheet.UsedRange.Value

David

http://rubyonwindows.blogspot.com/search/label/excel

 

[Analogy Analogy]

Thanks for the suggestion David. How would I get rid of the "nil" values
for the empty cells that are pulled into the 2D array? Thanks!!

 

[David Mullet]

Thanks for the suggestion David. How would I get rid of the "nil" values
for the empty cells that are pulled into the 2D array? Thanks!!

How you do this may depend on what you plan do with your data, and when.

I'm assuming you want to preserve the structure; otherwise the
aforementioned compact method will remove nil values for you.

I frequently convert nils to empty strings with the to_s method. You may
wish to do this when outputting your data, or in-place using a method
such as collect!:

data = worksheet.UsedRange.Value

=> [["A1", "B1", "C1", nil, "E1"], ["A2", nil, "C2", "D2", nil], [nil,
"B3", nil, "D3", "E3"]]

data.collect!{|row| row.collect!{|field| field.to_s}}

=> [["A1", "B1", "C1", "", "E1"], ["A2", "", "C2", "D2", ""], ["", "B3",
"", "D3", "E3"]]

If it's numeric data, perhaps to_i or to_f works better for you.

Hope that helps.

David

http://rubyonwindows.blogspot.com