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

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

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

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]