Your remark 9: well level 1,2 and 3 are put into one sheet and thus they are arrays, but all the other differ. Even in the deepest level it can be eighter array or hash. I hope you (guys) ran the script to give it a go.
I cannot try out the other suggestions yet because of the error written below, strange
#!/usr/bin/perl --
use strict;
use warnings;
use Cwd;
use Win32::OLE;
use XML::Simple;
use Win32::OLE::Const 'Microsoft Excel';
my $dir = cwd();
opendir(DIR, $dir) || die "can't opendir $dir: $!";
print "$dir\n";
my @files = grep(/\.xml$/,readdir(DIR));
closedir(DIR);
use Data::Dumper;
local $Data::Dumper::Indent=1;
#print Dumper($doc);
my $input = "x";
while ($input ne "y")
{
print "\n\nThis script will try to parse all the xml files in the
+scripts folder. \nThis might take up to 5 minutes depending on the xm
+l file(s) size. \nYou can see what is being parsed at a certain time.
+\n\nDo you want to run the script? [y/n]\n";
$input = <STDIN>;
chomp $input;
}
unless( scalar @files ){
print "\n\nNo Files Found in $dir,\nMake sure your script is in a di
+rectory with the XML files\n\n";
exit;
}
parseFile($_) for @files;
sub parseFile {
print "\n#############################################\n\nInitializing
+ script ...\n \nAnalysing file $_[0]\n\n#############################
+################\n\n";
print "ok";
my $xs1 = XML::Simple->new();
my $doc = $xs1->XMLin($_[0], keyattr=>[], ForceContent=>1, ForceArray=
+>1);
my $application = Win32::OLE->new("Excel.Application");
$application->{DisplayAlerts} = 0;
my $workbook = $application->Workbooks->Add();
for (my $count = 3; $count >= 0; $count--) {
if ($workbook->WorkSheets($count)) {$workbook->WorkSheets($count)->Del
+ete();}
}
print "ok";
my $worksheet = $workbook->Worksheets->Add({After=>$workbook->Workshee
+ts($workbook->Worksheets->{Count})});
$worksheet -> {Name} = "General Information";
$worksheet -> Range("A1") -> Font -> {Size}= 14;
$worksheet -> Range("A1") -> Font -> {ColorIndex}= 2;
$worksheet -> Range("A1") -> {Value} = "General Information";
$worksheet -> Range("A1:E1") -> Merge;
$worksheet -> Range("A1:E1") -> Interior -> {ColorIndex} = 25;
$worksheet -> Range("A1:E1") -> Borders() -> {Weight} = 3;
my $LastRow = 0;
my $new = 0;
my $mainHeaders = 3; #main header row
my $rowCount=4; #main info row
my $colCount = 2; #main info column
my $subHeaderRow = 0; #additional info header row
my $subDataRow = 50; #additional info data row
foreach my $sub1 ( sort keys %{ $doc } ){
unless (ref($doc->{$sub1})) {
$worksheet->Cells($rowCount, $colCount)->{Value} = $sub1;
$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight}
+ = 2;
$colCount++;
$worksheet->Cells($rowCount, $colCount)->{Value} = $doc->{$sub
+1};
$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight}
+ = 2;
$rowCount++;
$colCount--;
next;
}
my $count = $#{ $doc->{$sub1} };
foreach my $i(0 .. $count) {
foreach my $sub2 ( sort keys %{ $doc->{$sub1}[$i] } ){
unless (ref($doc->{$sub1}[$i]{$sub2})) {
$worksheet->Cells($rowCount, $colCount)->{Value} = $su
+b2;
$worksheet->Cells($rowCount, $colCount)->Borders() ->
+{Weight} = 2;
$colCount++;
$worksheet->Cells($rowCount, $colCount)->{Value} = $do
+c->{$sub1}[$i]{$sub2};
$worksheet->Cells($rowCount, $colCount)->Borders() ->
+{Weight} = 2;
$rowCount++;
$colCount--;
next;
}
my $count2 = $#{$doc->{$sub1}[$i]{$sub2}};
foreach my $j(0 .. $count2) {
foreach my $sub3 ( sort keys %{ $doc->{$sub1}[$i]{$sub
+2}[$j] } ){
unless (ref($doc->{$sub1}[$i]{$sub2}[$j]{$sub3}))
+{
$worksheet->Cells($rowCount, $colCount)->{Valu
+e} = $sub3;
$worksheet->Cells($rowCount, $colCount)->Borde
+rs() -> {Weight} = 2;
$colCount++;
$worksheet->Cells($rowCount, $colCount)->{Valu
+e} = $doc->{$sub1}[$i]{$sub2}[$j]{$sub3};
$worksheet->Cells($rowCount, $colCount)->Borde
+rs() -> {Weight} = 2;
$rowCount++;
$colCount--;
next;
}
my $count3 = $#{$doc->{$sub1}[$i]{$sub2}[$j]{$sub3
+}};
foreach my $k(0 .. $count3) {
print "Collecting general information...\n";
foreach my $sub4 ( sort keys %{ $doc->{$sub1}[
+$i]{$sub2}[$j]{$sub3}[$k] } ){
unless (ref($doc->{$sub1}[$i]{$sub2}[$j]{$
+sub3}[$k]{$sub4})) {
$worksheet->Cells($rowCount, $colCount
+)->{Value} = $sub4;
$worksheet->Cells($rowCount, $colCount
+)->Borders() -> {Weight} = 2;
$colCount++;
$worksheet->Cells($rowCount, $colCount
+)->{Value} = $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4};
$worksheet->Cells($rowCount, $colCount
+)->Borders() -> {Weight} = 2;
$rowCount++;
$colCount--;
+
next;
}
print "Collecting $sub4 ...\n";
my $count4 = $#{$doc->{$sub1}[$i]{$sub2}[$
+j]{$sub3}[$k]{$sub4}};
$subDataRow = ($count4 + 1) + ($rowCount +
+ 1);
my $worksheet = $workbook->Worksheets->Add
+({After=>$workbook->Worksheets($workbook->Worksheets->{Count})});
$worksheet -> {Name} = $sub4;
$worksheet -> Range("A1") -> Font -> {Size
+}= 14;
$worksheet -> Range("A1") -> Font -> {Colo
+rIndex}= 2;
$worksheet -> Range("A1") -> {Value} = "$s
+ub4";
$worksheet -> Range("A1:E1") -> Merge;
$worksheet -> Range("A1:E1") -> Interior -
+> {ColorIndex} = 25;
$worksheet -> Range("A1:E1") -> Borders()
+-> {Weight} = 3;
my $subDataCol = 1;
foreach my $l(0 .. $count4) {
my $temp = "";
foreach my $sub5 ( sort keys %{ $doc->
+{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l] } ){
my $curID = 0;
my $nextup = "";
#print "\n\t$sub4 -> $sub5 -> $do
+c->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}";
+
if($doc->{$sub1}[
+$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{number}){ $curID = $doc->{$sub1}
+[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{number};}
if($doc->{$sub1}[
+$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{enclosureNumber}){ $curID = $doc
+->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{enclosureNumber};}
if(ref($doc->{$su
+b1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5} )){
#################
# SUBINFORMATIE #
#################
+ $sub 4 is rank $sub5 is attribuut van rank $sub6 is element binnen
+rank eg arrayref
my $count5 =
+$#{$doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}};
foreach m
+y $m(0 .. $count5) {
# Nie
+uwe ArayRef, ExtentbyVol,StorageDeviceFRY (nieuwe titels maken
if($n
+extup ne $sub5){
$
+new = 1;
$
+subDataRow++;
$
+subHeaderRow = $subDataRow;
$
+subDataCol=1 ;
$
+nextup = $sub5;
}
else
+{$new = 0;}
$subDataRow++;
foreach my $sub6 (
+ sort keys %{ $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub
+5}[$m]} ){
#
+ print "\n$sub1->$sub2->$sub3->$sub4->$sub5->$sub6 : $doc->{$sub1}
+[$i]{$sub2}[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}[$m]{$sub6}";
# subinfo titl
+e on new element
if ($temp ne $
+curID){
$temp = $c
+urID;
$worksheet
+->Cells($subDataRow, $subDataCol)->{Value} = "$sub4 : $curID";
$worksheet
+->Cells($subDataRow, $subDataCol)->Interior->{ColorIndex} = 33;
$worksheet
+->Cells($subDataRow, $subDataCol)->{Font}->{Bold} = 1;
$worksheet
+ ->Cells($subDataRow, $subDataCol)-> Borders() -> {Weight} = 1;
$subDataRo
+w+=2;
$subHeader
+Row = $subDataRow;
$subDataRo
+w++;
}
# Header
if($new==1){
$worksheet
+->Cells($subHeaderRow, $subDataCol)->{Value} = $sub6;
$worksheet
+->Cells($subHeaderRow, $subDataCol)->{Font}->{Bold} = 1;
$worksheet
+ ->Cells($subHeaderRow, $subDataCol)-> Borders() -> {Weight} = 2;
$worksheet
+ ->Cells($subHeaderRow, $subDataCol)-> Borders() -> {ColorIndex} = 25
+;
}
$worksheet
+->Cells($subDataRow, $subDataCol)->{Value} = $doc->{$sub1}[$i]{$sub2}
+[$j]{$sub3}[$k]{$sub4}[$l]{$sub5}[$m]{$sub6};
$subDataCol++;
}
$subDataCol=1;
}
}
else{
#################
+##
# MAIN INFORMATIO
+N#
#################
+##
# Index, if a
+ny of those, will be placed in first column
if ($sub5 eq
+"number" | $sub5 eq "id" | $sub5 eq "enclosureNumber"){
$workshee
+t->Cells($mainHeaders, 1)->{Value} = $sub5;
$worksheet->Cells($mainHea
+ders, 1)->{Font} -> {ColorIndex} = 25;
$worksheet->Cells($mainHea
+ders, 1)->{Interior} -> {ColorIndex} = 15;
$worksheet->Cells($mainHea
+ders, 1)->Borders() -> {Weight} = 2;
$worksheet->Cells($mainHea
+ders, 1)->{Font}->{Bold} = 1;
$worksheet->Cell
+s($rowCount, 1)->{Value} = $doc->{$sub1}[$i]{$sub2}[$j]{$sub3}[$k]{$s
+ub4}[$l]{$sub5};
$worksheet->Cells($rowCoun
+t, 1)->{Font}->{Bold} = 1;
$worksheet->Cells($rowCoun
+t, 1)->Borders() -> {Weight} = 2;
}
else{
unless ($
+worksheet->Cells($mainHeaders, $colCount)->{Value}){
$work
+sheet->Cells($mainHeaders, $colCount)->{Value} = $sub5;
}
if ($sub5 eq "wwpn
+"){
$worksheet->Cells($row
+Count, $colCount)->{NumberFormat} = "0";
$worksheet->Cells($row
+Count, $colCount)->Borders() -> {Weight} = 2;
+
}
$workshee
+t->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
$workshee
+t->Cells($rowCount, $colCount)->{Value} = $doc->{$sub1}[$i]{$sub2}[$j
+]{$sub3}[$k]{$sub4}[$l]{$sub5};
$workshee
+t->Cells($mainHeaders, $colCount)->Borders() -> {Weight} = 2;
$worksheet->Cells($mainHea
+ders, $colCount)->{Font}->{Bold} = 1;
$worksheet->Cells($mainHea
+ders, $colCount)->{Font} -> {ColorIndex} = 25;
$worksheet->Cells($mainHea
+ders, $colCount)->{Interior} -> {ColorIndex} = 15;
$colCount++;
$LastRow = $worksheet->Use
+dRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xl
+ByRows})->{Row};
}
}
} $rowCount++;
$subDataRow++;
$colCount=2;
$worksheet -> Range("A:X") -> {Colum
+ns} -> Autofit;
}
$subDataRow=$rowCount+2;;
$colCount=2;
$rowCount=4;
#Seperate general information on first sheet.
} $rowCount=4; $colCount+=3;
}
} $rowCount=4; $colCount+=3;
}
} $rowCount=4; $colCount+=3;
}
}
$worksheet -> Range("A:X") -> {Columns} -> Autofit;
$workbook->SaveAs($dir . '/'.$_[0].'.xls');
$workbook->Close;
print "\n\n$_[0].xls generated in $dir\n" ;
print "\a";
}