PERL Beginners - Reading Excel spreadsheet into variables

Author

Reading Excel spreadsheet into variables

Stephan Gross

2006-08-28, 6:57 pm

I'm reading in an Excel spreadsheet using Win32::OLE. I want to read in
the entire spreadsheet. I found a piece of code that does that:
$everything = $sheet->UsedRange()->{Value};
for (@$everything)
{
for (@$_)
{
print defined($_) ? "$_|" : "<undef>|";
}

print "\n";
}

However, I don't understand what @$everything and @$_ are; arrays,
arrays of arrays, hashes? It looks like the entire spreadsheet goes
into $everything, which is more confusing since it is a scalar. I'd
like to extract the second and fifth elements of a row and create a hash
out of them.

On another note, I have to run my script three times to get it to work.
The first time it doesn't run at all, the second time it hangs and the
third time it works properly. I assume this is a function of the code
that starts/stops the excel application (the spreadsheet only becomes
visible on the third try). Any ideas on this?

Thanks!

________________________________________
______________________
Steve Gross Tel:
212-284-6558
Director of Information Technology Cell:
917-575-4028
JESNA Fax:
212-284-6951
www.jesna.org

Timothy Johnson

2006-08-28, 6:57 pm

$everything is a scalar reference to an array. You can dereference the
array with the '@$everything' notation or the '@{$everything}' notation.
(The second one removes any ambiguity about the reference, but 99% of
the time the first way is okay.)

You can access elements of the array by either doing this:

${$everything}[0];

or this:

$everything->[0];

The second way is easier to read for most people.

This also works for hashes.

my $hash =3D {name =3D> 'Tim', age =3D> 29};

print keys %{$hash};

print $hash->{age};

As for the second question, you would have to show us more of your code
to be sure.

-----Original Message-----
From: Stephan Gross [mailto:sgross@jesna.org]=20
Sent: Monday, August 28, 2006 3:44 PM
To: beginners@perl.org
Subject: Reading Excel spreadsheet into variables

<snip>

However, I don't understand what @$everything and @$_ are; arrays,
arrays of arrays, hashes? =20
<snip>
=20
On another note, I have to run my script three times to get it to work.
The first time it doesn't run at all, the second time it hangs and the
third time it works properly. I assume this is a function of the code
that starts/stops the excel application (the spreadsheet only becomes
visible on the third try). Any ideas on this?
=20
Thanks!

<snip>

=20

Emil Perhinschi

2006-08-29, 4:01 am

I don't know about Win32::OLE, but you could use Data::Dumper to print
$everything to STDERR and see how it looks inside.

print STDERR Dumper $everything;

On Mon, 28 Aug 2006 18:43:35 -0400
sgross@jesna.org ("Stephan Gross") wrote:

> I'm reading in an Excel spreadsheet using Win32::OLE. I want to read
> in the entire spreadsheet. I found a piece of code that does that:
> $everything = $sheet->UsedRange()->{Value};
> for (@$everything)
> {
> for (@$_)
> {
> print defined($_) ? "$_|" : "<undef>|";
> }
>
> print "\n";
> }
>
> However, I don't understand what @$everything and @$_ are; arrays,
> arrays of arrays, hashes? It looks like the entire spreadsheet goes
> into $everything, which is more confusing since it is a scalar. I'd
> like to extract the second and fifth elements of a row and create a
> hash out of them.
>
> On another note, I have to run my script three times to get it to
> work. The first time it doesn't run at all, the second time it hangs
> and the third time it works properly. I assume this is a function of
> the code that starts/stops the excel application (the spreadsheet
> only becomes visible on the third try). Any ideas on this?
>
> Thanks!
>
> ________________________________________
______________________
> Steve Gross Tel:
> 212-284-6558
> Director of Information Technology Cell:
> 917-575-4028
> JESNA Fax:
> 212-284-6951
> www.jesna.org
>
>
>

Timothy Johnson

2006-08-29, 9:57 pm

One more thing: If you're not already doing so, you should probably
start putting

use strict;
use warnings;
=20
at the top of your scripts. It will be a pain in the short term but
will help you out later on.

-----Original Message-----
From: Stephan Gross [mailto:sgross@jesna.org]=20
Sent: Monday, August 28, 2006 3:44 PM
To: beginners@perl.org
Subject: Reading Excel spreadsheet into variables

<snip>

$everything =3D $sheet->UsedRange()->{Value};
for (@$everything)=20
{
for (@$_)=20
{
print defined($_) ? "$_|" : "<undef>|";
}

print "\n";
}

However, I don't understand what @$everything and @$_ are; arrays,
arrays of arrays, hashes? It looks like the entire spreadsheet goes
into $everything, which is more confusing since it is a scalar.

<snip>
=20

Sponsored Links