ralph has asked for the wisdom of the Perl Monks concerning the following question:

Hello: I'm new to Perl and Perlmonks. I've been doing a lot of reading to prepare for an application (in Windows) in which I want to extract values from an Excel spreadsheet. Through some reading I've been able to get ideas about code fragments that seem to do what I need. However, I'm stuck on being able to access the values extracted from a spreadsheet using function Range. Here's the code:

#!/usr/bin/perl

use strict;
use warnings;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;                                # die on errors.
+..

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  # get already ac
+tive Excel
                                                      # application or
+ open new
                                                      
                                                  
#my $Book = $Excel->Workbooks->Open("C:\\DOCUMENTS\\test.xls"); # open
+ Excel file
my $Book = $Excel->Workbooks->Openmy $Sheet = $Book->Worksheets(1);   
+                  # select worksheet number 1
my $array = $Sheet->Range("A1:B10")->{'Value'};        # get the conte
+nts
$Book->Close;

[download]
I have not found a way to get the individual values out of $array. Everything works OK if the range is a single value, like "A1". However, when I change the range to something like ("A1:B10") I get errors or senseless data.

I suspect that I'm not indexing $array correctly, but I've not been able to figure out why, in spite of hours of searching through tutorials.

I would appreciate any help a more experienced monk could give as to how I can get to the individual values that I have extracted from the spreadsheet into $array.

Many thanks, and sorry about the long post.

Ralph

Replies are listed 'Best First'.
Re: How can I access the values of an "array" extracted using Range
by NetWallah (Canon) on Feb 19, 2012 at 07:27 UTC
    Code from the Activeperl example: 
    my $array = $Sheet->Range("A8:B9")->{'Value'};        # get the conten
+ts

foreach my $ref_array (@$array) {                     # loop through t
+he array
                                                      # referenced by 
+$array
    foreach my $scalar (@$ref_array) {
        print "$scalar\t";
    }
    print "\n";
}

    [download]

                “PHP is a minor evil perpetrated and created by incompetent amateurs, whereas Perl is a great and insidious evil perpetrated by skilled but perverted professionals.”
            ― Jon Ribbens

[reply]
[d/l]
Re: How can I access the values of an "array" extracted using Range
by CountZero (Bishop) on Feb 19, 2012 at 17:26 UTC
    When a variable does not contain what I expected, I grab my most trusted helper: Data::Dumper and add a quick print Dumper($foo); to the program. The data-structure will become clear at once.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
[reply]
[d/l]
      Thanks CountZero:

      That's good to know.

      One thing that has surprised me about Perl is how different it is from all the other languages with which I'm vaguely familiar.

      ralph

[reply]
Re: How can I access the values of an "array" extracted using Range
by oko1 (Deacon) on Feb 19, 2012 at 15:42 UTC

    The problem is that '$array' is not an array; it's an array reference - similar to a pointer. Your code seems fine, all except for that point. Quick overview of dealing with array references: 

    # Turning an arrayref into an array
my @arr = @$aref;
# Extracting a single value from an arrayref
print ${$aref}[0];
# Or, more readable and less problematic:
print $aref -> [0];
# Looping over the pointed-to list
for my $i (@$aref){
    ...
}

    [download]

    In the above case, the structure that you're getting back from 'Range()' is an "AoA" - an array of arrays - which looks like this: 

    $aref = [
          [
            1,
            2,
            3
          ],
          [
            4,
            5,
            6
          ],
          [
            7,
            8,
            9
          ]
        ];

    [download]

    Notice the second level of indirection, there? The result is that just converting the ref to an array is going to give you a list of... arrayrefs, which need to be expanded into arrays. The solution that netwallah shows is correct - I just wanted to give you a more general heads-up for this kind of problem. So, expanding the above looks like this: 

    for my $deref (@$aref){
    for my $element (@$deref){
        print "$element\n";
    }
}

    [download]

    Again, for more on data structures - AoAs, HoHs, etc. - see perldoc perlref and the excellent Data Structures Cookbook. 

    -- 
I hate storms, but calms undermine my spirits.
 -- Bernard Moitessier, "The Long Way"
[reply]
[d/l]
[select]
      Many thanks oko1--

      Your answer was most helpful. I appreciate the extra time you took to offer some additional details. It made a big difference.

      ralph

[reply]

Back to Seekers of Perl Wisdom