note
Anonymous Monk
<p> Here is a start at refactoring <readmore> <p> diff fudge fudge2 <spoiler><c>
--- 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 places 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 )->{'Value'};
- $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 )->{'Value'};
- $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 )->{'Value'};
- 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 )->{'Value'};
- push @right, $c5;
- push @legal, $c5;
- push @prod, $c5;
- }
-
- #Get the description that needs to be processed later
- foreach my $col6 (6) {
- next
- unless
- defined $Sheet->Cells( $row, $col6 )->{'Value'};
- $c6 = $Sheet->Cells( $row, $col6 )->{'Value'};
- push @legal, $c6;
- }
-
- #Gets area
- foreach my $col7 (7) {
- next
- unless
- defined $Sheet->Cells( $row, $col7 )->{'Value'};
- $c7 = $Sheet->Cells( $row, $col7 )->{'Value'};
- push @right, $c7;
-
- }
-
- #Gets column 8 information
- foreach my $col8 (8) {
- next
- unless
- defined $Sheet->Cells( $row, $col8 )->{'Value'};
- $c8 = $Sheet->Cells( $row, $col8 )->{'Value'};
- push @right, $c8;
- }
-
- #Gets column 9
- foreach my $col9 (9) {
- next
- unless
- defined $Sheet->Cells( $row, $col9 )->{'Value'};
- $c9 = $Sheet->Cells( $row, $col9 )->{'Value'};
- push @right, $c9;
- }
-
- #and so on....
- foreach my $col10 (10) {
- next
- unless
- defined $Sheet->Cells( $row, $col10 )->{'Value'};
- $c10 = $Sheet->Cells( $row, $col10 )->{'Value'};
- push @right, $c10;
- push @legal, $c10;
- }
-
- #same
- foreach my $col11 (11) {
- next
- unless
- defined $Sheet->Cells( $row, $col11 )->{'Value'};
- $c11 = $Sheet->Cells( $row, $col11 )->{'Value'};
- push @right, $c11;
- push @legal, $c11;
- }
-
- #same
- foreach my $col12 (12) {
- $c12 = $Sheet->Cells( $row, $col12 )->{'Value'};
- push @right, $c12;
- push @prod, $c12;
- }
-
- #same
- foreach my $col13 (13) {
- $c13 = $Sheet->Cells( $row, $col13 )->{'Value'};
- push @prod, $c13;
- }
-
- #same
- foreach my $col14 (14) {
- $c14 = $Sheet->Cells( $row, $col14 )->{'Value'};
- push @prod, $c14;
- }
-
- #same
- foreach my $col15 (15) {
- $c15 = $Sheet->Cells( $row, $col15 )->{'Value'};
- push @prod, $c15;
+ if( my $firstCol = eval {
+ $Sheet->Cells( $row, $col )->{'Value'}
}
+ ){
- #same
- foreach my $col17 (17) {
- $c17 = $Sheet->Cells( $row, $col17 )->{'Value'};
- push @prod, $c17;
- }
-
- #same
- foreach my $col18 (18) {
- $c18 = $Sheet->Cells( $row, $col18 )->{'Value'};
- push @prod, $c18;
- }
-
- #same
- foreach my $col20 (20) {
- $c20 = $Sheet->Cells( $row, $col20 )->{'Value'};
- push @prod, $c20;
- }
-
- #same
- foreach my $col21 (21) {
- $c21 = $Sheet->Cells( $row, $col21 )->{'Value'};
- 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 )->{'Value'};
- push @prod, $c22;
- }
-
- #same
- foreach my $col23 (23) {
- $c23 = $Sheet->Cells( $row, $col23 )->{'Value'};
- push @prod, $c23;
- }
-
- #same
- foreach my $col24 (24) {
- $c24 = $Sheet->Cells( $row, $col24 )->{'Value'};
- push @prod, $c24;
- }
-
- #same
- foreach my $col25 (25) {
- $c25 = $Sheet->Cells( $row, $col25 )->{'Value'};
- push @prod, $c25;
- }
- #same
- foreach my $col26 (26) {
- $c26 = $Sheet->Cells( $row, $col26 )->{'Value'};
- push @prod, $c26;
- }
-
- #same
- foreach my $col27 (27) {
- $c27 = $Sheet->Cells( $row, $col27 )->{'Value'};
- push @prod, $c27;
- }
-
- #32-39 production data
- foreach my $col32 (32) {
- $c32 = $Sheet->Cells( $row, $col32 )->{'Value'};
- push @prod, $c32;
- }
- foreach my $col33 (33) {
- $c33 = $Sheet->Cells( $row, $col33 )->{'Value'};
- push @prod, $c33;
- }
- foreach my $col34 (34) {
- $c34 = $Sheet->Cells( $row, $col34 )->{'Value'};
- push @prod, $c34;
- }
- foreach my $col35 (35) {
- $c35 = $Sheet->Cells( $row, $col35 )->{'Value'};
- push @prod, $c35;
- }
- foreach my $col36 (36) {
- $c36 = $Sheet->Cells( $row, $col36 )->{'Value'};
- push @prod, $c36;
- }
- foreach my $col37 (37) {
- $c37 = $Sheet->Cells( $row, $col37 )->{'Value'};
- push @prod, $c37;
- }
- foreach my $col38 (38) {
- $c38 = $Sheet->Cells( $row, $col38 )->{'Value'};
- push @prod, $c38;
- }
- foreach my $col39 (39) {
- $c39 = $Sheet->Cells( $row, $col39 )->{'Value'};
- push @prod, $c39;
- }
- }
}
}
}
</c></spoiler>
<p> diff fudge2 fudge3 <spoiler><c>
--- 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;
+ }
}
}
</c></spoiler>
<p> diff fudge3 fudge4 <spoiler><c>
--- 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 39 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;
}
}
</c></spoiler>
<p> diff fudge4 fudge5 <spoiler><c>
--- 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 39 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;
}
}
+
}
}
}
</c></spoiler>
<p> diff fudge5 fudge6 <spoiler><c>
--- fudge5 2012-10-08 02:21:55.468750000 -0700
+++ fudge6 2012-10-08 01:27:35.187500000 -0700
@@ -80,83 +80,52 @@
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;
- }
-
-
- for my $col (
- qw{
+ ValueTargetCols(
+ $Sheet,
+ [ \@right, \@legal, \@prod, ],
+ [ $row ],
+ [ qw{ 4 5 } ],
+ );
+
+ ValueTargetCols(
+ $Sheet,
+ [ \@legal, ],
+ [ $row ],
+ [ qw{ 6 } ],
+ );
+
+ ValueTargetCols(
+ $Sheet,
+ [ \@right, ],
+ [ $row ],
+ [ qw{ 7 8 9 } ],
+ );
+
+ ValueTargetCols(
+ $Sheet,
+ [ \@right, \@legal, ],
+ [ $row ],
+ [ qw{ 10 11 } ],
+ );
+
+ ValueTargetCols(
+ $Sheet,
+ [ \@prod, \@right, ],
+ [ $row ],
+ [ qw{ 12 } ],
+ );
+
+ ValueTargetCols(
+ $Sheet,
+ [ \@prod ],
+ [ $row ],
+ [ 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;
- }
- }
-
+ } ],
+ );
}
}
@@ -377,4 +346,20 @@
+
+sub ValueTargetCols {
+ my( $sheet, $targets, $row, $cols ) = @_;
+ for my $col ( @$cols )
+ {
+ for my $row( @$rows )
+ {
+ if ( my $val = eval { $sheet->Cells( $row, $col )->{Value} } ) {
+ for my $target ( @$targets ){
+ push @{$target}, $val;
+ }
+ }
+ }
+ }
+ return;
+}
__END__
</c></spoiler>
<p> diff fudge6 fudge7 <spoiler><c>
--- 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 places 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;
}
</c></spoiler>
<p> diff fudge7 fudge8 <spoiler><c>
--- fudge7 2012-10-08 01:40:55.031250000 -0700
+++ fudge8 2012-10-08 01:47:28.906250000 -0700
@@ -5,7 +5,7 @@
use strict;
use warnings;
use Win32::OLE qw(in with);
-use Win32::OLE::Const 'Microsoft Excel';
+#~ use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
use List::MoreUtils qw(natatime);
@@ -37,7 +37,6 @@
my $counti; #used to count the excelfiles
my $usefile; #The file that is being used
my @recID; #an array of ID's for the worksheets
-my $ID; #The actual ID being pushed to the array
my $val; #A test value that is used to test whether a row should be included
my @right
; #The first of the arrays that will be filled during the process and will be further disected later
@@ -59,20 +58,16 @@
#####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) {
- my $Sheet = $Book->Worksheets($r);
- $ID = $Sheet->{Name};
- push @recID, $ID;
+#~ foreach my $r (1) {
+ {
+ my $Sheet = $Book->Worksheets( 1 );
+ push @recID, $Sheet->{Name};
print "Worksheet name is $Sheet->{Name}\n";
- ##work on each row and column
my $Tot_Rows = $Sheet->UsedRange->Rows->{'Count'};
my $Tot_Cols = $Sheet->UsedRange->Columns->{'Count'};
+ my $firstCol = eval { $Sheet->Cells( 1, 1)->{'Value'} }
-#Extract the necessary information from the rows and columns and places them in three arrays @right @legal and @prod
-
- if( my $firstCol = eval { $Sheet->Cells( 1, 1)->{'Value'} }
- and $firstCol =~ /^\d{10}$/
- )
+ if( defined $firstCol and $firstCol =~ /^\d{10}$/ )
{
push @right, $firstCol;
push @legal, $firstCol;
@@ -127,7 +122,6 @@
}
}
- }
$Book->Close;
}
@@ -345,7 +339,7 @@
sub ValueTargetCols {
- my( $sheet, $targets, $row, $cols ) = @_;
+ my( $sheet, $targets, $rows, $cols ) = @_;
for my $col ( @$cols )
{
for my $row( @$rows )
</c></spoiler>
<p> diff fudge8 fudge9 <spoiler><c>
--- 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}$/ )
{
</c></spoiler>
<p> diff fudge9 fudge10 <spoiler><c>
--- 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/" . "$usefile";
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__
</c></spoiler>
</readmore>
<p> I end up with fudge10 <spoiler><c>#!C:\strawberry\perl\bin\perl
####Load Packages
use strict;
use warnings;
use Win32::OLE qw(in with);
#~ use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
use List::MoreUtils qw(natatime);
#~ use Excel::Writer::XLSX;
############################################################
####Initialize Excel Object and Count Files to Process
$Win32::OLE::Warn = 3; #die on errors...
#get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new( 'Excel.Application', 'Quit' );
#Get the file names of the production files and prints a status of the results
my $ProdDirect = 'C:/Users/McLovin/Documents/Thesis/Data/';
opendir DH, $ProdDirect or die "Cannot open $ProdDirect: $!";
my @files = grep { !-d } readdir DH;
print "@files\n";
closedir DH;
my $NumberofFiles = @files;
print "Number of files is: $NumberofFiles\n";
################################################################################
#################################################################
################Start Processing the Data########################
#################################################################
my $counti; #used to count the excelfiles
my $usefile; #The file that is being used
my @recID; #an array of ID's for the worksheets
my $val; #A test value that is used to test whether a row should be included
my @right
; #The first of the arrays that will be filled during the process and will be further disected later
my @prod; #The second array, same as above
my @legal; #The third
#####Loop over all the files
for ( $counti = 0 ; $counti < $NumberofFiles ; $counti++ ) {
##### open Excel file -- This is the input data files
print "Working on file $counti of $NumberofFiles\n";
$usefile = $files[$counti];
###This points to the folder where the excel files are placed
my $workfile = "C:/Users/McLovin/Documents/Thesis/Datatal/" . "$usefile";
print "$workfile\n";
BlahBlahNameHere(
$Excel,
$workfile,
\@recID, \@right, \@legal, \@prod,
);
}
################################################################################
#Prints of the created arrays
#print "@right\n";
#print "@legal\n";
#print "@prod\n";
################################################################################
####################################################################################
#the process that eliminates duplicates in the @right array based on criteria
#makes a copy of the array as it is destroyed in the next while statement, This is maybe one of the problems
my @rig = @right; #copy of array
my @righ; # a new array that is the result of the while statement below
my %seen; # a hash that stores agreementnumbers for unique entries
#The array is in exact sets of 9 strings and i want it spliced a intervals of exactly those intervals
while ( my ( $m, $n, $o, $p, $q, $r, $s, $t, $y ) = splice( @right, 0, 9 ) ) {
last if $m !~ /^\d{10}$/;
next if $seen{$m}++;
if ( $n =~ /Specific_region/ ) {
if ( $o =~ /NG/ ) {
push @righ, $m, $o, $p, $q, $r, $s, $t;
}
}
}
# print "@righ\n";
my @leg; #an array that holds the result for the next while statement
my @spli; #used as a container for certain entries in the while statement
my @joi; #another middle of equation array for picking up results
%seen = ()
; #emties the previous hash as the uniqueness of entries is also important here
my @tes; #yet another array for picking up results
################################################################################
#the process that insures that each returned value is printed in the correct
#form and coupled with the ten digit number
#again the array is organized in 6 values in a row that need to be seperated out into rows.
while ( my ( $h, $aa, $rr, $j, $k, $l ) = splice( @legal, 0, 6 ) ) {
last if $h !~ /^\d{10}$/;
if ( $aa =~ /Specific_region/ ) {
if ( $rr =~ /NG/ ) {
if ( $j =~ /\n/ ) {
next if $seen{$h}++;
my @spli = split( /\n/, $j );
foreach my $n (@spli) {
if ( $n =~ /LSD/ ) {
my @tes = split( /LSD/, $n );
foreach my $lon (@tes) {
if ( $lon =~ /SEC/ ) {
my @joi = split( /-|W|:|\s|,|\(/, $lon );
my $chans = @joi;
my $eleg =
join( "", @joi[ 0, 2, 1, $chans - 1 ] );
push @leg, $eleg, $h, $k, $l;
}
}
}
elsif ( $n =~ /\(/ ) {
my @joi = split( /-|W|:|\s|\(/, $n );
my $chans = @joi;
my $eleg = join( "", @joi[ 0, 2, 1, $chans - 2 ] );
push @leg, $eleg, $h, $k, $l;
}
else {
my @joi = split( /-|W|:|\s/, $n );
my $chans = @joi;
my $eleg = join( "", @joi[ 0, 2, 1, $chans - 1 ] );
push @leg, $eleg, $h, $k, $l;
}
}
}
else {
next if $seen{$h}++;
my @joi = split( /-|W|:|\s/, $j );
my $chans = @joi;
my $eleg = join( "", @joi[ 0, 2, 1, $chans - 1 ] );
push @leg, $eleg, $h, $k, $l;
}
}
}
}
#print "@leg\n";
my @peg;
foreach my $loma (@leg) {
if ( $loma =~ /^\d{7}$/ ) {
substr( $loma, 6, 0, 0 );
push @peg, $loma;
}
else {
push @peg, $loma;
}
}
####################################################################################
#the process that creates the production array for the entries
my @produ;
while (
my (
$cp, $aaa, $rrr, $dp, $ep, $fp, $gp, $hp, $ip,
$jp, $kp, $lp, $mp, $np, $op, $pp, $qp, $rp,
$sp, $tp, $up, $vp, $wp, $yp, $xp
)
= splice( @prod, 0, 25 )
)
{
last if $cp !~ /^\d{10}$/;
if ( $aaa =~ /Specific_region/ ) {
if ( $rrr =~ /NG/ ) {
unless (
$dp =~ /a specific repeated text for all relevant entries/ )
{
#eliminate this if statement for option two, where entries with no actual production is included
if ( defined($qp) && $qp =~ /\d\d-\d\d-\d{4}/ ) {
push @produ, $dp, $ep, $cp, $fp, $gp, $hp, $ip, $jp, $kp,
$lp, $mp, $np, $op, $pp, $qp, $rp, $sp, $tp, $up, $vp,
$wp, $yp, $xp;
}
}
}
}
}
# print "@produ\n";
my @nwells; #an array that collects the results
#creates the 4.2 entries "agreements with no wells
while ( my ( $mn, $nn, $on, $pn, $qn, $rn, $sn, $tn, $yn ) =
splice( @rig, 0, 9 ) )
{
last if $mn !~ /^\d{10}$/;
if ( $nn =~ /Specific_region/ ) {
if ( $on =~ /NG/ ) {
if ( $yn =~ /a specific repeated text for all relevant entries/ ) {
push @nwells, $mn, $on, $pn, $qn, $rn, $sn, $tn, $yn;
}
}
}
}
# print "@nwells\n";
#Places results into arrays of arrays for easy computation in excel. uses natatime again the arrays
# are of a specific size. per row.
my @AAR;
{
my $iter = natatime 7, @righ;
while ( my @tmp = $iter->() ) {
push @AAR, \@tmp;
}
}
my @BAR;
{
my $iter = natatime 4, @peg;
while ( my @tmp = $iter->() ) {
push @BAR, \@tmp;
}
}
my @CAR;
{
my $iter = natatime 23, @produ;
while ( my @tmp = $iter->() ) {
push @CAR, \@tmp;
}
}
my @DAR;
{
my $iter = natatime 8, @nwells;
while ( my @tmp = $iter->() ) {
push @DAR, \@tmp;
}
}
#####The new excel sheets that should contain the results
my $workbooknew = Excel::Writer::XLSX->new('re1.xlsx');
my $worksheetnew = $workbooknew->add_worksheet();
$worksheetnew->keep_leading_zeros();
$worksheetnew->set_column( 'A:G', 30 );
$worksheetnew->write_col( 'A2', \@AAR );
####This is the data on the legalnumber - introduce keep_leading zeroes for correct legal form
my $workbooknew1 = Excel::Writer::XLSX->new('re2.xlsx');
my $worksheetnew1 = $workbooknew1->add_worksheet();
$worksheetnew1->keep_leading_zeros();
$worksheetnew1->set_column( 'A:D', 15 );
$worksheetnew1->write_col( 'A2', \@BAR );
# # ####This is the data on the production of the wells - introduce keep_leading zeroes for correct legal form
my $workbooknew2 = Excel::Writer::XLSX->new('re3.xlsx');
my $worksheetnew2 = $workbooknew2->add_worksheet();
my $worksheetnew3 = $workbooknew2->add_worksheet();
$worksheetnew2->set_column( 'A:W', 50 );
$worksheetnew2->write_col( 'A2', \@CAR );
$worksheetnew3->set_column( 'A:H', 30 );
$worksheetnew3->write_col( 'A2', \@DAR );
sub ValueTargetCols {
my( $sheet, $targets, $rows, $cols ) = @_;
for my $col ( @$cols )
{
for my $row( @$rows )
{
if ( my $val = eval { $sheet->Cells( $row, $col )->{Value} } ) {
for my $target ( @$targets ){
push @{$target}, $val;
}
}
}
}
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__
</c></spoiler>
997733
997762
4