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 |