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

Here is a start at refactoring

diff fudge fudge2

diff fudge2 fudge3

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

diff fudge6 fudge7

diff fudge7 fudge8

diff fudge8 fudge9

diff fudge9 fudge10

--- fudge9    2012-10-08 01:48:45.046875000 -0700
+++ fudge10    2012-10-08 02:23:20.609375000 -0700
@@ -53,74 +53,13 @@
 ###This points to the folder where the excel files are placed
     my $workfile = "C:/Users/McLovin/Documents/Thesis/Datatal/" . "$u
+sefile";
     print "$workfile\n";
-    my $Book = $Excel->Workbooks->Open($workfile);
-
-    my $sheetcnt = $Book->Worksheets->Count();
-#~     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'} };
-        
-        if(  defined $firstCol and $firstCol =~ /^\d{10}$/  )
-        {            
-            push @right, $firstCol;
-            push @legal, $firstCol;
-            push @prod,  $firstCol;
-            
-            ValueTargetCols(
-                $Sheet,
-                [ \@right, \@legal, \@prod, ],
-                [ 1 .. $Tot_Rows ],
-                [ qw{ 4 5 } ],
-            );
             
-            ValueTargetCols(
-                $Sheet,
-                [ \@legal, ],
-                [ 1 .. $Tot_Rows ],
-                [ qw{ 6 } ],
-            );
             
-            ValueTargetCols(
-                $Sheet,
-                [ \@right, ],
-                [ 1 .. $Tot_Rows ],
-                [ qw{ 7 8 9  } ],
+    BlahBlahNameHere(
+        $Excel,
+        $workfile,
+        \@recID, \@right, \@legal, \@prod,
             );
-            
-            ValueTargetCols(
-                $Sheet,
-                [ \@right, \@legal, ],
-                [ 1 .. $Tot_Rows ],
-                [ qw{ 10 11  } ],
-            );
-            
-            ValueTargetCols(
-                $Sheet,
-                [ \@prod, \@right, ],
-                [ 1 .. $Tot_Rows ],
-                [ qw{ 12 } ],
-            );
-            
-            ValueTargetCols(
-                $Sheet,
-                [ \@prod ],
-                [ 1 .. $Tot_Rows ],
-                [ qw{
-                    13 14 15
-                    17 18
-                    20 21 22 23 24 25 26 27
-                    32 33 34 35 36 37 38 39
-                    } ],
-            );
-
-        }
-    }
-    $Book->Close;
 }
 
 #####################################################################
+###########
@@ -351,4 +290,78 @@
     }
     return;
 }
+
+
+    
+sub BlahBlahNameHere {
+    my( $Excel, $workfile, $recID, $right, $legal, $prod ) = @_;
+    my $Book = $Excel->Workbooks->Open($workfile);
+
+    my $sheetcnt = $Book->Worksheets->Count();
+#~     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'} };
+        
+        if(  defined $firstCol and $firstCol =~ /^\d{10}$/  )
+        {            
+            push @$right, $firstCol;
+            push @$legal, $firstCol;
+            push @$prod,  $firstCol;
+            
+            ValueTargetCols(
+                $Sheet,
+                [ $right, $legal, $prod, ],
+                [ 1 .. $Tot_Rows ],
+                [ qw{ 4 5 } ],
+            );
+            
+            ValueTargetCols(
+                $Sheet,
+                [ $legal, ],
+                [ 1 .. $Tot_Rows ],
+                [ qw{ 6 } ],
+            );
+            
+            ValueTargetCols(
+                $Sheet,
+                [ $right, ],
+                [ 1 .. $Tot_Rows ],
+                [ qw{ 7 8 9  } ],
+            );
+            
+            ValueTargetCols(
+                $Sheet,
+                [ $right, $legal, ],
+                [ 1 .. $Tot_Rows ],
+                [ qw{ 10 11  } ],
+            );
+            
+            ValueTargetCols(
+                $Sheet,
+                [ $prod, $right, ],
+                [ 1 .. $Tot_Rows ],
+                [ qw{ 12 } ],
+            );
+            
+            ValueTargetCols(
+                $Sheet,
+                [ $prod ],
+                [ 1 .. $Tot_Rows ],
+                [ qw{
+                    13 14 15
+                    17 18
+                    20 21 22 23 24 25 26 27
+                    32 33 34 35 36 37 38 39
+                    } ],
+            );
+
+        }
+    }
+    $Book->Close;
+}
 __END__
[download]

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]