WebPAC/Input/Excel.pm

package WebPAC::Input::Excel;

use warnings;
use strict;

use WebPAC::Input;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw/int2col/;

=head1 NAME

WebPAC::Input::Excel - support for Microsoft Excell and compatibile files

=head1 VERSION

Version 0.02

=cut

our $VERSION = '0.02';


=head1 SYNOPSIS

Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org
or Gnuemeric) in C<.xls> format.

 my $isis = new WebPAC::Input::Excel();
 $isis->open( path => '/path/to/workbook.xls' );

=head1 FUNCTIONS

=head2 open_db

Returns handle to database and size

  my ($db,$size) = $open_db(
        path => '/path/to/workbook.xls'
        worksheet => 'name of sheet',
        from => 42,
        to => 9999,
  }

C<worksheet> is case and white-space insensitive name of worksheet in Excel
file to use. If not specified, it will use first worksheet in file.

C<from> and C<to> specify row numbers to start and finish import.

=cut

my $sheet;
my ($from,$to);

sub open_db {
        my $self = shift;

        my $arg = {@_};

        my $log = $self->_get_logger();

        $log->logdie("can't open excel file $arg->{path}: $!") unless (-r $arg->{path} && -f $arg->{path});

        my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($arg->{path});

        my ($size, $wanted_worksheet);

        if ($wanted_worksheet = $arg->{worksheet}) {
                my $name;
                do {
                        $sheet = shift @{ $workbook->{Worksheet} };
                        $log->logdie("can't find sheet '$wanted_worksheet' in $arg->{path}\n") unless (defined($sheet));
                        $name = $sheet->{Name};
                        $name =~ s/\s\s+/ /g;
                } until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);

        } else {

                $sheet = shift @{ $workbook->{Worksheet} };
        
        }

        $from = $arg->{from} || $sheet->{MinRow};
        $to = $arg->{to} || $sheet->{MaxRow};

        $size = $to - $from;

        $log->warn("opening Excel file '$arg->{path}', using ",
                $wanted_worksheet ? '' : 'first ',
                "worksheet: $sheet->{Name} [$size rows]"
        );

        return (42, $size);
}

=head2 fetch_rec

Return record with ID C<$mfn> from database

  my $rec = $self->fetch_rec( $db, $mfn );

}

=cut

sub fetch_rec {
        my $self = shift;

        my (undef, $mfn) = @_;

        my $log = $self->_get_logger();

        $log->logdie("can't find sheet hash") unless (defined($sheet));
        $log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet'));

        my $rec;

        my $row = $from + $mfn - 1;

        $log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});

        foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
                if (my $v = $sheet->{Cells}->[$row]->[$col]->{Val}) {
                        $rec->{ int2col($col) } = $v;
                }
        }

        # add mfn only to records with data
        $rec->{'000'} = [ $mfn ] if ($rec);
        
        return $rec;
}

=head1 AUTHOR

Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>

=head1 COPYRIGHT & LICENSE

Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved.

This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=cut

1; # End of WebPAC::Input::Excel
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			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	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