WebPAC/Input/Excel.pm

package WebPAC::Input::Excel;

use warnings;
use strict;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw/int2col/;
use base qw/WebPAC::Common/;

=head1 NAME

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

=head1 VERSION

Version 0.04

=cut

our $VERSION = '0.04';


=head1 SYNOPSIS

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

=head1 FUNCTIONS

=head2 new

Returns handle to database and size

  my $excel = new WebPAC::Input::Excel(
        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

sub new {
        my $class = shift;
        my $self = {@_};
        bless($self, $class);

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

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

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

        my $sheet;
        my $wanted_worksheet;

        if ($wanted_worksheet = $self->{worksheet}) {
                my $name;
                do {
                        $sheet = shift @{ $workbook->{Worksheet} };
                        $log->logdie("can't find sheet '$wanted_worksheet' in $self->{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} };
        
        }

        $self->{sheet} = $sheet;

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

        my $size = $self->{to} - $self->{from};
        $self->{size} = $size;

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

        $self ? return $self : return undef;
}

=head2 fetch_rec

Return record with ID C<$mfn> from database

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

}

=cut

sub fetch_rec {
        my $self = shift;

        my $mfn = shift;

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

        my $sheet = $self->{sheet};
        $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 = $self->{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;
}

=head2 size

Return number of records in database

  my $size = $isis->size;

=cut

sub size {
        my $self = shift;
        return $self->{size};
}
=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	package WebPAC::Input::Excel;
2
3	use warnings;
4	use strict;
5
6	use Spreadsheet::ParseExcel;
7	use Spreadsheet::ParseExcel::Utility qw/int2col/;
8	use base qw/WebPAC::Common/;
9
10	=head1 NAME
11
12	WebPAC::Input::Excel - support for Microsoft Excell and compatibile files
13
14	=head1 VERSION
15
16	Version 0.04
17
18	=cut
19
20	our $VERSION = '0.04';
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	=head1 FUNCTIONS
29
30	=head2 new
31
32	Returns handle to database and size
33
34	my $excel = new WebPAC::Input::Excel(
35	path => '/path/to/workbook.xls'
36	worksheet => 'name of sheet',
37	from => 42,
38	to => 9999,
39	}
40
41	C<worksheet> is case and white-space insensitive name of worksheet in Excel
42	file to use. If not specified, it will use first worksheet in file.
43
44	C<from> and C<to> specify row numbers to start and finish import.
45
46	=cut
47
48	sub new {
49	my $class = shift;
50	my $self = {@_};
51	bless($self, $class);
52
53	my $log = $self->_get_logger();
54
55	$log->logdie("can't open excel file $self->{path}: $!") unless (-r $self->{path} && -f $self->{path});
56
57	my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{path});
58
59	my $sheet;
60	my $wanted_worksheet;
61
62	if ($wanted_worksheet = $self->{worksheet}) {
63	my $name;
64	do {
65	$sheet = shift @{ $workbook->{Worksheet} };
66	$log->logdie("can't find sheet '$wanted_worksheet' in $self->{path}\n") unless (defined($sheet));
67	$name = $sheet->{Name};
68	$name =~ s/\s\s+/ /g;
69	} until ($name =~ m/^\s\Q$wanted_worksheet\E\s$/i);
70
71	} else {
72
73	$sheet = shift @{ $workbook->{Worksheet} };
74
75	}
76
77	$self->{sheet} = $sheet;
78
79	$self->{from} \|\|= $sheet->{MinRow};
80	$self->{to} \|\|= $sheet->{MaxRow};
81
82	my $size = $self->{to} - $self->{from};
83	$self->{size} = $size;
84
85	$log->warn("opening Excel file '$self->{path}', using ",
86	$wanted_worksheet ? '' : 'first ',
87	"worksheet: $sheet->{Name} [$size rows]"
88	);
89
90	$self ? return $self : return undef;
91	}
92
93	=head2 fetch_rec
94
95	Return record with ID C<$mfn> from database
96
97	my $rec = $self->fetch_rec( $mfn );
98
99	}
100
101	=cut
102
103	sub fetch_rec {
104	my $self = shift;
105
106	my $mfn = shift;
107
108	my $log = $self->_get_logger();
109
110	my $sheet = $self->{sheet};
111	$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'));
113
114	my $rec;
115
116	my $row = $self->{from} + $mfn - 1;
117
118	$log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});
119
120	foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
121	if (my $v = $sheet->{Cells}->[$row]->[$col]->{Val}) {
122	$rec->{ int2col($col) } = $v;
123	}
124	}
125
126	# add mfn only to records with data
127	$rec->{'000'} = [ $mfn ] if ($rec);
128
129	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
145
146	Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>
147
148	=head1 COPYRIGHT & LICENSE
149
150	Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved.
151
152	This program is free software; you can redistribute it and/or modify it
153	under the same terms as Perl itself.
154
155	=cut
156
157	1; # End of WebPAC::Input::Excel