1 |
dpavlin |
498 |
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 |
dpavlin |
524 |
Version 0.02 |
17 |
dpavlin |
498 |
|
18 |
|
|
=cut |
19 |
|
|
|
20 |
dpavlin |
524 |
our $VERSION = '0.02'; |
21 |
dpavlin |
498 |
|
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 |
dpavlin |
524 |
from => 42, |
41 |
|
|
to => 9999, |
42 |
dpavlin |
498 |
} |
43 |
|
|
|
44 |
|
|
C<worksheet> is case and white-space insensitive name of worksheet in Excel |
45 |
|
|
file to use. If not specified, it will use first worksheet in file. |
46 |
|
|
|
47 |
dpavlin |
524 |
C<from> and C<to> specify row numbers to start and finish import. |
48 |
|
|
|
49 |
dpavlin |
498 |
=cut |
50 |
|
|
|
51 |
|
|
my $sheet; |
52 |
dpavlin |
524 |
my ($from,$to); |
53 |
dpavlin |
498 |
|
54 |
|
|
sub open_db { |
55 |
|
|
my $self = shift; |
56 |
|
|
|
57 |
|
|
my $arg = {@_}; |
58 |
|
|
|
59 |
|
|
my $log = $self->_get_logger(); |
60 |
|
|
|
61 |
|
|
$log->logdie("can't open excel file $arg->{path}: $!") unless (-r $arg->{path} && -f $arg->{path}); |
62 |
|
|
|
63 |
|
|
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($arg->{path}); |
64 |
|
|
|
65 |
|
|
my ($size, $wanted_worksheet); |
66 |
|
|
|
67 |
|
|
if ($wanted_worksheet = $arg->{worksheet}) { |
68 |
|
|
my $name; |
69 |
|
|
do { |
70 |
|
|
$sheet = shift @{ $workbook->{Worksheet} }; |
71 |
dpavlin |
524 |
$log->logdie("can't find sheet '$wanted_worksheet' in $arg->{path}\n") unless (defined($sheet)); |
72 |
dpavlin |
498 |
$name = $sheet->{Name}; |
73 |
|
|
$name =~ s/\s\s+/ /g; |
74 |
|
|
} until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i); |
75 |
|
|
|
76 |
|
|
} else { |
77 |
|
|
|
78 |
|
|
$sheet = shift @{ $workbook->{Worksheet} }; |
79 |
|
|
|
80 |
|
|
} |
81 |
|
|
|
82 |
dpavlin |
524 |
$from = $arg->{from} || $sheet->{MinRow}; |
83 |
|
|
$to = $arg->{to} || $sheet->{MaxRow}; |
84 |
dpavlin |
498 |
|
85 |
dpavlin |
524 |
$size = $to - $from; |
86 |
|
|
|
87 |
dpavlin |
498 |
$log->warn("opening Excel file '$arg->{path}', using ", |
88 |
|
|
$wanted_worksheet ? '' : 'first ', |
89 |
|
|
"worksheet: $sheet->{Name} [$size rows]" |
90 |
|
|
); |
91 |
|
|
|
92 |
|
|
return (42, $size); |
93 |
|
|
} |
94 |
|
|
|
95 |
|
|
=head2 fetch_rec |
96 |
|
|
|
97 |
|
|
Return record with ID C<$mfn> from database |
98 |
|
|
|
99 |
dpavlin |
652 |
my $rec = $self->fetch_rec( $mfn ); |
100 |
dpavlin |
498 |
|
101 |
|
|
} |
102 |
|
|
|
103 |
|
|
=cut |
104 |
|
|
|
105 |
|
|
sub fetch_rec { |
106 |
|
|
my $self = shift; |
107 |
|
|
|
108 |
dpavlin |
652 |
my $mfn = shift; |
109 |
dpavlin |
498 |
|
110 |
|
|
my $log = $self->_get_logger(); |
111 |
|
|
|
112 |
|
|
$log->logdie("can't find sheet hash") unless (defined($sheet)); |
113 |
|
|
$log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet')); |
114 |
|
|
|
115 |
|
|
my $rec; |
116 |
|
|
|
117 |
dpavlin |
524 |
my $row = $from + $mfn - 1; |
118 |
dpavlin |
498 |
|
119 |
|
|
$log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol}); |
120 |
|
|
|
121 |
|
|
foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) { |
122 |
|
|
if (my $v = $sheet->{Cells}->[$row]->[$col]->{Val}) { |
123 |
|
|
$rec->{ int2col($col) } = $v; |
124 |
|
|
} |
125 |
|
|
} |
126 |
|
|
|
127 |
|
|
# add mfn only to records with data |
128 |
dpavlin |
521 |
$rec->{'000'} = [ $mfn ] if ($rec); |
129 |
dpavlin |
498 |
|
130 |
|
|
return $rec; |
131 |
|
|
} |
132 |
|
|
|
133 |
|
|
=head1 AUTHOR |
134 |
|
|
|
135 |
|
|
Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >> |
136 |
|
|
|
137 |
|
|
=head1 COPYRIGHT & LICENSE |
138 |
|
|
|
139 |
|
|
Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved. |
140 |
|
|
|
141 |
|
|
This program is free software; you can redistribute it and/or modify it |
142 |
|
|
under the same terms as Perl itself. |
143 |
|
|
|
144 |
|
|
=cut |
145 |
|
|
|
146 |
|
|
1; # End of WebPAC::Input::Excel |