My father-in-law is a mostly retired mining engineer.  He keeps a lot
of data about mines in an Excel spreadsheet.

I'm a QA tester.  I've been working on a data-driven Fit-like harness
that holds test data in Excel spreadsheets.

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.

On 10/24/05, swille <sillewi...@gmail.com> wrote:

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,

On 10/27/05, Charles Roper <charles.ro...@gmail.com> wrote:

It's a simple trick but in case you don't know about it, turn off
screen updating while you're doing your routine using:
excel.screenupdating = false
.where excel is your reference to the application (e.g.
WIN32OLE::new('excel/application'))

Remember to turn it back on with '= true' when you're done or excel
won't show your changes.

Hacking Ruby at 14 is pretty cool though.  :)

And then one wonders why I didn't take a CS education. Must be all that
brain-washing from my parents and my teachers that programming "wouldn't put
bread on the table." Now I have to content myself with a M.Sc in Physics :( ...

Seriously though, it's indeed pretty cool to see people of the age of 14 already
hacking away :) .
--
Christophe Grandsire.

http://rainbow.conlang.free.fr

As for what I wrote, it was meant as a semi-joke (note the winkey!),
just to point out in (hopefully) a humorous way that some have begun
programming quite early in their life. I realise now it kind of made me
look like a grumpy old man who didn't feel he was recognised enough.
That wasn't my purpose (hey, I'm only 29, with the social skills of
closed oyster, so I may sound quite differently from what I mean).

Anyway, if my remarks were in any way out of place, I apologise. I
wasn't meaning to sound pretentious. Apparently I failed :( .
--
Christophe Grandsire.

http://rainbow.conlang.free.fr

So the cookie comment could be rephrased by saying, "Did you tell us
this just so we would all know how special you are? Would you like a
prize (a cookie) for your accomplishments as a kid?)

Many thanks for the suggestion. I'm actually doing something similar
and hiding the whole Excel app:

excel.Visible = false

I also tried your suggestion, but reading and writing to cells was still slow.

However, I found a solution. Iterating over a range of cells and
reading or writing to them individually is what was causing the
bottleneck, so I experimented with reading whole ranges into an array
like so:

my_range = "$A$2:$A$8000"
my_array = sheet.range(my_range).Value

Not only is this more terse, it's also much, much faster. Using my
8000 row sheet, it reads the range into an array in about a 1.5
seconds, as opposed to the iterator taking over 1 minute and 45
seconds.

I was also delighted to discover that this works for writing back to a
range of cells too:

sheet.range(my_range) = my_array

Beautiful.

sheet.range(my_range).Value = my_array