| 1 |
package WebPAC::Input::Excel; |
| 2 |
|
| 3 |
use warnings; |
| 4 |
use strict; |
| 5 |
|
| 6 |
use Spreadsheet::ParseExcel; |
| 7 |
use Spreadsheet::ParseExcel::Utility qw/int2col/; |
| 8 |
use base qw/WebPAC::Common/; |
| 9 |
|
| 10 |
=head1 NAME |
| 11 |
|
| 12 |
WebPAC::Input::Excel - support for Microsoft Excel and compatibile files |
| 13 |
|
| 14 |
=cut |
| 15 |
|
| 16 |
our $VERSION = '0.05'; |
| 17 |
|
| 18 |
|
| 19 |
=head1 SYNOPSIS |
| 20 |
|
| 21 |
Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org |
| 22 |
or Gnuemeric) in C<.xls> format. |
| 23 |
|
| 24 |
=head1 FUNCTIONS |
| 25 |
|
| 26 |
=head2 new |
| 27 |
|
| 28 |
Returns handle to database and size |
| 29 |
|
| 30 |
my $excel = new WebPAC::Input::Excel( |
| 31 |
path => '/path/to/workbook.xls' |
| 32 |
worksheet => 'name of sheet', |
| 33 |
from => 42, |
| 34 |
to => 9999, |
| 35 |
} |
| 36 |
|
| 37 |
C<worksheet> is case and white-space insensitive name of worksheet in Excel |
| 38 |
file to use. If not specified, name of input is used. If none of those |
| 39 |
methods returned sheet, first worksheet in file is used instead. |
| 40 |
|
| 41 |
C<from> and C<to> specify row numbers to start and finish import. |
| 42 |
|
| 43 |
=cut |
| 44 |
|
| 45 |
sub new { |
| 46 |
my $class = shift; |
| 47 |
my $self = {@_}; |
| 48 |
bless($self, $class); |
| 49 |
|
| 50 |
my $log = $self->_get_logger(); |
| 51 |
|
| 52 |
$log->logdie("can't open excel file $self->{path}: $!") unless (-r $self->{path} && -f $self->{path}); |
| 53 |
|
| 54 |
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{path}); |
| 55 |
|
| 56 |
my $sheet; |
| 57 |
my $wanted_worksheet = $self->{worksheet} || $self->{name}; |
| 58 |
|
| 59 |
if ($wanted_worksheet) { |
| 60 |
my $name; |
| 61 |
do { |
| 62 |
$sheet = shift @{ $workbook->{Worksheet} }; |
| 63 |
$log->logdie("can't find sheet '$wanted_worksheet' in $self->{path}\n") unless (defined($sheet)); |
| 64 |
$name = $sheet->{Name}; |
| 65 |
$name =~ s/\s\s+/ /g; |
| 66 |
} until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i); |
| 67 |
|
| 68 |
} |
| 69 |
|
| 70 |
$sheet ||= shift @{ $workbook->{Worksheet} }; |
| 71 |
|
| 72 |
$self->{sheet} = $sheet; |
| 73 |
|
| 74 |
$self->{from} ||= $sheet->{MinRow}; |
| 75 |
$self->{to} ||= $sheet->{MaxRow}; |
| 76 |
|
| 77 |
my $size = $self->{to} - $self->{from}; |
| 78 |
$self->{size} = $size; |
| 79 |
|
| 80 |
$log->warn("opening Excel file '$self->{path}', using ", |
| 81 |
$wanted_worksheet ? '' : 'first ', |
| 82 |
"worksheet: $sheet->{Name} [$size rows]" |
| 83 |
); |
| 84 |
|
| 85 |
$self ? return $self : return undef; |
| 86 |
} |
| 87 |
|
| 88 |
=head2 fetch_rec |
| 89 |
|
| 90 |
Return record with ID C<$mfn> from database |
| 91 |
|
| 92 |
my $rec = $self->fetch_rec( $mfn ); |
| 93 |
|
| 94 |
Columns are named C<A>, C<B> and so on... |
| 95 |
|
| 96 |
=cut |
| 97 |
|
| 98 |
sub fetch_rec { |
| 99 |
my $self = shift; |
| 100 |
|
| 101 |
my $mfn = shift; |
| 102 |
|
| 103 |
my $log = $self->_get_logger(); |
| 104 |
|
| 105 |
my $sheet = $self->{sheet}; |
| 106 |
$log->logdie("can't find sheet hash") unless (defined($sheet)); |
| 107 |
$log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet')); |
| 108 |
|
| 109 |
my $rec; |
| 110 |
|
| 111 |
my $row = $self->{from} + $mfn - 1; |
| 112 |
|
| 113 |
$log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol}); |
| 114 |
|
| 115 |
foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) { |
| 116 |
if (my $v = $sheet->{Cells}->[$row]->[$col]->{_Value}) { ## XXX _Value = formatted | Val = unformated ! |
| 117 |
$rec->{ int2col($col) } = $v; |
| 118 |
} |
| 119 |
} |
| 120 |
|
| 121 |
# add mfn only to records with data |
| 122 |
$rec->{'000'} = [ $mfn ] if ($rec); |
| 123 |
|
| 124 |
return $rec; |
| 125 |
} |
| 126 |
|
| 127 |
=head2 size |
| 128 |
|
| 129 |
Return number of records in database |
| 130 |
|
| 131 |
my $size = $isis->size; |
| 132 |
|
| 133 |
=cut |
| 134 |
|
| 135 |
sub size { |
| 136 |
my $self = shift; |
| 137 |
return $self->{size}; |
| 138 |
} |
| 139 |
|
| 140 |
sub default_encoding { 'UTF-16' } |
| 141 |
|
| 142 |
our @labels; |
| 143 |
our @names; |
| 144 |
|
| 145 |
sub normalize { |
| 146 |
my ($self,$mfn) = @_; |
| 147 |
|
| 148 |
my $log = $self->_get_logger(); |
| 149 |
|
| 150 |
my $sheet = $self->{sheet}; |
| 151 |
|
| 152 |
if ( ! @labels ) { |
| 153 |
push @labels, $sheet->{Cells}->[0]->[$_]->{_Value} |
| 154 |
foreach ( $sheet->{MinCol} ... $sheet->{MaxCol} ) |
| 155 |
; |
| 156 |
@names = map { s{\W+}{_}; $_ } @labels; |
| 157 |
$log->loginfo("column labels:", @labels, @names); |
| 158 |
} |
| 159 |
|
| 160 |
my $row = $self->{from} + $mfn - 1; |
| 161 |
|
| 162 |
|
| 163 |
} |
| 164 |
|
| 165 |
=head1 AUTHOR |
| 166 |
|
| 167 |
Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >> |
| 168 |
|
| 169 |
=head1 COPYRIGHT & LICENSE |
| 170 |
|
| 171 |
Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved. |
| 172 |
|
| 173 |
This program is free software; you can redistribute it and/or modify it |
| 174 |
under the same terms as Perl itself. |
| 175 |
|
| 176 |
=cut |
| 177 |
|
| 178 |
1; # End of WebPAC::Input::Excel |
Parent Directory
| 
Revision Log