1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
package Catmandu::Exporter::XLS;
our $VERSION = '0.10';
use namespace::clean;
use Catmandu::Sane;
use Spreadsheet::WriteExcel;
use Moo;
with 'Catmandu::TabularExporter';
has xls => (is => 'ro', lazy => 1, builder => '_build_xls');
has worksheet => (is => 'ro', lazy => 1, builder => '_build_worksheet');
has _n => (is => 'rw', default => sub {0});
sub BUILD {
my $self = shift;
my $columns = $self->columns;
my $fields = $self->fields;
if ($fields && $columns && scalar @{$fields} != scalar @{$columns}) {
Catmandu::Error->throw(
"arguments 'fields' and 'columns' have different number of elements"
);
}
}
sub _build_xls {
my $xls = Spreadsheet::WriteExcel->new($_[0]->fh);
$xls->set_properties(utf8 => 1);
$xls;
}
sub _build_worksheet {
$_[0]->xls->add_worksheet;
}
sub encoding {':raw'}
sub add {
my ($self, $data) = @_;
my $fields = $self->fields || $self->fields([sort keys %$data]);
if ($self->header && $self->_n == 0) {
for (my $i = 0; $i < @$fields; $i++) {
my $field = $self->columns ? $self->columns->[$i] : $fields->[$i];
# keep for backward compatibility (header could be a hashref)
$field = $self->header->{$field}
if ref $self->header && defined $self->header->{$field};
$self->worksheet->write_string($self->_n, $i, $field);
}
$self->{_n}++;
}
for (my $i = 0; $i < @$fields; $i++) {
$self->worksheet->write_string($self->_n, $i,
$data->{$fields->[$i]} // "");
}
$self->{_n}++;
}
sub commit {
$_[0]->xls->close;
}
=head1 NAME
Catmandu::Exporter::XLS - Package that exports XLS files
=head1 SYNOPSIS
# On the command line
$ printf "a,b,c\n1,2,3" | catmandu convert CSV to XLS --file test.xls
$ printf "a,b,c\n1,2,3" | catmandu convert CSV to XLS --file test.xls --header 0
$ printf "a,b,c\n1,2,3" | catmandu convert CSV to XLS --file test.xls --fields a,c
$ printf "a,b,c\n1,2,3" | catmandu convert CSV to XLS --file test.xls --fields a,c --columns ALPHA,CHARLIE
# Or in Perl
use Catmandu::Exporter::XLS;
my $exporter = Catmandu::Exporter::XLS->new(
file => 'test.xls',
fields => 'a,b,c',
columns => 'ALPHA,BRAVO,CHARLIE',
header => 1);
$exporter->add({a => 1, b => 2, c => 3});
$exporter->add_many($arrayref);
$exporter->commit;
printf "exported %d objects\n" , $exporter->count;
=head1 DESCRIPTION
L<Catmandu> exporter for Excel XLS files.
=head1 METHODS
See L<Catmandu::Exporter>, L<Catmandu::Addable>, L<Catmandu::Fixable>,
L<Catmandu::Counter>, and L<Catmandu::Logger> for a full list of methods.
=head1 CONFIGURATION
In addition to the configuration provided by L<Catmandu::Exporter> (C<file>,
C<fh>, etc.) the importer can be configured with the following parameters:
=over
=item header
Include a header line with column names, if set to 1 (default).
=item fields
List of fields to be used as columns, given as array reference or
comma-separated string
=item columns
List of custom column names, given as array reference or comma-separated
list.
=item collect_fields
This option will first read the complete stream to create a complete list
of fields to export. When this option is not set, only the fields of the first
record (or the ones provided in the C<fields> option will be exported).
=back
=head1 SEE ALSO
L<Catmandu::Exporter::CSV>, L<Catmandu::Exporter::XLSX>.
=cut
1;
|
