From: Charles Roper <charles.roper@...>
Date: 2005-10-28T03:22:07+09:00
Subject: Re: Oldest Ruby hacker?

Chris McMahon wrote:
> He had to do some math on the values in the table.  I hacked up my test
> data framework to parse his spreadsheet (took about 15 minutes), and
> now he's learning Ruby (and the very nice ScriptingExcel page at Ruby
> Garden) because he just couldn't do his work with VBA.

I too am learning Ruby (I am a mere 30 though) and have started with
hacking at Excel spreadsheets as a) I have to do a lot of this in my
work, thus making rather dry work quite fun (ah, the magic of Ruby);
and b) VBA makes my skin crawl. The Scripting Excel page on the Garden
has been invaluable for this, but I'm finding reading in values from
cells and writing them back again to be extremely slow. Once in a Ruby
array they process like lightening. Has anyone got any tips for
speeding up the parsing of ranges, and writing them back again?

Here's an example of how I'm iterating over a range (simplified for
clarity):

my_range = "$A$2:$A$3000"
my_array = []
sheet.range(my_range).each { |cell| my_array << cell.value }

On large ranges, where I slot arrays within arrays, doing this kind of
thing seems to take an age, even on my Athlon64 3200. To give you some
idea, reading in an 8000 row x 20 column worksheet takes over 1 minute
and 45 seconds, while writing takes about 80 seconds. The actual
processing Ruby does on the arrays is about 1 second from what I can
tell.

Chris - I'd love to take a look at your Excel parsing framework.
Perhaps it'll help me get my head around Ruby a little more.

Many thanks,

Charles Roper