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;
-                        }
-                    }
                 }
             }
         }

[download]

diff fudge2 fudge3

<Reveal this spoiler or all spoilers in this node or all in this thread>

diff fudge3 fudge4 

--- fudge3    2012-10-08 02:22:02.328125000 -0700
+++ fudge4    2012-10-08 02:21:58.968750000 -0700
@@ -142,115 +142,11 @@
         push @prod,  $val;
     }
 
-    if ( my $val = eval { $Sheet->Cells( $row, 13 )->{Value} } ) {
 
+        for my $col ( qw{ 13 14 15 17 18 20 21 22 23 24 25 26 27 32 3
+9 32 33 34 35 36 37 38 39 }){
+            if ( my $val = eval { $Sheet->Cells( $row, $col )->{Value
+} } ) {
         push @prod, $val;
     }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 14 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 15 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 17 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 18 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 20 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 21 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 22 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 23 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 24 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 25 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 26 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #same
-    if ( my $val = eval { $Sheet->Cells( $row, 27 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-
-    #32-39 production data
-    if ( my $val = eval { $Sheet->Cells( $row, 32 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-    if ( my $val = eval { $Sheet->Cells( $row, 33 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-    if ( my $val = eval { $Sheet->Cells( $row, 34 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-    if ( my $val = eval { $Sheet->Cells( $row, 35 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-    if ( my $val = eval { $Sheet->Cells( $row, 36 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-    if ( my $val = eval { $Sheet->Cells( $row, 37 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-    if ( my $val = eval { $Sheet->Cells( $row, 38 )->{Value} } ) {
-
-        push @prod, $val;
-    }
-    if ( my $val = eval { $Sheet->Cells( $row, 39 )->{Value} } ) {
-
-        push @prod, $val;
     }
 
                 }

[download]

diff fudge4 fudge5 

--- fudge4    2012-10-08 02:21:58.968750000 -0700
+++ fudge5    2012-10-08 02:21:55.468750000 -0700
@@ -143,12 +143,21 @@
     }
 
 
-        for my $col ( qw{ 13 14 15 17 18 20 21 22 23 24 25 26 27 32 3
+9 32 33 34 35 36 37 38 39 }){
+        for my $col (
+            qw{
+            13 14 15
+            17 18
+            20 21 22 23 24 25 26 27
+            32 33 34 35 36 37 38 39
+            }
+          )
+        {
             if ( my $val = eval { $Sheet->Cells( $row, $col )->{Value
+} } ) {
                 push @prod, $val;
             }
         }
 
+
                 }
             }
         }

[download]

diff fudge5 fudge6

<Reveal this spoiler>

diff fudge6 fudge7

<Reveal this spoiler>

diff fudge7 fudge8

<Reveal this spoiler>

diff fudge8 fudge9 

--- fudge8    2012-10-08 01:47:28.906250000 -0700
+++ fudge9    2012-10-08 01:48:45.046875000 -0700
@@ -55,17 +55,15 @@
     print "$workfile\n";
     my $Book = $Excel->Workbooks->Open($workfile);
 
-#####Work on each sheet in the workbook
-#####This is the number of worksheets in the workbook
     my $sheetcnt = $Book->Worksheets->Count();
-#~     foreach my $r (1) {
+#~     foreach my $r ( 1 .. $sheetcnt ) {
     {
         my $Sheet = $Book->Worksheets( 1 );
         push @recID, $Sheet->{Name};
         print "Worksheet name is $Sheet->{Name}\n";
         my $Tot_Rows = $Sheet->UsedRange->Rows->{'Count'};
         my $Tot_Cols = $Sheet->UsedRange->Columns->{'Count'};
-        my $firstCol = eval { $Sheet->Cells( 1, 1)->{'Value'} }
+        my $firstCol = eval { $Sheet->Cells( 1, 1)->{'Value'} };
         
         if(  defined $firstCol and $firstCol =~ /^\d{10}$/  )
         {

[download]

diff fudge9 fudge10

<Reveal this spoiler>

I end up with fudge10

<Reveal this spoiler>

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
[reply]

In Section Seekers of Perl Wisdom