Asp Forum
Home
|
Login
|
Register
|
Search
Forums
>
comp.lang.ruby
Ruby and Excel
Analogy Analogy
9/14/2007 10:19:00 PM
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-...
.
3 Answers
David Mullet
9/15/2007 1:36:00 AM
0
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!
>
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/l...
Analogy Analogy
9/17/2007 2:30:00 PM
0
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-...
.
David Mullet
9/18/2007 1:55:00 AM
0
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
http://rubyonwindows.bl...
--
Posted via
http://www.ruby-...
.
Servizio di avviso nuovi messaggi
Ricevi direttamente nella tua mail i nuovi messaggi per
Ruby and Excel
Inserendo la tua e-mail nella casella sotto, riceverai un avviso tramite posta elettronica ogni volta che il motore di ricerca troverà un nuovo messaggio per te
Il servizio è completamente GRATUITO!
x
Login to ForumsZone
Login with Google
Login with E-Mail & Password