# Ruby and Excel

**URL:** <https://www.ruby-forum.com/t/ruby-and-excel/114554>\
**Category:** Ruby\
**Created:** [September 15, 2007, 12:18am UTC](https://www.ruby-forum.com/t/ruby-and-excel/114554 "2007-09-15T00:18:33Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![analogy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@analogy](https://www.ruby-forum.com/u/analogy)\
**Post date:** [September 15, 2007, 12:18am UTC](https://www.ruby-forum.com/t/ruby-and-excel/114554/1 "2007-09-15T00:18:33Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![analogy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@analogy](https://www.ruby-forum.com/u/analogy)\
**Post date:** [September 17, 2007, 4:30pm UTC](https://www.ruby-forum.com/t/ruby-and-excel/114554/2 "2007-09-17T16:30:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![analogy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@analogy](https://www.ruby-forum.com/u/analogy)\
**Post date:** [September 17, 2007, 1:22pm UTC](https://www.ruby-forum.com/t/ruby-and-excel/114554/3 "2007-09-17T13:22:57Z")

</div>

On Sep 14, 6:18 pm, Analogy A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

> **[Ruby on Windows](http://rubyonwindows.blogspot.com/search/label/excel)**
>
> On using the Ruby programming language on the Microsoft Windows platform

---

<div class="post-metadata">

**Author:** ![analogy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@analogy](https://www.ruby-forum.com/u/analogy)\
**Post date:** [September 17, 2007, 10:42pm UTC](https://www.ruby-forum.com/t/ruby-and-excel/114554/4 "2007-09-17T22:42:53Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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/](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)

---

<div class="post-metadata">

**Author:** ![analogy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@analogy](https://www.ruby-forum.com/u/analogy)\
**Post date:** [September 18, 2007, 3:54am UTC](https://www.ruby-forum.com/t/ruby-and-excel/114554/5 "2007-09-18T03:54:39Z")

</div>

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

> **[Ruby on Windows](http://rubyonwindows.blogspot.com)**
>
> On using the Ruby programming language on the Microsoft Windows platform