Ruby and Excel

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!

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!!

On Sep 14, 6:18 pm, Analogy A. [email protected] wrote:

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

the compact method will return an array with nil values removed although
you
will lose the place holding functionality they are providing now.

On 9/17/07, Analogy A. [email protected] wrote:

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!!

Posted via http://www.ruby-forum.com/.


“Hey brother Christian with your high and mighty errand, Your actions
speak
so loud, I can’t hear a word you’re saying.”

-Greg Graffin (Bad Religion)

Analogy A. wrote:

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