Perl excel Working with Excel documents
From neil.tappsville.com
Jump to navigationJump to search
A quick thrown together script that takes an Excel document that contains network provisioning data and transforms it into specific csv files.
# PROCESS''Project''spreadsheet''for''AIG_Proviso.pl # # V1.0 neil.tapp@alcatel-lucent.com 26 April 2011 - new since no one else would do it. use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::Variant; use Data::Dumper; # Expect the user to pass in the location of an inventory spreadsheet my $filename = $ARGV[[1]]; my $FDSfilename = $ARGV[[0]]; # Otherwise tell them how to run the script if(!defined($filename) || $filename eq '' || !defined($FDSfilename) || $FDSfilename eq ''){ print "USAGE: perl PROCESS''Project''spreadsheet''for''AIG_Proviso.pl <full apth to FDS file> <full path to file>\n"; exit; }else{ : print "Transforms from $FDSfilename\n"; print "Working on $filename\n"; } # die on errors... $Win32::OLE::Warn = 3; # get already active Excel application or open new my $Excel = Win32::OLE->[[Get ActiveObject]]('Excel.Application') : || Win32::OLE->new('Excel.Application', 'Quit'); ### GET FDS Transforms - FDS IP / elt name and Ren details ## the FDS file is generated by Assure unfortunatly. print "-------------------------\n Working with FDS Transforms Document\n -------------------------\n"; my $Book = $Excel->Workbooks->Open($FDSfilename); my $FDSWorksheet = $Book->Worksheets('FDS'); my $lastrow = $FDSWorksheet->[[Used Range]]->Find({What=>"*",[[Search Direction]]=>xlPrevious,[[Search Order]]=>xlByRows})->{Row}; my $lastcolumn = $FDSWorksheet->[[Used Range]]->Find({What=>"*",[[Search Direction]]=>xlPrevious,[[Search Order]]=>xlByColumns})->{Column}; print "FDS lastrow $lastrow Lastcol $lastcolumn\n"; my %fdsHash; my @fdsColNames; foreach my $row (1..$lastrow){ # Full 7450 FDS Elt Object FDS IP FDS REN print " $row"; if($row == 1){ : #headers : foreach my $col (1..$lastcolumn){ : my $val = $FDSWorksheet->Cells($row,$col)->{'Value'}; : chomp($val); : $val =~ s/\n/ /g; : $val =~ s/\r//g; : chomp($val); : print "COL HEADER $val\n"; : $fdsColNames[[$col]] = $val; : } : next; : } my @fdsCols; #values : foreach my $col (1..$lastcolumn){ : my $val = $FDSWorksheet->Cells($row,$col)->{'Value'}; : chomp($val); : $val =~ s/\n/ /g; : $val =~ s/\r//g; : chomp($val); : $fdsCols[[$col]] = $val; : } : if ($fdsCols[[1]] ne ""){ : for(my $idx = 1; $idx <= scalar (@fdsColNames); $idx++){ : #foreach my $idx (scalar (@fdsColNames)){ : $fdsHash{$fdsCols[[1]]}{$fds ColNames[[$idx]]} = $fdsCols[[$idx]]; : print "$fdsCols[[$idx]] "; : } : print "\n"; : } } print "\n"; # clean up after ourselves $Book->Close; #print Dumper(%fdsHash); print "-------------------------\n Working with Inventory Document\n -------------------------\n"; # open Excel file my $Book = $Excel->Workbooks->Open($filename); #my $Book = $Excel->u->Open($filename); #my $Elements = $Book->Worksheets('Access Tails - FULL'); my $Subelements = $Book->Worksheets('Access Tails - FULL'); # First the elements - well we assume they are already there.. well heres hoping # Then the subelements my $subelement''OAM''filename = 'OAM''ATS''subelements.csv'; my $subelement''UFMS''filename = 'UFMS''ATS''subelements.csv'; open(SUBELEMENTS''OAM,">$subelement''OAM_filename"); open(SUBELEMENTS''UFMS,">$subelement''UFMS_filename"); print SUBELEMENTS''OAM "Type,Family,Element,name,RESOURCE''NE''ID,RESOURCE''FRIENDLY''NAME,RESOURCE''MAINT''STATUS,RESOURCE''MONITORED,Label,SLA''ID,customer,[[Service Type]],serviceDomain,[[Service Name]],CME''THR''rt-jitter,CME''THR_rtt-ms-avg,linkGroup\n"; print SUBELEMENTS''UFMS "Type,Family,Element,name,RESOURCE''NE''ID,RESOURCE''FRIENDLY''NAME,RESOURCE''MAINT''STATUS,RESOURCE''MONITORED,Label,SLA''ID,customer,[[Service Type]],serviceDomain,[[Service Name]],CME''THR''rt-jitter,CME''THR_rtt-ms-avg,linkGroup\n"; my $lastrow = $Subelements->[[Used Range]]->Find({What=>"*",[[Search Direction]]=>xlPrevious,[[Search Order]]=>xlByRows})->{Row}; my $lastcolumn = $Subelements->[[Used Range]]->Find({What=>"*",[[Search Direction]]=>xlPrevious,[[Search Order]]=>xlByColumns})->{Column}; print "lastrow $lastrow Lastcol $lastcolumn\n"; if($Subelements->Cells(1,3)->{'Value'} eq 'Last Updated'){ : # then the next col has the date : my $lastval = Variant(VT_DATE, $Subelements->Cells(1,4)->{'Value'}); : print "Last Updated $lastval\n"; } my $column = 1; #columns that we need data from ... #going to have to work magic as it contains line feeds in the excel ! ARGH my $descEDD = "Site Code"; # AKA EDD my $descHOPA = "Full EVC-1 HOP Name"; my $descHOPB = "Full EVC-2 HOP Name"; my $descEVPLA = "Full EVC-1 Service ID (UDL)"; my $descEVPLB = "Full EVC-2 Service ID (UDL)"; my $descFDS = "Project Full 7450"; # AKA FDS my $descMON = "Live for Voda"; #aka monitored true / false my $descIDA = "Full Access ID"; #needed for netcool CFS labels my $colEDD = 0; # AKA EDD my $colHOPB = 0; my $colHOPA = 0; my $colEVPLA = 0; my $colEVPLB = 0; my $colFDS = 0; # AKA FDS my $colMON = 0; #aka monitored true / false my $colIDA = 0; my $descExclude = "Full Status"; my $colExclude = 0; my $excText = "CANCELLED"; my @coldesc = ($descEDD, $descHOPA, $descHOPB, $descEVPLA, $descEVPLB, $descFDS, $descMON, $descIDA); my @colIndex; my $numdesc = scalar (@coldesc); # set the colindexes to zero = default : for(my $i = 0; $i <= $numdesc; $i++){ : $colIndex[[$i]] = 0; : } my $intodata = "false"; foreach my $row (1..$lastrow){ if($intodata eq "false"){ : # itterate through till we get past the header : if($Subelements->Cells($row,$column)->{'Value'} =~ /Site Code/i){ : print "header is at row $row\n"; : $intodata = "true"; : # now we must itterate across the columns to find which rows we care about. yeah we should : # be able to trust that the cols are always the same, but this is Project and this is Major Telco : # so we will cover someone elses arese as usual. : foreach my $col (1..$lastcolumn){ : my $val = $Subelements->Cells($row,$col)->{'Value'}; : chomp($val); : $val =~ s/\n/ /g; : $val =~ s/\r//g; : chomp($val); : print "COL HEADER $val\n"; : # foreach my $desc (@coldesc){ : for(my $i = 0; $i < $numdesc; $i++){ : if ($val eq $coldesc[[$i]]){ : print "COLUMN $col\n"; : $colIndex[[$i]] = $col; : } : } : if ($val =~ /$descExclude/i){ : $colExclude = $col; : print "===Column we will look for '$excText' in is $col - '$val'===\n"; : } : } : #now we have mapped the columns, check to see if we have all the data we need : for(my $i = 0; $i < $numdesc; $i++){ : if($colIndex[[$i]] == 0){ : print "ERROR $coldesc[[$i]] '$i' not found!!\n"; : die; : } : } : print "-------------------------\n Working with Inventory Data\n -------------------------\n"; : }else{ : #my $val = $Subelements->Cells($row,$column)->{'Value'}; : #print " DUD - $val \n"; : } }else{ : # now were into real data, lets start to do some magic : my @sitevals; : #get the values we require : my $error = "false"; : # check if this site has been cancelled or not : for(my $i = 0; $i < $numdesc; $i++){ : my $val = $Subelements->Cells($row,$colIndex[[$i]])->{'Value'}; : chomp($val); : $val =~ s/\n/ /g; : $val =~ s/\r//g; : chomp($val); : if($val ne ""){ : $sitevals[[$i]] = $val; : }else{ : print "ERROR row $row has a null value in $coldesc[[$i]] IGNORING DATA\n"; : $error = "true"; : } : } : if($colExclude ne 0){ : my $possibleError = $Subelements->Cells($row,$colExclude)->{'Value'}; : if ($possibleError =~ /$excText/i){ : print "INFO SITE CANCELLED row $row site $sitevals[[0]]\n"; : next; : } : } : if($error eq "true"){ : print "ERROR Skipping row $row due to error in source data\n"; : next; : } : # now we have the data lets manipulate it : # REMINDER : # 0 = $descEDD : # 1 = $descHOPA : # 2 = $descHOPB : # 3 = $descEVPLA : # 4 = $descEVPLB : # 5 = $descFDS : # 6 = $descMON : # 7 = $descIDA : #need to change site names from xxx, and xxx DUAL to xxx-EDD01 and xxx-EDD02 : if ($sitevals[[0]] =~ /(.*)\wTest/i){ : print "ERROR '$sitevals[[0]]' row $row skipping as name contains TEST\n"; : next; : }elsif(($sitevals[[| ($sitevals[[0 [0]] =~ /(.''')\w Dual/i)]] =~ /(.''')Dual/i)){ : $sitevals[[0]] = "$1-EDD02"; : }else{ : $sitevals[[0]] = "$sitevals[[0]]-EDD01"; : } : #need to change Monitored column from yes/ no to true / false : if($sitevals[[6]] =~ /yes/i) { : $sitevals[[6]] = "true"; : }else{ : $sitevals[[6]] = "false"; : } : # Is the FDS HOPA or HOPB ?? : if (($sitevals[[5]] eq $sitevals[[1]]) && ($sitevals[[5]] eq $sitevals[[2]])){ : #someone really stuffed up as both HOPs are the same and is the FDS : print "ERROR '$sitevals[[0]]' row $row skipping as FDS = HOPA = HOPB muppets\n"; : next; : } : # make sure we have the FDS data from the Transforms document before progressing : if (!exists $fdsHash{$sitevals[[5]]}->{"Full 7450"}){ : print "ERROR '$sitevals[[0]]' row $row skipping as FDS '$sitevals[[5]]' isnt in Transforms document\n"; : next; : } : # NOW We have some valid data, we can continue.. letttsss dooooooo it : # FDSHASH looks like ---> Full 7450, FDS Elt Object, FDS IP, FDS REN : ### what about netcool physical availablity (well when it finally computes it correctly we better not throw it out) : # OUTPUT : #Subelement,Service''Availability,Project,30219-EDD01''CFS,,,N,true,30219-EDD01 (IDA101961756),Project,Vodafone,Access,ATS REN 5,30219-EDD01,,, : #static'''''_,static'''''''''''''''''''''',static,descEDD''CDF'''''',,,N,MON'',descEDD''''''''(fullAccessID),Project,Vodafone,Access,fdsHashREn,descEDD'''_,,, : print SUBELEMENTS''UFMS "Subelement,Service''Availability,Project,3".$sitevals[[0]]."_CFS,,,N,".$sitevals[[6]].",".$sitevals[[0]]." (IDA".$sitevals[[7]].",Project,Vodafone,Access,".$fds Hash{$sitevals[[5]]}->{"FDS REN"}.",".$sitevals[[0]].",,,\n"; : #"Subelement,SAMO''OAM''Test,".$fdsHash{$sitevals[[5]]}->{"FDS Elt Object"}.",".$sitevals[[3]].",,,N,".$sitevals[[6]].",UDL".$sitevals[[3]].",Project,Vodafone,Access,".$fds Hash{$sitevals[[5]]}->{"FDS REN"}.",".$sitevals[[0]].",20,40,\n"; : #we want to produce output like the following FOR OAM.... : # OUTPUT : #Type,Family,Element,name,RESOURCE''NE''ID,RESOURCE''FRIENDLY''NAME,RESOURCE''MAINT''STATUS,RESOURCE''MONITORED,Label,SLA''ID,customer,[[Service Type]],serviceDomain,[[Service Name]],CME''THR''rt-jitter,CME''THR''rtt-ms-avg,linkGroup : #Subelement,SAMO''OAM''Test,222.153.217.92_OAM,101964400,,,N,true,UDL101964400,Project,Vodafone,Access,ATS REN 1-2,90PKH-EDD01,20,40, : #static'''''_,static''''''''''',fdsHash->Elt Objec,EVPLA/B''',,,N,MON'',UDL''EVPLA/B'',Static,Static''',Static,fdsHash''Ren,descEDD''_''_,St,St, : #INPUT : # : if($sitevals[[5]] eq $sitevals[[1]]){ : # ignore EVPLA as we dont have to do anything to this side : ##print "INFO $sitevals[[0]] - EVPLA is the HOP - $sitevals[[5]] $sitevals[[1]]\n"; : }else{ : #create EVPLA data : #yip doing it the ugly way. : print SUBELEMENTS''OAM "Subelement,SAMO''OAM_Test,".$fdsHash{$sitevals[[5]]}->{"FDS Elt Object"}.",".$sitevals[[3]].",,,N,".$sitevals[[6]].",UDL".$sitevals[[3]].",Project,Vodafone,Access,".$fds Hash{$sitevals[[5]]}->{"FDS REN"}.",".$sitevals[[0]].",20,40,\n"; : } : if($sitevals[[5]] eq $sitevals[[2]]){ : #ignore EVPLB and throw and error as this isnt the design we still dont need to output data : print "ERROR $sitevals[[0]] - EVPLB is the HOP - $sitevals[[5]] $sitevals[[1]]\n"; : }else{ : # create EVPLB data : print SUBELEMENTS''OAM "Subelement,SAMO''OAM_Test,".$fdsHash{$sitevals[[5]]}->{"FDS Elt Object"}.",".$sitevals[[4]].",,,N,".$sitevals[[6]].",UDL".$sitevals[[4]].",Project,Vodafone,Access,".$fds Hash{$sitevals[[5]]}->{"FDS REN"}.",".$sitevals[[0]].",20,40,\n"; : } : # END real data, lets start to do some magic } # if($Subelements->Cells($row,$column)->{'Value'} =~ /^#/){ # # Its a comment - ignore it # }else{ # my @data; # foreach my $col (1..$lastcolumn){ # push(@data, $Subelements->Cells($row,$col)->{'Value'}); # } # print SUBELEMENTS_OAM join(",", @data)."\n"; # } # } close(SUBELEMENTS_OAM); close(SUBELEMENTS_UFMS); # clean up after ourselves $Book->Close;