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.01

=cut

our $VERSION = '0.01';


=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',
  }

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.

=cut

my $sheet;

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} };
                        $name = $sheet->{Name};
                        $name =~ s/\s\s+/ /g;
                } until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);

        } else {

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

        $size = $sheet->{MaxRow} - $sheet->{MinRow};

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