1 package WebPAC::Input::Excel;
6 use Spreadsheet::ParseExcel;
7 use Spreadsheet::ParseExcel::Utility qw/int2col/;
8 use base qw/WebPAC::Common/;
12 WebPAC::Input::Excel - support for Microsoft Excel and compatibile files
20 our $VERSION = '0.04';
25 Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org
26 or Gnuemeric) in C<.xls> format.
32 Returns handle to database and size
34 my $excel = new WebPAC::Input::Excel(
35 path => '/path/to/workbook.xls'
36 worksheet => 'name of sheet',
41 C<worksheet> is case and white-space insensitive name of worksheet in Excel
42 file to use. If not specified, it will use first worksheet in file.
44 C<from> and C<to> specify row numbers to start and finish import.
53 my $log = $self->_get_logger();
55 $log->logdie("can't open excel file $self->{path}: $!") unless (-r $self->{path} && -f $self->{path});
57 my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{path});
62 if ($wanted_worksheet = $self->{worksheet}) {
65 $sheet = shift @{ $workbook->{Worksheet} };
66 $log->logdie("can't find sheet '$wanted_worksheet' in $self->{path}\n") unless (defined($sheet));
67 $name = $sheet->{Name};
69 } until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);
73 $sheet = shift @{ $workbook->{Worksheet} };
77 $self->{sheet} = $sheet;
79 $self->{from} ||= $sheet->{MinRow};
80 $self->{to} ||= $sheet->{MaxRow};
82 my $size = $self->{to} - $self->{from};
83 $self->{size} = $size;
85 $log->warn("opening Excel file '$self->{path}', using ",
86 $wanted_worksheet ? '' : 'first ',
87 "worksheet: $sheet->{Name} [$size rows]"
90 $self ? return $self : return undef;
95 Return record with ID C<$mfn> from database
97 my $rec = $self->fetch_rec( $mfn );
108 my $log = $self->_get_logger();
110 my $sheet = $self->{sheet};
111 $log->logdie("can't find sheet hash") unless (defined($sheet));
112 $log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet'));
116 my $row = $self->{from} + $mfn - 1;
118 $log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});
120 foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
121 if (my $v = $sheet->{Cells}->[$row]->[$col]->{Val}) {
122 $rec->{ int2col($col) } = $v;
126 # add mfn only to records with data
127 $rec->{'000'} = [ $mfn ] if ($rec);
134 Return number of records in database
136 my $size = $isis->size;
142 return $self->{size};
146 Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>
148 =head1 COPYRIGHT & LICENSE
150 Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved.
152 This program is free software; you can redistribute it and/or modify it
153 under the same terms as Perl itself.
157 1; # End of WebPAC::Input::Excel