line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package Spreadsheet::SimpleExcel; |
2
|
|
|
|
|
|
|
|
3
|
|
|
|
|
|
|
# ABSTRACT: Create Excel files with Perl |
4
|
|
|
|
|
|
|
|
5
|
2
|
|
|
2
|
|
80231
|
use 5.006; |
|
2
|
|
|
|
|
9
|
|
|
2
|
|
|
|
|
98
|
|
6
|
2
|
|
|
2
|
|
15
|
use strict; |
|
2
|
|
|
|
|
2
|
|
|
2
|
|
|
|
|
87
|
|
7
|
2
|
|
|
2
|
|
11
|
use warnings; |
|
2
|
|
|
|
|
8
|
|
|
2
|
|
|
|
|
87
|
|
8
|
|
|
|
|
|
|
|
9
|
2
|
|
|
2
|
|
4655
|
use Spreadsheet::WriteExcel; |
|
2
|
|
|
|
|
226673
|
|
|
2
|
|
|
|
|
105
|
|
10
|
2
|
|
|
2
|
|
1436
|
use IO::Scalar; |
|
2
|
|
|
|
|
8363
|
|
|
2
|
|
|
|
|
134
|
|
11
|
2
|
|
|
2
|
|
18
|
use IO::File; |
|
2
|
|
|
|
|
4
|
|
|
2
|
|
|
|
|
327
|
|
12
|
2
|
|
|
2
|
|
1885
|
use XML::Writer; |
|
2
|
|
|
|
|
14422
|
|
|
2
|
|
|
|
|
9770
|
|
13
|
|
|
|
|
|
|
|
14
|
|
|
|
|
|
|
our $VERSION = '1.92'; |
15
|
|
|
|
|
|
|
our $errstr = ''; |
16
|
|
|
|
|
|
|
|
17
|
|
|
|
|
|
|
sub new{ |
18
|
3
|
|
|
3
|
1
|
162
|
my ($class,%opts) = @_; |
19
|
3
|
|
|
|
|
8
|
my $self = {}; |
20
|
3
|
|
100
|
|
|
31
|
$self->{worksheets} = $opts{-worksheets} || []; |
21
|
3
|
|
|
|
|
16
|
$self->{type} = 'application/vnd.ms-excel'; |
22
|
3
|
|
50
|
|
|
59
|
$self->{BIG} = $opts{-big} || 0; |
23
|
3
|
|
50
|
|
|
39
|
$self->{FILE} = $opts{-filename} || ''; |
24
|
3
|
|
|
|
|
11
|
bless($self,$class); |
25
|
|
|
|
|
|
|
|
26
|
3
|
|
|
|
|
8
|
for my $sheet( @{ $self->{worksheets} } ){ |
|
3
|
|
|
|
|
22
|
|
27
|
1
|
50
|
|
|
|
8
|
if( length($sheet->[0]) > 31 ){ |
28
|
0
|
|
|
|
|
0
|
warn "length of worksheet name is greater than 31. It is truncated..."; |
29
|
0
|
|
|
|
|
0
|
$sheet->[0] = substr $sheet->[0], 0, 30; |
30
|
|
|
|
|
|
|
} |
31
|
|
|
|
|
|
|
} |
32
|
|
|
|
|
|
|
|
33
|
3
|
|
|
|
|
16
|
$self->_last_sheet(''); |
34
|
|
|
|
|
|
|
|
35
|
3
|
|
|
|
|
12
|
return $self; |
36
|
|
|
|
|
|
|
}# end new |
37
|
|
|
|
|
|
|
|
38
|
|
|
|
|
|
|
sub current_sheet{ |
39
|
3
|
|
|
3
|
1
|
1611
|
my ($self) = @_; |
40
|
3
|
|
|
|
|
11
|
return $self->_last_sheet; |
41
|
|
|
|
|
|
|
} |
42
|
|
|
|
|
|
|
|
43
|
|
|
|
|
|
|
sub add_worksheet{ |
44
|
5
|
|
|
5
|
1
|
1419
|
my ($self,@array) = @_; |
45
|
5
|
|
|
|
|
17
|
my ($package,$filename,$line) = caller(); |
46
|
5
|
50
|
|
|
|
18
|
unless(defined $array[0]){ |
47
|
0
|
|
|
|
|
0
|
$errstr = qq~No worksheet defined at Spreadsheet::SimpleExcel add_worksheet() from |
48
|
|
|
|
|
|
|
$filename line $line\n~; |
49
|
0
|
0
|
|
|
|
0
|
$array[0] = 'unknown' unless defined $array[0]; |
50
|
0
|
|
|
|
|
0
|
return undef; |
51
|
|
|
|
|
|
|
} |
52
|
|
|
|
|
|
|
|
53
|
5
|
50
|
|
|
|
19
|
if( length( $array[0] ) > 31 ){ |
54
|
0
|
|
|
|
|
0
|
$errstr = qq~Length of worksheet name has be at most 31~; |
55
|
0
|
|
|
|
|
0
|
return undef; |
56
|
|
|
|
|
|
|
} |
57
|
|
|
|
|
|
|
|
58
|
5
|
|
|
|
|
17
|
$self->_last_sheet($array[0]); |
59
|
|
|
|
|
|
|
|
60
|
5
|
50
|
|
|
|
6
|
if(grep{$_->[0] eq $array[0]}@{$self->{worksheets}}){ |
|
4
|
|
|
|
|
18
|
|
|
5
|
|
|
|
|
19
|
|
61
|
0
|
|
|
|
|
0
|
$errstr = qq~Duplicate worksheet-title at Spreadsheet::SimpleExcel add_worksheet() from |
62
|
|
|
|
|
|
|
$filename line $line\n~; |
63
|
0
|
|
|
|
|
0
|
return undef; |
64
|
|
|
|
|
|
|
} |
65
|
5
|
|
|
|
|
7
|
push(@{$self->{worksheets}},[@array]); |
|
5
|
|
|
|
|
28
|
|
66
|
5
|
|
|
|
|
17
|
return 1; |
67
|
|
|
|
|
|
|
}# end add_worksheet |
68
|
|
|
|
|
|
|
|
69
|
|
|
|
|
|
|
sub _last_sheet{ |
70
|
101
|
|
|
101
|
|
119
|
my ($self,$title) = @_; |
71
|
|
|
|
|
|
|
|
72
|
101
|
100
|
|
|
|
218
|
$self->{last_sheet} = $title if defined $title; |
73
|
|
|
|
|
|
|
|
74
|
101
|
|
|
|
|
184
|
return $self->{last_sheet}; |
75
|
|
|
|
|
|
|
} |
76
|
|
|
|
|
|
|
|
77
|
|
|
|
|
|
|
sub del_worksheet{ |
78
|
1
|
|
|
1
|
1
|
2
|
my ($self,$title) = @_; |
79
|
1
|
|
|
|
|
4
|
my ($package,$filename,$line) = caller(); |
80
|
|
|
|
|
|
|
|
81
|
1
|
50
|
|
|
|
5
|
$title = $self->_last_sheet unless defined $title; |
82
|
1
|
|
|
|
|
3
|
$self->_last_sheet( $title ); |
83
|
|
|
|
|
|
|
|
84
|
1
|
50
|
|
|
|
4
|
unless(defined $title){ |
85
|
0
|
|
|
|
|
0
|
$errstr = qq~No worksheet-title defined at Spreadsheet::SimpleExcel del_worksheet() from |
86
|
|
|
|
|
|
|
$filename line $line\n~; |
87
|
0
|
|
|
|
|
0
|
return undef; |
88
|
|
|
|
|
|
|
} |
89
|
1
|
|
|
|
|
2
|
my @worksheets = grep{$_->[0] ne $title}@{$self->{worksheets}}; |
|
3
|
|
|
|
|
9
|
|
|
1
|
|
|
|
|
3
|
|
90
|
1
|
|
|
|
|
5
|
$self->{worksheets} = [@worksheets]; |
91
|
|
|
|
|
|
|
}# end del_worksheet |
92
|
|
|
|
|
|
|
|
93
|
|
|
|
|
|
|
sub add_row{ |
94
|
43
|
|
|
43
|
1
|
149
|
my ($self,$title,$arref,$props) = @_; |
95
|
43
|
|
|
|
|
102
|
my ($package,$filename,$line) = caller(); |
96
|
|
|
|
|
|
|
|
97
|
43
|
100
|
|
|
|
108
|
if(ref $title eq 'ARRAY'){ |
98
|
42
|
|
|
|
|
43
|
$props = $arref; |
99
|
42
|
|
|
|
|
40
|
$arref = $title; |
100
|
42
|
|
|
|
|
71
|
$title = $self->_last_sheet; |
101
|
|
|
|
|
|
|
} |
102
|
|
|
|
|
|
|
|
103
|
43
|
50
|
|
|
|
82
|
$title = $self->_last_sheet unless $title; |
104
|
43
|
|
|
|
|
73
|
$self->_last_sheet( $title ); |
105
|
|
|
|
|
|
|
|
106
|
43
|
50
|
|
|
|
40
|
unless(grep{$_->[0] eq $title}@{$self->{worksheets}}){ |
|
85
|
|
|
|
|
231
|
|
|
43
|
|
|
|
|
78
|
|
107
|
0
|
|
|
|
|
0
|
$errstr = qq~Worksheet $title does not exist at Spreadsheet::SimpleExcel add_row() from |
108
|
|
|
|
|
|
|
$filename line $line\n~; |
109
|
0
|
|
|
|
|
0
|
return undef; |
110
|
|
|
|
|
|
|
} |
111
|
43
|
50
|
|
|
|
121
|
unless(ref($arref) eq 'ARRAY'){ |
112
|
0
|
|
|
|
|
0
|
$errstr = qq~Is not an arrayref at Spreadsheet::SimpleExcel add_row() from |
113
|
|
|
|
|
|
|
$filename line $line\n~; |
114
|
0
|
|
|
|
|
0
|
return undef; |
115
|
|
|
|
|
|
|
} |
116
|
43
|
|
|
|
|
70
|
foreach my $worksheet(@{$self->{worksheets}}){ |
|
43
|
|
|
|
|
81
|
|
117
|
85
|
100
|
|
|
|
169
|
if($worksheet->[0] eq $title){ |
118
|
43
|
|
|
|
|
30
|
push(@{$worksheet->[1]->{'-data'}},$arref); |
|
43
|
|
|
|
|
87
|
|
119
|
43
|
|
|
|
|
68
|
last; |
120
|
|
|
|
|
|
|
} |
121
|
|
|
|
|
|
|
} |
122
|
43
|
|
|
|
|
141
|
return 1; |
123
|
|
|
|
|
|
|
}# end add_data |
124
|
|
|
|
|
|
|
|
125
|
|
|
|
|
|
|
sub set_headers{ |
126
|
1
|
|
|
1
|
1
|
10
|
my ($self,$title,$arref,$props) = @_; |
127
|
1
|
|
|
|
|
4
|
my ($package,$filename,$line) = caller(); |
128
|
|
|
|
|
|
|
|
129
|
1
|
50
|
|
|
|
6
|
if(ref $title eq 'ARRAY'){ |
130
|
0
|
|
|
|
|
0
|
$props = $arref; |
131
|
0
|
|
|
|
|
0
|
$arref = $title; |
132
|
0
|
|
|
|
|
0
|
$title = $self->_last_sheet; |
133
|
|
|
|
|
|
|
} |
134
|
|
|
|
|
|
|
|
135
|
1
|
|
33
|
|
|
4
|
$title ||= $self->_last_sheet; |
136
|
1
|
|
|
|
|
4
|
$self->_last_sheet( $title ); |
137
|
|
|
|
|
|
|
|
138
|
1
|
50
|
|
|
|
2
|
unless(grep{$_->[0] eq $title}@{$self->{worksheets}}){ |
|
1
|
|
|
|
|
7
|
|
|
1
|
|
|
|
|
4
|
|
139
|
0
|
|
|
|
|
0
|
$errstr = qq~Worksheet $title does not exist at Spreadsheet::SimpleExcel set_headers() from |
140
|
|
|
|
|
|
|
$filename line $line\n~; |
141
|
0
|
|
|
|
|
0
|
return undef; |
142
|
|
|
|
|
|
|
} |
143
|
1
|
50
|
|
|
|
6
|
unless(ref($arref) eq 'ARRAY'){ |
144
|
0
|
|
|
|
|
0
|
$errstr = qq~Is not an arrayref at Spreadsheet::SimpleExcel set_headers() from |
145
|
|
|
|
|
|
|
$filename line $line\n~; |
146
|
0
|
|
|
|
|
0
|
return undef; |
147
|
|
|
|
|
|
|
} |
148
|
1
|
|
|
|
|
3
|
foreach my $worksheet(@{$self->{worksheets}}){ |
|
1
|
|
|
|
|
5
|
|
149
|
1
|
50
|
|
|
|
5
|
if($worksheet->[0] eq $title){ |
150
|
1
|
|
|
|
|
4
|
$worksheet->[1]->{'-headers'} = $arref; |
151
|
1
|
|
|
|
|
4
|
last; |
152
|
|
|
|
|
|
|
} |
153
|
|
|
|
|
|
|
} |
154
|
1
|
|
|
|
|
3
|
return 1; |
155
|
|
|
|
|
|
|
}# end add_headers |
156
|
|
|
|
|
|
|
|
157
|
|
|
|
|
|
|
sub set_headers_format{ |
158
|
0
|
|
|
0
|
1
|
0
|
my ($self,$title,$arref) = @_; |
159
|
0
|
|
|
|
|
0
|
my ($package,$filename,$line) = caller(); |
160
|
|
|
|
|
|
|
|
161
|
0
|
0
|
|
|
|
0
|
if(ref $title eq 'ARRAY'){ |
162
|
0
|
|
|
|
|
0
|
$arref = $title; |
163
|
0
|
|
|
|
|
0
|
$title = $self->_last_sheet; |
164
|
|
|
|
|
|
|
} |
165
|
|
|
|
|
|
|
|
166
|
0
|
0
|
|
|
|
0
|
$title = $self->_last_sheet unless defined $title; |
167
|
0
|
|
|
|
|
0
|
$self->_last_sheet( $title ); |
168
|
|
|
|
|
|
|
|
169
|
0
|
0
|
|
|
|
0
|
unless(grep{$_->[0] eq $title}@{$self->{worksheets}}){ |
|
0
|
|
|
|
|
0
|
|
|
0
|
|
|
|
|
0
|
|
170
|
0
|
|
|
|
|
0
|
$errstr = qq~Worksheet $title does not exist at Spreadsheet::SimpleExcel set_headers_format() from |
171
|
|
|
|
|
|
|
$filename line $line\n~; |
172
|
0
|
|
|
|
|
0
|
return undef; |
173
|
|
|
|
|
|
|
} |
174
|
0
|
0
|
|
|
|
0
|
unless(ref($arref) eq 'ARRAY'){ |
175
|
0
|
|
|
|
|
0
|
$errstr = qq~Is not an arrayref at Spreadsheet::SimpleExcel set_headers_format() from |
176
|
|
|
|
|
|
|
$filename line $line\n~; |
177
|
0
|
|
|
|
|
0
|
return undef; |
178
|
|
|
|
|
|
|
} |
179
|
0
|
|
|
|
|
0
|
foreach my $worksheet(@{$self->{worksheets}}){ |
|
0
|
|
|
|
|
0
|
|
180
|
0
|
0
|
|
|
|
0
|
if($worksheet->[0] eq $title){ |
181
|
0
|
|
|
|
|
0
|
$worksheet->[1]->{'-headers_format'} = $arref; |
182
|
0
|
|
|
|
|
0
|
last; |
183
|
|
|
|
|
|
|
} |
184
|
|
|
|
|
|
|
} |
185
|
0
|
|
|
|
|
0
|
return 1; |
186
|
|
|
|
|
|
|
}# end add_headers |
187
|
|
|
|
|
|
|
|
188
|
|
|
|
|
|
|
sub set_data_format{ |
189
|
0
|
|
|
0
|
1
|
0
|
my ($self,$title,$arref) = @_; |
190
|
0
|
|
|
|
|
0
|
my ($package,$filename,$line) = caller(); |
191
|
|
|
|
|
|
|
|
192
|
0
|
0
|
|
|
|
0
|
if(ref $title eq 'ARRAY'){ |
193
|
0
|
|
|
|
|
0
|
$arref = $title; |
194
|
0
|
|
|
|
|
0
|
$title = $self->_last_sheet; |
195
|
|
|
|
|
|
|
} |
196
|
|
|
|
|
|
|
|
197
|
0
|
0
|
|
|
|
0
|
$title = $self->_last_sheet unless defined $title; |
198
|
0
|
|
|
|
|
0
|
$self->_last_sheet( $title ); |
199
|
|
|
|
|
|
|
|
200
|
0
|
0
|
|
|
|
0
|
unless(grep{$_->[0] eq $title}@{$self->{worksheets}}){ |
|
0
|
|
|
|
|
0
|
|
|
0
|
|
|
|
|
0
|
|
201
|
0
|
|
|
|
|
0
|
$errstr = qq~Worksheet $title does not exist at Spreadsheet::SimpleExcel set_data_format() from |
202
|
|
|
|
|
|
|
$filename line $line\n~; |
203
|
0
|
|
|
|
|
0
|
return undef; |
204
|
|
|
|
|
|
|
} |
205
|
0
|
0
|
|
|
|
0
|
unless(ref($arref) eq 'ARRAY'){ |
206
|
0
|
|
|
|
|
0
|
$errstr = qq~Is not an arrayref at Spreadsheet::SimpleExcel set_data_format() from |
207
|
|
|
|
|
|
|
$filename line $line\n~; |
208
|
0
|
|
|
|
|
0
|
return undef; |
209
|
|
|
|
|
|
|
} |
210
|
0
|
|
|
|
|
0
|
foreach my $worksheet(@{$self->{worksheets}}){ |
|
0
|
|
|
|
|
0
|
|
211
|
0
|
0
|
|
|
|
0
|
if($worksheet->[0] eq $title){ |
212
|
0
|
|
|
|
|
0
|
$worksheet->[1]->{'-data_format'} = $arref; |
213
|
0
|
|
|
|
|
0
|
last; |
214
|
|
|
|
|
|
|
} |
215
|
|
|
|
|
|
|
} |
216
|
0
|
|
|
|
|
0
|
return 1; |
217
|
|
|
|
|
|
|
}# end add_headers |
218
|
|
|
|
|
|
|
|
219
|
|
|
|
|
|
|
sub add_row_at{ |
220
|
1
|
|
|
1
|
1
|
4
|
my ($self,$title,$index,$arref,$props) = @_; |
221
|
1
|
|
|
|
|
24
|
my ($package,$filename,$line) = caller(); |
222
|
|
|
|
|
|
|
|
223
|
1
|
50
|
|
|
|
6
|
if(ref $index eq 'ARRAY'){ |
224
|
0
|
|
|
|
|
0
|
$props = $arref; |
225
|
0
|
|
|
|
|
0
|
$arref = $index; |
226
|
0
|
|
|
|
|
0
|
$index = $title; |
227
|
0
|
|
|
|
|
0
|
$title = $self->_last_sheet; |
228
|
|
|
|
|
|
|
} |
229
|
|
|
|
|
|
|
|
230
|
1
|
50
|
|
|
|
4
|
$title = $self->_last_sheet unless defined $title; |
231
|
1
|
|
|
|
|
4
|
$self->_last_sheet( $title ); |
232
|
|
|
|
|
|
|
|
233
|
1
|
50
|
|
|
|
2
|
unless(grep{$_->[0] eq $title}@{$self->{worksheets}}){ |
|
3
|
|
|
|
|
11
|
|
|
1
|
|
|
|
|
4
|
|
234
|
0
|
|
|
|
|
0
|
$errstr = qq~Worksheet $title does not exist at Spreadsheet::SimpleExcel add_row_at() from |
235
|
|
|
|
|
|
|
$filename line $line\n~; |
236
|
0
|
|
|
|
|
0
|
return undef; |
237
|
|
|
|
|
|
|
} |
238
|
1
|
50
|
|
|
|
5
|
unless(ref($arref) eq 'ARRAY'){ |
239
|
0
|
|
|
|
|
0
|
$errstr = qq~Is not an arrayref at Spreadsheet::SimpleExcel add_row() from |
240
|
|
|
|
|
|
|
$filename line $line\n~; |
241
|
0
|
|
|
|
|
0
|
return undef; |
242
|
|
|
|
|
|
|
} |
243
|
1
|
|
|
|
|
2
|
foreach my $worksheet(@{$self->{worksheets}}){ |
|
1
|
|
|
|
|
5
|
|
244
|
1
|
50
|
|
|
|
5
|
if($worksheet->[0] eq $title){ |
245
|
1
|
|
|
|
|
2
|
my @array = @{$worksheet->[1]->{'-data'}}; |
|
1
|
|
|
|
|
5
|
|
246
|
1
|
50
|
33
|
|
|
12
|
if($index =~ /[^\d]/ || $index > $#array){ |
247
|
0
|
|
|
|
|
0
|
$errstr = qq~Index not in Array at Spreadsheet::SimpleExcel add_row_at() from |
248
|
|
|
|
|
|
|
$filename line $line\n~; |
249
|
0
|
|
|
|
|
0
|
return undef; |
250
|
|
|
|
|
|
|
} |
251
|
1
|
|
|
|
|
3
|
splice(@array,$index,0,$arref); |
252
|
1
|
|
|
|
|
4
|
$worksheet->[1]->{'-data'} = \@array; |
253
|
1
|
|
|
|
|
2
|
last; |
254
|
|
|
|
|
|
|
} |
255
|
|
|
|
|
|
|
} |
256
|
1
|
|
|
|
|
3
|
return 1; |
257
|
|
|
|
|
|
|
}# end add_row_at |
258
|
|
|
|
|
|
|
|
259
|
|
|
|
|
|
|
sub sort_data{ |
260
|
2
|
|
|
2
|
1
|
277
|
my ($self,$title,$index,$type) = @_; |
261
|
2
|
|
|
|
|
8
|
my ($package,$filename,$line) = caller(); |
262
|
|
|
|
|
|
|
|
263
|
2
|
50
|
|
|
|
29
|
if(scalar @_ == 1){ |
|
|
50
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
264
|
0
|
|
|
|
|
0
|
$errstr = qq~at least column index is missing ($filename line $line)~; |
265
|
0
|
|
|
|
|
0
|
return undef; |
266
|
|
|
|
|
|
|
} |
267
|
|
|
|
|
|
|
elsif(scalar @_ == 2){ |
268
|
0
|
0
|
|
|
|
0
|
if($title =~ /\D/){ |
269
|
0
|
|
|
|
|
0
|
$errstr = qq~Index not in Array at Spreadsheet::SimpleExcel sort_data() from |
270
|
|
|
|
|
|
|
$filename line $line\n~; |
271
|
0
|
|
|
|
|
0
|
return undef; |
272
|
|
|
|
|
|
|
|
273
|
|
|
|
|
|
|
} |
274
|
|
|
|
|
|
|
else{ |
275
|
0
|
|
|
|
|
0
|
$index = $title; |
276
|
0
|
|
|
|
|
0
|
$title = $self->_last_sheet; |
277
|
|
|
|
|
|
|
} |
278
|
|
|
|
|
|
|
} |
279
|
|
|
|
|
|
|
elsif(scalar @_ == 3){ |
280
|
0
|
0
|
0
|
|
|
0
|
if($title =~ /^\d+$/ and $index =~ /^ASC|DESC$/){ |
281
|
0
|
|
|
|
|
0
|
$type = $index; |
282
|
0
|
|
|
|
|
0
|
$index = $title; |
283
|
0
|
|
|
|
|
0
|
$title = $self->_last_sheet; |
284
|
|
|
|
|
|
|
} |
285
|
|
|
|
|
|
|
} |
286
|
|
|
|
|
|
|
|
287
|
2
|
50
|
|
|
|
8
|
$title = $self->_last_sheet unless defined $title; |
288
|
2
|
|
50
|
|
|
19
|
$type ||= 'ASC'; |
289
|
|
|
|
|
|
|
|
290
|
2
|
|
|
|
|
9
|
$self->_last_sheet( $title ); |
291
|
|
|
|
|
|
|
|
292
|
2
|
50
|
|
|
|
4
|
unless(grep{$_->[0] eq $title}@{$self->{worksheets}}){ |
|
4
|
|
|
|
|
18
|
|
|
2
|
|
|
|
|
7
|
|
293
|
0
|
|
|
|
|
0
|
$errstr = qq~Worksheet $title does not exist at Spreadsheet::SimpleExcel sort_data() from |
294
|
|
|
|
|
|
|
$filename line $line\n~; |
295
|
0
|
|
|
|
|
0
|
return undef; |
296
|
|
|
|
|
|
|
} |
297
|
|
|
|
|
|
|
|
298
|
2
|
|
|
|
|
8
|
foreach my $worksheet(@{$self->{worksheets}}){ |
|
2
|
|
|
|
|
7
|
|
299
|
2
|
50
|
|
|
|
16
|
if($worksheet->[0] eq $title){ |
300
|
2
|
50
|
|
|
|
13
|
$worksheet->[1]->{sortstring} = '' unless(exists $worksheet->[1]->{sortstring}); |
301
|
2
|
50
|
|
|
|
15
|
my $join = $worksheet->[1]->{sortstring} =~ /\w/ ? ' || ' : ''; |
302
|
2
|
|
|
|
|
5
|
my @array = @{$worksheet->[1]->{'-data'}}; |
|
2
|
|
|
|
|
11
|
|
303
|
2
|
50
|
|
|
|
10
|
last unless(scalar(@array) > 0); |
304
|
2
|
100
|
|
|
|
23
|
if($index >= scalar(@{$array[0]})){ |
|
2
|
|
|
|
|
10
|
|
305
|
1
|
|
|
|
|
6
|
$errstr = qq~Index not in Array at Spreadsheet::SimpleExcel sort_data() from |
306
|
|
|
|
|
|
|
$filename line $line\n~; |
307
|
1
|
|
|
|
|
6
|
return undef; |
308
|
|
|
|
|
|
|
} |
309
|
1
|
50
|
33
|
|
|
13
|
if(not defined $index || $index =~ /\D/){ |
310
|
0
|
|
|
|
|
0
|
$errstr = qq~Index not in Array at Spreadsheet::SimpleExcel sort_data() from |
311
|
|
|
|
|
|
|
$filename line $line\n~; |
312
|
0
|
|
|
|
|
0
|
return undef; |
313
|
|
|
|
|
|
|
} |
314
|
1
|
50
|
|
|
|
5
|
if(_is_numeric(\@array,$index)){ |
315
|
0
|
0
|
0
|
|
|
0
|
if($type && $type eq 'DESC'){ |
316
|
0
|
|
|
|
|
0
|
$worksheet->[1]->{sortstring} .= "$join \$b->[$index] <=> \$a->[$index]"; |
317
|
|
|
|
|
|
|
} |
318
|
|
|
|
|
|
|
else{ |
319
|
0
|
|
|
|
|
0
|
$worksheet->[1]->{sortstring} .= "$join \$a->[$index] <=> \$b->[$index]"; |
320
|
|
|
|
|
|
|
} |
321
|
|
|
|
|
|
|
} |
322
|
|
|
|
|
|
|
else{ |
323
|
1
|
50
|
33
|
|
|
14
|
if($type && $type eq 'DESC'){ |
324
|
1
|
|
|
|
|
8
|
$worksheet->[1]->{sortstring} .= "$join \$b->[$index] cmp \$a->[$index]"; |
325
|
|
|
|
|
|
|
} |
326
|
|
|
|
|
|
|
else{ |
327
|
0
|
|
|
|
|
0
|
$worksheet->[1]->{sortstring} .= "$join \$a->[$index] cmp \$b->[$index]"; |
328
|
|
|
|
|
|
|
} |
329
|
|
|
|
|
|
|
} |
330
|
1
|
|
|
|
|
3
|
last; |
331
|
|
|
|
|
|
|
} |
332
|
|
|
|
|
|
|
} |
333
|
1
|
|
|
|
|
3
|
return 1; |
334
|
|
|
|
|
|
|
}# end sort_data |
335
|
|
|
|
|
|
|
|
336
|
|
|
|
|
|
|
sub reset_sort{ |
337
|
0
|
|
|
0
|
1
|
0
|
my ($self,$title) = @_; |
338
|
0
|
|
|
|
|
0
|
my ($package,$filename,$line) = caller(); |
339
|
|
|
|
|
|
|
|
340
|
0
|
0
|
|
|
|
0
|
$title = $self->_last_sheet unless defined $title; |
341
|
0
|
|
|
|
|
0
|
$self->_last_sheet( $title ); |
342
|
|
|
|
|
|
|
|
343
|
0
|
0
|
|
|
|
0
|
unless(grep{$_->[0] eq $title}@{$self->{worksheets}}){ |
|
0
|
|
|
|
|
0
|
|
|
0
|
|
|
|
|
0
|
|
344
|
0
|
|
|
|
|
0
|
$errstr = qq~Worksheet $title does not exist at Spreadsheet::SimpleExcel add_row_at() from |
345
|
|
|
|
|
|
|
$filename line $line\n~; |
346
|
0
|
|
|
|
|
0
|
return undef; |
347
|
|
|
|
|
|
|
} |
348
|
0
|
|
|
|
|
0
|
my (@worksheets) = grep{$_->[0] eq $title}@{$self->{worksheets}}; |
|
0
|
|
|
|
|
0
|
|
|
0
|
|
|
|
|
0
|
|
349
|
0
|
|
|
|
|
0
|
for my $sheet(@worksheets){ |
350
|
0
|
|
|
|
|
0
|
$sheet->[1]->{sortstring} = ''; |
351
|
|
|
|
|
|
|
} |
352
|
|
|
|
|
|
|
}# reset_sort |
353
|
|
|
|
|
|
|
|
354
|
|
|
|
|
|
|
sub errstr{ |
355
|
1
|
|
|
1
|
1
|
7
|
return $errstr; |
356
|
|
|
|
|
|
|
}# end errstr |
357
|
|
|
|
|
|
|
|
358
|
|
|
|
|
|
|
sub sort_worksheets{ |
359
|
1
|
|
|
1
|
1
|
868
|
my ($self,$type) = @_; |
360
|
1
|
|
50
|
|
|
5
|
$type ||= 'ASC'; |
361
|
|
|
|
|
|
|
|
362
|
1
|
|
|
|
|
2
|
my @title_array = map{$_->[0]}@{$self->{worksheets}}; |
|
2
|
|
|
|
|
7
|
|
|
1
|
|
|
|
|
3
|
|
363
|
1
|
50
|
|
|
|
12
|
if( _is_title_numeric(\@title_array) ){ |
364
|
0
|
|
|
|
|
0
|
@{$self->{worksheets}} = sort{$a->[0] <=> $b->[0]}@{$self->{worksheets}}; |
|
0
|
|
|
|
|
0
|
|
|
0
|
|
|
|
|
0
|
|
|
0
|
|
|
|
|
0
|
|
365
|
|
|
|
|
|
|
} |
366
|
|
|
|
|
|
|
else{ |
367
|
1
|
|
|
|
|
2
|
@{$self->{worksheets}} = sort{$a->[0] cmp $b->[0]}@{$self->{worksheets}}; |
|
1
|
|
|
|
|
2
|
|
|
1
|
|
|
|
|
6
|
|
|
1
|
|
|
|
|
7
|
|
368
|
|
|
|
|
|
|
} |
369
|
1
|
50
|
33
|
|
|
9
|
@{$self->{worksheets}} = reverse(@{$self->{worksheets}}) if($type && $type eq 'DESC'); |
|
1
|
|
|
|
|
3
|
|
|
1
|
|
|
|
|
2
|
|
370
|
1
|
|
|
|
|
1
|
return @{$self->{worksheets}}; |
|
1
|
|
|
|
|
4
|
|
371
|
|
|
|
|
|
|
}# end sort_worksheets |
372
|
|
|
|
|
|
|
|
373
|
|
|
|
|
|
|
sub _is_numeric{ |
374
|
1
|
|
|
1
|
|
2
|
my ($arref,$index) = @_; |
375
|
1
|
|
|
|
|
3
|
foreach(@$arref){ |
376
|
1
|
50
|
|
|
|
11
|
return 0 if($_->[$index] =~ /[^\d\.]/); |
377
|
|
|
|
|
|
|
} |
378
|
0
|
|
|
|
|
0
|
return 1; |
379
|
|
|
|
|
|
|
}# end _is_numeric |
380
|
|
|
|
|
|
|
|
381
|
|
|
|
|
|
|
|
382
|
|
|
|
|
|
|
sub _is_title_numeric{ |
383
|
1
|
|
|
1
|
|
2
|
my ($arref,$index) = @_; |
384
|
1
|
|
|
|
|
4
|
foreach(@$arref){ |
385
|
1
|
50
|
|
|
|
11
|
return 0 if($_ =~ /[^\d\.]/); |
386
|
|
|
|
|
|
|
} |
387
|
0
|
|
|
|
|
0
|
return 1; |
388
|
|
|
|
|
|
|
}# end _is_numeric |
389
|
|
|
|
|
|
|
|
390
|
|
|
|
|
|
|
sub _do_sort{ |
391
|
11
|
|
|
11
|
|
32
|
my ($worksheet) = @_; |
392
|
11
|
|
|
|
|
17
|
my @array = @{$worksheet->[1]->{'-data'}}; |
|
11
|
|
|
|
|
65
|
|
393
|
11
|
100
|
66
|
|
|
124
|
if(exists $worksheet->[1]->{sortstring} && |
|
|
|
100
|
|
|
|
|
394
|
|
|
|
|
|
|
defined $worksheet->[1]->{sortstring} && |
395
|
|
|
|
|
|
|
$worksheet->[1]->{sortstring} =~ /\w/){ |
396
|
4
|
|
|
|
|
24
|
$worksheet->[1]->{-data} = [sort{eval($worksheet->[1]->{sortstring})}@array]; |
|
12
|
|
|
|
|
964
|
|
397
|
|
|
|
|
|
|
} |
398
|
|
|
|
|
|
|
}# _do_sort |
399
|
|
|
|
|
|
|
|
400
|
|
|
|
|
|
|
sub output{ |
401
|
2
|
|
|
2
|
1
|
594
|
my ($self,$lines) = @_; |
402
|
2
|
|
|
|
|
9
|
my ($package,$filename,$line) = caller(); |
403
|
2
|
|
50
|
|
|
13
|
$lines ||= 32000; |
404
|
2
|
|
|
|
|
7
|
$lines =~ s/\D//g; |
405
|
2
|
|
|
|
|
8
|
my $excel = $self->_make_excel($lines); |
406
|
2
|
50
|
|
|
|
683
|
unless(defined $excel){ |
407
|
0
|
|
|
|
|
0
|
$errstr = qq~Could not create Spreadsheet at Spreadsheet::SimpleExcel output() from |
408
|
|
|
|
|
|
|
$filename line $line\n~; |
409
|
0
|
|
|
|
|
0
|
return undef; |
410
|
|
|
|
|
|
|
} |
411
|
2
|
|
|
|
|
3292
|
print "Content-type: ".$self->{type}."\n\n", |
412
|
|
|
|
|
|
|
$excel; |
413
|
|
|
|
|
|
|
}# end output |
414
|
|
|
|
|
|
|
|
415
|
|
|
|
|
|
|
sub output_as_string{ |
416
|
1
|
|
|
1
|
1
|
10
|
my ($self,$lines) = @_; |
417
|
1
|
|
|
|
|
6
|
my ($package,$filename,$line) = caller(); |
418
|
1
|
|
50
|
|
|
11
|
$lines ||= 32000; |
419
|
1
|
|
|
|
|
4
|
$lines =~ s/\D//g; |
420
|
1
|
|
|
|
|
6
|
my $excel = $self->_make_excel($lines); |
421
|
1
|
50
|
|
|
|
360
|
unless(defined $excel){ |
422
|
0
|
|
|
|
|
0
|
$errstr = qq~Could not create Spreadsheet at Spreadsheet::SimpleExcel output_to_file() from |
423
|
|
|
|
|
|
|
$filename line $line\n~; |
424
|
0
|
|
|
|
|
0
|
return undef; |
425
|
|
|
|
|
|
|
} |
426
|
1
|
|
|
|
|
7
|
return $excel; |
427
|
|
|
|
|
|
|
}# end output_as_string |
428
|
|
|
|
|
|
|
|
429
|
|
|
|
|
|
|
sub output_to_file{ |
430
|
3
|
|
|
3
|
1
|
19
|
my ($self,$filename,$lines) = @_; |
431
|
3
|
|
|
|
|
19
|
my ($package,$file,$line) = caller(); |
432
|
3
|
|
100
|
|
|
25
|
$lines ||= 32000; |
433
|
3
|
|
|
|
|
14
|
$lines =~ s/\D//g; |
434
|
3
|
50
|
|
|
|
13
|
unless($filename){ |
435
|
0
|
0
|
|
|
|
0
|
if($self->{FILE}){ |
436
|
0
|
|
|
|
|
0
|
$filename = $self->{FILE}; |
437
|
|
|
|
|
|
|
} |
438
|
|
|
|
|
|
|
else{ |
439
|
0
|
|
|
|
|
0
|
$errstr = qq~No filename specified at Spreadsheet::SimpleExcel output_to_file() from |
440
|
|
|
|
|
|
|
$file line $line\n~; |
441
|
0
|
|
|
|
|
0
|
return undef; |
442
|
|
|
|
|
|
|
} |
443
|
|
|
|
|
|
|
} |
444
|
|
|
|
|
|
|
#$filename =~ s/[^A-Za-z0-9_\.\/]//g; #/ |
445
|
3
|
|
|
|
|
16
|
my $excel = $self->_make_excel($lines); |
446
|
3
|
50
|
|
|
|
1010
|
unless(defined $excel){ |
447
|
0
|
|
|
|
|
0
|
$errstr = qq~Could not create $filename at Spreadsheet::SimpleExcel output_to_file() from |
448
|
|
|
|
|
|
|
$file line $line\n~; |
449
|
0
|
|
|
|
|
0
|
return undef; |
450
|
|
|
|
|
|
|
} |
451
|
3
|
50
|
|
|
|
282
|
open(EXCEL,">$filename") or die $!; |
452
|
3
|
|
|
|
|
15
|
binmode EXCEL; |
453
|
3
|
|
|
|
|
71
|
print EXCEL $excel; |
454
|
3
|
|
|
|
|
146
|
close EXCEL; |
455
|
3
|
|
|
|
|
23
|
return 1; |
456
|
|
|
|
|
|
|
}# end output_to_file |
457
|
|
|
|
|
|
|
|
458
|
|
|
|
|
|
|
sub output_to_XML{ |
459
|
2
|
|
|
2
|
1
|
356
|
my ($self,$filename) = @_; |
460
|
2
|
|
|
|
|
10
|
my ($package,$file,$line) = caller(); |
461
|
2
|
50
|
|
|
|
10
|
unless($filename){ |
462
|
0
|
|
|
|
|
0
|
$errstr = qq~No filename specified at Spreadsheet::SimpleExcel output_to_XML() from |
463
|
|
|
|
|
|
|
$file line $line\n~; |
464
|
0
|
|
|
|
|
0
|
return undef; |
465
|
|
|
|
|
|
|
} |
466
|
2
|
50
|
|
|
|
4
|
unless(scalar(@{$self->{worksheets}}) >= 1){ |
|
2
|
|
|
|
|
15
|
|
467
|
0
|
|
|
|
|
0
|
$errstr = qq~No worksheets in Spreadsheet~; |
468
|
0
|
|
|
|
|
0
|
return undef; |
469
|
|
|
|
|
|
|
} |
470
|
|
|
|
|
|
|
|
471
|
2
|
|
|
|
|
26
|
my $fh = IO::File->new(">$filename"); |
472
|
2
|
|
|
|
|
354
|
my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2); |
473
|
2
|
|
|
|
|
541
|
$xml->xmlDecl('UTF-8','yes'); |
474
|
2
|
|
|
|
|
222
|
$xml->startTag('workbook'); |
475
|
2
|
|
|
|
|
151
|
for my $worksheet(@{$self->{worksheets}}){ |
|
2
|
|
|
|
|
9
|
|
476
|
2
|
|
|
|
|
6
|
my $name = $worksheet->[0]; |
477
|
2
|
|
|
|
|
13
|
$name =~ s~[^\w]~_~g; |
478
|
2
|
|
|
|
|
9
|
$xml->startTag($name); |
479
|
|
|
|
|
|
|
|
480
|
2
|
|
|
|
|
104
|
my @headers; |
481
|
2
|
|
|
|
|
6
|
my @datasets = @{$worksheet->[1]->{-data}}; |
|
2
|
|
|
|
|
20
|
|
482
|
2
|
50
|
|
|
|
19
|
if(exists $worksheet->[1]->{-headers}){ |
483
|
2
|
|
|
|
|
5
|
@headers = (@{$worksheet->[1]->{-headers}}); |
|
2
|
|
|
|
|
12
|
|
484
|
2
|
|
|
|
|
6
|
for(@headers){ |
485
|
6
|
|
|
|
|
19
|
s~[^\w]~_~g; |
486
|
|
|
|
|
|
|
} |
487
|
|
|
|
|
|
|
} |
488
|
|
|
|
|
|
|
else{ |
489
|
0
|
|
|
|
|
0
|
my $var = 'A'; |
490
|
0
|
|
|
|
|
0
|
for(0..scalar(@{$datasets[0]})-1){ |
|
0
|
|
|
|
|
0
|
|
491
|
0
|
|
|
|
|
0
|
++$var; |
492
|
0
|
|
|
|
|
0
|
push(@headers,$var); |
493
|
|
|
|
|
|
|
} |
494
|
|
|
|
|
|
|
} |
495
|
2
|
|
|
|
|
6
|
my $row = 0; |
496
|
2
|
|
|
|
|
6
|
for my $data(@datasets){ |
497
|
45
|
|
|
|
|
1426
|
$xml->startTag('Row'.(++$row)); |
498
|
45
|
|
|
|
|
2256
|
for my $i(0..scalar(@$data)-1){ |
499
|
90
|
|
|
|
|
1142
|
$xml->startTag($headers[$i]); |
500
|
90
|
|
|
|
|
4484
|
$xml->characters($data->[$i]); |
501
|
90
|
|
|
|
|
1911
|
$xml->endTag($headers[$i]); |
502
|
|
|
|
|
|
|
} |
503
|
45
|
|
|
|
|
1071
|
$xml->endTag('Row'.$row); |
504
|
|
|
|
|
|
|
} |
505
|
|
|
|
|
|
|
|
506
|
2
|
|
|
|
|
75
|
$xml->endTag($name); |
507
|
|
|
|
|
|
|
} |
508
|
2
|
|
|
|
|
78
|
$xml->endTag('workbook'); |
509
|
2
|
|
|
|
|
69
|
$xml->end(); |
510
|
2
|
|
|
|
|
68
|
$fh->close(); |
511
|
|
|
|
|
|
|
}# output_to_XML |
512
|
|
|
|
|
|
|
|
513
|
|
|
|
|
|
|
sub _make_excel{ |
514
|
6
|
|
|
6
|
|
13
|
my ($self,$nr_of_lines) = @_; |
515
|
6
|
|
|
|
|
23
|
my ($package,$filename,$line) = caller(); |
516
|
6
|
|
50
|
|
|
26
|
my $c_lines = $nr_of_lines || 32000; |
517
|
6
|
50
|
|
|
|
10
|
unless(scalar(@{$self->{worksheets}}) >= 1){ |
|
6
|
|
|
|
|
40
|
|
518
|
0
|
|
|
|
|
0
|
$errstr = qq~No worksheets in Spreadsheet~; |
519
|
0
|
|
|
|
|
0
|
return undef; |
520
|
|
|
|
|
|
|
} |
521
|
6
|
|
|
|
|
10
|
my $output; |
522
|
6
|
|
|
|
|
68
|
tie(*XLS,'IO::Scalar',\$output); |
523
|
6
|
|
|
|
|
629
|
my $excel; |
524
|
6
|
50
|
|
|
|
62
|
unless($excel = new Spreadsheet::WriteExcel(\*XLS)){ |
525
|
0
|
|
|
|
|
0
|
$errstr = qq~Could not create spreadsheet object ($!) from |
526
|
|
|
|
|
|
|
$filename line $line~; |
527
|
0
|
|
|
|
|
0
|
return undef; |
528
|
|
|
|
|
|
|
} |
529
|
6
|
50
|
|
|
|
42300
|
if($self->{BIG}){ |
530
|
0
|
|
|
|
|
0
|
eval{require Spreadsheet::WriteExcel::Big}; |
|
0
|
|
|
|
|
0
|
|
531
|
0
|
0
|
|
|
|
0
|
if($@){ |
532
|
0
|
|
|
|
|
0
|
$errstr = $@; |
533
|
0
|
|
|
|
|
0
|
return undef; |
534
|
|
|
|
|
|
|
} |
535
|
0
|
0
|
|
|
|
0
|
unless($excel = new Spreadsheet::WriteExcel::Big(\*XLS)){#$fname)){ |
536
|
0
|
|
|
|
|
0
|
$errstr = qq~Could not create spreadsheet object ($!) from |
537
|
|
|
|
|
|
|
$filename line $line~; |
538
|
0
|
|
|
|
|
0
|
return undef; |
539
|
|
|
|
|
|
|
} |
540
|
|
|
|
|
|
|
} |
541
|
|
|
|
|
|
|
#else{ |
542
|
6
|
|
|
|
|
12
|
my @titles = map{$_->[0]}@{$self->{worksheets}}; |
|
11
|
|
|
|
|
45
|
|
|
6
|
|
|
|
|
27
|
|
543
|
6
|
|
|
|
|
12
|
foreach my $worksheet(@{$self->{worksheets}}){ |
|
6
|
|
|
|
|
21
|
|
544
|
11
|
|
|
|
|
71
|
my $sheet = $excel->addworksheet($worksheet->[0]); |
545
|
11
|
|
|
|
|
5013
|
_do_sort($worksheet); |
546
|
11
|
|
|
|
|
26
|
my $col = 0; |
547
|
11
|
|
|
|
|
18
|
my $row = 0; |
548
|
11
|
|
|
|
|
16
|
my $page = 2; |
549
|
11
|
|
|
|
|
85
|
_header2sheet($sheet,$worksheet->[1]->{-headers},$worksheet->[1]->{-headers_format}); |
550
|
11
|
100
|
66
|
|
|
64
|
$row++ if(exists $worksheet->[1]->{'-headers'} && scalar(@{$worksheet->[1]->{'-headers'}}) > 0); |
|
6
|
|
|
|
|
92
|
|
551
|
11
|
|
|
|
|
20
|
foreach my $data(@{$worksheet->[1]->{-data}}){ |
|
11
|
|
|
|
|
43
|
|
552
|
107
|
|
|
|
|
100
|
$col = 0; |
553
|
107
|
50
|
|
|
|
199
|
if($row >= $c_lines){ |
554
|
0
|
|
|
|
|
0
|
my $title = $worksheet->[0].'_p'.$page; |
555
|
0
|
|
|
|
|
0
|
while(grep{$_ eq $title}@titles){ |
|
0
|
|
|
|
|
0
|
|
556
|
0
|
|
|
|
|
0
|
$page++; |
557
|
0
|
|
|
|
|
0
|
$title = $worksheet->[0].'_p'.$page; |
558
|
|
|
|
|
|
|
} |
559
|
0
|
|
|
|
|
0
|
push(@titles,$title); |
560
|
0
|
|
|
|
|
0
|
$sheet = $excel->addworksheet($title); |
561
|
0
|
|
|
|
|
0
|
$row = 0; |
562
|
0
|
0
|
|
|
|
0
|
if(scalar(@{$worksheet->[1]->{'-headers'}}) > 0){ |
|
0
|
|
|
|
|
0
|
|
563
|
0
|
|
|
|
|
0
|
$row = 1; |
564
|
0
|
|
|
|
|
0
|
_header2sheet($sheet,$worksheet->[1]->{-headers},$worksheet->[1]->{-headers_format}); |
565
|
|
|
|
|
|
|
} |
566
|
|
|
|
|
|
|
} |
567
|
107
|
|
|
|
|
184
|
my $formatref = $worksheet->[1]->{-data_format}; |
568
|
107
|
|
|
|
|
140
|
foreach my $value(@$data){ |
569
|
214
|
50
|
33
|
|
|
486
|
if(defined $formatref && defined $formatref->[$col]){ |
570
|
0
|
0
|
|
|
|
0
|
if($formatref->[$col] eq 's'){ |
|
|
0
|
|
|
|
|
|
571
|
0
|
|
|
|
|
0
|
$sheet->write_string($row,$col,$value); |
572
|
|
|
|
|
|
|
} |
573
|
|
|
|
|
|
|
elsif($formatref->[$col] eq 'n'){ |
574
|
0
|
|
|
|
|
0
|
$sheet->write_number($row,$col,$value); |
575
|
|
|
|
|
|
|
} |
576
|
|
|
|
|
|
|
else{ |
577
|
0
|
|
|
|
|
0
|
$sheet->write($row,$col,$value); |
578
|
|
|
|
|
|
|
} |
579
|
|
|
|
|
|
|
} |
580
|
|
|
|
|
|
|
#elsif($value =~ /^=/){ |
581
|
|
|
|
|
|
|
# $sheet->write_string($row,$col,$value); |
582
|
|
|
|
|
|
|
#} |
583
|
|
|
|
|
|
|
else{ |
584
|
214
|
|
|
|
|
460
|
$sheet->write($row,$col,$value); |
585
|
|
|
|
|
|
|
} |
586
|
214
|
|
|
|
|
13211
|
$col++; |
587
|
|
|
|
|
|
|
} |
588
|
107
|
|
|
|
|
186
|
$row++; |
589
|
|
|
|
|
|
|
} |
590
|
|
|
|
|
|
|
} |
591
|
6
|
|
|
|
|
49
|
$excel->close(); |
592
|
|
|
|
|
|
|
#} |
593
|
6
|
|
|
|
|
54854
|
return $output; |
594
|
|
|
|
|
|
|
}# end _make_excel |
595
|
|
|
|
|
|
|
|
596
|
|
|
|
|
|
|
sub _header2sheet{ |
597
|
11
|
|
|
11
|
|
34
|
my ($sheet,$arref,$formatref) = @_; |
598
|
11
|
|
|
|
|
15
|
my $col = 0; |
599
|
11
|
|
|
|
|
42
|
foreach(@$arref){ |
600
|
14
|
50
|
33
|
|
|
62
|
unless(defined $formatref && defined $formatref->[$col]){ |
601
|
14
|
|
|
|
|
60
|
$sheet->write(0,$col,$_); |
602
|
|
|
|
|
|
|
} |
603
|
|
|
|
|
|
|
else{ |
604
|
0
|
0
|
|
|
|
0
|
if($formatref->[$col] eq 's'){ |
|
|
0
|
|
|
|
|
|
605
|
0
|
|
|
|
|
0
|
$sheet->write_string(0,$col,$_); |
606
|
|
|
|
|
|
|
} |
607
|
|
|
|
|
|
|
elsif($formatref->[$col] eq 'n'){ |
608
|
0
|
|
|
|
|
0
|
$sheet->write_number(0,$col,$_); |
609
|
|
|
|
|
|
|
} |
610
|
|
|
|
|
|
|
else{ |
611
|
0
|
|
|
|
|
0
|
$sheet->write(0,$col,$_); |
612
|
|
|
|
|
|
|
} |
613
|
|
|
|
|
|
|
} |
614
|
14
|
|
|
|
|
1721
|
$col++; |
615
|
|
|
|
|
|
|
} |
616
|
|
|
|
|
|
|
}# end _header2sheet |
617
|
|
|
|
|
|
|
|
618
|
|
|
|
|
|
|
sub sheets{ |
619
|
4
|
|
|
4
|
1
|
22
|
my ($self) = @_; |
620
|
4
|
|
|
|
|
7
|
my @titles = map{$_->[0]}@{$self->{worksheets}}; |
|
8
|
|
|
|
|
23
|
|
|
4
|
|
|
|
|
12
|
|
621
|
4
|
100
|
|
|
|
20
|
return wantarray ? @titles : \@titles; |
622
|
|
|
|
|
|
|
}# end sheets |
623
|
|
|
|
|
|
|
|
624
|
|
|
|
|
|
|
1; |
625
|
|
|
|
|
|
|
|
626
|
|
|
|
|
|
|
__END__ |