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 WebPAC::Path;
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;
        }

        mk_base_path( $self->path );

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