Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Re^2: Thanks !! (XML + Excel) with (win32::OLE and XML::Simple)

by Sporti69 (Acolyte)
on Dec 06, 2008 at 18:23 UTC ( [id://728583]=note: print w/replies, xml ) Need Help??


in reply to Re: Thanks !! (XML + Excel) with (win32::OLE and XML::Simple)
in thread Thanks !! (XML + Excel) with (win32::OLE and XML::Simple)

Nice feedback !!!

I applied your first 8 remarks to the script.

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

I will paste the updated version, BUT I have an error now saying: Couldn't open encmap iso-859-1.enc: No such file or directory at C:/Perl/lib/XML/Parser.pm line 187 I dont get this ? It finds the file as you can see in the output, and I do not use the parser module in the script.

What's wrong, or do only I have this error all of a sudden.

XML File is in the first post, this is the updated script:
#!/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"; }

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://728583]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (7)
As of 2024-03-29 09:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found