Ruby and Excel

ruby-talk
1

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!

···

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

2

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

data = worksheet.UsedRange.Value

David

···

On Sep 14, 6:18 pm, Analogy Analogy <analog...@aol.com> 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!

3

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/.

4

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 Analogy <analogy47@aol.com> 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)

5

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

···

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