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

Here is a start at refactoring

diff fudge fudge2

diff fudge2 fudge3

--- fudge2    2012-10-08 02:22:06.140625000 -0700
+++ fudge3    2012-10-08 02:22:02.328125000 -0700
@@ -80,6 +80,178 @@
                     push @legal, $firstCol;
                     push @prod,  $firstCol;
                     
+    #Gets region from column 2
+    if ( my $val = eval { $Sheet->Cells( $row, 4 )->{Value} } ) {
+
+        push @right, $val;
+        push @legal, $val;
+        push @prod,  $val;
+    }
+
+    #Gets column 3 information
+    if ( my $val = eval { $Sheet->Cells( $row, 5 )->{Value} } ) {
+
+        push @right, $val;
+        push @legal, $val;
+        push @prod,  $val;
+    }
+
+    #Get the description that needs to be processed later
+    if ( my $val = eval { $Sheet->Cells( $row, 6 )->{Value} } ) {
+
+        push @legal, $val;
+    }
+
+    #Gets area
+    if ( my $val = eval { $Sheet->Cells( $row, 7 )->{Value} } ) {
+
+        push @right, $val;
+
+    }
+
+    #Gets column 8 information
+    if ( my $val = eval { $Sheet->Cells( $row, 8 )->{Value} } ) {
+
+        push @right, $val;
+    }
+
+    #Gets column 9
+    if ( my $val = eval { $Sheet->Cells( $row, 9 )->{Value} } ) {
+
+        push @right, $val;
+    }
+
+    #and so on....
+    if ( my $val = eval { $Sheet->Cells( $row, 10 )->{Value} } ) {
+
+        push @right, $val;
+        push @legal, $val;
+    }
+
+    #same
+    if ( my $val = eval { $Sheet->Cells( $row, 11 )->{Value} } ) {
+
+        push @right, $val;
+        push @legal, $val;
+    }
+
+    #same
+    if ( my $val = eval { $Sheet->Cells( $row, 12 )->{Value} } ) {
+
+        push @right, $val;
+        push @prod,  $val;
+    }
+
+    if ( my $val = eval { $Sheet->Cells( $row, 13 )->{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 fudge3 fudge4

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

diff fudge6 fudge7

diff fudge7 fudge8

diff fudge8 fudge9

diff fudge9 fudge10

I end up with fudge10

Comment on Re^2: Out of memory and While replacements with excel XLSX application Select or Download Code

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]