line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package Spreadsheet::WriteExcel::Simple::Tabs; |
2
|
1
|
|
|
1
|
|
25782
|
use strict; |
|
1
|
|
|
|
|
2
|
|
|
1
|
|
|
|
|
42
|
|
3
|
1
|
|
|
1
|
|
5
|
use warnings; |
|
1
|
|
|
|
|
2
|
|
|
1
|
|
|
|
|
33
|
|
4
|
1
|
|
|
1
|
|
955
|
use IO::Scalar qw{}; |
|
1
|
|
|
|
|
18635
|
|
|
1
|
|
|
|
|
25
|
|
5
|
1
|
|
|
1
|
|
2047
|
use Spreadsheet::WriteExcel qw{}; |
|
1
|
|
|
|
|
98745
|
|
|
1
|
|
|
|
|
1047
|
|
6
|
|
|
|
|
|
|
|
7
|
|
|
|
|
|
|
our $VERSION='0.10'; |
8
|
|
|
|
|
|
|
our $PACKAGE=__PACKAGE__; |
9
|
|
|
|
|
|
|
|
10
|
|
|
|
|
|
|
=head1 NAME |
11
|
|
|
|
|
|
|
|
12
|
|
|
|
|
|
|
Spreadsheet::WriteExcel::Simple::Tabs - Simple Interface to the Spreadsheet::WriteExcel Package |
13
|
|
|
|
|
|
|
|
14
|
|
|
|
|
|
|
=head1 SYNOPSIS |
15
|
|
|
|
|
|
|
|
16
|
|
|
|
|
|
|
use Spreadsheet::WriteExcel::Simple::Tabs; |
17
|
|
|
|
|
|
|
my $ss=Spreadsheet::WriteExcel::Simple::Tabs->new; |
18
|
|
|
|
|
|
|
my @data=( |
19
|
|
|
|
|
|
|
["Heading1", "Heading2"], |
20
|
|
|
|
|
|
|
["data1", "data2" ], |
21
|
|
|
|
|
|
|
["data3", "data4" ], |
22
|
|
|
|
|
|
|
); |
23
|
|
|
|
|
|
|
$ss->add(Tab1=>\@data, Tab2=>\@data); |
24
|
|
|
|
|
|
|
print $ss->header(filename=>"filename.xls"), $ss->content; |
25
|
|
|
|
|
|
|
|
26
|
|
|
|
|
|
|
=head1 DESCRIPTION |
27
|
|
|
|
|
|
|
|
28
|
|
|
|
|
|
|
This is a simple wrapper around Spreadsheet::WriteExcel that creates tabs for data. It is ment to be simple not full featured. I use this package to export data from the L sqlarrayarrayname method which is an array of array references where the first array is the column headings. |
29
|
|
|
|
|
|
|
|
30
|
|
|
|
|
|
|
=head1 USAGE |
31
|
|
|
|
|
|
|
|
32
|
|
|
|
|
|
|
=head1 CONSTRUCTOR |
33
|
|
|
|
|
|
|
|
34
|
|
|
|
|
|
|
=head2 new |
35
|
|
|
|
|
|
|
|
36
|
|
|
|
|
|
|
=cut |
37
|
|
|
|
|
|
|
|
38
|
|
|
|
|
|
|
sub new { |
39
|
1
|
|
|
1
|
1
|
13
|
my $this = shift(); |
40
|
1
|
|
33
|
|
|
7
|
my $class = ref($this) || $this; |
41
|
1
|
|
|
|
|
3
|
my $self = {}; |
42
|
1
|
|
|
|
|
3
|
bless $self, $class; |
43
|
1
|
|
|
|
|
4
|
$self->initialize(@_); |
44
|
1
|
|
|
|
|
3
|
return $self; |
45
|
|
|
|
|
|
|
} |
46
|
|
|
|
|
|
|
|
47
|
|
|
|
|
|
|
=head2 initialize |
48
|
|
|
|
|
|
|
|
49
|
|
|
|
|
|
|
=cut |
50
|
|
|
|
|
|
|
|
51
|
|
|
|
|
|
|
sub initialize { |
52
|
1
|
|
|
1
|
1
|
2
|
my $self=shift; |
53
|
1
|
|
|
|
|
5
|
%$self=@_; |
54
|
|
|
|
|
|
|
} |
55
|
|
|
|
|
|
|
|
56
|
|
|
|
|
|
|
=head2 book |
57
|
|
|
|
|
|
|
|
58
|
|
|
|
|
|
|
Returns the workbook object |
59
|
|
|
|
|
|
|
|
60
|
|
|
|
|
|
|
=cut |
61
|
|
|
|
|
|
|
|
62
|
|
|
|
|
|
|
sub book { |
63
|
20
|
|
|
20
|
1
|
418
|
my $self=shift; |
64
|
|
|
|
|
|
|
#Thanks to Tony Bowden for the IO::Scalar stuff |
65
|
20
|
100
|
|
|
|
57
|
unless (defined($self->{"book"})) { |
66
|
1
|
|
|
|
|
18
|
$self->{"book"}=Spreadsheet::WriteExcel->new( |
67
|
|
|
|
|
|
|
IO::Scalar->new_tie(\($self->{"content"})) |
68
|
|
|
|
|
|
|
); |
69
|
|
|
|
|
|
|
} |
70
|
20
|
|
|
|
|
13699
|
return $self->{"book"}; |
71
|
|
|
|
|
|
|
} |
72
|
|
|
|
|
|
|
|
73
|
|
|
|
|
|
|
=head2 add |
74
|
|
|
|
|
|
|
|
75
|
|
|
|
|
|
|
$ss->add("Tab Name", \@data); |
76
|
|
|
|
|
|
|
$ss->add(Tab1=>\@data, Tab2=>\@data); |
77
|
|
|
|
|
|
|
|
78
|
|
|
|
|
|
|
=cut |
79
|
|
|
|
|
|
|
|
80
|
|
|
|
|
|
|
sub add { |
81
|
1
|
|
|
1
|
1
|
332
|
my $self=shift; |
82
|
1
|
50
|
|
|
|
7
|
die("Error: The $PACKAGE->add method requires an even number of arguments") |
83
|
|
|
|
|
|
|
if scalar(@_) % 2; |
84
|
1
|
|
|
|
|
5
|
while (@_ > 0) { |
85
|
4
|
|
|
|
|
6
|
my $tab=shift; |
86
|
4
|
|
|
|
|
5
|
my $data=shift; |
87
|
4
|
50
|
|
|
|
13
|
die(sprintf(qq{Error: Expecting data to be an array reference but got "%s" in $PACKAGE->add}, ref($data))) |
88
|
|
|
|
|
|
|
unless ref($data) eq "ARRAY"; |
89
|
4
|
|
|
|
|
11
|
$self->_add1($tab=>$data); |
90
|
|
|
|
|
|
|
} |
91
|
1
|
|
|
|
|
3
|
return $self; |
92
|
|
|
|
|
|
|
} |
93
|
|
|
|
|
|
|
|
94
|
|
|
|
|
|
|
sub _add1 { |
95
|
4
|
|
|
4
|
|
5
|
my $self=shift; |
96
|
4
|
|
|
|
|
5
|
my $tab=shift; |
97
|
4
|
|
|
|
|
8
|
$tab=~s/[\[\]:\*\?\/\\]/ /g; #Invalid character []:*?/\ in worksheet name |
98
|
4
|
50
|
|
|
|
9
|
$tab=substr($tab,0,31) if length($tab) > 31; #must be <= 31 chars |
99
|
4
|
|
|
|
|
35
|
my $data=shift; |
100
|
4
|
|
|
|
|
7
|
my $sheet=$self->book->add_worksheet($tab); |
101
|
4
|
|
|
|
|
1542
|
my %format=$self->default; $format{"num_format"}='mm/dd/yyyy hh:mm:ss'; |
|
4
|
|
|
|
|
10
|
|
102
|
4
|
|
|
|
|
9
|
my $format_datetime=$self->book->add_format(%format); |
103
|
|
|
|
|
|
|
my $subref=sub { |
104
|
0
|
|
|
0
|
|
0
|
my $sheet=shift; |
105
|
0
|
|
|
|
|
0
|
my @args=@_; |
106
|
0
|
|
|
|
|
0
|
my ($m,$d,$y,$h,$n,$s)=split(/[\/ :]/, $args[2]); |
107
|
0
|
|
|
|
|
0
|
$args[2]=sprintf("%4d-%02d-%02dT%02d:%02d:%02d", $y, $m, $d, $h, $n, $s); |
108
|
0
|
|
|
|
|
0
|
$args[3]=$format_datetime; |
109
|
0
|
|
|
|
|
0
|
return $sheet->write_date_time(@args); |
110
|
4
|
|
|
|
|
753
|
}; |
111
|
4
|
|
|
0
|
|
31
|
$sheet->add_write_handler(qr/^\d{16,}$/, sub{shift->write_string(@_)}); #Long Integer Support - RT61869 |
|
0
|
|
|
|
|
0
|
|
112
|
4
|
|
|
0
|
|
57
|
$sheet->add_write_handler(qr/^0\d+$/, sub{shift->write_string(@_)}); #Leading Zero Support |
|
0
|
|
|
|
|
0
|
|
113
|
4
|
|
|
|
|
40
|
$sheet->add_write_handler(qr{^\d{2}/\d{2}/\d{4} \d{2}:\d{2}:\d{2}$}, $subref); #DateTime Support |
114
|
4
|
|
|
|
|
33
|
$self->_add_data($sheet, $data); |
115
|
4
|
|
|
|
|
12
|
$sheet->freeze_panes(1, 0); |
116
|
4
|
|
|
|
|
47
|
return $sheet; |
117
|
|
|
|
|
|
|
} |
118
|
|
|
|
|
|
|
|
119
|
|
|
|
|
|
|
sub _add_data { |
120
|
4
|
|
|
4
|
|
5
|
my $self=shift; |
121
|
4
|
|
|
|
|
4
|
my $worksheet=shift; |
122
|
4
|
|
|
|
|
5
|
my $data=shift; |
123
|
4
|
|
|
|
|
8
|
my $header=shift(@$data); |
124
|
4
|
|
|
|
|
9
|
$worksheet->write_col(0,0,[$header], $self->book->add_format($self->default, $self->first)); |
125
|
4
|
|
|
|
|
1675
|
$worksheet->write_col(1,0, $data, $self->book->add_format($self->default)); |
126
|
|
|
|
|
|
|
|
127
|
4
|
|
|
|
|
1922
|
unshift @$data, $header; #put the data back together it is a reference! |
128
|
|
|
|
|
|
|
|
129
|
|
|
|
|
|
|
#Auto resize columns |
130
|
4
|
|
|
|
|
12
|
foreach my $col (0 .. scalar(@$header) - 1) { |
131
|
7
|
|
50
|
|
|
95
|
my $width=(sort {$a<=>$b} map {length($_->[$col]||'')} @$data)[-1]; |
|
21
|
|
|
|
|
59
|
|
|
21
|
|
|
|
|
73
|
|
132
|
7
|
50
|
|
|
|
19
|
$width = 8 if $width < 8; |
133
|
7
|
|
|
|
|
23
|
$worksheet->set_column($col, $col, $width); |
134
|
|
|
|
|
|
|
} |
135
|
4
|
|
|
|
|
99
|
return $self; |
136
|
|
|
|
|
|
|
} |
137
|
|
|
|
|
|
|
|
138
|
|
|
|
|
|
|
=head2 header |
139
|
|
|
|
|
|
|
|
140
|
|
|
|
|
|
|
Returns a header appropriate for a web application |
141
|
|
|
|
|
|
|
|
142
|
|
|
|
|
|
|
Content-type: application/vnd.ms-excel |
143
|
|
|
|
|
|
|
Content-Disposition: attachment; filename=filename.xls |
144
|
|
|
|
|
|
|
|
145
|
|
|
|
|
|
|
$ss->header #embedded in browser |
146
|
|
|
|
|
|
|
$ss->header(filename=>"filename.xls") #download prompt |
147
|
|
|
|
|
|
|
$ss->header(content_type=>"application/vnd.ms-excel") #default content type |
148
|
|
|
|
|
|
|
|
149
|
|
|
|
|
|
|
=cut |
150
|
|
|
|
|
|
|
|
151
|
|
|
|
|
|
|
sub header { |
152
|
4
|
|
|
4
|
1
|
1558
|
my $self=shift; |
153
|
4
|
|
|
|
|
11
|
my %data=@_; |
154
|
4
|
100
|
|
|
|
15
|
$data{"content_type"}="application/vnd.ms-excel" |
155
|
|
|
|
|
|
|
unless defined $data{"content_type"}; |
156
|
4
|
|
|
|
|
15
|
my $header=sprintf("Content-type: %s\n", $data{"content_type"}); |
157
|
4
|
100
|
|
|
|
16
|
$header.=sprintf(qq{Content-Disposition: attachment; filename="%s";\n}, |
158
|
|
|
|
|
|
|
$data{"filename"}) if defined $data{"filename"}; |
159
|
4
|
|
|
|
|
6
|
$header.="\n"; |
160
|
4
|
|
|
|
|
13
|
return $header; |
161
|
|
|
|
|
|
|
} |
162
|
|
|
|
|
|
|
|
163
|
|
|
|
|
|
|
=head2 content |
164
|
|
|
|
|
|
|
|
165
|
|
|
|
|
|
|
This returns the binary content of the spreadsheet. |
166
|
|
|
|
|
|
|
|
167
|
|
|
|
|
|
|
print $ss->content; |
168
|
|
|
|
|
|
|
|
169
|
|
|
|
|
|
|
print $ss->header, $ss->content; #CGI Application |
170
|
|
|
|
|
|
|
|
171
|
|
|
|
|
|
|
binmod($fh); |
172
|
|
|
|
|
|
|
print $fh, $ss->content; |
173
|
|
|
|
|
|
|
|
174
|
|
|
|
|
|
|
=cut |
175
|
|
|
|
|
|
|
|
176
|
|
|
|
|
|
|
sub content { |
177
|
1
|
|
|
1
|
1
|
3218
|
my $self=shift; |
178
|
1
|
|
|
|
|
4
|
$self->book->close; |
179
|
1
|
|
|
|
|
12057
|
return $self->{"content"}; |
180
|
|
|
|
|
|
|
} |
181
|
|
|
|
|
|
|
|
182
|
|
|
|
|
|
|
=head1 PROPERTIES |
183
|
|
|
|
|
|
|
|
184
|
|
|
|
|
|
|
=head2 first |
185
|
|
|
|
|
|
|
|
186
|
|
|
|
|
|
|
Returns a hash of additional settings for the first row |
187
|
|
|
|
|
|
|
|
188
|
|
|
|
|
|
|
$ss->first({setting=>"value"}); #settings from L |
189
|
|
|
|
|
|
|
|
190
|
|
|
|
|
|
|
=cut |
191
|
|
|
|
|
|
|
|
192
|
|
|
|
|
|
|
sub first { |
193
|
4
|
|
|
4
|
1
|
6
|
my $self=shift; |
194
|
4
|
50
|
|
|
|
9
|
$self->{"first"}=shift if @_; |
195
|
4
|
100
|
|
|
|
13
|
$self->{"first"}={bg_color=>"silver", bold=>1} |
196
|
|
|
|
|
|
|
unless ref($self->{"first"}) eq "HASH"; |
197
|
4
|
50
|
|
|
|
6
|
return wantarray ? %{$self->{"first"}} : $self->{"first"}; |
|
4
|
|
|
|
|
30
|
|
198
|
|
|
|
|
|
|
} |
199
|
|
|
|
|
|
|
|
200
|
|
|
|
|
|
|
=head2 default |
201
|
|
|
|
|
|
|
|
202
|
|
|
|
|
|
|
Returns a hash of default settings for the body |
203
|
|
|
|
|
|
|
|
204
|
|
|
|
|
|
|
$ss->default({setting=>"value"}); #settings from L |
205
|
|
|
|
|
|
|
|
206
|
|
|
|
|
|
|
=cut |
207
|
|
|
|
|
|
|
|
208
|
|
|
|
|
|
|
sub default { |
209
|
12
|
|
|
12
|
1
|
14
|
my $self=shift; |
210
|
12
|
50
|
|
|
|
36
|
$self->{"default"}=shift if @_; |
211
|
12
|
100
|
|
|
|
35
|
$self->{"default"}={border=>1, border_color=>"gray"} |
212
|
|
|
|
|
|
|
unless ref($self->{"default"}) eq "HASH"; |
213
|
12
|
50
|
|
|
|
25
|
return wantarray ? %{$self->{"default"}} : $self->{"default"}; |
|
12
|
|
|
|
|
50
|
|
214
|
|
|
|
|
|
|
} |
215
|
|
|
|
|
|
|
|
216
|
|
|
|
|
|
|
=head1 BUGS |
217
|
|
|
|
|
|
|
|
218
|
|
|
|
|
|
|
Log on RT and contact the author. |
219
|
|
|
|
|
|
|
|
220
|
|
|
|
|
|
|
=head1 SUPPORT |
221
|
|
|
|
|
|
|
|
222
|
|
|
|
|
|
|
DavisNetworks.com provides support services for all Perl applications including this package. |
223
|
|
|
|
|
|
|
|
224
|
|
|
|
|
|
|
=head1 AUTHOR |
225
|
|
|
|
|
|
|
|
226
|
|
|
|
|
|
|
Michael R. Davis |
227
|
|
|
|
|
|
|
CPAN ID: MRDVT |
228
|
|
|
|
|
|
|
STOP, LLC |
229
|
|
|
|
|
|
|
domain=>michaelrdavis,tld=>com,account=>perl |
230
|
|
|
|
|
|
|
http://www.stopllc.com/ |
231
|
|
|
|
|
|
|
|
232
|
|
|
|
|
|
|
=head1 COPYRIGHT |
233
|
|
|
|
|
|
|
|
234
|
|
|
|
|
|
|
Copyright (c) 2009 Michael R. Davis |
235
|
|
|
|
|
|
|
Copyright (c) 2001-2005 Tony Bowden (IO::Scalar portion used here "under the same terms as Perl itself") |
236
|
|
|
|
|
|
|
|
237
|
|
|
|
|
|
|
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. |
238
|
|
|
|
|
|
|
|
239
|
|
|
|
|
|
|
The full text of the license can be found in the LICENSE file included with this module. |
240
|
|
|
|
|
|
|
|
241
|
|
|
|
|
|
|
=head1 SEE ALSO |
242
|
|
|
|
|
|
|
|
243
|
|
|
|
|
|
|
L, L sqlarrayarrayname method, L, L |
244
|
|
|
|
|
|
|
|
245
|
|
|
|
|
|
|
=cut |
246
|
|
|
|
|
|
|
|
247
|
|
|
|
|
|
|
1; |