Bio/SeqIO/excel.pm

   1 # $Id$
   2 #
   3 # BioPerl module for Bio::SeqIO::excel
   4 #
   5 # Please direct questions and support issues to <bioperl-l@bioperl.org>
   6 #
   7 # Cared for by Hilmar Lapp <hlapp at gmx.net>
   8 #
   9
  10 #
  11 # (c) Hilmar Lapp, hlapp at gmx.net, 2005.
  12 # (c) GNF, Genomics Institute of the Novartis Research Foundation, 2005.
  13 #
  14 # You may distribute this module under the same terms as perl itself.
  15 # Refer to the Perl Artistic License (see the license accompanying this
  16 # software package, or see http://www.perl.com/language/misc/Artistic.html)
  17 # for the terms under which you may use, modify, and redistribute this module.
  18 #
  19 # THIS PACKAGE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
  20 # WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
  21 # MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
  22 #
  23
  24 # POD documentation - main docs before the code
  25
  26 =head1 NAME
  27
  28 Bio::SeqIO::excel - sequence input/output stream from a
  29                     MSExcel-formatted table
  30
  31 =head1 SYNOPSIS
  32
  33   #It is probably best not to use this object directly, but
  34   #rather go through the SeqIO handler system. Go:
  35
  36   $stream = Bio::SeqIO->new(-file => $filename, -format => 'excel');
  37
  38   while ( my $seq = $stream->next_seq() ) {
  39         # do something with $seq
  40   }
  41
  42 =head1 DESCRIPTION
  43
  44 This class transforms records in a MS Excel workbook file into
  45 Bio::Seq objects. It is derived from the table format module and
  46 merely defines additional properties and overrides the way to get data
  47 from the file and advance to the next record.
  48
  49 The module permits specifying which columns hold which type of
  50 annotation. The semantics of certain attributes, if present, are
  51 pre-defined, e.g., accession number and sequence. Additional
  52 attributes may be added to the annotation bundle. See
  53 L<Bio::SeqIO::table> for a complete list of parameters and
  54 capabilities.
  55
  56 You may also specify the worksheet from which to obtain the data, and
  57 after finishing one worksheet you may change the name to keep reading
  58 from another worksheet (in the same file).
  59
  60 This module depends on Spreadsheet::ParseExcel to parse the underlying
  61 Excel file.
  62
  63 =head1 FEEDBACK
  64
  65 =head2 Mailing Lists
  66
  67 User feedback is an integral part of the evolution of this and other
  68 Bioperl modules. Send your comments and suggestions preferably to one
  69 of the Bioperl mailing lists.  Your participation is much appreciated.
  70
  71   bioperl-l@bioperl.org                  - General discussion
  72   http://bioperl.org/wiki/Mailing_lists  - About the mailing lists
  73
  74 =head2 Support
  75
  76 Please direct usage questions or support issues to the mailing list:
  77
  78 L<bioperl-l@bioperl.org>
  79
  80 rather than to the module maintainer directly. Many experienced and
  81 reponsive experts will be able look at the problem and quickly
  82 address it. Please include a thorough description of the problem
  83 with code and data examples if at all possible.
  84
  85 =head2 Reporting Bugs
  86
  87 Report bugs to the Bioperl bug tracking system to help us keep track
  88 the bugs and their resolution.
  89
  90 Bug reports can be submitted via email or the web:
  91
  92   http://bugzilla.open-bio.org/
  93
  94 =head1 AUTHOR - Hilmar Lapp
  95
  96 Email hlapp at gmx.net
  97
  98 =head1 APPENDIX
  99
 100 The rest of the documentation details each of the object
 101 methods. Internal methods are usually preceded with a _
 102
 103 =cut
 104
 105 # Let the code begin...
 106
 107 package Bio::SeqIO::excel;
 108 use strict;
 109
 110 use Bio::SeqIO;
 111 use Spreadsheet::ParseExcel;
 112 #use Spreadsheet::ParseExcel::Workbook;
 113
 114 use base qw(Bio::SeqIO::table);
 115
 116 =head2 new
 117
 118  Title   : new
 119  Usage   : $stream = Bio::SeqIO->new(-file => $filename, -format => 'excel')
 120  Function: Returns a new seqstream
 121  Returns : A Bio::SeqIO stream for a MS Excel format
 122
 123  Args    : Supports the same named parameters as Bio::SeqIO::table,
 124            except -delim, which obviously does not apply to a binary
 125            format. In addition, the following parameters are supported.
 126
 127              -worksheet the name of the worksheet holding the table;
 128                         if unspecified the first worksheet will be
 129                         used
 130
 131
 132 =cut
 133
 134 sub _initialize {
 135     my($self,@args) = @_;
 136
 137     # chained initialization
 138     $self->SUPER::_initialize(@args);
 139
 140     # our own parameters
 141     my ($worksheet) = $self->_rearrange([qw(WORKSHEET)], @args);
 142
 143     # store options and apply defaults
 144     $self->worksheet($worksheet || 0);
 145
 146 }
 147
 148 =head2 worksheet
 149
 150  Title   : worksheet
 151  Usage   : $obj->worksheet($newval)
 152  Function: Get/set the name of the worksheet holding the table. The
 153            worksheet name may also be a numeric index.
 154
 155            You may change the value during parsing at any time in
 156            order to start reading from a different worksheet (in the
 157            same file).
 158
 159  Example :
 160  Returns : value of worksheet (a scalar)
 161  Args    : on set, new value (a scalar or undef, optional)
 162
 163
 164 =cut
 165
 166 sub worksheet{
 167     my $self = shift;
 168
 169     if (@_) {
 170         my $sheetname = shift;
 171         # on set we reset the parser here in order to allow reading
 172         # from multiple worksheets in a row
 173         $self->_worksheet(undef) if defined($sheetname);
 174         return $self->{'worksheet'} = $sheetname;
 175     }
 176     return $self->{'worksheet'};
 177 }
 178
 179 =head2 close
 180
 181  Title   : close
 182  Usage   :
 183  Function: Close and/or release the resources used by this parser instance.
 184
 185            We override this here in order to free up the worksheet and
 186            other related objects.
 187
 188  Example :
 189  Returns :
 190  Args    :
 191
 192
 193 =cut
 194
 195 sub close{
 196     my $self = shift;
 197
 198     $self->_worksheet(undef);
 199     # make sure we chain to the inherited method
 200     $self->SUPER::close(@_);
 201 }
 202
 203 =head1 Internal methods
 204
 205 All methods with a leading underscore are not meant to be part of the
 206 'official' API. They are for use by this module only, consider them
 207 private unless you are a developer trying to modify this module.
 208
 209 =cut
 210
 211 =head2 _worksheet
 212
 213  Title   : _worksheet
 214  Usage   : $obj->_worksheet($newval)
 215  Function: Get/set the worksheet object to be used for accessing cells.
 216  Example :
 217  Returns : value of _worksheet (a Spreadsheet::ParseExcel::Worksheet object)
 218  Args    : on set, new value (a Spreadsheet::ParseExcel::Worksheet
 219            object or undef, optional)
 220
 221
 222 =cut
 223
 224 sub _worksheet{
 225     my $self = shift;
 226
 227     return $self->{'_worksheet'} = shift if @_;
 228     return $self->{'_worksheet'};
 229 }
 230
 231 =head2 _next_record
 232
 233  Title   : _next_record
 234  Usage   :
 235  Function: Navigates the underlying file to the next record.
 236
 237            We override this here in order to adapt navigation to data
 238            in an Excel worksheet.
 239
 240  Example :
 241  Returns : TRUE if the navigation was successful and FALSE
 242            otherwise. Unsuccessful navigation will usually be treated
 243            as an end-of-file condition.
 244  Args    :
 245
 246
 247 =cut
 248
 249 sub _next_record{
 250     my $self = shift;
 251
 252     my $wsheet = $self->_worksheet();
 253     if (! defined($wsheet)) {
 254         # worksheet hasn't been initialized yet, do so now
 255         my $wbook = Spreadsheet::ParseExcel::Workbook->Parse($self->_fh);
 256         $wsheet = $wbook->Worksheet($self->worksheet);
 257         # store the result
 258         $self->_worksheet($wsheet);
 259         # re-initialize the current row
 260         $self->{'_row'} = -1;
 261     }
 262
 263     # we need a valid worksheet to continue
 264     return unless defined($wsheet);
 265
 266     # check whether we are at or beyond the last defined row
 267     my ($minrow, $maxrow) = $wsheet->RowRange();
 268     return if $self->{'_row'} >= $maxrow;
 269
 270     # we don't check for empty rows here as in order to do that we'd
 271     # have to know in which column to look
 272     # so, just advance to the next row
 273     $self->{'_row'}++;
 274
 275     # done
 276     return 1;
 277 }
 278
 279 =head2 _get_row_values
 280
 281  Title   : _get_row_values
 282  Usage   :
 283  Function: Get the values for the current line (or row) as an array in
 284            the order of columns.
 285
 286            We override this here in order to adapt access to column
 287            values to data contained in an Excel worksheet.
 288
 289  Example :
 290  Returns : An array of column values for the current row.
 291  Args    :
 292
 293
 294 =cut
 295
 296 sub _get_row_values{
 297     my $self = shift;
 298
 299     # obtain the range of columns - we use all that are defined
 300     my $wsheet = $self->_worksheet();
 301     my ($colmin,$colmax) = $wsheet->ColRange();
 302
 303     # build the array of columns for the current row
 304     my @cols = ();
 305     my $row = $self->{'_row'};
 306     for (my $i = $colmin; $i <= $colmax; $i++) {
 307         my $cell = $wsheet->Cell($row, $i);
 308         push(@cols, defined($cell) ? $cell->Value : $cell);
 309     }
 310     # done
 311     return @cols;
 312 }
 313
 314 1;