/[webpac2]/trunk/lib/WebPAC/Input/Excel.pm
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /trunk/lib/WebPAC/Input/Excel.pm

Parent Directory Parent Directory | Revision Log Revision Log


Revision 524 - (show annotations)
Sun May 21 19:38:56 2006 UTC (17 years, 10 months ago) by dpavlin
File size: 3008 byte(s)
added from and to parametars for start and end row to import

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.02
17
18 =cut
19
20 our $VERSION = '0.02';
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 from => 42,
41 to => 9999,
42 }
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 C<from> and C<to> specify row numbers to start and finish import.
48
49 =cut
50
51 my $sheet;
52 my ($from,$to);
53
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 $log->logdie("can't find sheet '$wanted_worksheet' in $arg->{path}\n") unless (defined($sheet));
72 $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 $from = $arg->{from} || $sheet->{MinRow};
83 $to = $arg->{to} || $sheet->{MaxRow};
84
85 $size = $to - $from;
86
87 $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 my $rec = $self->fetch_rec( $db, $mfn );
100
101 }
102
103 =cut
104
105 sub fetch_rec {
106 my $self = shift;
107
108 my (undef, $mfn) = @_;
109
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 my $row = $from + $mfn - 1;
118
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 $rec->{'000'} = [ $mfn ] if ($rec);
129
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

  ViewVC Help
Powered by ViewVC 1.1.26