Pipeline/Input/Excel.pm

filter: Statementstmt 100% 83 / 83 Branchbran 78.5% 22 / 28 Conditioncond 33.3% 1 / 3 MC/DCmcdc 0.0% 0 / 2 Subroutinesub 100% 12 / 12 Podpod n/a totaltotal 92.1% 118 / 128 CC 22 SCAR 27.3
CC
22
Cov
93%
run
22.5
BEGIN
1.0
BEGIN
1.0
CRAP 15.3
↑ Summary Pipeline/Input/DelimitedText.pm »
1 =pod
2
3 =head1 NAME
4
5 ETL::Pipeline::Input::Excel - Input source for Microsoft Excel spreadsheets
6
7 =head1 SYNOPSIS
8
9 use ETL::Pipeline;
10 ETL::Pipeline->new( {
11 input => ['Excel', iname => qr/\.xlsx$/i],
12 mapping => {First => 'A', Second => qr/ID\s*Num/i},
13 output => ['UnitTest']
14 } )->process;
15
16 =head1 DESCRIPTION
17
18 B<ETL::Pipeline::Input::Excel> defines an input source for reading MS Excel
19 spreadsheets. It uses L<Spreadsheet::XLSX> or L<Spreadsheet::ParseExcel>,
20 depending on the file type (XLSX or XLS).
21
22 =cut
23
24 package ETL::Pipeline::Input::Excel;
25
26 1 ▶use 5.014000;
Subroutine BEGIN: called CC=1 SCAR=0.0
27 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
28
29 1 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
30 1 ▶use List::AllUtils qw/first none/;
Subroutine BEGIN: called CC=1 SCAR=0.0
31 1 ▶use Moose;
Subroutine BEGIN: called CC=1 SCAR=0.0
32 1 ▶use Spreadsheet::ParseExcel;
Subroutine BEGIN: called CC=1 SCAR=0.0
33 1 ▶use Spreadsheet::ParseExcel::Utility qw/int2col/;
Subroutine BEGIN: called CC=1 SCAR=0.0
34 1 ▶use Spreadsheet::XLSX;
Subroutine BEGIN: called CC=1 SCAR=0.0
35 1 ▶use String::Util qw/hascontent/;
Subroutine BEGIN: called CC=1 SCAR=0.0
36
37
38 1 our $VERSION = '3.00';
39
40
41 =head1 METHODS & ATTRIBUTES
42
43 =head2 Arguments for L<ETL::Pipeline/input>
44
45 B<ETL::Pipeline::Input::Excel> implements the L<ETL::Pipeline::Input::File>
46 and L<ETL::Pipeline::Input::File::Table> roles. It supports all of the
47 attributes from these roles.
48
49 =head3 worksheet
50
51 Optional. B<worksheet> reads data from a specific worksheet. By default,
52 B<ETL::Pipeline::Input::Excel> uses the first worksheet.
53
54 B<worksheet> accepts a string or regular expression. As a string, B<worksheet>
55 looks for an exact match. As a regular expression, B<worksheet> finds the first
56 worksheet whose name matches the regular expression. Note that B<worksheet>
57 stops looking once it finds the first mach.
58
59 B<ETL::Pipeline::Input::Excel> throws an error if it cannot find a worksheet
60 with a matching name.
61
62 =cut
63
64 1 has 'worksheet' => (
65 is => 'ro',
66 isa => 'Maybe[RegexpRef|Str]',
67 );
68
69
70 =head3 password
71
72 Optional. B<password> works with encrypted files. B<ETL::Pipeline::Input::Excel>
73 decrypts the file automatically.
74
75 B<Warning:> B<password> only works with Excel 2003 file (XLS). Encrypted XLSX
76 files always fail. L<Spreadsheet::XLSX> does not support encryption.
77
78 =cut
79
80 1 has 'password' => (
81 is => 'ro',
82 isa => 'Maybe[Str]',
83 );
84
85
86 =head3 skipping
87
88 Optional. If you use a code reference for B<skipping>, this input source sends a
89 hash reference. You can access the columns by number or letter.
90
91 If you pass an integer, the input source completely skips over that many lines.
92
93 =head2 Methods
94
95 =head3 run
96
97 This is the main loop. It opens the file, reads records, and closes it when
98 done. This is the place to look if there are problems.
99
100 L<ETL::Pipeline> automatically calls this method.
101
102 =cut
103
104 8 ▶sub run {
Subroutine run: called CC=22 SCAR=31.1
105 8 my ($self, $etl) = @_;
106
107 #----------------------------------------------------------------------
108 # Open the file.
109
110 # Create the correct workbook object based on the file format.
111 8 my $path = $self->path;
112 8 my $workbook;
113
114 8 ▶ if ("$path" =~ m/\.xls$/i) {
Branch: 1 branch
BranchTrueFalseTotal
if ("$path" =~ /\.xls$/iu) { } 1 7 8
115 1 my $excel = Spreadsheet::ParseExcel->new( Password => $self->password );
116 1 $workbook = $excel->parse( "$path" );
117 1 ▶ croak "Unable to open the Excel file '$path'" unless defined $workbook;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $workbook 0 1 1
118 } else {
119 7 $workbook = Spreadsheet::XLSX->new( "$path" );
120 7 ▶ croak "Unable to open the Excel file '$path'" unless defined $workbook;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $workbook 0 7 7
121 }
122
123 # Find the worksheet with data...
124 8 my $name = $self->worksheet;
125 8 my $worksheet;
126 8 ▶ if (hascontent( $name )) {
Branch: 1 branch
BranchTrueFalseTotal
if (hascontent($name)) { } 2 6 8
127 2 ▶ if (ref( $name ) eq 'Regexp') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $name eq 'Regexp') { } 1 1 2
128 1 ▶ $worksheet = first { $_->get_name() =~ m/$name/ } $workbook->worksheets();
Subroutine __ANON__: called CC=1 SCAR=0.0
129 } else {
130 1 $worksheet = $workbook->worksheet( $name );
131 }
132 2 ▶ croak "No worksheets match '$name'" unless defined $worksheet;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $worksheet 0 2 2
133 } else {
134 6 $worksheet = $workbook->worksheet( 0 );
135 6 ▶ croak "'$path' has no worksheets" unless defined $worksheet;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $worksheet 0 6 6
136 }
137
138 # Convert the column numbers into their letter designations.
139 8 my %names;
140 8 $names{int2col( $_ )} = $_ foreach ($worksheet->{MinCol} .. $worksheet->{MaxCol});
141 8 $etl->aliases( \%names );
142
143 #----------------------------------------------------------------------
144 # Read the records.
145 8 my $cells = $worksheet->{Cells};
146 8 my $start = $worksheet->{MinRow};
147
148 # Skip over report headers. These are not data. They are extra rows put
149 # there for report formats. The data starts after these rows.
150 8 my $skip = $self->skipping;
151 8 ▶ if (ref( $skip ) eq 'CODE') {
Branch: 2 branches
BranchTrueFalseTotal
if (ref $skip eq 'CODE') { } 2 6 8
elsif ($skip > 0) { } 1 5 6
152 2 ▶ while ($start <= $worksheet->{MaxRow}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($start <= $worksheet->{"MaxRow"}) 4 0 4
153 4 my %record;
154 4 foreach my $column ($worksheet->{MinCol} .. $worksheet->{MaxCol}) {
155 20 $record{$column} = $cells->[$start][$column]->value;
156 }
157 4 foreach my $alias ($etl->aliases) {
158 4 ▶ while (my ($name, $column) = each %$alias) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = each %$alias) 20 4 24
159 $record{$name} = $record{$column} if
160 exists( $record{$column} )
161 20 ▶ && !exists( $record{$name} )
Branch: 1 branch
BranchTrueFalseTotal
if exists $record{$column} and not exists $record{$name} 20 0 20
Condition33%1/3
exists $record{$column} and not exists $record{$name}
!ll&&!rl&&r
0020
MC/DC0%0/2
exists $record{$column} and not exists $record{$name}
exists $record{$column} not exists $record{$name}
Truth table33%1/3
A and not(B)
A= exists $record{$column}B= not exists $record{$name}
ABresult
0X0
100
111
162 ;
163 }
164 }
165 4 ▶ last if !$skip->( \%record );
Branch: 1 branch
BranchTrueFalseTotal
unless &$skip(\%record) 2 2 4
166 2 $start++;
167 }
168 } elsif ($skip > 0) {
169 1 $start += $skip;
170 }
171
172 # Load field names.
173 8 ▶ unless ($self->no_column_names) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->no_column_names) 2 6 8
174 2 my @names;
175 push( @names, {$cells->[$start][$_]->value => $_} )
176 2 foreach ($worksheet->{MinCol} .. $worksheet->{MaxCol});
177 2 $etl->aliases( @names );
178
179 2 $start++;
180 }
181
182 # Load the data.
183 8 my $source = $self->source;
184 8 foreach my $row ($start .. $worksheet->{MaxRow}) {
185 9 $self->source( sprintf( '%s at row %d', $source, $row + 1 ) );
186
187 9 my $record = [];
188 push( @$record, $cells->[$row][$_]->value )
189 9 foreach ($worksheet->{MinCol} .. $worksheet->{MaxCol});
190 9 $etl->record( $record );
191 }
192 }
193
194
195 =head1 SEE ALSO
196
197 L<ETL::Pipeline>, L<ETL::Pipeline::Input>, L<ETL::Pipeline::Input::File>,
198 L<ETL::Pipeline::Input::File::Table>
199
200 =cut
201
202 1 with 'ETL::Pipeline::Input';
203 1 with 'ETL::Pipeline::Input::File';
204 1 with 'ETL::Pipeline::Input::File::Table';
205
206
207 =head1 AUTHOR
208
209 Robert Wohlfarth <robert.j.wohlfarth@vumc.org>
210
211 =head1 LICENSE
212
213 Copyright 2021 (c) Vanderbilt University Medical Center
214
215 This program is free software; you can redistribute it and/or modify it under
216 the same terms as Perl itself.
217
218 =cut
219
220 1 ▶no Moose;
Subroutine BEGIN: called CC=1 SCAR=0.0
221 1 __PACKAGE__->meta->make_immutable;
↑ Summary Pipeline/Input/DelimitedText.pm »