Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^2: Out of memory and While replacements with excel XLSX application

by Anonymous Monk
on Oct 08, 2012 at 09:29 UTC ( [id://997792]=note: print w/replies, xml ) Need Help??


in reply to Re: Out of memory and While replacements with excel XLSX application
in thread Out of memory and While replacements with excel XLSX application

Here is a start at refactoring

diff fudge fudge2

--- fudge 2012-10-08 02:22:09.312500000 -0700 +++ fudge2 2012-10-08 02:22:06.140625000 -0700 @@ -70,222 +70,17 @@ #Extract the necessary information from the rows and columns and plac +es them in three arrays @right @legal and @prod foreach my $row ( 1 .. $Tot_Rows ) { - foreach my $col (1) { - - # skip empty cells - next unless defined $Sheet->Cells( $row, $col )->{'Va +lue'}; - $val = $Sheet->Cells( $row, $col )->{'Value'}; - - if ( $val =~ /^\d{10}$/ ) { - - #Gets column one information - foreach my $col1 (1) { - next - unless - defined $Sheet->Cells( $row, $col1 )->{'Val +ue'}; - $c1 = $Sheet->Cells( $row, $col1 )->{'Value'} +; - push @right, $c1; - push @legal, $c1; - push @prod, $c1; - - #Gets region from column 2 - foreach my $col4 (4) { - next - unless - defined $Sheet->Cells( $row, $col4 )->{ +'Value'}; - $c4 = $Sheet->Cells( $row, $col4 )->{'Val +ue'}; - push @right, $c4; - push @legal, $c4; - push @prod, $c4; - } - - #Gets column 3 information - foreach my $col5 (5) { - next - unless - defined $Sheet->Cells( $row, $col5 )->{ +'Value'}; - $c5 = $Sheet->Cells( $row, $col5 )->{'Val +ue'}; - push @right, $c5; - push @legal, $c5; - push @prod, $c5; - } - - #Get the description that needs to be process +ed later - foreach my $col6 (6) { - next - unless - defined $Sheet->Cells( $row, $col6 )->{ +'Value'}; - $c6 = $Sheet->Cells( $row, $col6 )->{'Val +ue'}; - push @legal, $c6; - } - - #Gets area - foreach my $col7 (7) { - next - unless - defined $Sheet->Cells( $row, $col7 )->{ +'Value'}; - $c7 = $Sheet->Cells( $row, $col7 )->{'Val +ue'}; - push @right, $c7; - - } - - #Gets column 8 information - foreach my $col8 (8) { - next - unless - defined $Sheet->Cells( $row, $col8 )->{ +'Value'}; - $c8 = $Sheet->Cells( $row, $col8 )->{'Val +ue'}; - push @right, $c8; - } - - #Gets column 9 - foreach my $col9 (9) { - next - unless - defined $Sheet->Cells( $row, $col9 )->{ +'Value'}; - $c9 = $Sheet->Cells( $row, $col9 )->{'Val +ue'}; - push @right, $c9; - } - - #and so on.... - foreach my $col10 (10) { - next - unless - defined $Sheet->Cells( $row, $col10 )-> +{'Value'}; - $c10 = $Sheet->Cells( $row, $col10 )->{'V +alue'}; - push @right, $c10; - push @legal, $c10; - } - - #same - foreach my $col11 (11) { - next - unless - defined $Sheet->Cells( $row, $col11 )-> +{'Value'}; - $c11 = $Sheet->Cells( $row, $col11 )->{'V +alue'}; - push @right, $c11; - push @legal, $c11; - } - - #same - foreach my $col12 (12) { - $c12 = $Sheet->Cells( $row, $col12 )->{'V +alue'}; - push @right, $c12; - push @prod, $c12; - } - - #same - foreach my $col13 (13) { - $c13 = $Sheet->Cells( $row, $col13 )->{'V +alue'}; - push @prod, $c13; - } - - #same - foreach my $col14 (14) { - $c14 = $Sheet->Cells( $row, $col14 )->{'V +alue'}; - push @prod, $c14; - } - - #same - foreach my $col15 (15) { - $c15 = $Sheet->Cells( $row, $col15 )->{'V +alue'}; - push @prod, $c15; + if( my $firstCol = eval { + $Sheet->Cells( $row, $col )->{'Value'} } + ){ - #same - foreach my $col17 (17) { - $c17 = $Sheet->Cells( $row, $col17 )->{'V +alue'}; - push @prod, $c17; - } - - #same - foreach my $col18 (18) { - $c18 = $Sheet->Cells( $row, $col18 )->{'V +alue'}; - push @prod, $c18; - } - - #same - foreach my $col20 (20) { - $c20 = $Sheet->Cells( $row, $col20 )->{'V +alue'}; - push @prod, $c20; - } - - #same - foreach my $col21 (21) { - $c21 = $Sheet->Cells( $row, $col21 )->{'V +alue'}; - push @prod, $c21; - } + if ( $firstCol =~ /^\d{10}$/ ) { + push @right, $firstCol; + push @legal, $firstCol; + push @prod, $firstCol; - #same - foreach my $col22 (22) { - $c22 = $Sheet->Cells( $row, $col22 )->{'V +alue'}; - push @prod, $c22; - } - - #same - foreach my $col23 (23) { - $c23 = $Sheet->Cells( $row, $col23 )->{'V +alue'}; - push @prod, $c23; - } - - #same - foreach my $col24 (24) { - $c24 = $Sheet->Cells( $row, $col24 )->{'V +alue'}; - push @prod, $c24; - } - - #same - foreach my $col25 (25) { - $c25 = $Sheet->Cells( $row, $col25 )->{'V +alue'}; - push @prod, $c25; - } - #same - foreach my $col26 (26) { - $c26 = $Sheet->Cells( $row, $col26 )->{'V +alue'}; - push @prod, $c26; - } - - #same - foreach my $col27 (27) { - $c27 = $Sheet->Cells( $row, $col27 )->{'V +alue'}; - push @prod, $c27; - } - - #32-39 production data - foreach my $col32 (32) { - $c32 = $Sheet->Cells( $row, $col32 )->{'V +alue'}; - push @prod, $c32; - } - foreach my $col33 (33) { - $c33 = $Sheet->Cells( $row, $col33 )->{'V +alue'}; - push @prod, $c33; - } - foreach my $col34 (34) { - $c34 = $Sheet->Cells( $row, $col34 )->{'V +alue'}; - push @prod, $c34; - } - foreach my $col35 (35) { - $c35 = $Sheet->Cells( $row, $col35 )->{'V +alue'}; - push @prod, $c35; - } - foreach my $col36 (36) { - $c36 = $Sheet->Cells( $row, $col36 )->{'V +alue'}; - push @prod, $c36; - } - foreach my $col37 (37) { - $c37 = $Sheet->Cells( $row, $col37 )->{'V +alue'}; - push @prod, $c37; - } - foreach my $col38 (38) { - $c38 = $Sheet->Cells( $row, $col38 )->{'V +alue'}; - push @prod, $c38; - } - foreach my $col39 (39) { - $c39 = $Sheet->Cells( $row, $col39 )->{'V +alue'}; - push @prod, $c39; - } - } } } }

diff fudge2 fudge3

diff fudge3 fudge4

diff fudge4 fudge5

diff fudge5 fudge6

diff fudge6 fudge7

diff fudge7 fudge8

diff fudge8 fudge9

diff fudge9 fudge10

I end up with fudge10

Replies are listed 'Best First'.
Re^3: Out of memory and While replacements with excel XLSX application
by Anonymous Monk on Oct 08, 2012 at 09:41 UTC
    ValueTargetCols should loop over rows first then cols, but it probably makes no difference in the results in this case

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others chilling in the Monastery: (3)
As of 2024-03-29 04:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found