3 |
use warnings; |
use warnings; |
4 |
use strict; |
use strict; |
5 |
|
|
|
use WebPAC::Input; |
|
6 |
use Spreadsheet::ParseExcel; |
use Spreadsheet::ParseExcel; |
7 |
use Spreadsheet::ParseExcel::Utility qw/int2col/; |
use Spreadsheet::ParseExcel::Utility qw/int2col/; |
8 |
|
use base qw/WebPAC::Common/; |
9 |
|
|
10 |
=head1 NAME |
=head1 NAME |
11 |
|
|
13 |
|
|
14 |
=head1 VERSION |
=head1 VERSION |
15 |
|
|
16 |
Version 0.02 |
Version 0.04 |
17 |
|
|
18 |
=cut |
=cut |
19 |
|
|
20 |
our $VERSION = '0.02'; |
our $VERSION = '0.04'; |
21 |
|
|
22 |
|
|
23 |
=head1 SYNOPSIS |
=head1 SYNOPSIS |
25 |
Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org |
Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org |
26 |
or Gnuemeric) in C<.xls> format. |
or Gnuemeric) in C<.xls> format. |
27 |
|
|
|
my $isis = new WebPAC::Input::Excel(); |
|
|
$isis->open( path => '/path/to/workbook.xls' ); |
|
|
|
|
28 |
=head1 FUNCTIONS |
=head1 FUNCTIONS |
29 |
|
|
30 |
=head2 open_db |
=head2 new |
31 |
|
|
32 |
Returns handle to database and size |
Returns handle to database and size |
33 |
|
|
34 |
my ($db,$size) = $open_db( |
my $excel = new WebPAC::Input::Excel( |
35 |
path => '/path/to/workbook.xls' |
path => '/path/to/workbook.xls' |
36 |
worksheet => 'name of sheet', |
worksheet => 'name of sheet', |
37 |
from => 42, |
from => 42, |
45 |
|
|
46 |
=cut |
=cut |
47 |
|
|
48 |
my $sheet; |
sub new { |
49 |
my ($from,$to); |
my $class = shift; |
50 |
|
my $self = {@_}; |
51 |
sub open_db { |
bless($self, $class); |
|
my $self = shift; |
|
|
|
|
|
my $arg = {@_}; |
|
52 |
|
|
53 |
my $log = $self->_get_logger(); |
my $log = $self->_get_logger(); |
54 |
|
|
55 |
$log->logdie("can't open excel file $arg->{path}: $!") unless (-r $arg->{path} && -f $arg->{path}); |
$log->logdie("can't open excel file $self->{path}: $!") unless (-r $self->{path} && -f $self->{path}); |
56 |
|
|
57 |
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($arg->{path}); |
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{path}); |
58 |
|
|
59 |
my ($size, $wanted_worksheet); |
my $sheet; |
60 |
|
my $wanted_worksheet; |
61 |
|
|
62 |
if ($wanted_worksheet = $arg->{worksheet}) { |
if ($wanted_worksheet = $self->{worksheet}) { |
63 |
my $name; |
my $name; |
64 |
do { |
do { |
65 |
$sheet = shift @{ $workbook->{Worksheet} }; |
$sheet = shift @{ $workbook->{Worksheet} }; |
66 |
$log->logdie("can't find sheet '$wanted_worksheet' in $arg->{path}\n") unless (defined($sheet)); |
$log->logdie("can't find sheet '$wanted_worksheet' in $self->{path}\n") unless (defined($sheet)); |
67 |
$name = $sheet->{Name}; |
$name = $sheet->{Name}; |
68 |
$name =~ s/\s\s+/ /g; |
$name =~ s/\s\s+/ /g; |
69 |
} until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i); |
} until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i); |
74 |
|
|
75 |
} |
} |
76 |
|
|
77 |
$from = $arg->{from} || $sheet->{MinRow}; |
$self->{sheet} = $sheet; |
78 |
$to = $arg->{to} || $sheet->{MaxRow}; |
|
79 |
|
$self->{from} ||= $sheet->{MinRow}; |
80 |
|
$self->{to} ||= $sheet->{MaxRow}; |
81 |
|
|
82 |
$size = $to - $from; |
my $size = $self->{to} - $self->{from}; |
83 |
|
$self->{size} = $size; |
84 |
|
|
85 |
$log->warn("opening Excel file '$arg->{path}', using ", |
$log->warn("opening Excel file '$self->{path}', using ", |
86 |
$wanted_worksheet ? '' : 'first ', |
$wanted_worksheet ? '' : 'first ', |
87 |
"worksheet: $sheet->{Name} [$size rows]" |
"worksheet: $sheet->{Name} [$size rows]" |
88 |
); |
); |
89 |
|
|
90 |
return (42, $size); |
$self ? return $self : return undef; |
91 |
} |
} |
92 |
|
|
93 |
=head2 fetch_rec |
=head2 fetch_rec |
107 |
|
|
108 |
my $log = $self->_get_logger(); |
my $log = $self->_get_logger(); |
109 |
|
|
110 |
|
my $sheet = $self->{sheet}; |
111 |
$log->logdie("can't find sheet hash") unless (defined($sheet)); |
$log->logdie("can't find sheet hash") unless (defined($sheet)); |
112 |
$log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet')); |
$log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet')); |
113 |
|
|
114 |
my $rec; |
my $rec; |
115 |
|
|
116 |
my $row = $from + $mfn - 1; |
my $row = $self->{from} + $mfn - 1; |
117 |
|
|
118 |
$log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol}); |
$log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol}); |
119 |
|
|
129 |
return $rec; |
return $rec; |
130 |
} |
} |
131 |
|
|
132 |
|
=head2 size |
133 |
|
|
134 |
|
Return number of records in database |
135 |
|
|
136 |
|
my $size = $isis->size; |
137 |
|
|
138 |
|
=cut |
139 |
|
|
140 |
|
sub size { |
141 |
|
my $self = shift; |
142 |
|
return $self->{size}; |
143 |
|
} |
144 |
=head1 AUTHOR |
=head1 AUTHOR |
145 |
|
|
146 |
Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >> |
Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >> |