http://www.perlmonks.org?node_id=10799

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

Nearly every example I can find of using OLE to manipulate Excel spreadsheets shows me I can use the "Range" method to define a set of values I can get with an array reference: 
use OLE;

my $count;
my $xl = CreateObject OLE 'Excel.Application' || die $!;
my $workbook = $xl->Workbooks->Open("C:\\test.xls");
my $worksheet = $workbook->Worksheets(1);
my $array = $worksheet->Range("A1:B10")->{'Value'};

for (@$array) { print ++$count, ":", join(",", @$_), "\n" }

$xl->ActiveWorkbook->Close(0);
$xl->Quit();

[download]
But I haven't seen any examples of reading the entire worksheet at once. How can I find out how many rows (and columns, less importantly) I'm dealing with?

I tried my $rows = $worksheet->Rows(); but that seems to return a reference to something that isn't a scalar.

Replies are listed 'Best First'.
Re: OLE - Getting all rows from Excel
by t0mas (Priest) on May 10, 2000 at 12:00 UTC
    The following code works fine for me ... 
    use Win32::OLE;

$excel = Win32::OLE->new("Excel.Application");
$excel->{Visible} = 1;
$workbook = $excel->Workbooks->Open("test.xls");
$sheet = $workbook->Worksheets(1);
$everything=$sheet->UsedRange()->{Value};
for (@$everything) {
    for (@$_) {
        print defined($_) ? "$_|" : "<undef>|";
    }
    print "\n";
}
$excel->Quit;

    [download]
    /t0mas
[reply]
[d/l]
      That's terrific... Where did you find that? I can't seem to find any good Excel OLE references for Perl beyond the simple "create a worksheet and insert X rows" examples. What do you use as your reference material?
[reply]
        I actually only use Objectviewer in the Visual Basic Editor...
        There is a good overview of the Excel Object model in the online help.
        Sorry to dissapoint you.

        /t0mas
[reply]
Re: OLE - Getting all rows from Excel
by Anonymous Monk on May 10, 2000 at 00:10 UTC
    I've worked quite a bit with OLE and excell though not through perl, and the best I've been able to come up with for this problem is to iterate down the rows, checking for an empty value in a column that you know will be filled, and thus building a totalrows scalar.... ie.
    my $test;
my $totalrows = 1;
for ($test=$worksheet->Range("A$totalrows:A$totalrows")->('Value');$te
+st ne "";$totalrows++){
     $test=$worksheet->Range("A$totalrows:A$totalrows")->('Value');
}
$totalrows--;

    [download]
    This should give you the total number of rows with information. In VBA I would be checking for a Null value rather than an empty string, but I'm not sure how to do that in Perl, and I believe this should work (untested).
    If someone knows how to check for a Null value in perl (not the same as undefined.) let me know.
    This code snippet is based on the understanding that your current code works and is correctly using OLE for Perl.
[reply]
[d/l]
      Damn it all to hell! How many times must I tell myself to login before posting.
[reply]
Re: OLE - Getting all rows from Excel
by lhoward (Vicar) on May 10, 2000 at 01:51 UTC
    One opther option would be to access the Excel spreadsheet via ODBC. Excel spreadsheets can be ODBC datasources and Perl can talk ODBC, so it may just work. (I've never actually done this, so for me this is a purely theoretical suggestion, but may be possible).
[reply]

Back to Seekers of Perl Wisdom