Perl excel Working with Excel documents

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;