autoexcel02.pl to HTML.

index -|- end

Generated: Tue Feb 2 17:54:21 2010 from autoexcel02.pl 2006/06/12 2.4 KB.

#!/Perl
# from : http://aspn.activestate.com/ASPN/docs/ActivePerl/5.8/faq/Windows/ActivePerl-Winfaq12.html
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my $File = "C:\\tmp\\tpj\\data.xls";
my $Range = "A1:E5";
my ($LOG);
my $write_log = 0;
my $outfile = 'tempauto.txt';
if ( open( $LOG, ">$outfile" ) ) {
    $write_log = 1;
} else {
    $write_log = 0;
    prt( "WARNING: Unable to open $outfile LOG ...\n" );
}
$Win32::OLE::Warn = 3;                                # die on errors...
prt("Starting Excel ...\n");
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');  # open new
##my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
##    || Win32::OLE->new('Excel.Application', 'Quit');  # get already active Excel
                                                      # application or open new
my $Book = $Excel->Workbooks->Open($File); # open Excel file
my $Sheet = $Book->Worksheets(1);                     # select worksheet number 1
my $Text = $Sheet->Range($Range)->{'Text'}; # DOES NOT APPEAR TO WORK???
my $array = $Sheet->Range($Range)->{'Value'};        # get the contents
$Book->Close;
prt("Closed excell ... print array, if any ...\n");
foreach my $ref_array (@$array) {                     # loop through the array
                                                      # referenced by $array
    foreach my $scalar (@$ref_array) {
        prt( "$scalar\t" );
    }
    prt( "\n" );
} 
prt("Done 'value' ...\n");
prt("Now 'text' ... if ANY - this seems to FAIL???\n");
foreach my $ref_array (@$Text) {                     # loop through the array
                                                      # referenced by $array
    foreach my $scalar (@$ref_array) {
        prt( "$scalar\t" );
    }
    prt( "\n" );
} 
prt("All done ...\n");
# To retrieve the formatted value of a cell you should use the {'Text'} 
# property instead of the {'Value'} property. This returns exactly what is being 
# displayed on the screen though! If the column is not wide enough, you get a value of '######':
# my $array = $Sheet->Range("A8:B9")->{'Text'};
################################
### output and log file
sub wlog {
   my $ml = shift;
   print $LOG $ml;
}
sub prt {
   my $m = shift;
   if ($write_log) {
      wlog($m);
   }
   print $m;
}
sub mydie {
   my $msg = shift;
   if ($write_log) {
      wlog($msg);
   }
   die $msg;
}
# eof - autoexcel02.pl

index -|- top