| 1 |
package WebPAC::Input::Excel; |
| 2 |
|
| 3 |
use warnings; |
| 4 |
use strict; |
| 5 |
|
| 6 |
use WebPAC::Input; |
| 7 |
use Spreadsheet::ParseExcel; |
| 8 |
use Spreadsheet::ParseExcel::Utility qw/int2col/; |
| 9 |
|
| 10 |
=head1 NAME |
| 11 |
|
| 12 |
WebPAC::Input::Excel - support for Microsoft Excell and compatibile files |
| 13 |
|
| 14 |
=head1 VERSION |
| 15 |
|
| 16 |
Version 0.01 |
| 17 |
|
| 18 |
=cut |
| 19 |
|
| 20 |
our $VERSION = '0.01'; |
| 21 |
|
| 22 |
|
| 23 |
=head1 SYNOPSIS |
| 24 |
|
| 25 |
Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org |
| 26 |
or Gnuemeric) in C<.xls> format. |
| 27 |
|
| 28 |
my $isis = new WebPAC::Input::Excel(); |
| 29 |
$isis->open( path => '/path/to/workbook.xls' ); |
| 30 |
|
| 31 |
=head1 FUNCTIONS |
| 32 |
|
| 33 |
=head2 open_db |
| 34 |
|
| 35 |
Returns handle to database and size |
| 36 |
|
| 37 |
my ($db,$size) = $open_db( |
| 38 |
path => '/path/to/workbook.xls' |
| 39 |
worksheet => 'name of sheet', |
| 40 |
} |
| 41 |
|
| 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. |
| 44 |
|
| 45 |
=cut |
| 46 |
|
| 47 |
my $sheet; |
| 48 |
|
| 49 |
sub open_db { |
| 50 |
my $self = shift; |
| 51 |
|
| 52 |
my $arg = {@_}; |
| 53 |
|
| 54 |
my $log = $self->_get_logger(); |
| 55 |
|
| 56 |
$log->logdie("can't open excel file $arg->{path}: $!") unless (-r $arg->{path} && -f $arg->{path}); |
| 57 |
|
| 58 |
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($arg->{path}); |
| 59 |
|
| 60 |
my ($size, $wanted_worksheet); |
| 61 |
|
| 62 |
if ($wanted_worksheet = $arg->{worksheet}) { |
| 63 |
my $name; |
| 64 |
do { |
| 65 |
$sheet = shift @{ $workbook->{Worksheet} }; |
| 66 |
$name = $sheet->{Name}; |
| 67 |
$name =~ s/\s\s+/ /g; |
| 68 |
} until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i); |
| 69 |
|
| 70 |
} else { |
| 71 |
|
| 72 |
$sheet = shift @{ $workbook->{Worksheet} }; |
| 73 |
|
| 74 |
} |
| 75 |
|
| 76 |
$size = $sheet->{MaxRow} - $sheet->{MinRow}; |
| 77 |
|
| 78 |
$log->warn("opening Excel file '$arg->{path}', using ", |
| 79 |
$wanted_worksheet ? '' : 'first ', |
| 80 |
"worksheet: $sheet->{Name} [$size rows]" |
| 81 |
); |
| 82 |
|
| 83 |
return (42, $size); |
| 84 |
} |
| 85 |
|
| 86 |
=head2 fetch_rec |
| 87 |
|
| 88 |
Return record with ID C<$mfn> from database |
| 89 |
|
| 90 |
my $rec = $self->fetch_rec( $db, $mfn ); |
| 91 |
|
| 92 |
} |
| 93 |
|
| 94 |
=cut |
| 95 |
|
| 96 |
sub fetch_rec { |
| 97 |
my $self = shift; |
| 98 |
|
| 99 |
my (undef, $mfn) = @_; |
| 100 |
|
| 101 |
my $log = $self->_get_logger(); |
| 102 |
|
| 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')); |
| 105 |
|
| 106 |
my $rec; |
| 107 |
|
| 108 |
my $row = $sheet->{MinRow} + $mfn - 1; |
| 109 |
|
| 110 |
$log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol}); |
| 111 |
|
| 112 |
foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) { |
| 113 |
if (my $v = $sheet->{Cells}->[$row]->[$col]->{Val}) { |
| 114 |
$rec->{ int2col($col) } = $v; |
| 115 |
} |
| 116 |
} |
| 117 |
|
| 118 |
# add mfn only to records with data |
| 119 |
$rec->{'000'} = $mfn if ($rec); |
| 120 |
|
| 121 |
return $rec; |
| 122 |
} |
| 123 |
|
| 124 |
=head1 AUTHOR |
| 125 |
|
| 126 |
Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >> |
| 127 |
|
| 128 |
=head1 COPYRIGHT & LICENSE |
| 129 |
|
| 130 |
Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved. |
| 131 |
|
| 132 |
This program is free software; you can redistribute it and/or modify it |
| 133 |
under the same terms as Perl itself. |
| 134 |
|
| 135 |
=cut |
| 136 |
|
| 137 |
1; # End of WebPAC::Input::Excel |
Parent Directory
| 
Revision Log