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