Parse Excel Data using Win32::OLE 1

[bigbalbossa]

Perl Gurus,

i've read numerous threads concering perl and excel, but unfortunately none seem to enlighten me. I have a number of excel files that i need to read, if the string "TEST" exists, replace with "TESTED" and save the file. Seems easy enough, but i've not worked with this module much.

Any thoughts are greatly appreciated.

 

[dmazzini]

Hi

Here we go:

I have done just for 1 excel file, it was tested and it worked.

In order to test it, just create one excel file, fill out some "TEST" in several cells. Change the path of the excel file inside the code and execute the perl script.

What yo have to do it just incorporate it to do recursively for several xls files...Yo could use glob(*.xls), keep in mind that you must pass the fullpath and name of the excel file to the open event:

$Book_Data = $Excel->Workbooks->Open($xlsfile);

1 thing that I haven't test is when sheets does not contain data, maybe the function getLastRow and getLatCol could fail,and stop script execution. Have a look at it

Here the code mate:

#!/opt/nokianms/bin/perl

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

my $xlsfile="C:\\Documents and Settings\\dmazzini\\Desktop\\TEST.xls";
open_excel();


# ###################################################################
#  Sub Open Excel File
# ###################################################################


sub open_excel { #Open Sub
    
print "Processing Excel File:$xlsfile\n\n";
$Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
	   Win32::OLE->new('Excel.Application');
$Excel->{'Visible'} = 0;		#0 is hidden, 1 is visible
$Excel->{DisplayAlerts}=0;	    #0 is hide alerts

$Book_Data = $Excel->Workbooks->Open($xlsfile); # open Excel file
$sheetcnt = $Book_Data->Worksheets->Count();
$iSheet=1; 

foreach ($iSheet..$sheetcnt){
   print "Processing SHEET:$iSheet\n";
   $icol=0;$irow=0;    
   $Sheet_compare = $Book_Data->Worksheets($iSheet);      

   # Find Last Column and Row
   
   $LastRow = $Sheet_compare->UsedRange->Find({What=>"*",
            SearchDirection=>xlPrevious,
            SearchOrder=>xlByRows})->{Row};

   $LastCol = $Sheet_compare->UsedRange->Find({What=>"*",
            SearchDirection=>xlPrevious,
            SearchOrder=>xlByColumns})->{Column};

   # Range for Excel Data

   $firstcell="A".1;
   $lastcell= "IV".$LastRow;   #(Last Column Excel File. Last Row with Data)

   my $array = $Sheet_compare->Range("$firstcell:$lastcell")->{'Value'};

   do_sheet_compare($array);
   $iSheet++;
   
      
 }
 
 
$Book_Data->SaveAs($xlsfile);

$Excel->{'Visible'} = 1;
undef $Book_Data;
undef $Excel;
print "Checking/Replacement is done! See you soon\n";

} 

####################################################################
#  Sub Comparison Routine
####################################################################

sub do_sheet_compare{
        my($array)= @_;
        $irow=1;
        $icol=1;
        foreach my $ref_array (@$array) { # Open for 
                 @columns=(1 .. $LastCol); 
                 for $col(@columns){
                     $data=@$ref_array[$col-1];
                      $data=trim($data);
                      if ($data eq "TEST"){
                          print "I have found TEST in Sheet:$iSheet. (Row,Col)=>($irow,$col)\n";
                          $Sheet_compare->Cells($irow,$col)->{Value} = "TESTED"; #Replacing
                      }
                                     
                  }
        $irow++;                                                           
        }          
          
}
   
# ###################################################################
# Sub Routine Trim. Remove blanks spaces (right and left)
# ###################################################################

sub trim {
    my @out = @_;
    for (@out) {
        s/^\s+//;          # trim left
        s/\s+$//;          # trim right
    }
    return @out == 1
              ? $out[0]   # only one to return
              : @out;     # or many
           
} 
__END__

dmazzini
GSM System and Telecomm Consultant

 

[bigbalbossa]

Dmazzini....THANK YOU!

 

[dmazzini]

Your welcome

dmazzini
GSM System and Telecomm Consultant