#!perl
use strict;
use warnings;
use Data::Dump 'pp';
use Win32::OLE;
$Win32::OLE::Warn = 3;      

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

# open Excel file
my $dir = "E:\\Automation Related\\Perl Scripts - ALL\\IMS_debugging\\
+Excel\\";
my $excelfile = 'UE_NW_Parameters.xlsx';

my $Book = $Excel->Workbooks->Open($dir.$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;
    
    # 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);
    }
    
  }  
  close $fh;
}
#pp $kwhashref; # debug

# save as new spreadsheet
$Book->SaveAs($dir.'updated_'.$excelfile);         
$Book->Close;

# update sheet
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
}

# 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;
}
[download]