WebPAC/Output/Excel.pm

package WebPAC::Output::Excel;

use warnings;
use strict;

use WebPAC::Common;
use base qw/WebPAC::Common WebPAC::Output Class::Accessor/;
__PACKAGE__->mk_accessors(qw(
path
filter

workbook
worksheet
line
));

use Spreadsheet::WriteExcel;
use File::Slurp;
use Encode qw/decode/;

=head1 NAME

WebPAC::Output::Excel - Create binary Excel file

=cut

=head1 SYNOPSIS

Create Excel output for export into other systems from C<csv()> constructs
with columns named C<A>..C<Z> (or whatever L<Spreadsheet::WriteExcel> supports)

=head1 FUNCTIONS

=head2 new

 my $out = new WebPAC::Output::Excel({
        path => '/path/to/file.xls',
        filter => 'csv',
 });

Options are:

=over 4

=item path

path to Excel file

=item filter

select name of variable from C<< to('csv','A',...) >> constructs used by
L<WebPAC::Normalize>

=back

=head2 init

  $out->init;

=cut

sub init {
        my $self = shift;
        my $log = $self->_get_logger;

        if ( ! $self->path ) {
                $log->logwarn("need path for ", __PACKAGE__);
                return 0;
        }

        $self->workbook( Spreadsheet::WriteExcel->new( $self->path ) ) ||
                $log->logdie("can't open ", $self->path,": $!");

        $self->worksheet( $self->workbook->add_worksheet() ) ||
                $log->logdie("can't add_worksheet");

        $self->line( 1 );

        $self->filter( 'csv' ) unless $self->filter;

        return 1;
}


=head2 add

Adds one entry to database.

  $out->add( 42, $ds );

Returns number of columns added

=cut

sub add {
        my $self = shift;

        my ( $id, $ds ) = @_;

        my $log = $self->_get_logger;
        $log->logdie("need id") unless defined $id;
        $log->logdie("need ds") unless $ds;

        $log->debug("id: $id ds = ",sub { dump($ds) });

        my $l = $self->line;

        my $cols = 0;

        my $hash = $self->ds_to_hash( $ds, $self->filter, disable_key_mungle => 1, single_values => 1 );
        $log->debug("hash from ",$self->filter," = ", sub { dump( $hash ) });

        my $worksheet = $self->worksheet || $log->logconfess("no worksheet?");
        foreach my $col ( sort grep { /^[A-Z]/ } keys %$hash ) {
                # FIXME internal WebPAC encoding is ISO-8859-2
                my $val = decode('ISO-8859-2', $hash->{$col});
                $log->debug("$col$l|$val");
                $worksheet->write( $col . $l , $val );
                $cols++;
        }

        $self->line( $l + 1 ) if $cols;

        return $cols;
}

=head2 finish

 $out->finish;

=cut

sub finish {
        my $self = shift;

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

        my $path = $self->path;

        $self->workbook->close() ||
                $log->logdie("can't close Excel file $path: $!");

        $log->info("created $path ", -s $path, " bytes with ", $self->line, " rows");

        return $self->line;
}

=head1 AUTHOR

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

=head1 COPYRIGHT & LICENSE

Copyright 2007 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::Output::Excel
1	dpavlin	1068	package WebPAC::Output::Excel;
2
3			use warnings;
4			use strict;
5
6			use WebPAC::Common;
7			use base qw/WebPAC::Common WebPAC::Output Class::Accessor/;
8			__PACKAGE__->mk_accessors(qw(
9			path
10			filter
11
12			workbook
13			worksheet
14			line
15			));
16
17			use Spreadsheet::WriteExcel;
18			use File::Slurp;
19	dpavlin	1071	use Encode qw/decode/;
20	dpavlin	1068
21			=head1 NAME
22
23			WebPAC::Output::Excel - Create binary Excel file
24
25			=cut
26
27			=head1 SYNOPSIS
28
29			Create Excel output for export into other systems from C<csv()> constructs
30			with columns named C<A>..C<Z> (or whatever L<Spreadsheet::WriteExcel> supports)
31
32			=head1 FUNCTIONS
33
34			=head2 new
35
36			my $out = new WebPAC::Output::Excel({
37			path => '/path/to/file.xls',
38			filter => 'csv',
39			});
40
41			Options are:
42
43			=over 4
44
45			=item path
46
47			path to Excel file
48
49			=item filter
50
51			select name of variable from C<< to('csv','A',...) >> constructs used by
52			L<WebPAC::Normalize>
53
54			=back
55
56			=head2 init
57
58			$out->init;
59
60			=cut
61
62			sub init {
63			my $self = shift;
64			my $log = $self->_get_logger;
65
66			if ( ! $self->path ) {
67			$log->logwarn("need path for ", __PACKAGE__);
68			return 0;
69			}
70
71			$self->workbook( Spreadsheet::WriteExcel->new( $self->path ) ) \|\|
72			$log->logdie("can't open ", $self->path,": $!");
73
74			$self->worksheet( $self->workbook->add_worksheet() ) \|\|
75			$log->logdie("can't add_worksheet");
76
77			$self->line( 1 );
78
79			$self->filter( 'csv' ) unless $self->filter;
80
81			return 1;
82			}
83
84
85			=head2 add
86
87			Adds one entry to database.
88
89			$out->add( 42, $ds );
90
91			Returns number of columns added
92
93			=cut
94
95			sub add {
96			my $self = shift;
97
98			my ( $id, $ds ) = @_;
99
100			my $log = $self->_get_logger;
101			$log->logdie("need id") unless defined $id;
102			$log->logdie("need ds") unless $ds;
103
104			$log->debug("id: $id ds = ",sub { dump($ds) });
105
106			my $l = $self->line;
107
108			my $cols = 0;
109
110	dpavlin	1070	my $hash = $self->ds_to_hash( $ds, $self->filter, disable_key_mungle => 1, single_values => 1 );
111	dpavlin	1068	$log->debug("hash from ",$self->filter," = ", sub { dump( $hash ) });
112
113			my $worksheet = $self->worksheet \|\| $log->logconfess("no worksheet?");
114			foreach my $col ( sort grep { /^[A-Z]/ } keys %$hash ) {
115	dpavlin	1071	# FIXME internal WebPAC encoding is ISO-8859-2
116			my $val = decode('ISO-8859-2', $hash->{$col});
117	dpavlin	1068	$log->debug("$col$l\|$val");
118			$worksheet->write( $col . $l , $val );
119			$cols++;
120			}
121
122			$self->line( $l + 1 ) if $cols;
123
124			return $cols;
125			}
126
127			=head2 finish
128
129			$out->finish;
130
131			=cut
132
133			sub finish {
134			my $self = shift;
135
136			my $log = $self->_get_logger();
137
138	dpavlin	1070	my $path = $self->path;
139
140	dpavlin	1068	$self->workbook->close() \|\|
141	dpavlin	1070	$log->logdie("can't close Excel file $path: $!");
142	dpavlin	1068
143	dpavlin	1070	$log->info("created $path ", -s $path, " bytes with ", $self->line, " rows");
144
145			return $self->line;
146	dpavlin	1068	}
147
148			=head1 AUTHOR
149
150			Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>
151
152			=head1 COPYRIGHT & LICENSE
153
154			Copyright 2007 Dobrica Pavlinusic, All Rights Reserved.
155
156			This program is free software; you can redistribute it and/or modify it
157			under the same terms as Perl itself.
158
159			=cut
160
161			1; # End of WebPAC::Output::Excel