sub __add_non_array {
my ($worksheet, $rowCount, $colCount, $sub) = @_;
$worksheet->Cells($rowCount, $colCount)->{Value} = $sub;
$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
$colCount++;
$worksheet->Cells($rowCount, $colCount)->{Value} = $doc->{$sub};
$worksheet->Cells($rowCount, $colCount)->Borders() -> {Weight} = 2;
$rowCount++;
$colCount--;
return ($rowCount, $colCount);
}
while( my ($sub1, $aoh1) = each %$doc ){
unless (ref($aoh1) eq 'ARRAY') {
($rowCount,$colCount) = __add_non_array($worksheet, $rowCount, $
+colCount, $sub1);
next;
}
foreach my $sub2Hash ( @$aoh1 ){
while( my ($sub2, $aoh2) = each %$sub2Hash ){
unless (ref($aoh2) eq 'ARRAY') {
($rowCount,$colCount) = __add_non_array($worksheet, $rowCo
+unt, $colCount, $sub2);
next;
}
foreach my $sub3Hash ( @$aoh2 ){
while( my ($sub3, $aoh3) = each %$sub3Hash ){
unless (ref($aoh3) eq 'ARRAY') {
($rowCount,$colCount) = __add_non_array($worksheet
+, $rowCount, $colCount, $sub3);
next;
}
foreach my $sub4Hash ( @$aoh3 ){
print "Collecting general information...\n";
while( my ($sub4, $aoh4) = each %$sub4Hash ){
unless (ref($aoh4) eq 'ARRAY') {
($rowCount,$colCount) = __add_non_array($w
+orksheet, $rowCount, $colCount, $sub4);
next;
}
print "Collecting $sub4 ...\n";
$subDataRow = scalar(@$aoh4) + ($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 $sub5Hash ( @$aoh4 ){
my $temp = "";
while( my ($sub5, $aoh5) = each %$sub5
+Hash ){
my $curID = $sub5Hash{enclosureNum
+ber} || $sub5Hash{number} || 0;;
my $nextup = "";
if(ref($aoh5) eq
+'ARRAY'){
#################
# SUBINFORMATIE #
#################
+ $sub 4 is rank $sub5 is attribuut van rank $sub6 is element binnen
+rank eg arrayref
foreach my $s
+ub6Hash ( @$aoh5 ){
# 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++;
while( m
+y ($sub6, $val6) = each %$sub6Hash ){
# 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){
$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} = $val6;
$subDataCol++;
}
$subDataCol=1;
}
}
else{ # aoh5 is
+not an array
#################
+##
# 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} = $aoh5;
$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} = $aoh5;
$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;
}
}
NOTE: I noticed after i wrote it out that the