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

[Anonymous Monk]

Here is a start at refactoring

diff fudge fudge2

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

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

diff fudge3 fudge4

<Reveal this spoiler>

diff fudge4 fudge5

<Reveal this spoiler>

diff fudge5 fudge6

<Reveal this spoiler>

diff fudge6 fudge7

--- fudge6    2012-10-08 01:27:35.187500000 -0700
+++ fudge7    2012-10-08 01:40:55.031250000 -0700
@@ -9,7 +9,7 @@
 use Win32::OLE::Variant;
 use Win32::OLE::NLS qw(:LOCALE :DATE);
 use List::MoreUtils qw(natatime);
-use Excel::Writer::XLSX;
+#~ use Excel::Writer::XLSX;
 
 ############################################################
 
@@ -69,13 +69,11 @@
         my $Tot_Cols = $Sheet->UsedRange->Columns->{'Count'};
 
 #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 ) {
-            if( my $firstCol = eval {
-                    $Sheet->Cells( $row, $col )->{'Value'}
-                }
-                ){
                     
-                if ( $firstCol =~ /^\d{10}$/ ) {            
+            if( my $firstCol = eval { $Sheet->Cells( 1, 1)->{'Value'}
+ }
+                and $firstCol =~ /^\d{10}$/ 
+            )
+            {            
                     push @right, $firstCol;
                     push @legal, $firstCol;
                     push @prod,  $firstCol;
@@ -83,42 +81,42 @@
                     ValueTargetCols(
                         $Sheet,
                         [ \@right, \@legal, \@prod, ],
-                        [ $row ],
+                    [ 1 .. $Tot_Rows ],
                         [ qw{ 4 5 } ],
                     );
                     
                     ValueTargetCols(
                         $Sheet,
                         [ \@legal, ],
-                        [ $row ],
+                    [ 1 .. $Tot_Rows ],
                         [ qw{ 6 } ],
                     );
                     
                     ValueTargetCols(
                         $Sheet,
                         [ \@right, ],
-                        [ $row ],
+                    [ 1 .. $Tot_Rows ],
                         [ qw{ 7 8 9  } ],
                     );
                     
                     ValueTargetCols(
                         $Sheet,
                         [ \@right, \@legal, ],
-                        [ $row ],
+                    [ 1 .. $Tot_Rows ],
                         [ qw{ 10 11  } ],
                     );
                     
                     ValueTargetCols(
                         $Sheet,
                         [ \@prod, \@right, ],
-                        [ $row ],
+                    [ 1 .. $Tot_Rows ],
                         [ qw{ 12 } ],
                     );
                     
                     ValueTargetCols(
                         $Sheet,
                         [ \@prod ],
-                        [ $row ],
+                    [ 1 .. $Tot_Rows ],
                         [ qw{
                             13 14 15
                             17 18
@@ -130,7 +128,6 @@
                 }
             }
         }
-    }
     $Book->Close;
 }

diff fudge7 fudge8

<Reveal this spoiler>

diff fudge8 fudge9

<Reveal this spoiler>

diff fudge9 fudge10

<Reveal this spoiler>

I end up with fudge10

<Reveal this spoiler>

[Anonymous Monk]

ValueTargetCols should loop over rows first then cols, but it probably makes no difference in the results in this case