Hi.. I was trying to read the excel file Independent of the Path i.e. from the current directory and I was able to do with help of abs_path () but I am not able to use SaveAs function to save the updated file Into the same current directory In the below program .. can you please help ?
# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
my $Excel_path = 'Parameters.xlsx';
print $Excel_path;
#Need to use absolute path for Excel files
my $excelfile = abs_path("$Excel_path") or die "Error: the file $Excel
+_path has not been found\n";
print $excelfile;
# open Excel file
#my $dir = "E:\\Automation Related\\Perl Scripts - ALL\\IMS_debugging\
+\Excel\\";
#my $Book = $Excel->Workbooks->Open($dir.$excelfile)
my $Book = $Excel->Workbooks->Open($excelfile)
or die Win32::OLE->LastError();
# get keywords
my $kwhashref = get_keywords($Book);
# create regex
my $match = join '|',map{quotemeta} keys %$kwhashref;
my $re = qr/$match/;
#print $re;
# search files and update workbook
my @files = grep { -f } (<main_log*>);
for my $file (@files){
my $line_no = 0;
print "Reading $file\n";
open my $fh, '<', $file or die $!;
while (<$fh>){
chomp;
++$line_no;
s/ +/ /g; # remove multiple spaces
# Original
# # special pcscf_v4 extract addr
# if (/($re).*addr = ([.\d]+)/){
# print "Line $line_no kw=[$1] value=[$2] special\n";
# update_sheet($1,$2);
# next;
# }
# # match more than 1 per line e.g. usim and isim
# while (/($re)[ =]*([.\d]+)/g){
# print "Line $line_no kw=[$1] value=[$2]\n";
# update_sheet($1,$2);
# }
if (/($re).*addr = ([.\da-fA-F:]+)/){
my $msg = update_sheet($1,$2,$_);
print "Line $line_no $msg special\n";
next;
}
# match more than 1 per line e.g. usim and isim
while (/($re)[ =]*([^,]+)/g){
my $msg = update_sheet($1,$2,$_);
print "Line $line_no $msg\n";
}
}
close $fh;
}
#pp $kwhashref; # debug
#my$var = 'updated';
# save as new spreadsheet
my $temp = cwd();
#$Excel->{DisplayAlerts}=0; # This turns off the "This file already ex
+ists" message.
#$Book->Save; #Or $Book->SaveAs("C:\\file_name.xls");
#$Book->Close; #or $Excel->Quit;
#$Book->SaveAs($dir.'updated_'.$excelfile);
# working saveas if path is provided
#$Book->SaveAs("E:\\Automation Related\\Perl Scripts - ALL\\IMS_debugg
+ing\\Final_Scripts\\Final_Script_Working\\temporary.xlsx");
# working if given directly as file name. under User/recent/
#$Book->SaveAs("temporary.xlsx");
$Book->SaveAs($excelfile.'.xlsx');
$Book->Close;
# update sheet
# original
# sub update_sheet {
# my ($kw,$value) = @_;
# my ($sht,$r,$c) = @{$kwhashref->{$kw}}[0..2];
# $Book->Worksheets($sht)->Cells($r,$c+1)->{Value} = $value;
# $kwhashref->{$kw}[3] = $value; # for debugging
# }
sub update_sheet {
my ($kw,$value,$comment) = @_;
my $clean = cleanup($kw,$value);
my ($sht,$r,$c) = @{$kwhashref->{$kw}}[0..2];
my $sel = $Book->Worksheets($sht)->Cells($r,$c+1);
$sel->Insert({ Shift => -4161}); # xlToRight
$sel->{Value} = $clean;
$sel->AddComment( { Text=> $comment } );
$sel->Comment->Shape->TextFrame->{ AutoSize } = 1;
$kwhashref->{$kw}[3] = $value; # for debugging
$kwhashref->{$kw}[4] = $clean; # for debugging
return "kw=[$kw] value=[$value] clean=[$clean]";
}
# get keywords
sub get_keywords {
my ($book) = @_;;
my %word = ();
for my $sht (1..$book->Worksheets->Count){
my $Sheet = $book->Worksheets($sht);
# determine position of keywords
# $Sheet->UsedRange->Value;
my $rng = $Sheet->UsedRange->Address(
{ ReferenceStyle => -4150 } ); # R1C1:R2C2
my ($r1,$c1,$r2,$c2) = ($rng =~ m/[RC](\d+)/g);
$r2 = $r2 || $r1;
$c2 = $c2 || $c1;
print "Sheet $sht Range $rng r1=$r1 c1=$c1 r2=$r2 c2=$c2\n";
# build keyword hash storing cell position
for my $c ($c1..$c2){
for my $r ($r1..$r2){
my $val = $Sheet->Cells($r,$c)->Value || '';
if ($val){
$word{$val} = [$sht,$r,$c]; # sheet row col
}
}
}
}
return \%word;
}
# remove unwanted chars from value
sub cleanup {
my ($kw,$value) = @_;
# generic cleansing
$value =~ s/[\[\]]//g; # remove all [] brackets
$value =~ s/^\s+//g; # remove leading whitespace
$value =~ s/\s+$//g; # remove trailing whitespace
# specific cleansing
#if ($kw =~ //){
# $value =~ s///g
#}
# specific cleaning for isim usim sim_invalid
if ( $kw =~ /^(isim|usim|sim_invalid|pcscf_discovery_flag|signalin
+g_flag|EVS payload type|evs_mode_switch|check_nw_im_cn_signaling_flag
+|is_ems_support|emergency_ind|ran_type|plmn|VT_SUPPORT|WFC_SUPPORT|ra
+t_type:|access RAT|port|integrity_algo|profile.home_uri|profile.imei|
+profile.private_uid|profile.public_uid|ua rat)/ ){
$value =~ s/(\d+).*/$1/; # remove trailing text
}
return $value;
}
|