WIN::32 OLE Excel help needed

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

Hello Monks, I humbly seek any and all guidance you can give me. I have three issues I am trying to solve:

1. How do you match an empty excel cell?
I use the $Sheet->UsedRange()->{'Value'} expression to retrieve all used objects. But the range is off by a few hundred rows.

I'm trying to match the cell like this:

if ($rangeobject =~ /""/)
(I'm told that in VB you match with this expression: <"">

2. I want to loop through the range object row by row. And return all the defined cells in that row. How can I do this with OLE? I have code that will let me iterate over the cells but not over the range.

3. The excel API is very picky about the file it wants to open. If I hardcode the value I can open the file in excel. If I try to pick a value from a traversed directory it fails. See below:


#!c:\perl\bin\perl -w

use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

if(scalar @ARGV != 2) {

    usage();
    exit 1;
}

my $outputFile = $ARGV[0];
my $inputDir   = $ARGV[1];

open (OUTFILE, ">$outputFile");

&traverse($inputDir);

exit 1;

##########################
# traverse directories
#########################
sub traverse {
    my($dir) = shift;
    my($path);
    unless (opendir(DIR, $dir)) {
        warn "Can't open $dir\n";
        closedir(DIR);
        return;
    }
    foreach (readdir(DIR)) {
        next if $_ eq '.' || $_ eq '..';
        $path = "$dir/$_";
        if (-d $path) {     # a directory
            &traverse($path);
        } 
        elsif (-f _) {  # a plain file
        
                next if ( $_ !~ /\.xls$/i);
        &open_xls($path);
        } #else-if file
    }
    closedir(DIR);
}

##########################
# Open spreadsheet
#########################
sub open_xls{
  my $path = shift;
  print "PATH1=$path\n";

  $path =~ s/\//\\\\/;
  print "PATH2=$path\n";

  $Win32::OLE::Warn = 3;                                # die on error
+s...

# get already active Excel application or open new
  my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit')  or die Win32::OLE
+->LastError(); 

# open Excel file
  my $Book = $Excel->Workbooks->Open($path) or die Win32::OLE->LastErr
+or(); 

  my $wkSheetCount = $Book->Worksheets->Count;

  foreach my $sheetnum (1..$wkSheetCount)
  {
     my $Sheet = $Book->Worksheets($sheetnum);
     $Sheet -> Activate();
     
     my $name = $Sheet->Name;
#       print "Working on sheet # $sheetnum - Its name is $name\n";

     my $everythingArray=$Sheet->UsedRange()->{'Value'};
#    my $everythingArray = $Sheet->Range("A8:B9")->{'Value'};
    
    foreach my $ref_array (@$everythingArray) {    
    foreach my $scalar (@$ref_array) {
         print "$scalar\t";
     }
     print "\n";
    }
  } #foreach sheet



$Book->Close;
undef $Book;
$Excel->Quit;
}


sub usage{
    print "Usage:\n" .
        "\tperl $0 OutputFile DirectoryPath\n";
}
[download]

Edit, BazB: add readmore

Comment on WIN::32 OLE Excel help needed Download Code

Replies are listed 'Best First'.
Re: WIN::32 OLE Excel help needed by Art_XIV (Hermit) on Oct 29, 2003 at 21:07 UTC
This won't help with your specific issue, but Spreadsheet::ParseExcel (available from CPAN!) is a much nicer module for reading from Excel. It bypasses most of the OLE/COM cruft for you.	[reply]
Re: WIN::32 OLE Excel help needed by Solo (Deacon) on Oct 30, 2003 at 19:53 UTC
1. See Using Win32::OLE and Excel - Tips and Tricks for some examples on searching. 2. A question more likely to be answered in an Excel forum since it has more to do with the object model than Perl. 3. My gut feeling is there's a relative vs. absolute path problem. Does you path include the drive letter? The cwd of your script is probably not the same as Excel. --Solo -- You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.	[reply]