1 package WebPAC::Input::Excel;
7 use Spreadsheet::ParseExcel;
8 use Spreadsheet::ParseExcel::Utility qw/int2col/;
12 WebPAC::Input::Excel - support for Microsoft Excell and compatibile files
20 our $VERSION = '0.01';
25 Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org
26 or Gnuemeric) in C<.xls> format.
28 my $isis = new WebPAC::Input::Excel();
29 $isis->open( path => '/path/to/workbook.xls' );
35 Returns handle to database and size
37 my ($db,$size) = $open_db(
38 path => '/path/to/workbook.xls'
39 worksheet => 'name of sheet',
42 C<worksheet> is case and white-space insensitive name of worksheet in Excel
43 file to use. If not specified, it will use first worksheet in file.
54 my $log = $self->_get_logger();
56 $log->logdie("can't open excel file $arg->{path}: $!") unless (-r $arg->{path} && -f $arg->{path});
58 my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($arg->{path});
60 my ($size, $wanted_worksheet);
62 if ($wanted_worksheet = $arg->{worksheet}) {
65 $sheet = shift @{ $workbook->{Worksheet} };
66 $name = $sheet->{Name};
68 } until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);
72 $sheet = shift @{ $workbook->{Worksheet} };
76 $size = $sheet->{MaxRow} - $sheet->{MinRow};
78 $log->warn("opening Excel file '$arg->{path}', using ",
79 $wanted_worksheet ? '' : 'first ',
80 "worksheet: $sheet->{Name} [$size rows]"
88 Return record with ID C<$mfn> from database
90 my $rec = $self->fetch_rec( $db, $mfn );
99 my (undef, $mfn) = @_;
101 my $log = $self->_get_logger();
103 $log->logdie("can't find sheet hash") unless (defined($sheet));
104 $log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet'));
108 my $row = $sheet->{MinRow} + $mfn - 1;
110 $log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});
112 foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
113 if (my $v = $sheet->{Cells}->[$row]->[$col]->{Val}) {
114 $rec->{ int2col($col) } = $v;
118 # add mfn only to records with data
119 $rec->{'000'} = [ $mfn ] if ($rec);
126 Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>
128 =head1 COPYRIGHT & LICENSE
130 Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved.
132 This program is free software; you can redistribute it and/or modify it
133 under the same terms as Perl itself.
137 1; # End of WebPAC::Input::Excel