Code:
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
# $Win32::OLE::Warn = 3; # die on errors...
# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
my $inp;
my (@in01,@in02,@in03,@in04,@in05,@in06,@in07,@in08,@in09,@in10,@in11,@in12,@in13,@in14,@in15,@in16,@in17,@in18,@in19,@in20,@in21,@in22,@in23);
my (@out01,@out02,@out03,@out04,@out05,@out06,@out07,@out08,@out09,@out10,@out11,@out12,@out13,@out14,@out15,@out16,@out17,@out18,@out19,@out20,@out21,@out22,@out23,@out24,@out25);
my (@inVL08,@inVL10,@inVL13,@inVL14,@inVL23);
my (@outVL08,@outVL10,@outVL11,@outVL12,@outVL13,@outVL25);
# supplemental Arrays
my (@supi1,@supi2,@supo1,@supo2);
# additional data
our %AdditionalData;
# hash tables to prevent duplicate VL entries in ICD_MODULE
our (%inputVL,%outputVL);
# Parameters
my $partname = $ARGV[0];
my $csvfile = $ARGV[1];
my $xlfile = $ARGV[2];
my $adatafile = $ARGV[3];
my $outfile = $ARGV[4];
# import additional data
if (defined $adatafile){
import_additional_data($adatafile);
}
if (defined $outfile){
# open csv-file
open (IN1, "$csvfile") or die "Kann die Datei $csvfile nicht lesen\n";
# clear VL hash tables
clearVLTables();
# fill csv-Data into arrays (one Array for each column)
my $incnt=0;
my $outcnt=0;
my $inVLcnt=0;
my $outVLcnt=0;
$inp= <IN1>;
if (defined $inp) {
chomp($inp);
}
while (defined $inp){
# Input-Lines
if ($inp =~ /^AFDX_INPUT_VL;port1/){
$incnt++;
($in01[$incnt],$in02[$incnt],$in03[$incnt],$in04[$incnt],$in05[$incnt],$in06[$incnt],$in07[$incnt],$in08[$incnt],$in09[$incnt],$in10[$incnt],$in11[$incnt],$in12[$incnt],$in13[$incnt],$in14[$incnt],$in15[$incnt],$in16[$incnt],$in17[$incnt],$in18[$incnt],$in19[$incnt],$in20[$incnt],$in21[$incnt],$in22[$incnt],$in23[$incnt]) = split(";",$inp);
if ($in19[$incnt] eq "Sampling"){
$supi1[$incnt]=$in19[$incnt];
$supi2[$incnt]="LANE_MSG_NPB";
}
if ($in19[$incnt] eq "Queuing"){
$supi1[$incnt]=$in19[$incnt];
$supi2[$incnt]="LANE_MSG_PB";
}
if ($in19[$incnt] eq "SAP"){
$supi1[$incnt]="Queuing";
$supi2[$incnt]="LANE_MSG_PB";
}
if ( !defined($inputVL{$in09[$incnt]}) )
{
$inVLcnt++;
$inVL08[$inVLcnt]=$in08[$incnt];
$inVL10[$inVLcnt]=$in10[$incnt];
$inVL13[$inVLcnt]=$in13[$incnt];
$inVL14[$inVLcnt]=$in14[$incnt];
$inVL23[$inVLcnt]=$in23[$incnt];
$inputVL{$in09[$incnt]} = $in09[$incnt];
}
}
# Output-lines
if ($inp =~ /^AFDX_OUTPUT_VL;port1/){
$outcnt++;
($out01[$outcnt],$out02[$outcnt],$out03[$outcnt],$out04[$outcnt],$out05[$outcnt],$out06[$outcnt],$out07[$outcnt],$out08[$outcnt],$out09[$outcnt],$out10[$outcnt],$out11[$outcnt],$out12[$outcnt],$out13[$outcnt],$out14[$outcnt],$out15[$outcnt],$out16[$outcnt],$out17[$outcnt],$out18[$outcnt],$out19[$outcnt],$out20[$outcnt],$out21[$outcnt],$out22[$outcnt],$out23[$outcnt],$out24[$outcnt],$out25[$outcnt]) = split(";",$inp);
if ($out18[$outcnt] eq "Sampling"){
$supo1[$outcnt]=$out18[$outcnt];
$supo2[$outcnt]="LANE_MSG_NPB";
}
if ($out18[$outcnt] eq "Queuing"){
$supo1[$outcnt]=$out18[$outcnt];
$supo2[$outcnt]="LANE_MSG_PB";
}
if ($out18[$outcnt] eq "SAP"){
$supo1[$outcnt]="Queuing";
$supo2[$outcnt]="LANE_MSG_PB";
}
if ( !defined($outputVL{$out09[$outcnt]}) )
{
$outVLcnt++;
$outVL08[$outVLcnt]=$out08[$outcnt];
$outVL10[$outVLcnt]=$out10[$outcnt];
$outVL11[$outVLcnt]=$out11[$outcnt];
$outVL12[$outVLcnt]=$out12[$outcnt];
$outVL13[$outVLcnt]=$out13[$outcnt];
$outVL25[$outVLcnt]=$out25[$outcnt];
$outputVL{$out09[$outcnt]} = $out09[$outcnt];
}
}
$inp= <IN1>;
if (defined $inp) {
chomp($inp);
}
}
# Variables
my $rcnt;
my ($l1i,$l2i,$l3i,$l1o,$l2o,$l3o); # first data line, begin of global datablocks
my ($di,$do); # delta between find first data line and selection in first worksheet
my ($li,$lo); # length of selection in first worksheet
# Limits, number of lines in Array
my $nli = @in01-1; # Input-Lines
my $nlo = @out01-1; # Output-Lines
my $nliVL = @inVL08-1; # Input-Lines VL
my $nloVL = @outVL08-1; # Output-Lines VL
# start Output
# open Excel workbook
my $Book = $Excel->Workbooks->Open($xlfile);
# select worksheet 1 (IM_ICD Local Partition)
$Excel->Sheets('IM_ICD Local Partition')->Select();
# First Input-Lines
# find first data line
$l1i = findl("Block: Input API Port / FDS / Data",2);
# find datablock selective
my ($rwb,$rwe) = findb("INPUT_DATA",2,$partname,3);
$di=$rwb-$l1i; # set delta
$li=$rwe-$rwb; # set datablock length for other worksheets
delrow ($rwb,$rwe) if (defined $rwb); # delete old lines
insrow ($rwb,$nli) if (defined $rwb); # insert empty lines
formsel ($rwb,$nli) if (defined $rwb); # formating inserted Lines
# fill cells with data
for ($rcnt=0;$rcnt<$nli;$rcnt++){
fillrow_i1($rwb+$rcnt,$rcnt+1,$partname);
}
# Next Output-Lines
# find first data line
$l1o = findl("Block: Output API Port / FDS / Data",2);
# find datablock selective
($rwb,$rwe) = findb("OUTPUT_DATA",2,$partname,3);
$do=$rwb-$l1o; # set delta
$lo=$rwe-$rwb; # set datablock length for other worksheets
delrow ($rwb,$rwe) if (defined $rwb); # delete old lines
insrow ($rwb,$nlo) if (defined $rwb); # insert empty lines
formsel ($rwb,$nlo) if (defined $rwb); # formating inserted Lines
# fill cells with data
for ($rcnt=0;$rcnt<$nlo;$rcnt++){
fillrow_o1($rwb+$rcnt,$rcnt+1,$partname);
}
# after filling all data in worksheet 1, always complete rebuild LANE-ID !!!
# Lane-ID is beginning on Output !!!
my $laneid=1;
($rwb,$rwe) = findb("OUTPUT_DATA",2,"DATA",1,);
for ($rcnt=$rwb;$rcnt<=$rwe;$rcnt++){
$Excel->Cells($rcnt,11)->{'Value'}=$laneid;
$laneid++;
}
($rwb,$rwe) = findb("INPUT_DATA",2,"DATA",1,);
for ($rcnt=$rwb;$rcnt<=$rwe;$rcnt++){
$Excel->Cells($rcnt,11)->{'Value'}=$laneid;
$laneid++;
}
#
## select worksheet number 2
$Excel->Sheets('ICD Local Partition')->Select();
# First Input-Lines
# find first data line
$l2i = findl("Block: connection-VL Input",2);
$rwb=$l2i+$di; # block begin
$rwe=$rwb+$li; # block end
delrow ($rwb,$rwe) if (defined $rwb); # delete old lines
insrow ($rwb,$nli) if (defined $rwb); # insert empty lines
formsel ($rwb,$nli) if (defined $rwb); # formating inserted Lines
# fill cells with data
for ($rcnt=0;$rcnt<$nli;$rcnt++){
fillrow_i2($rwb+$rcnt,$rcnt+1);
}
# Next Output-Lines
# find first data line
$l2o = findl("Block: connection-VL Output",2);
$rwb=$l2o+$do; # block begin
$rwe=$rwb+$lo; # block end
delrow ($rwb,$rwe) if (defined $rwb); # delete old lines
insrow ($rwb,$nlo) if (defined $rwb); # insert empty lines
formsel ($rwb,$nlo) if (defined $rwb); # formating inserted Lines
# fill cells with data
for ($rcnt=0;$rcnt<$nlo;$rcnt++){
fillrow_o2($rwb+$rcnt,$rcnt+1);
}
## select worksheet number 3
$Excel->Sheets('ICD Module')->Select();
# First Input-Lines
# find first data line
$l3i = findl("Block: connection-VL Input",2);
$rwb=$l3i+$di; # block begin
$rwe=$rwb+$li; # block end
delrow ($rwb,$rwe) if (defined $rwb); # delete old lines
insrow ($rwb,$nliVL) if (defined $rwb); # insert empty lines
formsel ($rwb,$nliVL) if (defined $rwb); # formating inserted Lines
# fill cells with data
for ($rcnt=0;$rcnt<$nliVL;$rcnt++){
fillrow_i3($rwb+$rcnt,$rcnt+1);
}
# Next Output-Lines
# find first data line
$l3o = findl("Block: connection-VL Output",2);
$rwb=$l3o+$do; # block begin
$rwe=$rwb+$lo; # block end
delrow ($rwb,$rwe) if (defined $rwb); # delete old lines
insrow ($rwb,$nloVL) if (defined $rwb); # insert empty lines
formsel ($rwb,$nloVL) if (defined $rwb); # formating inserted Lines
# fill cells with data
for ($rcnt=0;$rcnt<$nloVL;$rcnt++){
fillrow_o3($rwb+$rcnt,$rcnt+1);
}
# End of working in Excel
# save as (Overwrite??? , before check if it exists)
$Excel->ActiveWorkbook->SaveAs({Filename => $outfile, FileFormat => xlNormal, Password => '', WriteResPassword => '', ReadOnlyRecommended => 0, CreateBackup => 0});
$Book->Close;
close IN1;
}
else{
usage();
}
# end main
# sub: find lines
sub findl{
my ($exp,$col)= @_; # $exp = expression to find, $col - column to search in
my $val;
my $line;
my $cnt=0;
for($cnt=1;$cnt<=65536;$cnt++){
$val = $Excel->Cells($cnt,$col)->{'Value'};
if ((defined $val) && (index($val,$exp) != -1)){
$line =$cnt if (!defined $line);
last;
}
}
return ($line);
}
# sub: find block (in cols), return line-numbers (begin-end) or 0 if not found
sub findb{
my ($exp1,$col1,$exp2,$col2)= @_; # $exp = expression to find, $col - column to search in
my $cnt=0;
my ($val1,$val2);
my ($beg,$end);
for($cnt=1;$cnt<=65536;$cnt++){
$val1 = $Excel->Cells($cnt,$col1)->{'Value'};
$val2 = $Excel->Cells($cnt,$col2)->{'Value'};
if ((defined $val1)&&(index($val1,$exp1) != -1)&&(defined $val2)&&(index($val2,$exp2) != -1)){
$beg =$cnt if (!defined $beg);
}
else{
if (defined $beg){
$end =$cnt-1;
last;
}
}
}
return ($beg,$end);
}
# delete lines, from line_a to line_b
sub delrow{
my ($srwb,$srwe)= @_; # $srwb = begin of selection ,$srwe = end of selection
$Excel->Rows("$srwb:$srwe")->Select();
$Excel->Selection->Delete({Shift => xlUp});
}
# insert lines, at line_a a number of lines
sub insrow{
my ($srwi,$srws)= @_; # $srwi = row to insert , $srws =number of rows to insert
my $srwe= $srwi+$srws-1;
$Excel->Rows("$srwi:$srwe")->Select();
$Excel->Selection->Insert({Shift => xlDown});
}
# subs: fill in values into a row
sub fillrow_i1{
my ($srwf,$idx,$pname) =@_; # $srwf = Row to fill , $idx = line index of array, $pname = partition name
# Worksheet IM_ICD Local Partition, InputLines
$Excel->Cells($srwf,1)->{'Value'}="DATA";
$Excel->Cells($srwf,2)->{'Value'}="INPUT_DATA";
$Excel->Cells($srwf,3)->{'Value'}=$pname;
$Excel->Cells($srwf,5)->{'Value'}=$in16[$idx];
$Excel->Cells($srwf,6)->{'Value'}=$supi1[$idx];
$Excel->Cells($srwf,9)->{'Value'}=$in12[$idx];
if ($supi1[$idx] eq "Queuing"){
$Excel->Cells($srwf,10)->{'Value'}= get_port_max_msg_nb("INPUT_DATA", $pname, $in16[$idx]);
}
$Excel->Cells($srwf,12)->{'Value'}="AFDX";
$Excel->Cells($srwf,13)->{'Value'}=$supi2[$idx];
$Excel->Cells($srwf,20)->{'Value'}=$in16[$idx];
}
sub fillrow_o1{
my ($srwf,$idx,$pname) =@_; # $srwf = Row to fill , $idx = line index of array, $pname = partition name
# Worksheet IM_ICD Local Partition, InputLines
$Excel->Cells($srwf,1)->{'Value'}="DATA";
$Excel->Cells($srwf,2)->{'Value'}="OUTPUT_DATA";
$Excel->Cells($srwf,3)->{'Value'}=$pname;
$Excel->Cells($srwf,5)->{'Value'}=$out15[$idx];
$Excel->Cells($srwf,6)->{'Value'}=$supo1[$idx];
$Excel->Cells($srwf,9)->{'Value'}=$out25[$idx];
if ($supo1[$idx] eq "Queuing"){
$Excel->Cells($srwf,10)->{'Value'}= get_port_max_msg_nb("OUTPUT_DATA", $pname, $out15[$idx]);
}
$Excel->Cells($srwf,12)->{'Value'}="AFDX";
$Excel->Cells($srwf,13)->{'Value'}=$supo2[$idx];
$Excel->Cells($srwf,19)->{'Value'}=$out15[$idx];
}
sub fillrow_i2{
my ($srwf,$idx) =@_; # $srwf = Row to fill , $idx = line index of array
# Worksheet ICD Local Partition, InputLines
$Excel->Cells($srwf,1)->{'Value'}="DATA";
$Excel->Cells($srwf,2)->{'Value'}="AFDX_INPUT_VL";
$Excel->Cells($srwf,9)->{'Value'}=$in08[$idx];
$Excel->Cells($srwf,17)->{'Value'}=$in16[$idx];
$Excel->Cells($srwf,19)->{'Value'}=$in18[$idx];
$Excel->Cells($srwf,20)->{'Value'}=$supi1[$idx];
# $Excel->Cells($srwf,21)->{'Value'}=$in20[$idx]; # empty
$Excel->Cells($srwf,22)->{'NumberFormat'} = "@";
$Excel->Cells($srwf,22)->{'Value'}=$in21[$idx];
$Excel->Cells($srwf,23)->{'Value'}=$in22[$idx];
$Excel->Cells($srwf,24)->{'Value'}=$in23[$idx];
}
sub fillrow_o2{
my ($srwf,$idx) =@_; # $srwf = Row to fill , $idx = line index of array
# Worksheet ICD Local Partition, OutputLines
$Excel->Cells($srwf,1)->{'Value'}="DATA";
$Excel->Cells($srwf,2)->{'Value'}="AFDX_OUTPUT_VL";
$Excel->Cells($srwf,9)->{'Value'}=$out08[$idx];
$Excel->Cells($srwf,15)->{'Value'}=$out14[$idx];
$Excel->Cells($srwf,16)->{'Value'}=$out15[$idx];
# $Excel->Cells($srwf,17)->{'Value'}=$out16[$idx]; # remaining empty
$Excel->Cells($srwf,18)->{'Value'}=$out17[$idx];
$Excel->Cells($srwf,19)->{'Value'}=$supo1[$idx];
$Excel->Cells($srwf,20)->{'Value'}=$out19[$idx];
# $Excel->Cells($srwf,21)->{'Value'}=$out20[$idx]; # remaining empty
$Excel->Cells($srwf,22)->{'Value'}=$out21[$idx];
$Excel->Cells($srwf,23)->{'NumberFormat'} = "@";
$Excel->Cells($srwf,23)->{'Value'}=$out22[$idx];
$Excel->Cells($srwf,24)->{'Value'}=$out23[$idx];
$Excel->Cells($srwf,25)->{'Value'}=$out24[$idx];
$Excel->Cells($srwf,26)->{'Value'}=$out25[$idx];
}
sub fillrow_i3{
my ($srwf,$idx,$pname) =@_; # $srwf = Row to fill , $idx = line index of array
# Worksheet ICD Module, InputLines
$Excel->Cells($srwf,1)->{'Value'}="DATA";
$Excel->Cells($srwf,2)->{'Value'}="AFDX_INPUT_VL";
$Excel->Cells($srwf,9)->{'Value'}=$inVL08[$idx];
$Excel->Cells($srwf,11)->{'Value'}=$inVL10[$idx];
$Excel->Cells($srwf,14)->{'Value'}=$inVL13[$idx];
$Excel->Cells($srwf,15)->{'Value'}=$inVL14[$idx];
# $Excel->Cells($srwf,24)->{'Value'}=$inVL23[$idx]; # remaining empty
}
sub fillrow_o3{
my ($srwf,$idx,$pname) =@_; # $srwf = Row to fill , $idx = line index of array
# Worksheet ICD Module, OutputLines
$Excel->Cells($srwf,1)->{'Value'}="DATA";
$Excel->Cells($srwf,2)->{'Value'}="AFDX_OUTPUT_VL";
$Excel->Cells($srwf,9)->{'Value'}=$outVL08[$idx];
$Excel->Cells($srwf,11)->{'Value'}=$outVL10[$idx];
$Excel->Cells($srwf,12)->{'Value'}=$outVL11[$idx];
$Excel->Cells($srwf,13)->{'Value'}=$outVL12[$idx];
$Excel->Cells($srwf,14)->{'Value'}=$outVL13[$idx];
# $Excel->Cells($srwf,26)->{'Value'}=$outVL25[$idx]; # remaining empty
}
# formatting selections (several formats)
sub formsel{
my ($srwb,$srwn)= @_; # $srwb = begin of selection, $srwi = number of lines
my $srwe= $srwb+$srwn-1;
# first column A
$Excel->Range("A$srwb:A$srwe")->Select();
my $_sel1 = $Excel->Selection->Font;
with ($_sel1,
Name => 'Arial',
Size => 6,
Strikethrough => 0,
Superscript => 0,
Subscript => 0,
OutlineFont => 0,
Shadow => 0,
Underline => xlUnderlineStyleNone,
);
$Excel->Selection->Font->{ColorIndex} = 3;
$Excel->Selection->Font->{Bold}= 1;
$Excel->Selection->Interior->{ColorIndex} = 36;
$Excel->Selection->Interior->{Pattern} = xlSolid;
# column B
$Excel->Range("B$srwb:B$srwe")->Select();
my $_sel2 = $Excel->Selection->Font;
with ($_sel2,
Name => 'Arial',
Size => 10,
Strikethrough => 0,
Superscript => 0,
Subscript => 0,
OutlineFont => 0,
Shadow => 0,
Underline => xlUnderlineStyleNone,
ColorIndex => xlAutomatic,
);
# $Excel->Selection->Font->{ColorIndex} = xlAutomatic;
$Excel->Selection->Font->{Bold}= 1;
$Excel->Selection->Interior->{ColorIndex} = xlNone;
$Excel->Selection->Interior->{Pattern} = xlSolid;
# column C - CZ
$Excel->Range("C$srwb:CZ$srwe")->Select();
my $_sel3 = $Excel->Selection->Font;
with ($_sel2,
Name => 'Arial',
Size => 10,
Strikethrough => 0,
Superscript => 0,
Subscript => 0,
OutlineFont => 0,
Shadow => 0,
Underline => xlUnderlineStyleNone,
ColorIndex => xlAutomatic,
);
# $Excel->Selection->Font->{ColorIndex} = xlAutomatic;
$Excel->Selection->Font->{Bold}= 0;
$Excel->Selection->Interior->{ColorIndex} = xlNone;
$Excel->Selection->Interior->{Pattern} = xlSolid;
}
sub clearVLTables{
my ($key1,$key2); # input/output VL keys
our (%inputVL,%outputVL); # hash tables to prevent duplicate VL entries in ICD_MODULE
# clear hash tables for input VL
foreach $key1 (keys(%inputVL)) {
delete $inputVL {$key1}
}
# clear hash tables for output VL
foreach $key2 (keys(%outputVL)) {
delete $outputVL {$key2}
}
}
sub usage{
print "Wrong Syntax !!!\n";
print "Partname : $partname\n";
print "CSV-File : $csvfile\n";
print "Excelfile : $xlfile\n";
print "Outfile : $outfile\n";
}
####################################
# import additional data
####################################
sub import_additional_data
{
my ( $A, $B, $C, $D );
my ( $additional_data_file ) = @_;
my ($line, $count_line, # variable for input
@tmpa, # excel sheet one line
$message_group, # column A (INPUT_DATA or OUTPUT_DATA)
$partition_name, # column B
$port_id, # column C
$port_max_msg_nb # column D
);
my $key1;
our %AdditionalData;
my $I_OUTPUT_DATA = "OUTPUT_DATA";
my $I_INPUT_DATA = "INPUT_DATA";
$A = 0; $B = 1; $C = 2; $D = 3;
# clear hash tables for additional data
foreach $key1 (keys(%AdditionalData)) {
delete $AdditionalData {$key1}
}
open (FPI, "<$additional_data_file") || deb_msg (1, 0, "can't open $additional_data_file : $! ");
while ( defined ( $line = <FPI> ) )
{
chomp ($line);
$line =~ s/\r//g;
@tmpa = split ( /;/, $line);
next if (! defined ($tmpa[$A]));
$message_group = "$tmpa[$A]";
if ( $message_group eq $I_OUTPUT_DATA
|| $message_group eq $I_INPUT_DATA )
{
$partition_name = $tmpa[$B];
$port_id = $tmpa[$C];
$port_max_msg_nb = $tmpa[$D];
$key1 = sprintf("%s-%s-%s-%s", $message_group, $partition_name, $port_id, "PORT_MAX_MSG_NB");
# deb_msg (0,0, sprintf ("Add: \$AdditionalData{%s} = \"%s\"\n", $key1, $port_max_msg_nb));
$AdditionalData{$key1} = $port_max_msg_nb;
}
}
close(FPI) || deb_msg (1, 0, "can't close $additional_data_file : $! ");
}
##################################################
# get port max msg nb
##################################################
sub get_port_max_msg_nb
{
my ($message_group, $partition_name, $port_id) = @_;
my $port_max_msg_nb = "";
my $key1;
our %AdditionalData;
$key1 = sprintf("%s-%s-%s-%s", $message_group, $partition_name, $port_id, "PORT_MAX_MSG_NB");
if ( defined($AdditionalData{$key1}) )
{
$port_max_msg_nb = $AdditionalData{$key1};
}
else
{
deb_msg (0,0, sprintf ("##### Error: No port_max_msg_nb available for message_group=%s, partition_name=%s, port_id=%s!\n", $message_group, $partition_name, $port_id));
}
return $port_max_msg_nb;
}
####################################
# logging
####################################
sub deb_msg
{
my ($error, $debug, @message) = @_;
if ( $error > 0 )
{
print "-----: @message ";
die "PLERR: $error @message \n";
}
else
{
print "-----: @message";
}
}
__END__