2 # BioPerl module for Bio::SeqIO::excel
4 # Please direct questions and support issues to <bioperl-l@bioperl.org>
6 # Cared for by Hilmar Lapp <hlapp at gmx.net>
10 # (c) Hilmar Lapp, hlapp at gmx.net, 2005.
11 # (c) GNF, Genomics Institute of the Novartis Research Foundation, 2005.
13 # You may distribute this module under the same terms as perl itself.
14 # Refer to the Perl Artistic License (see the license accompanying this
15 # software package, or see http://www.perl.com/language/misc/Artistic.html)
16 # for the terms under which you may use, modify, and redistribute this module.
18 # THIS PACKAGE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
19 # WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
20 # MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
23 # POD documentation - main docs before the code
27 Bio::SeqIO::excel - sequence input/output stream from a
28 MSExcel-formatted table
32 #It is probably best not to use this object directly, but
33 #rather go through the SeqIO handler system. Go:
35 $stream = Bio::SeqIO->new(-file => $filename, -format => 'excel');
37 while ( my $seq = $stream->next_seq() ) {
38 # do something with $seq
43 This class transforms records in a MS Excel workbook file into
44 Bio::Seq objects. It is derived from the table format module and
45 merely defines additional properties and overrides the way to get data
46 from the file and advance to the next record.
48 The module permits specifying which columns hold which type of
49 annotation. The semantics of certain attributes, if present, are
50 pre-defined, e.g., accession number and sequence. Additional
51 attributes may be added to the annotation bundle. See
52 L<Bio::SeqIO::table> for a complete list of parameters and
55 You may also specify the worksheet from which to obtain the data, and
56 after finishing one worksheet you may change the name to keep reading
57 from another worksheet (in the same file).
59 This module depends on Spreadsheet::ParseExcel to parse the underlying
66 User feedback is an integral part of the evolution of this and other
67 Bioperl modules. Send your comments and suggestions preferably to one
68 of the Bioperl mailing lists. Your participation is much appreciated.
70 bioperl-l@bioperl.org - General discussion
71 http://bioperl.org/wiki/Mailing_lists - About the mailing lists
75 Please direct usage questions or support issues to the mailing list:
77 I<bioperl-l@bioperl.org>
79 rather than to the module maintainer directly. Many experienced and
80 reponsive experts will be able look at the problem and quickly
81 address it. Please include a thorough description of the problem
82 with code and data examples if at all possible.
86 Report bugs to the Bioperl bug tracking system to help us keep track
87 the bugs and their resolution.
89 Bug reports can be submitted via email or the web:
91 https://redmine.open-bio.org/projects/bioperl/
93 =head1 AUTHOR - Hilmar Lapp
95 Email hlapp at gmx.net
99 The rest of the documentation details each of the object
100 methods. Internal methods are usually preceded with a _
104 # Let the code begin...
106 package Bio
::SeqIO
::excel
;
110 use Spreadsheet
::ParseExcel
;
111 #use Spreadsheet::ParseExcel::Workbook;
113 use base
qw(Bio::SeqIO::table);
118 Usage : $stream = Bio::SeqIO->new(-file => $filename, -format => 'excel')
119 Function: Returns a new seqstream
120 Returns : A Bio::SeqIO stream for a MS Excel format
122 Args : Supports the same named parameters as Bio::SeqIO::table,
123 except -delim, which obviously does not apply to a binary
124 format. In addition, the following parameters are supported.
126 -worksheet the name of the worksheet holding the table;
127 if unspecified the first worksheet will be
134 my($self,@args) = @_;
136 # chained initialization
137 $self->SUPER::_initialize
(@args);
140 my ($worksheet) = $self->_rearrange([qw(WORKSHEET)], @args);
142 # store options and apply defaults
143 $self->worksheet($worksheet || 0);
150 Usage : $obj->worksheet($newval)
151 Function: Get/set the name of the worksheet holding the table. The
152 worksheet name may also be a numeric index.
154 You may change the value during parsing at any time in
155 order to start reading from a different worksheet (in the
159 Returns : value of worksheet (a scalar)
160 Args : on set, new value (a scalar or undef, optional)
169 my $sheetname = shift;
170 # on set we reset the parser here in order to allow reading
171 # from multiple worksheets in a row
172 $self->_worksheet(undef) if defined($sheetname);
173 return $self->{'worksheet'} = $sheetname;
175 return $self->{'worksheet'};
182 Function: Close and/or release the resources used by this parser instance.
184 We override this here in order to free up the worksheet and
185 other related objects.
197 $self->_worksheet(undef);
198 # make sure we chain to the inherited method
199 $self->SUPER::close(@_);
202 =head1 Internal methods
204 All methods with a leading underscore are not meant to be part of the
205 'official' API. They are for use by this module only, consider them
206 private unless you are a developer trying to modify this module.
213 Usage : $obj->_worksheet($newval)
214 Function: Get/set the worksheet object to be used for accessing cells.
216 Returns : value of _worksheet (a Spreadsheet::ParseExcel::Worksheet object)
217 Args : on set, new value (a Spreadsheet::ParseExcel::Worksheet
218 object or undef, optional)
226 return $self->{'_worksheet'} = shift if @_;
227 return $self->{'_worksheet'};
234 Function: Navigates the underlying file to the next record.
236 We override this here in order to adapt navigation to data
237 in an Excel worksheet.
240 Returns : TRUE if the navigation was successful and FALSE
241 otherwise. Unsuccessful navigation will usually be treated
242 as an end-of-file condition.
251 my $wsheet = $self->_worksheet();
252 if (! defined($wsheet)) {
253 # worksheet hasn't been initialized yet, do so now
254 my $wbook = Spreadsheet
::ParseExcel
::Workbook
->Parse($self->_fh);
255 $wsheet = $wbook->Worksheet($self->worksheet);
257 $self->_worksheet($wsheet);
258 # re-initialize the current row
259 $self->{'_row'} = -1;
262 # we need a valid worksheet to continue
263 return unless defined($wsheet);
265 # check whether we are at or beyond the last defined row
266 my ($minrow, $maxrow) = $wsheet->RowRange();
267 return if $self->{'_row'} >= $maxrow;
269 # we don't check for empty rows here as in order to do that we'd
270 # have to know in which column to look
271 # so, just advance to the next row
278 =head2 _get_row_values
280 Title : _get_row_values
282 Function: Get the values for the current line (or row) as an array in
283 the order of columns.
285 We override this here in order to adapt access to column
286 values to data contained in an Excel worksheet.
289 Returns : An array of column values for the current row.
298 # obtain the range of columns - we use all that are defined
299 my $wsheet = $self->_worksheet();
300 my ($colmin,$colmax) = $wsheet->ColRange();
302 # build the array of columns for the current row
304 my $row = $self->{'_row'};
305 for (my $i = $colmin; $i <= $colmax; $i++) {
306 my $cell = $wsheet->Cell($row, $i);
307 push(@cols, defined($cell) ?
$cell->Value : $cell);