line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
|
2
|
|
|
|
|
|
|
############################################################################### |
3
|
|
|
|
|
|
|
# |
4
|
|
|
|
|
|
|
# Workbook - A class for writing Excel Workbooks. |
5
|
|
|
|
|
|
|
# |
6
|
|
|
|
|
|
|
# |
7
|
|
|
|
|
|
|
# Used in conjunction with Excel::Writer::XLSX |
8
|
|
|
|
|
|
|
# |
9
|
|
|
|
|
|
|
# Copyright 2000-2021, John McNamara, jmcnamara@cpan.org |
10
|
|
|
|
|
|
|
# |
11
|
|
|
|
|
|
|
# Documentation after __END__ |
12
|
|
|
|
|
|
|
# |
13
|
|
|
|
|
|
|
|
14
|
|
|
|
|
|
|
# perltidy with the following options: -mbl=2 -pt=0 -nola |
15
|
|
|
|
|
|
|
|
16
|
|
|
|
|
|
|
use 5.008002; |
17
|
1124
|
|
|
1124
|
|
14976
|
use strict; |
|
1124
|
|
|
|
|
3119
|
|
18
|
1124
|
|
|
1124
|
|
4549
|
use warnings; |
|
1124
|
|
|
|
|
1870
|
|
|
1124
|
|
|
|
|
18083
|
|
19
|
1124
|
|
|
1124
|
|
4532
|
use Carp; |
|
1124
|
|
|
|
|
2205
|
|
|
1124
|
|
|
|
|
24149
|
|
20
|
1124
|
|
|
1124
|
|
5323
|
use IO::File; |
|
1124
|
|
|
|
|
2080
|
|
|
1124
|
|
|
|
|
57986
|
|
21
|
1124
|
|
|
1124
|
|
407765
|
use File::Find; |
|
1124
|
|
|
|
|
7410708
|
|
|
1124
|
|
|
|
|
112017
|
|
22
|
1124
|
|
|
1124
|
|
8284
|
use File::Temp qw(tempfile); |
|
1124
|
|
|
|
|
2147
|
|
|
1124
|
|
|
|
|
60662
|
|
23
|
1124
|
|
|
1124
|
|
667757
|
use File::Basename 'fileparse'; |
|
1124
|
|
|
|
|
11936793
|
|
|
1124
|
|
|
|
|
61521
|
|
24
|
1124
|
|
|
1124
|
|
7527
|
use Archive::Zip; |
|
1124
|
|
|
|
|
2119
|
|
|
1124
|
|
|
|
|
71452
|
|
25
|
1124
|
|
|
1124
|
|
557795
|
use Digest::MD5 qw(md5_hex); |
|
1124
|
|
|
|
|
54673593
|
|
|
1124
|
|
|
|
|
46096
|
|
26
|
1124
|
|
|
1124
|
|
8130
|
use Excel::Writer::XLSX::Worksheet; |
|
1124
|
|
|
|
|
2226
|
|
|
1124
|
|
|
|
|
56886
|
|
27
|
1124
|
|
|
1124
|
|
1253512
|
use Excel::Writer::XLSX::Chartsheet; |
|
1124
|
|
|
|
|
5863
|
|
|
1124
|
|
|
|
|
97750
|
|
28
|
1124
|
|
|
1124
|
|
524436
|
use Excel::Writer::XLSX::Format; |
|
1124
|
|
|
|
|
3717
|
|
|
1124
|
|
|
|
|
46772
|
|
29
|
1124
|
|
|
1124
|
|
7648
|
use Excel::Writer::XLSX::Shape; |
|
1124
|
|
|
|
|
2740
|
|
|
1124
|
|
|
|
|
32901
|
|
30
|
1124
|
|
|
1124
|
|
408324
|
use Excel::Writer::XLSX::Chart; |
|
1124
|
|
|
|
|
2656
|
|
|
1124
|
|
|
|
|
46859
|
|
31
|
1124
|
|
|
1124
|
|
1026368
|
use Excel::Writer::XLSX::Package::Packager; |
|
1124
|
|
|
|
|
3158
|
|
|
1124
|
|
|
|
|
65359
|
|
32
|
1124
|
|
|
1124
|
|
537761
|
use Excel::Writer::XLSX::Package::XMLwriter; |
|
1124
|
|
|
|
|
3653
|
|
|
1124
|
|
|
|
|
57323
|
|
33
|
1124
|
|
|
1124
|
|
8512
|
use Excel::Writer::XLSX::Utility qw(xl_cell_to_rowcol xl_rowcol_to_cell); |
|
1124
|
|
|
|
|
2025
|
|
|
1124
|
|
|
|
|
32757
|
|
34
|
1124
|
|
|
1124
|
|
5342
|
|
|
1124
|
|
|
|
|
2002
|
|
|
1124
|
|
|
|
|
9283465
|
|
35
|
|
|
|
|
|
|
our @ISA = qw(Excel::Writer::XLSX::Package::XMLwriter); |
36
|
|
|
|
|
|
|
our $VERSION = '1.09'; |
37
|
|
|
|
|
|
|
|
38
|
|
|
|
|
|
|
|
39
|
|
|
|
|
|
|
############################################################################### |
40
|
|
|
|
|
|
|
# |
41
|
|
|
|
|
|
|
# Public and private API methods. |
42
|
|
|
|
|
|
|
# |
43
|
|
|
|
|
|
|
############################################################################### |
44
|
|
|
|
|
|
|
|
45
|
|
|
|
|
|
|
|
46
|
|
|
|
|
|
|
############################################################################### |
47
|
|
|
|
|
|
|
# |
48
|
|
|
|
|
|
|
# new() |
49
|
|
|
|
|
|
|
# |
50
|
|
|
|
|
|
|
# Constructor. |
51
|
|
|
|
|
|
|
# |
52
|
|
|
|
|
|
|
|
53
|
|
|
|
|
|
|
my $class = shift; |
54
|
|
|
|
|
|
|
my $self = Excel::Writer::XLSX::Package::XMLwriter->new(); |
55
|
892
|
|
|
892
|
0
|
2157
|
|
56
|
892
|
|
|
|
|
7080
|
$self->{_filename} = $_[0] || ''; |
57
|
|
|
|
|
|
|
my $options = $_[1] || {}; |
58
|
892
|
|
50
|
|
|
6855
|
|
59
|
892
|
|
100
|
|
|
5347
|
$self->{_tempdir} = undef; |
60
|
|
|
|
|
|
|
$self->{_date_1904} = 0; |
61
|
892
|
|
|
|
|
2464
|
$self->{_activesheet} = 0; |
62
|
892
|
|
|
|
|
2189
|
$self->{_firstsheet} = 0; |
63
|
892
|
|
|
|
|
2043
|
$self->{_selected} = 0; |
64
|
892
|
|
|
|
|
2044
|
$self->{_fileclosed} = 0; |
65
|
892
|
|
|
|
|
2012
|
$self->{_filehandle} = undef; |
66
|
892
|
|
|
|
|
2480
|
$self->{_internal_fh} = 0; |
67
|
892
|
|
|
|
|
1940
|
$self->{_sheet_name} = 'Sheet'; |
68
|
892
|
|
|
|
|
1974
|
$self->{_chart_name} = 'Chart'; |
69
|
892
|
|
|
|
|
2061
|
$self->{_sheetname_count} = 0; |
70
|
892
|
|
|
|
|
2221
|
$self->{_chartname_count} = 0; |
71
|
892
|
|
|
|
|
1866
|
$self->{_worksheets} = []; |
72
|
892
|
|
|
|
|
1950
|
$self->{_charts} = []; |
73
|
892
|
|
|
|
|
2200
|
$self->{_drawings} = []; |
74
|
892
|
|
|
|
|
2829
|
$self->{_sheetnames} = {}; |
75
|
892
|
|
|
|
|
2377
|
$self->{_formats} = []; |
76
|
892
|
|
|
|
|
2263
|
$self->{_xf_formats} = []; |
77
|
892
|
|
|
|
|
2186
|
$self->{_xf_format_indices} = {}; |
78
|
892
|
|
|
|
|
2196
|
$self->{_dxf_formats} = []; |
79
|
892
|
|
|
|
|
2223
|
$self->{_dxf_format_indices} = {}; |
80
|
892
|
|
|
|
|
2141
|
$self->{_palette} = []; |
81
|
892
|
|
|
|
|
2182
|
$self->{_font_count} = 0; |
82
|
892
|
|
|
|
|
2146
|
$self->{_num_format_count} = 0; |
83
|
892
|
|
|
|
|
1992
|
$self->{_defined_names} = []; |
84
|
892
|
|
|
|
|
2055
|
$self->{_named_ranges} = []; |
85
|
892
|
|
|
|
|
2142
|
$self->{_custom_colors} = []; |
86
|
892
|
|
|
|
|
2202
|
$self->{_doc_properties} = {}; |
87
|
892
|
|
|
|
|
2161
|
$self->{_custom_properties} = []; |
88
|
892
|
|
|
|
|
2339
|
$self->{_createtime} = [ gmtime() ]; |
89
|
892
|
|
|
|
|
2214
|
$self->{_num_vml_files} = 0; |
90
|
892
|
|
|
|
|
7758
|
$self->{_num_comment_files} = 0; |
91
|
892
|
|
|
|
|
2477
|
$self->{_optimization} = 0; |
92
|
892
|
|
|
|
|
2004
|
$self->{_x_window} = 240; |
93
|
892
|
|
|
|
|
2083
|
$self->{_y_window} = 15; |
94
|
892
|
|
|
|
|
1888
|
$self->{_window_width} = 16095; |
95
|
892
|
|
|
|
|
1800
|
$self->{_window_height} = 9660; |
96
|
892
|
|
|
|
|
1836
|
$self->{_tab_ratio} = 600; |
97
|
892
|
|
|
|
|
1770
|
$self->{_excel2003_style} = 0; |
98
|
892
|
|
|
|
|
1785
|
$self->{_max_url_length} = 2079; |
99
|
892
|
|
|
|
|
1921
|
$self->{_has_comments} = 0; |
100
|
892
|
|
|
|
|
2302
|
$self->{_read_only} = 0; |
101
|
892
|
|
|
|
|
1893
|
$self->{_has_metadata} = 0; |
102
|
892
|
|
|
|
|
1855
|
|
103
|
892
|
|
|
|
|
1917
|
$self->{_default_format_properties} = {}; |
104
|
|
|
|
|
|
|
|
105
|
892
|
|
|
|
|
2025
|
if ( exists $options->{tempdir} ) { |
106
|
|
|
|
|
|
|
$self->{_tempdir} = $options->{tempdir}; |
107
|
892
|
50
|
|
|
|
3146
|
} |
108
|
0
|
|
|
|
|
0
|
|
109
|
|
|
|
|
|
|
if ( exists $options->{date_1904} ) { |
110
|
|
|
|
|
|
|
$self->{_date_1904} = $options->{date_1904}; |
111
|
892
|
50
|
|
|
|
2977
|
} |
112
|
0
|
|
|
|
|
0
|
|
113
|
|
|
|
|
|
|
if ( exists $options->{optimization} ) { |
114
|
|
|
|
|
|
|
$self->{_optimization} = $options->{optimization}; |
115
|
892
|
50
|
|
|
|
2889
|
} |
116
|
0
|
|
|
|
|
0
|
|
117
|
|
|
|
|
|
|
if ( exists $options->{default_format_properties} ) { |
118
|
|
|
|
|
|
|
$self->{_default_format_properties} = |
119
|
892
|
100
|
|
|
|
2840
|
$options->{default_format_properties}; |
120
|
|
|
|
|
|
|
} |
121
|
1
|
|
|
|
|
3
|
|
122
|
|
|
|
|
|
|
if ( exists $options->{excel2003_style} ) { |
123
|
|
|
|
|
|
|
$self->{_excel2003_style} = 1; |
124
|
892
|
100
|
|
|
|
3849
|
} |
125
|
8
|
|
|
|
|
23
|
|
126
|
|
|
|
|
|
|
if ( exists $options->{max_url_length} ) { |
127
|
|
|
|
|
|
|
$self->{_max_url_length} = $options->{max_url_length}; |
128
|
892
|
100
|
|
|
|
3055
|
|
129
|
1
|
|
|
|
|
3
|
if ($self->{_max_url_length} < 255) { |
130
|
|
|
|
|
|
|
$self->{_max_url_length} = 2079; |
131
|
1
|
50
|
|
|
|
3
|
} |
132
|
0
|
|
|
|
|
0
|
} |
133
|
|
|
|
|
|
|
|
134
|
|
|
|
|
|
|
# Structures for the shared strings data. |
135
|
|
|
|
|
|
|
$self->{_str_total} = 0; |
136
|
|
|
|
|
|
|
$self->{_str_unique} = 0; |
137
|
892
|
|
|
|
|
2311
|
$self->{_str_table} = {}; |
138
|
892
|
|
|
|
|
2009
|
$self->{_str_array} = []; |
139
|
892
|
|
|
|
|
2082
|
|
140
|
892
|
|
|
|
|
2122
|
# Formula calculation default settings. |
141
|
|
|
|
|
|
|
$self->{_calc_id} = 124519; |
142
|
|
|
|
|
|
|
$self->{_calc_mode} = 'auto'; |
143
|
892
|
|
|
|
|
1952
|
$self->{_calc_on_load} = 1; |
144
|
892
|
|
|
|
|
2043
|
|
145
|
892
|
|
|
|
|
1858
|
|
146
|
|
|
|
|
|
|
bless $self, $class; |
147
|
|
|
|
|
|
|
|
148
|
892
|
|
|
|
|
2148
|
# Add the default cell format. |
149
|
|
|
|
|
|
|
if ( $self->{_excel2003_style} ) { |
150
|
|
|
|
|
|
|
$self->add_format( xf_index => 0, font_family => 0 ); |
151
|
892
|
100
|
|
|
|
5707
|
} |
152
|
8
|
|
|
|
|
35
|
else { |
153
|
|
|
|
|
|
|
$self->add_format( xf_index => 0 ); |
154
|
|
|
|
|
|
|
} |
155
|
884
|
|
|
|
|
4106
|
|
156
|
|
|
|
|
|
|
# Add a default URL format. |
157
|
|
|
|
|
|
|
$self->{_default_url_format} = $self->add_format( hyperlink => 1 ); |
158
|
|
|
|
|
|
|
|
159
|
892
|
|
|
|
|
3426
|
# Check for a filename unless it is an existing filehandle |
160
|
|
|
|
|
|
|
if ( not ref $self->{_filename} and $self->{_filename} eq '' ) { |
161
|
|
|
|
|
|
|
carp 'Filename required by Excel::Writer::XLSX->new()'; |
162
|
892
|
50
|
66
|
|
|
6445
|
return undef; |
163
|
0
|
|
|
|
|
0
|
} |
164
|
0
|
|
|
|
|
0
|
|
165
|
|
|
|
|
|
|
|
166
|
|
|
|
|
|
|
# If filename is a reference we assume that it is a valid filehandle. |
167
|
|
|
|
|
|
|
if ( ref $self->{_filename} ) { |
168
|
|
|
|
|
|
|
|
169
|
892
|
100
|
|
|
|
4931
|
$self->{_filehandle} = $self->{_filename}; |
|
|
50
|
|
|
|
|
|
170
|
|
|
|
|
|
|
$self->{_internal_fh} = 0; |
171
|
55
|
|
|
|
|
151
|
} |
172
|
55
|
|
|
|
|
118
|
elsif ( $self->{_filename} eq '-' ) { |
173
|
|
|
|
|
|
|
|
174
|
|
|
|
|
|
|
# Support special filename/filehandle '-' for backward compatibility. |
175
|
|
|
|
|
|
|
binmode STDOUT; |
176
|
|
|
|
|
|
|
$self->{_filehandle} = \*STDOUT; |
177
|
0
|
|
|
|
|
0
|
$self->{_internal_fh} = 0; |
178
|
0
|
|
|
|
|
0
|
} |
179
|
0
|
|
|
|
|
0
|
else { |
180
|
|
|
|
|
|
|
my $fh = IO::File->new( $self->{_filename}, 'w' ); |
181
|
|
|
|
|
|
|
|
182
|
837
|
|
|
|
|
7064
|
return undef unless defined $fh; |
183
|
|
|
|
|
|
|
|
184
|
837
|
50
|
|
|
|
173543
|
$self->{_filehandle} = $fh; |
185
|
|
|
|
|
|
|
$self->{_internal_fh} = 1; |
186
|
837
|
|
|
|
|
2562
|
} |
187
|
837
|
|
|
|
|
2309
|
|
188
|
|
|
|
|
|
|
|
189
|
|
|
|
|
|
|
# Set colour palette. |
190
|
|
|
|
|
|
|
$self->set_color_palette(); |
191
|
|
|
|
|
|
|
|
192
|
892
|
|
|
|
|
4486
|
return $self; |
193
|
|
|
|
|
|
|
} |
194
|
892
|
|
|
|
|
2899
|
|
195
|
|
|
|
|
|
|
|
196
|
|
|
|
|
|
|
############################################################################### |
197
|
|
|
|
|
|
|
# |
198
|
|
|
|
|
|
|
# _assemble_xml_file() |
199
|
|
|
|
|
|
|
# |
200
|
|
|
|
|
|
|
# Assemble and write the XML file. |
201
|
|
|
|
|
|
|
# |
202
|
|
|
|
|
|
|
|
203
|
|
|
|
|
|
|
my $self = shift; |
204
|
|
|
|
|
|
|
|
205
|
|
|
|
|
|
|
# Prepare format object for passing to Style.pm. |
206
|
895
|
|
|
895
|
|
2076
|
$self->_prepare_format_properties(); |
207
|
|
|
|
|
|
|
|
208
|
|
|
|
|
|
|
$self->xml_declaration; |
209
|
895
|
|
|
|
|
5108
|
|
210
|
|
|
|
|
|
|
# Write the root workbook element. |
211
|
895
|
|
|
|
|
6952
|
$self->_write_workbook(); |
212
|
|
|
|
|
|
|
|
213
|
|
|
|
|
|
|
# Write the XLSX file version. |
214
|
895
|
|
|
|
|
6354
|
$self->_write_file_version(); |
215
|
|
|
|
|
|
|
|
216
|
|
|
|
|
|
|
# Write the fileSharing element. |
217
|
895
|
|
|
|
|
5238
|
$self->_write_file_sharing(); |
218
|
|
|
|
|
|
|
|
219
|
|
|
|
|
|
|
# Write the workbook properties. |
220
|
895
|
|
|
|
|
4838
|
$self->_write_workbook_pr(); |
221
|
|
|
|
|
|
|
|
222
|
|
|
|
|
|
|
# Write the workbook view properties. |
223
|
895
|
|
|
|
|
5386
|
$self->_write_book_views(); |
224
|
|
|
|
|
|
|
|
225
|
|
|
|
|
|
|
# Write the worksheet names and ids. |
226
|
895
|
|
|
|
|
4746
|
$self->_write_sheets(); |
227
|
|
|
|
|
|
|
|
228
|
|
|
|
|
|
|
# Write the workbook defined names. |
229
|
895
|
|
|
|
|
4773
|
$self->_write_defined_names(); |
230
|
|
|
|
|
|
|
|
231
|
|
|
|
|
|
|
# Write the workbook calculation properties. |
232
|
895
|
|
|
|
|
5496
|
$self->_write_calc_pr(); |
233
|
|
|
|
|
|
|
|
234
|
|
|
|
|
|
|
# Write the workbook extension storage. |
235
|
895
|
|
|
|
|
4788
|
#$self->_write_ext_lst(); |
236
|
|
|
|
|
|
|
|
237
|
|
|
|
|
|
|
# Close the workbook tag. |
238
|
|
|
|
|
|
|
$self->xml_end_tag( 'workbook' ); |
239
|
|
|
|
|
|
|
|
240
|
|
|
|
|
|
|
# Close the XML writer filehandle. |
241
|
895
|
|
|
|
|
3282
|
$self->xml_get_fh()->close(); |
242
|
|
|
|
|
|
|
} |
243
|
|
|
|
|
|
|
|
244
|
895
|
|
|
|
|
4568
|
|
245
|
|
|
|
|
|
|
############################################################################### |
246
|
|
|
|
|
|
|
# |
247
|
|
|
|
|
|
|
# close() |
248
|
|
|
|
|
|
|
# |
249
|
|
|
|
|
|
|
# Calls finalization methods. |
250
|
|
|
|
|
|
|
# |
251
|
|
|
|
|
|
|
|
252
|
|
|
|
|
|
|
my $self = shift; |
253
|
|
|
|
|
|
|
|
254
|
|
|
|
|
|
|
# In case close() is called twice, by user and by DESTROY. |
255
|
|
|
|
|
|
|
return if $self->{_fileclosed}; |
256
|
892
|
|
|
892
|
0
|
9178
|
|
257
|
|
|
|
|
|
|
# Test filehandle in case new() failed and the user didn't check. |
258
|
|
|
|
|
|
|
return undef if !defined $self->{_filehandle}; |
259
|
892
|
50
|
|
|
|
3715
|
|
260
|
|
|
|
|
|
|
$self->{_fileclosed} = 1; |
261
|
|
|
|
|
|
|
$self->_store_workbook(); |
262
|
892
|
50
|
|
|
|
3563
|
|
263
|
|
|
|
|
|
|
# Return the file close value. |
264
|
892
|
|
|
|
|
2084
|
if ( $self->{_internal_fh} ) { |
265
|
892
|
|
|
|
|
6118
|
return $self->{_filehandle}->close(); |
266
|
|
|
|
|
|
|
} |
267
|
|
|
|
|
|
|
else { |
268
|
892
|
100
|
|
|
|
15585363
|
# Return true and let users deal with their own filehandles. |
269
|
837
|
|
|
|
|
5370
|
return 1; |
270
|
|
|
|
|
|
|
} |
271
|
|
|
|
|
|
|
} |
272
|
|
|
|
|
|
|
|
273
|
55
|
|
|
|
|
2563
|
|
274
|
|
|
|
|
|
|
############################################################################### |
275
|
|
|
|
|
|
|
# |
276
|
|
|
|
|
|
|
# DESTROY() |
277
|
|
|
|
|
|
|
# |
278
|
|
|
|
|
|
|
# Close the workbook if it hasn't already been explicitly closed. |
279
|
|
|
|
|
|
|
# |
280
|
|
|
|
|
|
|
|
281
|
|
|
|
|
|
|
my $self = shift; |
282
|
|
|
|
|
|
|
|
283
|
|
|
|
|
|
|
local ( $@, $!, $^E, $? ); |
284
|
|
|
|
|
|
|
|
285
|
|
|
|
|
|
|
$self->close() if not $self->{_fileclosed}; |
286
|
892
|
|
|
892
|
|
27156676
|
} |
287
|
|
|
|
|
|
|
|
288
|
892
|
|
|
|
|
6874
|
|
289
|
|
|
|
|
|
|
############################################################################### |
290
|
892
|
100
|
|
|
|
66733
|
# |
291
|
|
|
|
|
|
|
# sheets(slice,...) |
292
|
|
|
|
|
|
|
# |
293
|
|
|
|
|
|
|
# An accessor for the _worksheets[] array |
294
|
|
|
|
|
|
|
# |
295
|
|
|
|
|
|
|
# Returns: an optionally sliced list of the worksheet objects in a workbook. |
296
|
|
|
|
|
|
|
# |
297
|
|
|
|
|
|
|
|
298
|
|
|
|
|
|
|
my $self = shift; |
299
|
|
|
|
|
|
|
|
300
|
|
|
|
|
|
|
if ( @_ ) { |
301
|
|
|
|
|
|
|
|
302
|
|
|
|
|
|
|
# Return a slice of the array |
303
|
|
|
|
|
|
|
return @{ $self->{_worksheets} }[@_]; |
304
|
11
|
|
|
11
|
0
|
54
|
} |
305
|
|
|
|
|
|
|
else { |
306
|
11
|
50
|
|
|
|
42
|
|
307
|
|
|
|
|
|
|
# Return the entire list |
308
|
|
|
|
|
|
|
return @{ $self->{_worksheets} }; |
309
|
0
|
|
|
|
|
0
|
} |
|
0
|
|
|
|
|
0
|
|
310
|
|
|
|
|
|
|
} |
311
|
|
|
|
|
|
|
|
312
|
|
|
|
|
|
|
|
313
|
|
|
|
|
|
|
############################################################################### |
314
|
11
|
|
|
|
|
17
|
# |
|
11
|
|
|
|
|
78
|
|
315
|
|
|
|
|
|
|
# get_worksheet_by_name(name) |
316
|
|
|
|
|
|
|
# |
317
|
|
|
|
|
|
|
# Return a worksheet object in the workbook using the sheetname. |
318
|
|
|
|
|
|
|
# |
319
|
|
|
|
|
|
|
|
320
|
|
|
|
|
|
|
my $self = shift; |
321
|
|
|
|
|
|
|
my $sheetname = shift; |
322
|
|
|
|
|
|
|
|
323
|
|
|
|
|
|
|
return undef if not defined $sheetname; |
324
|
|
|
|
|
|
|
|
325
|
|
|
|
|
|
|
return $self->{_sheetnames}->{$sheetname}; |
326
|
|
|
|
|
|
|
} |
327
|
4
|
|
|
4
|
0
|
1190
|
|
328
|
4
|
|
|
|
|
7
|
|
329
|
|
|
|
|
|
|
############################################################################### |
330
|
4
|
100
|
|
|
|
9
|
# |
331
|
|
|
|
|
|
|
# worksheets() |
332
|
3
|
|
|
|
|
7
|
# |
333
|
|
|
|
|
|
|
# An accessor for the _worksheets[] array. |
334
|
|
|
|
|
|
|
# This method is now deprecated. Use the sheets() method instead. |
335
|
|
|
|
|
|
|
# |
336
|
|
|
|
|
|
|
# Returns: an array reference |
337
|
|
|
|
|
|
|
# |
338
|
|
|
|
|
|
|
|
339
|
|
|
|
|
|
|
my $self = shift; |
340
|
|
|
|
|
|
|
|
341
|
|
|
|
|
|
|
return $self->{_worksheets}; |
342
|
|
|
|
|
|
|
} |
343
|
|
|
|
|
|
|
|
344
|
|
|
|
|
|
|
|
345
|
|
|
|
|
|
|
############################################################################### |
346
|
|
|
|
|
|
|
# |
347
|
0
|
|
|
0
|
0
|
0
|
# add_worksheet($name) |
348
|
|
|
|
|
|
|
# |
349
|
0
|
|
|
|
|
0
|
# Add a new worksheet to the Excel workbook. |
350
|
|
|
|
|
|
|
# |
351
|
|
|
|
|
|
|
# Returns: reference to a worksheet object |
352
|
|
|
|
|
|
|
# |
353
|
|
|
|
|
|
|
|
354
|
|
|
|
|
|
|
my $self = shift; |
355
|
|
|
|
|
|
|
my $index = @{ $self->{_worksheets} }; |
356
|
|
|
|
|
|
|
my $name = $self->_check_sheetname( $_[0] ); |
357
|
|
|
|
|
|
|
my $fh = undef; |
358
|
|
|
|
|
|
|
|
359
|
|
|
|
|
|
|
# Porters take note, the following scheme of passing references to Workbook |
360
|
|
|
|
|
|
|
# data (in the \$self->{_foo} cases) instead of a reference to the Workbook |
361
|
|
|
|
|
|
|
# itself is a workaround to avoid circular references between Workbook and |
362
|
|
|
|
|
|
|
# Worksheet objects. Feel free to implement this in any way the suits your |
363
|
1033
|
|
|
1033
|
0
|
12547
|
# language. |
364
|
1033
|
|
|
|
|
1862
|
# |
|
1033
|
|
|
|
|
6465
|
|
365
|
1033
|
|
|
|
|
7232
|
my @init_data = ( |
366
|
1021
|
|
|
|
|
2560
|
$fh, |
367
|
|
|
|
|
|
|
$name, |
368
|
|
|
|
|
|
|
$index, |
369
|
|
|
|
|
|
|
|
370
|
|
|
|
|
|
|
\$self->{_activesheet}, |
371
|
|
|
|
|
|
|
\$self->{_firstsheet}, |
372
|
|
|
|
|
|
|
|
373
|
|
|
|
|
|
|
\$self->{_str_total}, |
374
|
|
|
|
|
|
|
\$self->{_str_unique}, |
375
|
|
|
|
|
|
|
\$self->{_str_table}, |
376
|
|
|
|
|
|
|
|
377
|
|
|
|
|
|
|
$self->{_date_1904}, |
378
|
|
|
|
|
|
|
$self->{_palette}, |
379
|
|
|
|
|
|
|
$self->{_optimization}, |
380
|
|
|
|
|
|
|
$self->{_tempdir}, |
381
|
|
|
|
|
|
|
$self->{_excel2003_style}, |
382
|
|
|
|
|
|
|
$self->{_default_url_format}, |
383
|
|
|
|
|
|
|
$self->{_max_url_length}, |
384
|
|
|
|
|
|
|
); |
385
|
|
|
|
|
|
|
|
386
|
|
|
|
|
|
|
my $worksheet = Excel::Writer::XLSX::Worksheet->new( @init_data ); |
387
|
|
|
|
|
|
|
$self->{_worksheets}->[$index] = $worksheet; |
388
|
|
|
|
|
|
|
$self->{_sheetnames}->{$name} = $worksheet; |
389
|
|
|
|
|
|
|
|
390
|
|
|
|
|
|
|
return $worksheet; |
391
|
|
|
|
|
|
|
} |
392
|
|
|
|
|
|
|
|
393
|
1021
|
|
|
|
|
6210
|
|
394
|
|
|
|
|
|
|
############################################################################### |
395
|
1021
|
|
|
|
|
8366
|
# |
396
|
1021
|
|
|
|
|
2704
|
# add_chart( %args ) |
397
|
1021
|
|
|
|
|
2880
|
# |
398
|
|
|
|
|
|
|
# Create a chart for embedding or as a new sheet. |
399
|
1021
|
|
|
|
|
3478
|
# |
400
|
|
|
|
|
|
|
|
401
|
|
|
|
|
|
|
my $self = shift; |
402
|
|
|
|
|
|
|
my %arg = @_; |
403
|
|
|
|
|
|
|
my $name = ''; |
404
|
|
|
|
|
|
|
my $index = @{ $self->{_worksheets} }; |
405
|
|
|
|
|
|
|
my $fh = undef; |
406
|
|
|
|
|
|
|
|
407
|
|
|
|
|
|
|
# Type must be specified so we can create the required chart instance. |
408
|
|
|
|
|
|
|
my $type = $arg{type}; |
409
|
|
|
|
|
|
|
if ( !defined $type ) { |
410
|
|
|
|
|
|
|
croak "Must define chart type in add_chart()"; |
411
|
437
|
|
|
437
|
0
|
3291
|
} |
412
|
437
|
|
|
|
|
1836
|
|
413
|
437
|
|
|
|
|
916
|
# Ensure that the chart defaults to non embedded. |
414
|
437
|
|
|
|
|
786
|
my $embedded = $arg{embedded} || 0; |
|
437
|
|
|
|
|
1116
|
|
415
|
437
|
|
|
|
|
819
|
|
416
|
|
|
|
|
|
|
# Check the worksheet name for non-embedded charts. |
417
|
|
|
|
|
|
|
if ( !$embedded ) { |
418
|
437
|
|
|
|
|
920
|
$name = $self->_check_sheetname( $arg{name}, 1 ); |
419
|
437
|
50
|
|
|
|
1401
|
} |
420
|
0
|
|
|
|
|
0
|
|
421
|
|
|
|
|
|
|
|
422
|
|
|
|
|
|
|
my @init_data = ( |
423
|
|
|
|
|
|
|
|
424
|
437
|
|
100
|
|
|
1414
|
$fh, |
425
|
|
|
|
|
|
|
$name, |
426
|
|
|
|
|
|
|
$index, |
427
|
437
|
100
|
|
|
|
1377
|
|
428
|
20
|
|
|
|
|
85
|
\$self->{_activesheet}, |
429
|
|
|
|
|
|
|
\$self->{_firstsheet}, |
430
|
|
|
|
|
|
|
|
431
|
|
|
|
|
|
|
\$self->{_str_total}, |
432
|
|
|
|
|
|
|
\$self->{_str_unique}, |
433
|
|
|
|
|
|
|
\$self->{_str_table}, |
434
|
|
|
|
|
|
|
|
435
|
|
|
|
|
|
|
$self->{_date_1904}, |
436
|
|
|
|
|
|
|
$self->{_palette}, |
437
|
|
|
|
|
|
|
$self->{_optimization}, |
438
|
|
|
|
|
|
|
); |
439
|
|
|
|
|
|
|
|
440
|
|
|
|
|
|
|
|
441
|
|
|
|
|
|
|
my $chart = Excel::Writer::XLSX::Chart->factory( $type, $arg{subtype} ); |
442
|
|
|
|
|
|
|
|
443
|
|
|
|
|
|
|
# If the chart isn't embedded let the workbook control it. |
444
|
|
|
|
|
|
|
if ( !$embedded ) { |
445
|
|
|
|
|
|
|
|
446
|
|
|
|
|
|
|
my $drawing = Excel::Writer::XLSX::Drawing->new(); |
447
|
|
|
|
|
|
|
my $chartsheet = Excel::Writer::XLSX::Chartsheet->new( @init_data ); |
448
|
437
|
|
|
|
|
2488
|
|
449
|
|
|
|
|
|
|
$chart->{_palette} = $self->{_palette}; |
450
|
|
|
|
|
|
|
|
451
|
437
|
|
|
|
|
3615
|
$chartsheet->{_chart} = $chart; |
452
|
|
|
|
|
|
|
$chartsheet->{_drawing} = $drawing; |
453
|
|
|
|
|
|
|
|
454
|
437
|
100
|
|
|
|
1700
|
$self->{_worksheets}->[$index] = $chartsheet; |
455
|
|
|
|
|
|
|
$self->{_sheetnames}->{$name} = $chartsheet; |
456
|
20
|
|
|
|
|
147
|
|
457
|
20
|
|
|
|
|
146
|
push @{ $self->{_charts} }, $chart; |
458
|
|
|
|
|
|
|
|
459
|
20
|
|
|
|
|
130
|
return $chartsheet; |
460
|
|
|
|
|
|
|
} |
461
|
20
|
|
|
|
|
84
|
else { |
462
|
20
|
|
|
|
|
52
|
|
463
|
|
|
|
|
|
|
# Set the embedded chart name if present. |
464
|
20
|
|
|
|
|
45
|
$chart->{_chart_name} = $arg{name} if $arg{name}; |
465
|
20
|
|
|
|
|
46
|
|
466
|
|
|
|
|
|
|
# Set index to 0 so that the activate() and set_first_sheet() methods |
467
|
20
|
|
|
|
|
36
|
# point back to the first worksheet if used for embedded charts. |
|
20
|
|
|
|
|
59
|
|
468
|
|
|
|
|
|
|
$chart->{_index} = 0; |
469
|
20
|
|
|
|
|
92
|
$chart->{_palette} = $self->{_palette}; |
470
|
|
|
|
|
|
|
$chart->_set_embedded_config_data(); |
471
|
|
|
|
|
|
|
push @{ $self->{_charts} }, $chart; |
472
|
|
|
|
|
|
|
|
473
|
|
|
|
|
|
|
return $chart; |
474
|
417
|
100
|
|
|
|
1388
|
} |
475
|
|
|
|
|
|
|
|
476
|
|
|
|
|
|
|
} |
477
|
|
|
|
|
|
|
|
478
|
417
|
|
|
|
|
2165
|
|
479
|
417
|
|
|
|
|
1005
|
############################################################################### |
480
|
417
|
|
|
|
|
2708
|
# |
481
|
417
|
|
|
|
|
630
|
# _check_sheetname( $name ) |
|
417
|
|
|
|
|
1376
|
|
482
|
|
|
|
|
|
|
# |
483
|
417
|
|
|
|
|
2112
|
# Check for valid worksheet names. We check the length, if it contains any |
484
|
|
|
|
|
|
|
# invalid characters and if the name is unique in the workbook. |
485
|
|
|
|
|
|
|
# |
486
|
|
|
|
|
|
|
|
487
|
|
|
|
|
|
|
my $self = shift; |
488
|
|
|
|
|
|
|
my $name = shift || ""; |
489
|
|
|
|
|
|
|
my $chart = shift || 0; |
490
|
|
|
|
|
|
|
my $invalid_char = qr([\[\]:*?/\\]); |
491
|
|
|
|
|
|
|
|
492
|
|
|
|
|
|
|
# Increment the Sheet/Chart number used for default sheet names below. |
493
|
|
|
|
|
|
|
if ( $chart ) { |
494
|
|
|
|
|
|
|
$self->{_chartname_count}++; |
495
|
|
|
|
|
|
|
} |
496
|
|
|
|
|
|
|
else { |
497
|
|
|
|
|
|
|
$self->{_sheetname_count}++; |
498
|
1053
|
|
|
1053
|
|
2366
|
} |
499
|
1053
|
|
100
|
|
|
6432
|
|
500
|
1053
|
|
100
|
|
|
4545
|
# Supply default Sheet/Chart name if none has been defined. |
501
|
1053
|
|
|
|
|
5239
|
if ( $name eq "" ) { |
502
|
|
|
|
|
|
|
|
503
|
|
|
|
|
|
|
if ( $chart ) { |
504
|
1053
|
100
|
|
|
|
3448
|
$name = $self->{_chart_name} . $self->{_chartname_count}; |
505
|
20
|
|
|
|
|
47
|
} |
506
|
|
|
|
|
|
|
else { |
507
|
|
|
|
|
|
|
$name = $self->{_sheet_name} . $self->{_sheetname_count}; |
508
|
1033
|
|
|
|
|
2551
|
} |
509
|
|
|
|
|
|
|
} |
510
|
|
|
|
|
|
|
|
511
|
|
|
|
|
|
|
# Check that sheet name is <= 31. Excel limit. |
512
|
1053
|
100
|
|
|
|
3657
|
croak "Sheetname $name must be <= 31 chars" if length $name > 31; |
513
|
|
|
|
|
|
|
|
514
|
990
|
100
|
|
|
|
2733
|
# Check that sheetname doesn't contain any invalid characters |
515
|
20
|
|
|
|
|
55
|
if ( $name =~ $invalid_char ) { |
516
|
|
|
|
|
|
|
croak 'Invalid character []:*?/\\ in worksheet name: ' . $name; |
517
|
|
|
|
|
|
|
} |
518
|
970
|
|
|
|
|
3325
|
|
519
|
|
|
|
|
|
|
# Check that sheetname doesn't start or end with an apostrophe. |
520
|
|
|
|
|
|
|
if ( $name =~ /^'/ || $name =~ /'$/) { |
521
|
|
|
|
|
|
|
croak "Worksheet name $name cannot start or end with an apostrophe"; |
522
|
|
|
|
|
|
|
} |
523
|
1053
|
100
|
|
|
|
5805
|
|
524
|
|
|
|
|
|
|
# Check that the worksheet name doesn't already exist since this is a fatal |
525
|
|
|
|
|
|
|
# error in Excel 97. The check must also exclude case insensitive matches. |
526
|
1052
|
100
|
|
|
|
6042
|
foreach my $worksheet ( @{ $self->{_worksheets} } ) { |
527
|
6
|
|
|
|
|
416
|
my $name_a = $name; |
528
|
|
|
|
|
|
|
my $name_b = $worksheet->{_name}; |
529
|
|
|
|
|
|
|
|
530
|
|
|
|
|
|
|
if ( lc( $name_a ) eq lc( $name_b ) ) { |
531
|
1046
|
100
|
100
|
|
|
6683
|
croak "Worksheet name '$name', with case ignored, is already used."; |
532
|
3
|
|
|
|
|
208
|
} |
533
|
|
|
|
|
|
|
} |
534
|
|
|
|
|
|
|
|
535
|
|
|
|
|
|
|
return $name; |
536
|
|
|
|
|
|
|
} |
537
|
1043
|
|
|
|
|
2165
|
|
|
1043
|
|
|
|
|
3657
|
|
538
|
500
|
|
|
|
|
622
|
|
539
|
500
|
|
|
|
|
922
|
############################################################################### |
540
|
|
|
|
|
|
|
# |
541
|
500
|
100
|
|
|
|
1129
|
# add_format(%properties) |
542
|
2
|
|
|
|
|
334
|
# |
543
|
|
|
|
|
|
|
# Add a new format to the Excel workbook. |
544
|
|
|
|
|
|
|
# |
545
|
|
|
|
|
|
|
|
546
|
1041
|
|
|
|
|
4126
|
my $self = shift; |
547
|
|
|
|
|
|
|
|
548
|
|
|
|
|
|
|
my @init_data = |
549
|
|
|
|
|
|
|
( \$self->{_xf_format_indices}, \$self->{_dxf_format_indices} ); |
550
|
|
|
|
|
|
|
|
551
|
|
|
|
|
|
|
# Change default format style for Excel2003/XLS format. |
552
|
|
|
|
|
|
|
if ( $self->{_excel2003_style} ) { |
553
|
|
|
|
|
|
|
push @init_data, ( font => 'Arial', size => 10, theme => -1 ); |
554
|
|
|
|
|
|
|
} |
555
|
|
|
|
|
|
|
|
556
|
|
|
|
|
|
|
# Add the default format properties. |
557
|
|
|
|
|
|
|
push @init_data, %{ $self->{_default_format_properties} }; |
558
|
2044
|
|
|
2044
|
0
|
5082
|
|
559
|
|
|
|
|
|
|
# Add the user defined properties. |
560
|
|
|
|
|
|
|
push @init_data, @_; |
561
|
2044
|
|
|
|
|
5624
|
|
562
|
|
|
|
|
|
|
my $format = Excel::Writer::XLSX::Format->new( @init_data ); |
563
|
|
|
|
|
|
|
|
564
|
2044
|
100
|
|
|
|
5199
|
push @{ $self->{_formats} }, $format; # Store format reference |
565
|
19
|
|
|
|
|
50
|
|
566
|
|
|
|
|
|
|
return $format; |
567
|
|
|
|
|
|
|
} |
568
|
|
|
|
|
|
|
|
569
|
2044
|
|
|
|
|
3209
|
|
|
2044
|
|
|
|
|
4544
|
|
570
|
|
|
|
|
|
|
############################################################################### |
571
|
|
|
|
|
|
|
# |
572
|
2044
|
|
|
|
|
4481
|
# add_shape(%properties) |
573
|
|
|
|
|
|
|
# |
574
|
2044
|
|
|
|
|
9615
|
# Add a new shape to the Excel workbook. |
575
|
|
|
|
|
|
|
# |
576
|
2044
|
|
|
|
|
3285
|
|
|
2044
|
|
|
|
|
5357
|
|
577
|
|
|
|
|
|
|
my $self = shift; |
578
|
2044
|
|
|
|
|
4904
|
my $fh = undef; |
579
|
|
|
|
|
|
|
my $shape = Excel::Writer::XLSX::Shape->new( $fh, @_ ); |
580
|
|
|
|
|
|
|
|
581
|
|
|
|
|
|
|
$shape->{_palette} = $self->{_palette}; |
582
|
|
|
|
|
|
|
|
583
|
|
|
|
|
|
|
|
584
|
|
|
|
|
|
|
push @{ $self->{_shapes} }, $shape; # Store shape reference. |
585
|
|
|
|
|
|
|
|
586
|
|
|
|
|
|
|
return $shape; |
587
|
|
|
|
|
|
|
} |
588
|
|
|
|
|
|
|
|
589
|
|
|
|
|
|
|
############################################################################### |
590
|
19
|
|
|
19
|
0
|
130
|
# |
591
|
19
|
|
|
|
|
24
|
# set_1904() |
592
|
19
|
|
|
|
|
99
|
# |
593
|
|
|
|
|
|
|
# Set the date system: 0 = 1900 (the default), 1 = 1904 |
594
|
19
|
|
|
|
|
73
|
# |
595
|
|
|
|
|
|
|
|
596
|
|
|
|
|
|
|
my $self = shift; |
597
|
19
|
|
|
|
|
402
|
|
|
19
|
|
|
|
|
278
|
|
598
|
|
|
|
|
|
|
if ( defined( $_[0] ) ) { |
599
|
19
|
|
|
|
|
59
|
$self->{_date_1904} = $_[0]; |
600
|
|
|
|
|
|
|
} |
601
|
|
|
|
|
|
|
else { |
602
|
|
|
|
|
|
|
$self->{_date_1904} = 1; |
603
|
|
|
|
|
|
|
} |
604
|
|
|
|
|
|
|
} |
605
|
|
|
|
|
|
|
|
606
|
|
|
|
|
|
|
|
607
|
|
|
|
|
|
|
############################################################################### |
608
|
|
|
|
|
|
|
# |
609
|
|
|
|
|
|
|
# get_1904() |
610
|
1
|
|
|
1
|
0
|
19
|
# |
611
|
|
|
|
|
|
|
# Return the date system: 0 = 1900, 1 = 1904 |
612
|
1
|
50
|
|
|
|
4
|
# |
613
|
0
|
|
|
|
|
0
|
|
614
|
|
|
|
|
|
|
my $self = shift; |
615
|
|
|
|
|
|
|
|
616
|
1
|
|
|
|
|
7
|
return $self->{_date_1904}; |
617
|
|
|
|
|
|
|
} |
618
|
|
|
|
|
|
|
|
619
|
|
|
|
|
|
|
|
620
|
|
|
|
|
|
|
############################################################################### |
621
|
|
|
|
|
|
|
# |
622
|
|
|
|
|
|
|
# set_custom_color() |
623
|
|
|
|
|
|
|
# |
624
|
|
|
|
|
|
|
# Change the RGB components of the elements in the colour palette. |
625
|
|
|
|
|
|
|
# |
626
|
|
|
|
|
|
|
|
627
|
|
|
|
|
|
|
my $self = shift; |
628
|
|
|
|
|
|
|
|
629
|
0
|
|
|
0
|
0
|
0
|
|
630
|
|
|
|
|
|
|
# Match a HTML #xxyyzz style parameter |
631
|
0
|
|
|
|
|
0
|
if ( defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) { |
632
|
|
|
|
|
|
|
@_ = ( $_[0], hex $1, hex $2, hex $3 ); |
633
|
|
|
|
|
|
|
} |
634
|
|
|
|
|
|
|
|
635
|
|
|
|
|
|
|
|
636
|
|
|
|
|
|
|
my $index = $_[0] || 0; |
637
|
|
|
|
|
|
|
my $red = $_[1] || 0; |
638
|
|
|
|
|
|
|
my $green = $_[2] || 0; |
639
|
|
|
|
|
|
|
my $blue = $_[3] || 0; |
640
|
|
|
|
|
|
|
|
641
|
|
|
|
|
|
|
my $aref = $self->{_palette}; |
642
|
|
|
|
|
|
|
|
643
|
5
|
|
|
5
|
0
|
26
|
# Check that the colour index is the right range |
644
|
|
|
|
|
|
|
if ( $index < 8 or $index > 64 ) { |
645
|
|
|
|
|
|
|
carp "Color index $index outside range: 8 <= index <= 64"; |
646
|
|
|
|
|
|
|
return 0; |
647
|
5
|
100
|
66
|
|
|
25
|
} |
648
|
3
|
|
|
|
|
12
|
|
649
|
|
|
|
|
|
|
# Check that the colour components are in the right range |
650
|
|
|
|
|
|
|
if ( ( $red < 0 or $red > 255 ) |
651
|
|
|
|
|
|
|
|| ( $green < 0 or $green > 255 ) |
652
|
5
|
|
50
|
|
|
14
|
|| ( $blue < 0 or $blue > 255 ) ) |
653
|
5
|
|
50
|
|
|
8
|
{ |
654
|
5
|
|
100
|
|
|
20
|
carp "Color component outside range: 0 <= color <= 255"; |
655
|
5
|
|
100
|
|
|
11
|
return 0; |
656
|
|
|
|
|
|
|
} |
657
|
5
|
|
|
|
|
7
|
|
658
|
|
|
|
|
|
|
$index -= 8; # Adjust colour index (wingless dragonfly) |
659
|
|
|
|
|
|
|
|
660
|
5
|
50
|
33
|
|
|
17
|
# Set the RGB value. |
661
|
0
|
|
|
|
|
0
|
my @rgb = ( $red, $green, $blue ); |
662
|
0
|
|
|
|
|
0
|
$aref->[$index] = [@rgb]; |
663
|
|
|
|
|
|
|
|
664
|
|
|
|
|
|
|
# Store the custom colors for the style.xml file. |
665
|
|
|
|
|
|
|
push @{ $self->{_custom_colors} }, sprintf "FF%02X%02X%02X", @rgb; |
666
|
5
|
50
|
33
|
|
|
42
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
|
33
|
|
|
|
|
667
|
|
|
|
|
|
|
return $index + 8; |
668
|
|
|
|
|
|
|
} |
669
|
|
|
|
|
|
|
|
670
|
0
|
|
|
|
|
0
|
|
671
|
0
|
|
|
|
|
0
|
############################################################################### |
672
|
|
|
|
|
|
|
# |
673
|
|
|
|
|
|
|
# set_color_palette() |
674
|
5
|
|
|
|
|
10
|
# |
675
|
|
|
|
|
|
|
# Sets the colour palette to the Excel defaults. |
676
|
|
|
|
|
|
|
# |
677
|
5
|
|
|
|
|
11
|
|
678
|
5
|
|
|
|
|
11
|
my $self = shift; |
679
|
|
|
|
|
|
|
|
680
|
|
|
|
|
|
|
$self->{_palette} = [ |
681
|
5
|
|
|
|
|
7
|
[ 0x00, 0x00, 0x00, 0x00 ], # 8 |
|
5
|
|
|
|
|
21
|
|
682
|
|
|
|
|
|
|
[ 0xff, 0xff, 0xff, 0x00 ], # 9 |
683
|
5
|
|
|
|
|
12
|
[ 0xff, 0x00, 0x00, 0x00 ], # 10 |
684
|
|
|
|
|
|
|
[ 0x00, 0xff, 0x00, 0x00 ], # 11 |
685
|
|
|
|
|
|
|
[ 0x00, 0x00, 0xff, 0x00 ], # 12 |
686
|
|
|
|
|
|
|
[ 0xff, 0xff, 0x00, 0x00 ], # 13 |
687
|
|
|
|
|
|
|
[ 0xff, 0x00, 0xff, 0x00 ], # 14 |
688
|
|
|
|
|
|
|
[ 0x00, 0xff, 0xff, 0x00 ], # 15 |
689
|
|
|
|
|
|
|
[ 0x80, 0x00, 0x00, 0x00 ], # 16 |
690
|
|
|
|
|
|
|
[ 0x00, 0x80, 0x00, 0x00 ], # 17 |
691
|
|
|
|
|
|
|
[ 0x00, 0x00, 0x80, 0x00 ], # 18 |
692
|
|
|
|
|
|
|
[ 0x80, 0x80, 0x00, 0x00 ], # 19 |
693
|
|
|
|
|
|
|
[ 0x80, 0x00, 0x80, 0x00 ], # 20 |
694
|
|
|
|
|
|
|
[ 0x00, 0x80, 0x80, 0x00 ], # 21 |
695
|
892
|
|
|
892
|
0
|
2097
|
[ 0xc0, 0xc0, 0xc0, 0x00 ], # 22 |
696
|
|
|
|
|
|
|
[ 0x80, 0x80, 0x80, 0x00 ], # 23 |
697
|
|
|
|
|
|
|
[ 0x99, 0x99, 0xff, 0x00 ], # 24 |
698
|
892
|
|
|
|
|
28618
|
[ 0x99, 0x33, 0x66, 0x00 ], # 25 |
699
|
|
|
|
|
|
|
[ 0xff, 0xff, 0xcc, 0x00 ], # 26 |
700
|
|
|
|
|
|
|
[ 0xcc, 0xff, 0xff, 0x00 ], # 27 |
701
|
|
|
|
|
|
|
[ 0x66, 0x00, 0x66, 0x00 ], # 28 |
702
|
|
|
|
|
|
|
[ 0xff, 0x80, 0x80, 0x00 ], # 29 |
703
|
|
|
|
|
|
|
[ 0x00, 0x66, 0xcc, 0x00 ], # 30 |
704
|
|
|
|
|
|
|
[ 0xcc, 0xcc, 0xff, 0x00 ], # 31 |
705
|
|
|
|
|
|
|
[ 0x00, 0x00, 0x80, 0x00 ], # 32 |
706
|
|
|
|
|
|
|
[ 0xff, 0x00, 0xff, 0x00 ], # 33 |
707
|
|
|
|
|
|
|
[ 0xff, 0xff, 0x00, 0x00 ], # 34 |
708
|
|
|
|
|
|
|
[ 0x00, 0xff, 0xff, 0x00 ], # 35 |
709
|
|
|
|
|
|
|
[ 0x80, 0x00, 0x80, 0x00 ], # 36 |
710
|
|
|
|
|
|
|
[ 0x80, 0x00, 0x00, 0x00 ], # 37 |
711
|
|
|
|
|
|
|
[ 0x00, 0x80, 0x80, 0x00 ], # 38 |
712
|
|
|
|
|
|
|
[ 0x00, 0x00, 0xff, 0x00 ], # 39 |
713
|
|
|
|
|
|
|
[ 0x00, 0xcc, 0xff, 0x00 ], # 40 |
714
|
|
|
|
|
|
|
[ 0xcc, 0xff, 0xff, 0x00 ], # 41 |
715
|
|
|
|
|
|
|
[ 0xcc, 0xff, 0xcc, 0x00 ], # 42 |
716
|
|
|
|
|
|
|
[ 0xff, 0xff, 0x99, 0x00 ], # 43 |
717
|
|
|
|
|
|
|
[ 0x99, 0xcc, 0xff, 0x00 ], # 44 |
718
|
|
|
|
|
|
|
[ 0xff, 0x99, 0xcc, 0x00 ], # 45 |
719
|
|
|
|
|
|
|
[ 0xcc, 0x99, 0xff, 0x00 ], # 46 |
720
|
|
|
|
|
|
|
[ 0xff, 0xcc, 0x99, 0x00 ], # 47 |
721
|
|
|
|
|
|
|
[ 0x33, 0x66, 0xff, 0x00 ], # 48 |
722
|
|
|
|
|
|
|
[ 0x33, 0xcc, 0xcc, 0x00 ], # 49 |
723
|
|
|
|
|
|
|
[ 0x99, 0xcc, 0x00, 0x00 ], # 50 |
724
|
|
|
|
|
|
|
[ 0xff, 0xcc, 0x00, 0x00 ], # 51 |
725
|
|
|
|
|
|
|
[ 0xff, 0x99, 0x00, 0x00 ], # 52 |
726
|
|
|
|
|
|
|
[ 0xff, 0x66, 0x00, 0x00 ], # 53 |
727
|
|
|
|
|
|
|
[ 0x66, 0x66, 0x99, 0x00 ], # 54 |
728
|
|
|
|
|
|
|
[ 0x96, 0x96, 0x96, 0x00 ], # 55 |
729
|
|
|
|
|
|
|
[ 0x00, 0x33, 0x66, 0x00 ], # 56 |
730
|
|
|
|
|
|
|
[ 0x33, 0x99, 0x66, 0x00 ], # 57 |
731
|
|
|
|
|
|
|
[ 0x00, 0x33, 0x00, 0x00 ], # 58 |
732
|
|
|
|
|
|
|
[ 0x33, 0x33, 0x00, 0x00 ], # 59 |
733
|
|
|
|
|
|
|
[ 0x99, 0x33, 0x00, 0x00 ], # 60 |
734
|
|
|
|
|
|
|
[ 0x99, 0x33, 0x66, 0x00 ], # 61 |
735
|
|
|
|
|
|
|
[ 0x33, 0x33, 0x99, 0x00 ], # 62 |
736
|
|
|
|
|
|
|
[ 0x33, 0x33, 0x33, 0x00 ], # 63 |
737
|
|
|
|
|
|
|
]; |
738
|
|
|
|
|
|
|
|
739
|
|
|
|
|
|
|
return 0; |
740
|
|
|
|
|
|
|
} |
741
|
|
|
|
|
|
|
|
742
|
|
|
|
|
|
|
|
743
|
|
|
|
|
|
|
############################################################################### |
744
|
|
|
|
|
|
|
# |
745
|
|
|
|
|
|
|
# set_tempdir() |
746
|
|
|
|
|
|
|
# |
747
|
|
|
|
|
|
|
# Change the default temp directory. |
748
|
|
|
|
|
|
|
# |
749
|
|
|
|
|
|
|
|
750
|
|
|
|
|
|
|
my $self = shift; |
751
|
|
|
|
|
|
|
my $dir = shift; |
752
|
|
|
|
|
|
|
|
753
|
|
|
|
|
|
|
croak "$dir is not a valid directory" if defined $dir and not -d $dir; |
754
|
|
|
|
|
|
|
|
755
|
|
|
|
|
|
|
$self->{_tempdir} = $dir; |
756
|
892
|
|
|
|
|
2719
|
|
757
|
|
|
|
|
|
|
} |
758
|
|
|
|
|
|
|
|
759
|
|
|
|
|
|
|
|
760
|
|
|
|
|
|
|
############################################################################### |
761
|
|
|
|
|
|
|
# |
762
|
|
|
|
|
|
|
# define_name() |
763
|
|
|
|
|
|
|
# |
764
|
|
|
|
|
|
|
# Create a defined name in Excel. We handle global/workbook level names and |
765
|
|
|
|
|
|
|
# local/worksheet names. |
766
|
|
|
|
|
|
|
# |
767
|
|
|
|
|
|
|
|
768
|
1
|
|
|
1
|
0
|
18
|
my $self = shift; |
769
|
1
|
|
|
|
|
3
|
my $name = shift; |
770
|
|
|
|
|
|
|
my $formula = shift; |
771
|
1
|
50
|
33
|
|
|
26
|
my $sheet_index = undef; |
772
|
|
|
|
|
|
|
my $sheetname = ''; |
773
|
1
|
|
|
|
|
6
|
my $full_name = $name; |
774
|
|
|
|
|
|
|
|
775
|
|
|
|
|
|
|
# Remove the = sign from the formula if it exists. |
776
|
|
|
|
|
|
|
$formula =~ s/^=//; |
777
|
|
|
|
|
|
|
|
778
|
|
|
|
|
|
|
# Local defined names are formatted like "Sheet1!name". |
779
|
|
|
|
|
|
|
if ( $name =~ /^(.*)!(.*)$/ ) { |
780
|
|
|
|
|
|
|
$sheetname = $1; |
781
|
|
|
|
|
|
|
$name = $2; |
782
|
|
|
|
|
|
|
$sheet_index = $self->_get_sheet_index( $sheetname ); |
783
|
|
|
|
|
|
|
} |
784
|
|
|
|
|
|
|
else { |
785
|
|
|
|
|
|
|
$sheet_index = -1; # Use -1 to indicate global names. |
786
|
|
|
|
|
|
|
} |
787
|
39
|
|
|
39
|
0
|
180
|
|
788
|
39
|
|
|
|
|
57
|
# Warn if the sheet index wasn't found. |
789
|
39
|
|
|
|
|
53
|
if ( !defined $sheet_index ) { |
790
|
39
|
|
|
|
|
47
|
carp "Unknown sheet name $sheetname in defined_name()"; |
791
|
39
|
|
|
|
|
49
|
return -1; |
792
|
39
|
|
|
|
|
55
|
} |
793
|
|
|
|
|
|
|
|
794
|
|
|
|
|
|
|
# Warn if the name contains invalid chars as defined by Excel help. |
795
|
39
|
|
|
|
|
115
|
if ( $name !~ m/^[\w\\][\w\\.]*$/ || $name =~ m/^\d/ ) { |
796
|
|
|
|
|
|
|
carp "Invalid character in name '$name' used in defined_name()"; |
797
|
|
|
|
|
|
|
return -1; |
798
|
39
|
100
|
|
|
|
104
|
} |
799
|
9
|
|
|
|
|
19
|
|
800
|
9
|
|
|
|
|
20
|
# Warn if the name looks like a cell name. |
801
|
9
|
|
|
|
|
25
|
if ( $name =~ m/^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$/ ) { |
802
|
|
|
|
|
|
|
carp "Invalid name '$name' looks like a cell name in defined_name()"; |
803
|
|
|
|
|
|
|
return -1; |
804
|
30
|
|
|
|
|
43
|
} |
805
|
|
|
|
|
|
|
|
806
|
|
|
|
|
|
|
# Warn if the name looks like a R1C1. |
807
|
|
|
|
|
|
|
if ( $name =~ m/^[rcRC]$/ || $name =~ m/^[rcRC]\d+[rcRC]\d+$/ ) { |
808
|
39
|
50
|
|
|
|
68
|
carp "Invalid name '$name' like a RC cell ref in defined_name()"; |
809
|
0
|
|
|
|
|
0
|
return -1; |
810
|
0
|
|
|
|
|
0
|
} |
811
|
|
|
|
|
|
|
|
812
|
|
|
|
|
|
|
push @{ $self->{_defined_names} }, [ $name, $sheet_index, $formula ]; |
813
|
|
|
|
|
|
|
} |
814
|
39
|
100
|
100
|
|
|
189
|
|
815
|
4
|
|
|
|
|
423
|
|
816
|
4
|
|
|
|
|
170
|
############################################################################### |
817
|
|
|
|
|
|
|
# |
818
|
|
|
|
|
|
|
# set_size() |
819
|
|
|
|
|
|
|
# |
820
|
35
|
100
|
|
|
|
89
|
# Set the workbook size. |
821
|
4
|
|
|
|
|
275
|
# |
822
|
4
|
|
|
|
|
126
|
|
823
|
|
|
|
|
|
|
my $self = shift; |
824
|
|
|
|
|
|
|
my $width = shift; |
825
|
|
|
|
|
|
|
my $height = shift; |
826
|
31
|
100
|
100
|
|
|
439
|
|
827
|
6
|
|
|
|
|
419
|
if ( !$width ) { |
828
|
6
|
|
|
|
|
184
|
$self->{_window_width} = 16095; |
829
|
|
|
|
|
|
|
} |
830
|
|
|
|
|
|
|
else { |
831
|
25
|
|
|
|
|
33
|
# Convert to twips at 96 dpi. |
|
25
|
|
|
|
|
83
|
|
832
|
|
|
|
|
|
|
$self->{_window_width} = int( $width * 1440 / 96 ); |
833
|
|
|
|
|
|
|
} |
834
|
|
|
|
|
|
|
|
835
|
|
|
|
|
|
|
if ( !$height ) { |
836
|
|
|
|
|
|
|
$self->{_window_height} = 9660; |
837
|
|
|
|
|
|
|
} |
838
|
|
|
|
|
|
|
else { |
839
|
|
|
|
|
|
|
# Convert to twips at 96 dpi. |
840
|
|
|
|
|
|
|
$self->{_window_height} = int( $height * 1440 / 96 ); |
841
|
|
|
|
|
|
|
} |
842
|
|
|
|
|
|
|
} |
843
|
5
|
|
|
5
|
0
|
36
|
|
844
|
5
|
|
|
|
|
10
|
|
845
|
5
|
|
|
|
|
7
|
############################################################################### |
846
|
|
|
|
|
|
|
# |
847
|
5
|
100
|
|
|
|
19
|
# set_tab_ratio() |
848
|
2
|
|
|
|
|
4
|
# |
849
|
|
|
|
|
|
|
# Set the ratio of space for worksheet tabs. |
850
|
|
|
|
|
|
|
# |
851
|
|
|
|
|
|
|
|
852
|
3
|
|
|
|
|
12
|
my $self = shift; |
853
|
|
|
|
|
|
|
my $tab_ratio = shift; |
854
|
|
|
|
|
|
|
|
855
|
5
|
100
|
|
|
|
17
|
if (!defined $tab_ratio) { |
856
|
2
|
|
|
|
|
6
|
return; |
857
|
|
|
|
|
|
|
} |
858
|
|
|
|
|
|
|
|
859
|
|
|
|
|
|
|
if ( $tab_ratio < 0 or $tab_ratio > 100 ) { |
860
|
3
|
|
|
|
|
9
|
carp "Tab ratio outside range: 0 <= zoom <= 100"; |
861
|
|
|
|
|
|
|
} |
862
|
|
|
|
|
|
|
else { |
863
|
|
|
|
|
|
|
$self->{_tab_ratio} = int( $tab_ratio * 10 ); |
864
|
|
|
|
|
|
|
} |
865
|
|
|
|
|
|
|
} |
866
|
|
|
|
|
|
|
|
867
|
|
|
|
|
|
|
|
868
|
|
|
|
|
|
|
############################################################################### |
869
|
|
|
|
|
|
|
# |
870
|
|
|
|
|
|
|
# set_properties() |
871
|
|
|
|
|
|
|
# |
872
|
|
|
|
|
|
|
# Set the document properties such as Title, Author etc. These are written to |
873
|
4
|
|
|
4
|
0
|
30
|
# property sets in the OLE container. |
874
|
4
|
|
|
|
|
7
|
# |
875
|
|
|
|
|
|
|
|
876
|
4
|
100
|
|
|
|
13
|
my $self = shift; |
877
|
1
|
|
|
|
|
3
|
my %param = @_; |
878
|
|
|
|
|
|
|
|
879
|
|
|
|
|
|
|
# Ignore if no args were passed. |
880
|
3
|
50
|
33
|
|
|
18
|
return -1 unless @_; |
881
|
0
|
|
|
|
|
0
|
|
882
|
|
|
|
|
|
|
# List of valid input parameters. |
883
|
|
|
|
|
|
|
my %valid = ( |
884
|
3
|
|
|
|
|
13
|
title => 1, |
885
|
|
|
|
|
|
|
subject => 1, |
886
|
|
|
|
|
|
|
author => 1, |
887
|
|
|
|
|
|
|
keywords => 1, |
888
|
|
|
|
|
|
|
comments => 1, |
889
|
|
|
|
|
|
|
last_author => 1, |
890
|
|
|
|
|
|
|
created => 1, |
891
|
|
|
|
|
|
|
category => 1, |
892
|
|
|
|
|
|
|
manager => 1, |
893
|
|
|
|
|
|
|
company => 1, |
894
|
|
|
|
|
|
|
status => 1, |
895
|
|
|
|
|
|
|
hyperlink_base => 1, |
896
|
|
|
|
|
|
|
); |
897
|
|
|
|
|
|
|
|
898
|
2
|
|
|
2
|
0
|
38
|
# Check for valid input parameters. |
899
|
2
|
|
|
|
|
11
|
for my $parameter ( keys %param ) { |
900
|
|
|
|
|
|
|
if ( not exists $valid{$parameter} ) { |
901
|
|
|
|
|
|
|
carp "Unknown parameter '$parameter' in set_properties()"; |
902
|
2
|
50
|
|
|
|
8
|
return -1; |
903
|
|
|
|
|
|
|
} |
904
|
|
|
|
|
|
|
} |
905
|
2
|
|
|
|
|
15
|
|
906
|
|
|
|
|
|
|
# Set the creation time unless specified by the user. |
907
|
|
|
|
|
|
|
if ( !exists $param{created} ) { |
908
|
|
|
|
|
|
|
$param{created} = $self->{_createtime}; |
909
|
|
|
|
|
|
|
} |
910
|
|
|
|
|
|
|
|
911
|
|
|
|
|
|
|
|
912
|
|
|
|
|
|
|
$self->{_doc_properties} = \%param; |
913
|
|
|
|
|
|
|
} |
914
|
|
|
|
|
|
|
|
915
|
|
|
|
|
|
|
|
916
|
|
|
|
|
|
|
############################################################################### |
917
|
|
|
|
|
|
|
# |
918
|
|
|
|
|
|
|
# set_custom_property() |
919
|
|
|
|
|
|
|
# |
920
|
|
|
|
|
|
|
# Set a user defined custom document property. |
921
|
2
|
|
|
|
|
7
|
# |
922
|
10
|
50
|
|
|
|
18
|
|
923
|
0
|
|
|
|
|
0
|
my $self = shift; |
924
|
0
|
|
|
|
|
0
|
my $name = shift; |
925
|
|
|
|
|
|
|
my $value = shift; |
926
|
|
|
|
|
|
|
my $type = shift; |
927
|
|
|
|
|
|
|
|
928
|
|
|
|
|
|
|
|
929
|
2
|
50
|
|
|
|
4
|
# Valid types. |
930
|
2
|
|
|
|
|
5
|
my %valid_type = ( |
931
|
|
|
|
|
|
|
'text' => 1, |
932
|
|
|
|
|
|
|
'date' => 1, |
933
|
|
|
|
|
|
|
'number' => 1, |
934
|
2
|
|
|
|
|
7
|
'number_int' => 1, |
935
|
|
|
|
|
|
|
'bool' => 1, |
936
|
|
|
|
|
|
|
); |
937
|
|
|
|
|
|
|
|
938
|
|
|
|
|
|
|
if ( !defined $name || !defined $value ) { |
939
|
|
|
|
|
|
|
carp "The name and value parameters must be defined " |
940
|
|
|
|
|
|
|
. "in set_custom_property()"; |
941
|
|
|
|
|
|
|
|
942
|
|
|
|
|
|
|
return -1; |
943
|
|
|
|
|
|
|
} |
944
|
|
|
|
|
|
|
|
945
|
|
|
|
|
|
|
# Determine the type for strings and numbers if it hasn't been specified. |
946
|
18
|
|
|
18
|
0
|
80
|
if ( !$type ) { |
947
|
18
|
|
|
|
|
26
|
if ( $value =~ /^\d+$/ ) { |
948
|
18
|
|
|
|
|
28
|
$type = 'number_int'; |
949
|
18
|
|
|
|
|
21
|
} |
950
|
|
|
|
|
|
|
elsif ( $value =~ |
951
|
|
|
|
|
|
|
/^([+-]?)(?=[0-9]|\.[0-9])[0-9]*(\.[0-9]*)?([Ee]([+-]?[0-9]+))?$/ ) |
952
|
|
|
|
|
|
|
{ |
953
|
18
|
|
|
|
|
58
|
$type = 'number'; |
954
|
|
|
|
|
|
|
} |
955
|
|
|
|
|
|
|
else { |
956
|
|
|
|
|
|
|
$type = 'text'; |
957
|
|
|
|
|
|
|
} |
958
|
|
|
|
|
|
|
} |
959
|
|
|
|
|
|
|
|
960
|
|
|
|
|
|
|
# Check for valid validation types. |
961
|
18
|
50
|
33
|
|
|
60
|
if ( !exists $valid_type{$type} ) { |
962
|
0
|
|
|
|
|
0
|
carp "Unknown custom type '$type' in set_custom_property()"; |
963
|
|
|
|
|
|
|
return -1; |
964
|
|
|
|
|
|
|
} |
965
|
0
|
|
|
|
|
0
|
|
966
|
|
|
|
|
|
|
# Check for strings longer than Excel's limit of 255 chars. |
967
|
|
|
|
|
|
|
if ( $type eq 'text' and length $value > 255 ) { |
968
|
|
|
|
|
|
|
carp "Length of text custom value '$value' exceeds " |
969
|
18
|
100
|
|
|
|
33
|
. "Excel's limit of 255 in set_custom_property()"; |
970
|
7
|
100
|
|
|
|
53
|
return -1; |
|
|
100
|
|
|
|
|
|
971
|
1
|
|
|
|
|
3
|
} |
972
|
|
|
|
|
|
|
if ( length $value > 255 ) { |
973
|
|
|
|
|
|
|
carp "Length of custom name '$name' exceeds " |
974
|
|
|
|
|
|
|
. "Excel's limit of 255 in set_custom_property()"; |
975
|
|
|
|
|
|
|
return -1; |
976
|
2
|
|
|
|
|
4
|
} |
977
|
|
|
|
|
|
|
|
978
|
|
|
|
|
|
|
push @{ $self->{_custom_properties} }, [ $name, $value, $type ]; |
979
|
4
|
|
|
|
|
9
|
} |
980
|
|
|
|
|
|
|
|
981
|
|
|
|
|
|
|
|
982
|
|
|
|
|
|
|
|
983
|
|
|
|
|
|
|
############################################################################### |
984
|
18
|
50
|
|
|
|
35
|
# |
985
|
0
|
|
|
|
|
0
|
# add_vba_project() |
986
|
0
|
|
|
|
|
0
|
# |
987
|
|
|
|
|
|
|
# Add a vbaProject binary to the XLSX file. |
988
|
|
|
|
|
|
|
# |
989
|
|
|
|
|
|
|
|
990
|
18
|
50
|
66
|
|
|
50
|
my $self = shift; |
991
|
0
|
|
|
|
|
0
|
my $vba_project = shift; |
992
|
|
|
|
|
|
|
|
993
|
0
|
|
|
|
|
0
|
croak "No vbaProject.bin specified in add_vba_project()" |
994
|
|
|
|
|
|
|
if not $vba_project; |
995
|
18
|
50
|
|
|
|
32
|
|
996
|
0
|
|
|
|
|
0
|
croak "Couldn't locate $vba_project in add_vba_project(): $!" |
997
|
|
|
|
|
|
|
unless -e $vba_project; |
998
|
0
|
|
|
|
|
0
|
|
999
|
|
|
|
|
|
|
if ( !$self->{_vba_codemame} ) { |
1000
|
|
|
|
|
|
|
$self->{_vba_codename} = 'ThisWorkbook'; |
1001
|
18
|
|
|
|
|
26
|
} |
|
18
|
|
|
|
|
64
|
|
1002
|
|
|
|
|
|
|
|
1003
|
|
|
|
|
|
|
$self->{_vba_project} = $vba_project; |
1004
|
|
|
|
|
|
|
} |
1005
|
|
|
|
|
|
|
|
1006
|
|
|
|
|
|
|
|
1007
|
|
|
|
|
|
|
############################################################################### |
1008
|
|
|
|
|
|
|
# |
1009
|
|
|
|
|
|
|
# set_vba_name() |
1010
|
|
|
|
|
|
|
# |
1011
|
|
|
|
|
|
|
# Set the VBA name for the workbook. |
1012
|
|
|
|
|
|
|
# |
1013
|
|
|
|
|
|
|
|
1014
|
6
|
|
|
6
|
0
|
60
|
my $self = shift; |
1015
|
6
|
|
|
|
|
10
|
my $vba_codemame = shift; |
1016
|
|
|
|
|
|
|
|
1017
|
6
|
50
|
|
|
|
17
|
if ( $vba_codemame ) { |
1018
|
|
|
|
|
|
|
$self->{_vba_codename} = $vba_codemame; |
1019
|
|
|
|
|
|
|
} |
1020
|
6
|
50
|
|
|
|
120
|
else { |
1021
|
|
|
|
|
|
|
$self->{_vba_codename} = 'ThisWorkbook'; |
1022
|
|
|
|
|
|
|
} |
1023
|
6
|
50
|
|
|
|
27
|
} |
1024
|
6
|
|
|
|
|
15
|
|
1025
|
|
|
|
|
|
|
|
1026
|
|
|
|
|
|
|
############################################################################### |
1027
|
6
|
|
|
|
|
17
|
# |
1028
|
|
|
|
|
|
|
# read_only_recommended() |
1029
|
|
|
|
|
|
|
# |
1030
|
|
|
|
|
|
|
# Set the Excel "Read-only recommended" save option. |
1031
|
|
|
|
|
|
|
# |
1032
|
|
|
|
|
|
|
|
1033
|
|
|
|
|
|
|
my $self = shift; |
1034
|
|
|
|
|
|
|
|
1035
|
|
|
|
|
|
|
$self->{_read_only} = 2; |
1036
|
|
|
|
|
|
|
} |
1037
|
|
|
|
|
|
|
|
1038
|
|
|
|
|
|
|
|
1039
|
4
|
|
|
4
|
0
|
27
|
############################################################################### |
1040
|
4
|
|
|
|
|
5
|
# |
1041
|
|
|
|
|
|
|
# set_calc_mode() |
1042
|
4
|
100
|
|
|
|
13
|
# |
1043
|
2
|
|
|
|
|
4
|
# Set the Excel caclcuation mode for the workbook. |
1044
|
|
|
|
|
|
|
# |
1045
|
|
|
|
|
|
|
|
1046
|
2
|
|
|
|
|
5
|
my $self = shift; |
1047
|
|
|
|
|
|
|
my $mode = shift || 'auto'; |
1048
|
|
|
|
|
|
|
my $calc_id = shift; |
1049
|
|
|
|
|
|
|
|
1050
|
|
|
|
|
|
|
$self->{_calc_mode} = $mode; |
1051
|
|
|
|
|
|
|
|
1052
|
|
|
|
|
|
|
if ( $mode eq 'manual' ) { |
1053
|
|
|
|
|
|
|
$self->{_calc_mode} = 'manual'; |
1054
|
|
|
|
|
|
|
$self->{_calc_on_load} = 0; |
1055
|
|
|
|
|
|
|
} |
1056
|
|
|
|
|
|
|
elsif ( $mode eq 'auto_except_tables' ) { |
1057
|
|
|
|
|
|
|
$self->{_calc_mode} = 'autoNoTable'; |
1058
|
|
|
|
|
|
|
} |
1059
|
1
|
|
|
1
|
0
|
9
|
|
1060
|
|
|
|
|
|
|
$self->{_calc_id} = $calc_id if defined $calc_id; |
1061
|
1
|
|
|
|
|
2
|
} |
1062
|
|
|
|
|
|
|
|
1063
|
|
|
|
|
|
|
|
1064
|
|
|
|
|
|
|
|
1065
|
|
|
|
|
|
|
############################################################################### |
1066
|
|
|
|
|
|
|
# |
1067
|
|
|
|
|
|
|
# get_default_url_format() |
1068
|
|
|
|
|
|
|
# |
1069
|
|
|
|
|
|
|
# Get the default url format used when a user defined format isn't specified |
1070
|
|
|
|
|
|
|
# with write_url(). The format is the hyperlink style defined by Excel for the |
1071
|
|
|
|
|
|
|
# default theme. |
1072
|
|
|
|
|
|
|
# |
1073
|
3
|
|
|
3
|
0
|
29
|
|
1074
|
3
|
|
50
|
|
|
12
|
my $self = shift; |
1075
|
3
|
|
|
|
|
7
|
|
1076
|
|
|
|
|
|
|
return $self->{_default_url_format}; |
1077
|
3
|
|
|
|
|
10
|
} |
1078
|
|
|
|
|
|
|
|
1079
|
3
|
100
|
|
|
|
15
|
|
|
|
100
|
|
|
|
|
|
1080
|
1
|
|
|
|
|
3
|
############################################################################### |
1081
|
1
|
|
|
|
|
4
|
# |
1082
|
|
|
|
|
|
|
# _store_workbook() |
1083
|
|
|
|
|
|
|
# |
1084
|
1
|
|
|
|
|
3
|
# Assemble worksheets into a workbook. |
1085
|
|
|
|
|
|
|
# |
1086
|
|
|
|
|
|
|
|
1087
|
3
|
100
|
|
|
|
12
|
my $self = shift; |
1088
|
|
|
|
|
|
|
my $tempdir = File::Temp->newdir( DIR => $self->{_tempdir} ); |
1089
|
|
|
|
|
|
|
my $packager = Excel::Writer::XLSX::Package::Packager->new(); |
1090
|
|
|
|
|
|
|
my $zip = Archive::Zip->new(); |
1091
|
|
|
|
|
|
|
|
1092
|
|
|
|
|
|
|
|
1093
|
|
|
|
|
|
|
# Add a default worksheet if non have been added. |
1094
|
|
|
|
|
|
|
$self->add_worksheet() if not @{ $self->{_worksheets} }; |
1095
|
|
|
|
|
|
|
|
1096
|
|
|
|
|
|
|
# Ensure that at least one worksheet has been selected. |
1097
|
|
|
|
|
|
|
if ( $self->{_activesheet} == 0 ) { |
1098
|
|
|
|
|
|
|
$self->{_worksheets}->[0]->{_selected} = 1; |
1099
|
|
|
|
|
|
|
$self->{_worksheets}->[0]->{_hidden} = 0; |
1100
|
|
|
|
|
|
|
} |
1101
|
|
|
|
|
|
|
|
1102
|
1
|
|
|
1
|
0
|
9
|
# Set the active sheet. |
1103
|
|
|
|
|
|
|
for my $sheet ( @{ $self->{_worksheets} } ) { |
1104
|
1
|
|
|
|
|
5
|
$sheet->{_active} = 1 if $sheet->{_index} == $self->{_activesheet}; |
1105
|
|
|
|
|
|
|
} |
1106
|
|
|
|
|
|
|
|
1107
|
|
|
|
|
|
|
# Set the sheet vba_codename if the workbook has a vbaProject binary. |
1108
|
|
|
|
|
|
|
if ( $self->{_vba_project} ) { |
1109
|
|
|
|
|
|
|
for my $sheet ( @{ $self->{_worksheets} } ) { |
1110
|
|
|
|
|
|
|
if ( !$sheet->{_vba_codename} ) { |
1111
|
|
|
|
|
|
|
$sheet->set_vba_name(); |
1112
|
|
|
|
|
|
|
} |
1113
|
|
|
|
|
|
|
} |
1114
|
|
|
|
|
|
|
} |
1115
|
|
|
|
|
|
|
|
1116
|
892
|
|
|
892
|
|
3285
|
# Convert the SST strings data structure. |
1117
|
892
|
|
|
|
|
8654
|
$self->_prepare_sst_string_data(); |
1118
|
892
|
|
|
|
|
494424
|
|
1119
|
892
|
|
|
|
|
7248
|
# Prepare the worksheet VML elements such as comments and buttons. |
1120
|
|
|
|
|
|
|
$self->_prepare_vml_objects(); |
1121
|
|
|
|
|
|
|
|
1122
|
|
|
|
|
|
|
# Set the defined names for the worksheets such as Print Titles. |
1123
|
892
|
100
|
|
|
|
34255
|
$self->_prepare_defined_names(); |
|
892
|
|
|
|
|
4400
|
|
1124
|
|
|
|
|
|
|
|
1125
|
|
|
|
|
|
|
# Prepare the drawings, charts and images. |
1126
|
892
|
100
|
|
|
|
3618
|
$self->_prepare_drawings(); |
1127
|
883
|
|
|
|
|
2737
|
|
1128
|
883
|
|
|
|
|
2346
|
# Add cached data to charts. |
1129
|
|
|
|
|
|
|
$self->_add_chart_data(); |
1130
|
|
|
|
|
|
|
|
1131
|
|
|
|
|
|
|
# Prepare the worksheet tables. |
1132
|
892
|
|
|
|
|
1822
|
$self->_prepare_tables(); |
|
892
|
|
|
|
|
3243
|
|
1133
|
1041
|
100
|
|
|
|
4720
|
|
1134
|
|
|
|
|
|
|
# Prepare the metadata file links. |
1135
|
|
|
|
|
|
|
$self->_prepare_metadata(); |
1136
|
|
|
|
|
|
|
|
1137
|
892
|
100
|
|
|
|
3753
|
# Package the workbook. |
1138
|
6
|
|
|
|
|
11
|
$packager->_add_workbook( $self ); |
|
6
|
|
|
|
|
12
|
|
1139
|
6
|
100
|
|
|
|
27
|
$packager->_set_package_dir( $tempdir ); |
1140
|
3
|
|
|
|
|
10
|
$packager->_create_package(); |
1141
|
|
|
|
|
|
|
|
1142
|
|
|
|
|
|
|
# Free up the Packager object. |
1143
|
|
|
|
|
|
|
$packager = undef; |
1144
|
|
|
|
|
|
|
|
1145
|
|
|
|
|
|
|
# Add the files to the zip archive. Due to issues with Archive::Zip in |
1146
|
892
|
|
|
|
|
6607
|
# taint mode we can't use addTree() so we have to build the file list |
1147
|
|
|
|
|
|
|
# with File::Find and pass each one to addFile(). |
1148
|
|
|
|
|
|
|
my @xlsx_files; |
1149
|
892
|
|
|
|
|
6394
|
|
1150
|
|
|
|
|
|
|
my $wanted = sub { push @xlsx_files, $File::Find::name if -f }; |
1151
|
|
|
|
|
|
|
|
1152
|
892
|
|
|
|
|
5778
|
File::Find::find( |
1153
|
|
|
|
|
|
|
{ |
1154
|
|
|
|
|
|
|
wanted => $wanted, |
1155
|
892
|
|
|
|
|
5253
|
untaint => 1, |
1156
|
|
|
|
|
|
|
untaint_pattern => qr|^(.+)$| |
1157
|
|
|
|
|
|
|
}, |
1158
|
892
|
|
|
|
|
8565
|
$tempdir |
1159
|
|
|
|
|
|
|
); |
1160
|
|
|
|
|
|
|
|
1161
|
892
|
|
|
|
|
5546
|
# Re-order the XML files before adding them to the Zip container to match |
1162
|
|
|
|
|
|
|
# (mainly) the order used by Excel and thus satisfy mime-type heuristics |
1163
|
|
|
|
|
|
|
# such as file(1) and magic. |
1164
|
892
|
|
|
|
|
5393
|
my @tmp = grep { m{/xl/} } @xlsx_files; |
1165
|
|
|
|
|
|
|
@xlsx_files = grep { !m{/xl/} } @xlsx_files; |
1166
|
|
|
|
|
|
|
@xlsx_files = ( @tmp, @xlsx_files ); |
1167
|
892
|
|
|
|
|
5090
|
|
1168
|
892
|
|
|
|
|
4203
|
@tmp = grep { m{workbook\.xml$} } @xlsx_files; |
1169
|
892
|
|
|
|
|
3885
|
@xlsx_files = grep { !m{workbook\.xml$} } @xlsx_files; |
1170
|
|
|
|
|
|
|
@xlsx_files = ( @tmp, @xlsx_files ); |
1171
|
|
|
|
|
|
|
|
1172
|
892
|
|
|
|
|
6040
|
@tmp = grep { m{_rels/workbook\.xml\.rels$} } @xlsx_files; |
1173
|
|
|
|
|
|
|
@xlsx_files = grep { !m{_rels/workbook\.xml\.rels$} } @xlsx_files; |
1174
|
|
|
|
|
|
|
@xlsx_files = ( @tmp, @xlsx_files ); |
1175
|
|
|
|
|
|
|
|
1176
|
|
|
|
|
|
|
@tmp = grep { m{_rels/\.rels$} } @xlsx_files; |
1177
|
892
|
|
|
|
|
2039
|
@xlsx_files = grep { !m{_rels/\.rels$} } @xlsx_files; |
1178
|
|
|
|
|
|
|
@xlsx_files = ( @tmp, @xlsx_files ); |
1179
|
892
|
100
|
|
19331
|
|
5020
|
|
|
19331
|
|
|
|
|
895149
|
|
1180
|
|
|
|
|
|
|
@tmp = grep { m{\[Content_Types\]\.xml$} } @xlsx_files; |
1181
|
892
|
|
|
|
|
73972
|
@xlsx_files = grep { !m{\[Content_Types\]\.xml$} } @xlsx_files; |
1182
|
|
|
|
|
|
|
@xlsx_files = ( @tmp, @xlsx_files ); |
1183
|
|
|
|
|
|
|
|
1184
|
|
|
|
|
|
|
# Store the xlsx component files with the temp dir name removed. |
1185
|
|
|
|
|
|
|
for my $filename ( @xlsx_files ) { |
1186
|
|
|
|
|
|
|
my $short_name = $filename; |
1187
|
|
|
|
|
|
|
$short_name =~ s{^\Q$tempdir\E/?}{}; |
1188
|
|
|
|
|
|
|
my $member = $zip->addFile( $filename, $short_name ); |
1189
|
|
|
|
|
|
|
|
1190
|
|
|
|
|
|
|
# Set the file member datetime to 1980-01-01 00:00:00 like Excel so |
1191
|
|
|
|
|
|
|
# that apps can produce a consistent binary file. Note, we don't use |
1192
|
|
|
|
|
|
|
# the Archive::Zip::setLastModFileDateTimeFromUnix() function directly |
1193
|
892
|
|
|
|
|
5140
|
# since it doesn't allow the time 00:00:00 for this date. |
|
10903
|
|
|
|
|
21334
|
|
1194
|
892
|
|
|
|
|
2192
|
$member->{'lastModFileDateTime'} = 2162688; |
|
10903
|
|
|
|
|
18477
|
|
1195
|
892
|
|
|
|
|
3076
|
} |
1196
|
|
|
|
|
|
|
|
1197
|
892
|
|
|
|
|
2203
|
if ( $self->{_internal_fh} ) { |
|
10903
|
|
|
|
|
17433
|
|
1198
|
892
|
|
|
|
|
2324
|
|
|
10903
|
|
|
|
|
17259
|
|
1199
|
892
|
|
|
|
|
3002
|
if ( $zip->writeToFileHandle( $self->{_filehandle} ) != 0 ) { |
1200
|
|
|
|
|
|
|
carp 'Error writing zip container for xlsx file.'; |
1201
|
892
|
|
|
|
|
2120
|
} |
|
10903
|
|
|
|
|
17742
|
|
1202
|
892
|
|
|
|
|
2177
|
} |
|
10903
|
|
|
|
|
17395
|
|
1203
|
892
|
|
|
|
|
2921
|
else { |
1204
|
|
|
|
|
|
|
|
1205
|
892
|
|
|
|
|
2094
|
# Archive::Zip needs to rewind a filehandle to write the zip headers. |
|
10903
|
|
|
|
|
16612
|
|
1206
|
892
|
|
|
|
|
2165
|
# This won't work for arbitrary user defined filehandles so we use |
|
10903
|
|
|
|
|
17095
|
|
1207
|
892
|
|
|
|
|
2953
|
# a temp file based filehandle to create the zip archive and then |
1208
|
|
|
|
|
|
|
# stream that to the filehandle. |
1209
|
892
|
|
|
|
|
2086
|
my $tmp_fh = tempfile( DIR => $self->{_tempdir} ); |
|
10903
|
|
|
|
|
16352
|
|
1210
|
892
|
|
|
|
|
2316
|
my $is_seekable = 1; |
|
10903
|
|
|
|
|
17348
|
|
1211
|
892
|
|
|
|
|
3208
|
|
1212
|
|
|
|
|
|
|
if ( $zip->writeToFileHandle( $tmp_fh, $is_seekable ) != 0 ) { |
1213
|
|
|
|
|
|
|
carp 'Error writing zip container for xlsx file.'; |
1214
|
892
|
|
|
|
|
2657
|
} |
1215
|
10903
|
|
|
|
|
16252
|
|
1216
|
10903
|
|
|
|
|
29509
|
my $buffer; |
1217
|
10903
|
|
|
|
|
141963
|
seek $tmp_fh, 0, 0; |
1218
|
|
|
|
|
|
|
|
1219
|
|
|
|
|
|
|
while ( read( $tmp_fh, $buffer, 4_096 ) ) { |
1220
|
|
|
|
|
|
|
local $\ = undef; # Protect print from -l on commandline. |
1221
|
|
|
|
|
|
|
print { $self->{_filehandle} } $buffer; |
1222
|
|
|
|
|
|
|
} |
1223
|
10903
|
|
|
|
|
2481384
|
} |
1224
|
|
|
|
|
|
|
} |
1225
|
|
|
|
|
|
|
|
1226
|
892
|
100
|
|
|
|
4522
|
|
1227
|
|
|
|
|
|
|
############################################################################### |
1228
|
837
|
50
|
|
|
|
4845
|
# |
1229
|
0
|
|
|
|
|
0
|
# _prepare_sst_string_data() |
1230
|
|
|
|
|
|
|
# |
1231
|
|
|
|
|
|
|
# Convert the SST string data from a hash to an array. |
1232
|
|
|
|
|
|
|
# |
1233
|
|
|
|
|
|
|
|
1234
|
|
|
|
|
|
|
my $self = shift; |
1235
|
|
|
|
|
|
|
|
1236
|
|
|
|
|
|
|
my @strings; |
1237
|
|
|
|
|
|
|
$#strings = $self->{_str_unique} - 1; # Pre-extend array |
1238
|
55
|
|
|
|
|
253
|
|
1239
|
55
|
|
|
|
|
26247
|
while ( my $key = each %{ $self->{_str_table} } ) { |
1240
|
|
|
|
|
|
|
$strings[ $self->{_str_table}->{$key} ] = $key; |
1241
|
55
|
50
|
|
|
|
299
|
} |
1242
|
0
|
|
|
|
|
0
|
|
1243
|
|
|
|
|
|
|
# The SST data could be very large, free some memory (maybe). |
1244
|
|
|
|
|
|
|
$self->{_str_table} = undef; |
1245
|
55
|
|
|
|
|
693609
|
$self->{_str_array} = \@strings; |
1246
|
55
|
|
|
|
|
1134
|
|
1247
|
|
|
|
|
|
|
} |
1248
|
55
|
|
|
|
|
948
|
|
1249
|
110
|
|
|
|
|
467
|
|
1250
|
110
|
|
|
|
|
179
|
############################################################################### |
|
110
|
|
|
|
|
5588
|
|
1251
|
|
|
|
|
|
|
# |
1252
|
|
|
|
|
|
|
# _prepare_format_properties() |
1253
|
|
|
|
|
|
|
# |
1254
|
|
|
|
|
|
|
# Prepare all of the format properties prior to passing them to Styles.pm. |
1255
|
|
|
|
|
|
|
# |
1256
|
|
|
|
|
|
|
|
1257
|
|
|
|
|
|
|
my $self = shift; |
1258
|
|
|
|
|
|
|
|
1259
|
|
|
|
|
|
|
# Separate format objects into XF and DXF formats. |
1260
|
|
|
|
|
|
|
$self->_prepare_formats(); |
1261
|
|
|
|
|
|
|
|
1262
|
|
|
|
|
|
|
# Set the font index for the format objects. |
1263
|
|
|
|
|
|
|
$self->_prepare_fonts(); |
1264
|
892
|
|
|
892
|
|
2223
|
|
1265
|
|
|
|
|
|
|
# Set the number format index for the format objects. |
1266
|
892
|
|
|
|
|
1994
|
$self->_prepare_num_formats(); |
1267
|
892
|
|
|
|
|
4067
|
|
1268
|
|
|
|
|
|
|
# Set the border index for the format objects. |
1269
|
892
|
|
|
|
|
2052
|
$self->_prepare_borders(); |
|
1961
|
|
|
|
|
7004
|
|
1270
|
1069
|
|
|
|
|
2044
|
|
1271
|
|
|
|
|
|
|
# Set the fill index for the format objects. |
1272
|
|
|
|
|
|
|
$self->_prepare_fills(); |
1273
|
|
|
|
|
|
|
|
1274
|
892
|
|
|
|
|
2699
|
|
1275
|
892
|
|
|
|
|
2724
|
} |
1276
|
|
|
|
|
|
|
|
1277
|
|
|
|
|
|
|
|
1278
|
|
|
|
|
|
|
############################################################################### |
1279
|
|
|
|
|
|
|
# |
1280
|
|
|
|
|
|
|
# _prepare_formats() |
1281
|
|
|
|
|
|
|
# |
1282
|
|
|
|
|
|
|
# Iterate through the XF Format objects and separate them into XF and DXF |
1283
|
|
|
|
|
|
|
# formats. |
1284
|
|
|
|
|
|
|
# |
1285
|
|
|
|
|
|
|
|
1286
|
|
|
|
|
|
|
my $self = shift; |
1287
|
|
|
|
|
|
|
|
1288
|
904
|
|
|
904
|
|
1807
|
for my $format ( @{ $self->{_formats} } ) { |
1289
|
|
|
|
|
|
|
my $xf_index = $format->{_xf_index}; |
1290
|
|
|
|
|
|
|
my $dxf_index = $format->{_dxf_index}; |
1291
|
904
|
|
|
|
|
5871
|
|
1292
|
|
|
|
|
|
|
if ( defined $xf_index ) { |
1293
|
|
|
|
|
|
|
$self->{_xf_formats}->[$xf_index] = $format; |
1294
|
904
|
|
|
|
|
5202
|
} |
1295
|
|
|
|
|
|
|
|
1296
|
|
|
|
|
|
|
if ( defined $dxf_index ) { |
1297
|
904
|
|
|
|
|
5571
|
$self->{_dxf_formats}->[$dxf_index] = $format; |
1298
|
|
|
|
|
|
|
} |
1299
|
|
|
|
|
|
|
} |
1300
|
904
|
|
|
|
|
5742
|
} |
1301
|
|
|
|
|
|
|
|
1302
|
|
|
|
|
|
|
|
1303
|
904
|
|
|
|
|
4427
|
############################################################################### |
1304
|
|
|
|
|
|
|
# |
1305
|
|
|
|
|
|
|
# _set_default_xf_indices() |
1306
|
|
|
|
|
|
|
# |
1307
|
|
|
|
|
|
|
# Set the default index for each format. This is mainly used for testing. |
1308
|
|
|
|
|
|
|
# |
1309
|
|
|
|
|
|
|
|
1310
|
|
|
|
|
|
|
my $self = shift; |
1311
|
|
|
|
|
|
|
|
1312
|
|
|
|
|
|
|
# Delete the default url format. |
1313
|
|
|
|
|
|
|
splice @{ $self->{_formats} }, 1, 1; |
1314
|
|
|
|
|
|
|
|
1315
|
|
|
|
|
|
|
for my $format ( @{ $self->{_formats} } ) { |
1316
|
|
|
|
|
|
|
$format->get_xf_index(); |
1317
|
|
|
|
|
|
|
} |
1318
|
904
|
|
|
904
|
|
1947
|
} |
1319
|
|
|
|
|
|
|
|
1320
|
904
|
|
|
|
|
1623
|
|
|
904
|
|
|
|
|
3128
|
|
1321
|
2087
|
|
|
|
|
3706
|
############################################################################### |
1322
|
2087
|
|
|
|
|
3295
|
# |
1323
|
|
|
|
|
|
|
# _prepare_fonts() |
1324
|
2087
|
100
|
|
|
|
4721
|
# |
1325
|
1173
|
|
|
|
|
2846
|
# Iterate through the XF Format objects and give them an index to non-default |
1326
|
|
|
|
|
|
|
# font elements. |
1327
|
|
|
|
|
|
|
# |
1328
|
2087
|
100
|
|
|
|
5141
|
|
1329
|
33
|
|
|
|
|
88
|
my $self = shift; |
1330
|
|
|
|
|
|
|
|
1331
|
|
|
|
|
|
|
my %fonts; |
1332
|
|
|
|
|
|
|
my $index = 0; |
1333
|
|
|
|
|
|
|
|
1334
|
|
|
|
|
|
|
for my $format ( @{ $self->{_xf_formats} } ) { |
1335
|
|
|
|
|
|
|
my $key = $format->get_font_key(); |
1336
|
|
|
|
|
|
|
|
1337
|
|
|
|
|
|
|
if ( exists $fonts{$key} ) { |
1338
|
|
|
|
|
|
|
|
1339
|
|
|
|
|
|
|
# Font has already been used. |
1340
|
|
|
|
|
|
|
$format->{_font_index} = $fonts{$key}; |
1341
|
|
|
|
|
|
|
$format->{_has_font} = 0; |
1342
|
|
|
|
|
|
|
} |
1343
|
9
|
|
|
9
|
|
68
|
else { |
1344
|
|
|
|
|
|
|
|
1345
|
|
|
|
|
|
|
# This is a new font. |
1346
|
9
|
|
|
|
|
16
|
$fonts{$key} = $index; |
|
9
|
|
|
|
|
25
|
|
1347
|
|
|
|
|
|
|
$format->{_font_index} = $index; |
1348
|
9
|
|
|
|
|
16
|
$format->{_has_font} = 1; |
|
9
|
|
|
|
|
25
|
|
1349
|
49
|
|
|
|
|
95
|
$index++; |
1350
|
|
|
|
|
|
|
} |
1351
|
|
|
|
|
|
|
} |
1352
|
|
|
|
|
|
|
|
1353
|
|
|
|
|
|
|
$self->{_font_count} = $index; |
1354
|
|
|
|
|
|
|
|
1355
|
|
|
|
|
|
|
# For the DXF formats we only need to check if the properties have changed. |
1356
|
|
|
|
|
|
|
for my $format ( @{ $self->{_dxf_formats} } ) { |
1357
|
|
|
|
|
|
|
|
1358
|
|
|
|
|
|
|
# The only font properties that can change for a DXF format are: color, |
1359
|
|
|
|
|
|
|
# bold, italic, underline and strikethrough. |
1360
|
|
|
|
|
|
|
if ( $format->{_color} |
1361
|
|
|
|
|
|
|
|| $format->{_bold} |
1362
|
|
|
|
|
|
|
|| $format->{_italic} |
1363
|
904
|
|
|
904
|
|
1787
|
|| $format->{_underline} |
1364
|
|
|
|
|
|
|
|| $format->{_font_strikeout} ) |
1365
|
904
|
|
|
|
|
1794
|
{ |
1366
|
904
|
|
|
|
|
1696
|
$format->{_has_dxf_font} = 1; |
1367
|
|
|
|
|
|
|
} |
1368
|
904
|
|
|
|
|
1708
|
} |
|
904
|
|
|
|
|
2616
|
|
1369
|
1173
|
|
|
|
|
4640
|
} |
1370
|
|
|
|
|
|
|
|
1371
|
1173
|
100
|
|
|
|
4033
|
|
1372
|
|
|
|
|
|
|
############################################################################### |
1373
|
|
|
|
|
|
|
# |
1374
|
130
|
|
|
|
|
219
|
# _prepare_num_formats() |
1375
|
130
|
|
|
|
|
253
|
# |
1376
|
|
|
|
|
|
|
# Iterate through the XF Format objects and give them an index to non-default |
1377
|
|
|
|
|
|
|
# number format elements. |
1378
|
|
|
|
|
|
|
# |
1379
|
|
|
|
|
|
|
# User defined records start from index 0xA4. |
1380
|
1043
|
|
|
|
|
2866
|
# |
1381
|
1043
|
|
|
|
|
2321
|
|
1382
|
1043
|
|
|
|
|
1992
|
my $self = shift; |
1383
|
1043
|
|
|
|
|
2313
|
|
1384
|
|
|
|
|
|
|
my %num_formats; |
1385
|
|
|
|
|
|
|
my $index = 164; |
1386
|
|
|
|
|
|
|
my $num_format_count = 0; |
1387
|
904
|
|
|
|
|
2038
|
|
1388
|
|
|
|
|
|
|
for my $format ( @{ $self->{_xf_formats} }, @{ $self->{_dxf_formats} } ) { |
1389
|
|
|
|
|
|
|
my $num_format = $format->{_num_format}; |
1390
|
904
|
|
|
|
|
1702
|
|
|
904
|
|
|
|
|
3463
|
|
1391
|
|
|
|
|
|
|
|
1392
|
|
|
|
|
|
|
# Check if $num_format is an index to a built-in number format. |
1393
|
|
|
|
|
|
|
# Also check for a string of zeros, which is a valid number format |
1394
|
33
|
100
|
100
|
|
|
389
|
# string but would evaluate to zero. |
|
|
|
66
|
|
|
|
|
|
|
|
66
|
|
|
|
|
|
|
|
100
|
|
|
|
|
1395
|
|
|
|
|
|
|
# |
1396
|
|
|
|
|
|
|
if ( $num_format =~ m/^\d+$/ && $num_format !~ m/^0+\d/ ) { |
1397
|
|
|
|
|
|
|
|
1398
|
|
|
|
|
|
|
# Number format '0' is indexed as 1 in Excel. |
1399
|
|
|
|
|
|
|
if ($num_format == 0) { |
1400
|
11
|
|
|
|
|
36
|
$num_format = 1; |
1401
|
|
|
|
|
|
|
} |
1402
|
|
|
|
|
|
|
|
1403
|
|
|
|
|
|
|
# Index to a built-in number format. |
1404
|
|
|
|
|
|
|
$format->{_num_format_index} = $num_format; |
1405
|
|
|
|
|
|
|
next; |
1406
|
|
|
|
|
|
|
} |
1407
|
|
|
|
|
|
|
elsif ( $num_format eq 'General' ) { |
1408
|
|
|
|
|
|
|
# The 'General' format has an number format index of 0. |
1409
|
|
|
|
|
|
|
$format->{_num_format_index} = 0; |
1410
|
|
|
|
|
|
|
next; |
1411
|
|
|
|
|
|
|
} |
1412
|
|
|
|
|
|
|
|
1413
|
|
|
|
|
|
|
|
1414
|
|
|
|
|
|
|
if ( exists( $num_formats{$num_format} ) ) { |
1415
|
|
|
|
|
|
|
|
1416
|
|
|
|
|
|
|
# Number format has already been used. |
1417
|
904
|
|
|
904
|
|
2017
|
$format->{_num_format_index} = $num_formats{$num_format}; |
1418
|
|
|
|
|
|
|
} |
1419
|
904
|
|
|
|
|
1802
|
else { |
1420
|
904
|
|
|
|
|
1705
|
|
1421
|
904
|
|
|
|
|
1723
|
# Add a new number format. |
1422
|
|
|
|
|
|
|
$num_formats{$num_format} = $index; |
1423
|
904
|
|
|
|
|
1723
|
$format->{_num_format_index} = $index; |
|
904
|
|
|
|
|
2222
|
|
|
904
|
|
|
|
|
2506
|
|
1424
|
1206
|
|
|
|
|
2879
|
$index++; |
1425
|
|
|
|
|
|
|
|
1426
|
|
|
|
|
|
|
# Only increase font count for XF formats (not for DXF formats). |
1427
|
|
|
|
|
|
|
if ( $format->{_xf_index} ) { |
1428
|
|
|
|
|
|
|
$num_format_count++; |
1429
|
|
|
|
|
|
|
} |
1430
|
|
|
|
|
|
|
} |
1431
|
1206
|
100
|
66
|
|
|
7910
|
} |
|
|
100
|
|
|
|
|
|
1432
|
|
|
|
|
|
|
|
1433
|
|
|
|
|
|
|
$self->{_num_format_count} = $num_format_count; |
1434
|
34
|
100
|
|
|
|
128
|
} |
1435
|
1
|
|
|
|
|
2
|
|
1436
|
|
|
|
|
|
|
|
1437
|
|
|
|
|
|
|
############################################################################### |
1438
|
|
|
|
|
|
|
# |
1439
|
34
|
|
|
|
|
68
|
# _prepare_borders() |
1440
|
34
|
|
|
|
|
75
|
# |
1441
|
|
|
|
|
|
|
# Iterate through the XF Format objects and give them an index to non-default |
1442
|
|
|
|
|
|
|
# border elements. |
1443
|
|
|
|
|
|
|
# |
1444
|
1149
|
|
|
|
|
2495
|
|
1445
|
1149
|
|
|
|
|
2745
|
my $self = shift; |
1446
|
|
|
|
|
|
|
|
1447
|
|
|
|
|
|
|
my %borders; |
1448
|
|
|
|
|
|
|
my $index = 0; |
1449
|
23
|
100
|
|
|
|
53
|
|
1450
|
|
|
|
|
|
|
for my $format ( @{ $self->{_xf_formats} } ) { |
1451
|
|
|
|
|
|
|
my $key = $format->get_border_key(); |
1452
|
3
|
|
|
|
|
5
|
|
1453
|
|
|
|
|
|
|
if ( exists $borders{$key} ) { |
1454
|
|
|
|
|
|
|
|
1455
|
|
|
|
|
|
|
# Border has already been used. |
1456
|
|
|
|
|
|
|
$format->{_border_index} = $borders{$key}; |
1457
|
20
|
|
|
|
|
42
|
$format->{_has_border} = 0; |
1458
|
20
|
|
|
|
|
29
|
} |
1459
|
20
|
|
|
|
|
32
|
else { |
1460
|
|
|
|
|
|
|
|
1461
|
|
|
|
|
|
|
# This is a new border. |
1462
|
20
|
100
|
|
|
|
45
|
$borders{$key} = $index; |
1463
|
18
|
|
|
|
|
31
|
$format->{_border_index} = $index; |
1464
|
|
|
|
|
|
|
$format->{_has_border} = 1; |
1465
|
|
|
|
|
|
|
$index++; |
1466
|
|
|
|
|
|
|
} |
1467
|
|
|
|
|
|
|
} |
1468
|
904
|
|
|
|
|
2630
|
|
1469
|
|
|
|
|
|
|
$self->{_border_count} = $index; |
1470
|
|
|
|
|
|
|
|
1471
|
|
|
|
|
|
|
# For the DXF formats we only need to check if the properties have changed. |
1472
|
|
|
|
|
|
|
for my $format ( @{ $self->{_dxf_formats} } ) { |
1473
|
|
|
|
|
|
|
my $key = $format->get_border_key(); |
1474
|
|
|
|
|
|
|
|
1475
|
|
|
|
|
|
|
if ( $key =~ m/[^0:]/ ) { |
1476
|
|
|
|
|
|
|
$format->{_has_dxf_border} = 1; |
1477
|
|
|
|
|
|
|
} |
1478
|
|
|
|
|
|
|
} |
1479
|
|
|
|
|
|
|
|
1480
|
|
|
|
|
|
|
} |
1481
|
904
|
|
|
904
|
|
2127
|
|
1482
|
|
|
|
|
|
|
|
1483
|
904
|
|
|
|
|
1698
|
############################################################################### |
1484
|
904
|
|
|
|
|
1932
|
# |
1485
|
|
|
|
|
|
|
# _prepare_fills() |
1486
|
904
|
|
|
|
|
1911
|
# |
|
904
|
|
|
|
|
2599
|
|
1487
|
1173
|
|
|
|
|
3997
|
# Iterate through the XF Format objects and give them an index to non-default |
1488
|
|
|
|
|
|
|
# fill elements. |
1489
|
1173
|
100
|
|
|
|
3473
|
# |
1490
|
|
|
|
|
|
|
# The user defined fill properties start from 2 since there are 2 default |
1491
|
|
|
|
|
|
|
# fills: patternType="none" and patternType="gray125". |
1492
|
224
|
|
|
|
|
404
|
# |
1493
|
224
|
|
|
|
|
467
|
|
1494
|
|
|
|
|
|
|
my $self = shift; |
1495
|
|
|
|
|
|
|
|
1496
|
|
|
|
|
|
|
my %fills; |
1497
|
|
|
|
|
|
|
my $index = 2; # Start from 2. See above. |
1498
|
949
|
|
|
|
|
2446
|
|
1499
|
949
|
|
|
|
|
2144
|
# Add the default fills. |
1500
|
949
|
|
|
|
|
2084
|
$fills{'0:0:0'} = 0; |
1501
|
949
|
|
|
|
|
2222
|
$fills{'17:0:0'} = 1; |
1502
|
|
|
|
|
|
|
|
1503
|
|
|
|
|
|
|
|
1504
|
|
|
|
|
|
|
# Store the DXF colours separately since them may be reversed below. |
1505
|
904
|
|
|
|
|
2416
|
for my $format ( @{ $self->{_dxf_formats} } ) { |
1506
|
|
|
|
|
|
|
if ( $format->{_pattern} |
1507
|
|
|
|
|
|
|
|| $format->{_bg_color} |
1508
|
904
|
|
|
|
|
1602
|
|| $format->{_fg_color} ) |
|
904
|
|
|
|
|
2910
|
|
1509
|
33
|
|
|
|
|
91
|
{ |
1510
|
|
|
|
|
|
|
$format->{_has_dxf_fill} = 1; |
1511
|
33
|
100
|
|
|
|
160
|
$format->{_dxf_bg_color} = $format->{_bg_color}; |
1512
|
1
|
|
|
|
|
3
|
$format->{_dxf_fg_color} = $format->{_fg_color}; |
1513
|
|
|
|
|
|
|
} |
1514
|
|
|
|
|
|
|
} |
1515
|
|
|
|
|
|
|
|
1516
|
|
|
|
|
|
|
|
1517
|
|
|
|
|
|
|
for my $format ( @{ $self->{_xf_formats} } ) { |
1518
|
|
|
|
|
|
|
|
1519
|
|
|
|
|
|
|
# The following logical statements jointly take care of special cases |
1520
|
|
|
|
|
|
|
# in relation to cell colours and patterns: |
1521
|
|
|
|
|
|
|
# 1. For a solid fill (_pattern == 1) Excel reverses the role of |
1522
|
|
|
|
|
|
|
# foreground and background colours, and |
1523
|
|
|
|
|
|
|
# 2. If the user specifies a foreground or background colour without |
1524
|
|
|
|
|
|
|
# a pattern they probably wanted a solid fill, so we fill in the |
1525
|
|
|
|
|
|
|
# defaults. |
1526
|
|
|
|
|
|
|
# |
1527
|
|
|
|
|
|
|
if ( $format->{_pattern} == 1 |
1528
|
|
|
|
|
|
|
&& $format->{_bg_color} ne '0' |
1529
|
|
|
|
|
|
|
&& $format->{_fg_color} ne '0' ) |
1530
|
|
|
|
|
|
|
{ |
1531
|
904
|
|
|
904
|
|
1845
|
my $tmp = $format->{_fg_color}; |
1532
|
|
|
|
|
|
|
$format->{_fg_color} = $format->{_bg_color}; |
1533
|
904
|
|
|
|
|
1695
|
$format->{_bg_color} = $tmp; |
1534
|
904
|
|
|
|
|
1694
|
} |
1535
|
|
|
|
|
|
|
|
1536
|
|
|
|
|
|
|
if ( $format->{_pattern} <= 1 |
1537
|
904
|
|
|
|
|
2358
|
&& $format->{_bg_color} ne '0' |
1538
|
904
|
|
|
|
|
2080
|
&& $format->{_fg_color} eq '0' ) |
1539
|
|
|
|
|
|
|
{ |
1540
|
|
|
|
|
|
|
$format->{_fg_color} = $format->{_bg_color}; |
1541
|
|
|
|
|
|
|
$format->{_bg_color} = 0; |
1542
|
904
|
|
|
|
|
1657
|
$format->{_pattern} = 1; |
|
904
|
|
|
|
|
2806
|
|
1543
|
33
|
100
|
100
|
|
|
242
|
} |
|
|
|
66
|
|
|
|
|
1544
|
|
|
|
|
|
|
|
1545
|
|
|
|
|
|
|
if ( $format->{_pattern} <= 1 |
1546
|
|
|
|
|
|
|
&& $format->{_bg_color} eq '0' |
1547
|
17
|
|
|
|
|
36
|
&& $format->{_fg_color} ne '0' ) |
1548
|
17
|
|
|
|
|
129
|
{ |
1549
|
17
|
|
|
|
|
64
|
$format->{_bg_color} = 0; |
1550
|
|
|
|
|
|
|
$format->{_pattern} = 1; |
1551
|
|
|
|
|
|
|
} |
1552
|
|
|
|
|
|
|
|
1553
|
|
|
|
|
|
|
|
1554
|
904
|
|
|
|
|
1817
|
my $key = $format->get_fill_key(); |
|
904
|
|
|
|
|
2417
|
|
1555
|
|
|
|
|
|
|
|
1556
|
|
|
|
|
|
|
if ( exists $fills{$key} ) { |
1557
|
|
|
|
|
|
|
|
1558
|
|
|
|
|
|
|
# Fill has already been used. |
1559
|
|
|
|
|
|
|
$format->{_fill_index} = $fills{$key}; |
1560
|
|
|
|
|
|
|
$format->{_has_fill} = 0; |
1561
|
|
|
|
|
|
|
} |
1562
|
|
|
|
|
|
|
else { |
1563
|
|
|
|
|
|
|
|
1564
|
1173
|
100
|
100
|
|
|
4986
|
# This is a new fill. |
|
|
|
100
|
|
|
|
|
1565
|
|
|
|
|
|
|
$fills{$key} = $index; |
1566
|
|
|
|
|
|
|
$format->{_fill_index} = $index; |
1567
|
|
|
|
|
|
|
$format->{_has_fill} = 1; |
1568
|
3
|
|
|
|
|
5
|
$index++; |
1569
|
3
|
|
|
|
|
11
|
} |
1570
|
3
|
|
|
|
|
5
|
} |
1571
|
|
|
|
|
|
|
|
1572
|
|
|
|
|
|
|
$self->{_fill_count} = $index; |
1573
|
1173
|
100
|
100
|
|
|
8663
|
|
|
|
|
100
|
|
|
|
|
1574
|
|
|
|
|
|
|
|
1575
|
|
|
|
|
|
|
} |
1576
|
|
|
|
|
|
|
|
1577
|
6
|
|
|
|
|
16
|
|
1578
|
6
|
|
|
|
|
8
|
############################################################################### |
1579
|
6
|
|
|
|
|
9
|
# |
1580
|
|
|
|
|
|
|
# _prepare_defined_names() |
1581
|
|
|
|
|
|
|
# |
1582
|
1173
|
100
|
100
|
|
|
10335
|
# Iterate through the worksheets and store any defined names in addition to |
|
|
|
100
|
|
|
|
|
1583
|
|
|
|
|
|
|
# any user defined names. Stores the defined names for the Workbook.xml and |
1584
|
|
|
|
|
|
|
# the named ranges for App.xml. |
1585
|
|
|
|
|
|
|
# |
1586
|
13
|
|
|
|
|
17
|
|
1587
|
13
|
|
|
|
|
16
|
my $self = shift; |
1588
|
|
|
|
|
|
|
|
1589
|
|
|
|
|
|
|
my @defined_names = @{ $self->{_defined_names} }; |
1590
|
|
|
|
|
|
|
|
1591
|
1173
|
|
|
|
|
4314
|
for my $sheet ( @{ $self->{_worksheets} } ) { |
1592
|
|
|
|
|
|
|
|
1593
|
1173
|
100
|
|
|
|
3542
|
# Check for Print Area settings. |
1594
|
|
|
|
|
|
|
if ( $sheet->{_autofilter} ) { |
1595
|
|
|
|
|
|
|
|
1596
|
1155
|
|
|
|
|
2646
|
my $range = $sheet->{_autofilter}; |
1597
|
1155
|
|
|
|
|
2779
|
my $hidden = 1; |
1598
|
|
|
|
|
|
|
|
1599
|
|
|
|
|
|
|
# Store the defined names. |
1600
|
|
|
|
|
|
|
push @defined_names, |
1601
|
|
|
|
|
|
|
[ '_xlnm._FilterDatabase', $sheet->{_index}, $range, $hidden ]; |
1602
|
18
|
|
|
|
|
38
|
|
1603
|
18
|
|
|
|
|
28
|
} |
1604
|
18
|
|
|
|
|
28
|
|
1605
|
18
|
|
|
|
|
27
|
# Check for Print Area settings. |
1606
|
|
|
|
|
|
|
if ( $sheet->{_print_area} ) { |
1607
|
|
|
|
|
|
|
|
1608
|
|
|
|
|
|
|
my $range = $sheet->{_print_area}; |
1609
|
904
|
|
|
|
|
2540
|
|
1610
|
|
|
|
|
|
|
# Store the defined names. |
1611
|
|
|
|
|
|
|
push @defined_names, |
1612
|
|
|
|
|
|
|
[ '_xlnm.Print_Area', $sheet->{_index}, $range ]; |
1613
|
|
|
|
|
|
|
} |
1614
|
|
|
|
|
|
|
|
1615
|
|
|
|
|
|
|
# Check for repeat rows/cols. aka, Print Titles. |
1616
|
|
|
|
|
|
|
if ( $sheet->{_repeat_cols} || $sheet->{_repeat_rows} ) { |
1617
|
|
|
|
|
|
|
my $range = ''; |
1618
|
|
|
|
|
|
|
|
1619
|
|
|
|
|
|
|
if ( $sheet->{_repeat_cols} && $sheet->{_repeat_rows} ) { |
1620
|
|
|
|
|
|
|
$range = $sheet->{_repeat_cols} . ',' . $sheet->{_repeat_rows}; |
1621
|
|
|
|
|
|
|
} |
1622
|
|
|
|
|
|
|
else { |
1623
|
|
|
|
|
|
|
$range = $sheet->{_repeat_cols} . $sheet->{_repeat_rows}; |
1624
|
|
|
|
|
|
|
} |
1625
|
893
|
|
|
893
|
|
1954
|
|
1626
|
|
|
|
|
|
|
# Store the defined names. |
1627
|
893
|
|
|
|
|
2048
|
push @defined_names, |
|
893
|
|
|
|
|
2672
|
|
1628
|
|
|
|
|
|
|
[ '_xlnm.Print_Titles', $sheet->{_index}, $range ]; |
1629
|
893
|
|
|
|
|
1931
|
} |
|
893
|
|
|
|
|
2689
|
|
1630
|
|
|
|
|
|
|
|
1631
|
|
|
|
|
|
|
} |
1632
|
1044
|
100
|
|
|
|
3695
|
|
1633
|
|
|
|
|
|
|
@defined_names = _sort_defined_names( @defined_names ); |
1634
|
11
|
|
|
|
|
44
|
$self->{_defined_names} = \@defined_names; |
1635
|
11
|
|
|
|
|
21
|
$self->{_named_ranges} = _extract_named_ranges( @defined_names ); |
1636
|
|
|
|
|
|
|
} |
1637
|
|
|
|
|
|
|
|
1638
|
|
|
|
|
|
|
|
1639
|
11
|
|
|
|
|
46
|
############################################################################### |
1640
|
|
|
|
|
|
|
# |
1641
|
|
|
|
|
|
|
# _sort_defined_names() |
1642
|
|
|
|
|
|
|
# |
1643
|
|
|
|
|
|
|
# Sort internal and user defined names in the same order as used by Excel. |
1644
|
1044
|
100
|
|
|
|
3224
|
# This may not be strictly necessary but unsorted elements caused a lot of |
1645
|
|
|
|
|
|
|
# issues in the Spreadsheet::WriteExcel binary version. Also makes |
1646
|
8
|
|
|
|
|
20
|
# comparison testing easier. |
1647
|
|
|
|
|
|
|
# |
1648
|
|
|
|
|
|
|
|
1649
|
|
|
|
|
|
|
my @names = @_; |
1650
|
8
|
|
|
|
|
26
|
|
1651
|
|
|
|
|
|
|
#<<< Perltidy ignore this. |
1652
|
|
|
|
|
|
|
|
1653
|
|
|
|
|
|
|
@names = sort { |
1654
|
1044
|
100
|
100
|
|
|
6751
|
# Primary sort based on the defined name. |
1655
|
7
|
|
|
|
|
25
|
_normalise_defined_name( $a->[0] ) |
1656
|
|
|
|
|
|
|
cmp |
1657
|
7
|
100
|
100
|
|
|
33
|
_normalise_defined_name( $b->[0] ) |
1658
|
2
|
|
|
|
|
6
|
|
1659
|
|
|
|
|
|
|
|| |
1660
|
|
|
|
|
|
|
# Secondary sort based on the sheet name. |
1661
|
5
|
|
|
|
|
15
|
_normalise_sheet_name( $a->[2] ) |
1662
|
|
|
|
|
|
|
cmp |
1663
|
|
|
|
|
|
|
_normalise_sheet_name( $b->[2] ) |
1664
|
|
|
|
|
|
|
|
1665
|
|
|
|
|
|
|
} @names; |
1666
|
7
|
|
|
|
|
34
|
#>>> |
1667
|
|
|
|
|
|
|
|
1668
|
|
|
|
|
|
|
return @names; |
1669
|
|
|
|
|
|
|
} |
1670
|
|
|
|
|
|
|
|
1671
|
893
|
|
|
|
|
3863
|
# Used in the above sort routine to normalise the defined names. Removes any |
1672
|
893
|
|
|
|
|
2229
|
# leading '_xmln.' from internal names and lowercases the strings. |
1673
|
893
|
|
|
|
|
3704
|
my $name = shift; |
1674
|
|
|
|
|
|
|
|
1675
|
|
|
|
|
|
|
$name =~ s/^_xlnm.//; |
1676
|
|
|
|
|
|
|
$name = lc $name; |
1677
|
|
|
|
|
|
|
|
1678
|
|
|
|
|
|
|
return $name; |
1679
|
|
|
|
|
|
|
} |
1680
|
|
|
|
|
|
|
|
1681
|
|
|
|
|
|
|
# Used in the above sort routine to normalise the worksheet names for the |
1682
|
|
|
|
|
|
|
# secondary sort. Removes leading quote and lowercases the strings. |
1683
|
|
|
|
|
|
|
my $name = shift; |
1684
|
|
|
|
|
|
|
|
1685
|
|
|
|
|
|
|
$name =~ s/^'//; |
1686
|
|
|
|
|
|
|
$name = lc $name; |
1687
|
|
|
|
|
|
|
|
1688
|
894
|
|
|
894
|
|
2407
|
return $name; |
1689
|
|
|
|
|
|
|
} |
1690
|
|
|
|
|
|
|
|
1691
|
|
|
|
|
|
|
|
1692
|
|
|
|
|
|
|
############################################################################### |
1693
|
|
|
|
|
|
|
# |
1694
|
894
|
50
|
|
|
|
2864
|
# _extract_named_ranges() |
|
117
|
|
|
|
|
181
|
|
1695
|
|
|
|
|
|
|
# |
1696
|
|
|
|
|
|
|
# Extract the named ranges from the sorted list of defined names. These are |
1697
|
|
|
|
|
|
|
# used in the App.xml file. |
1698
|
|
|
|
|
|
|
# |
1699
|
|
|
|
|
|
|
|
1700
|
|
|
|
|
|
|
my @defined_names = @_; |
1701
|
|
|
|
|
|
|
my @named_ranges; |
1702
|
|
|
|
|
|
|
|
1703
|
|
|
|
|
|
|
NAME: |
1704
|
|
|
|
|
|
|
for my $defined_name ( @defined_names ) { |
1705
|
|
|
|
|
|
|
|
1706
|
|
|
|
|
|
|
my $name = $defined_name->[0]; |
1707
|
894
|
|
|
|
|
2029
|
my $index = $defined_name->[1]; |
1708
|
|
|
|
|
|
|
my $range = $defined_name->[2]; |
1709
|
|
|
|
|
|
|
|
1710
|
|
|
|
|
|
|
# Skip autoFilter ranges. |
1711
|
|
|
|
|
|
|
next NAME if $name eq '_xlnm._FilterDatabase'; |
1712
|
|
|
|
|
|
|
|
1713
|
234
|
|
|
234
|
|
265
|
# We are only interested in defined names with ranges. |
1714
|
|
|
|
|
|
|
if ( $range =~ /^([^!]+)!/ ) { |
1715
|
234
|
|
|
|
|
296
|
my $sheet_name = $1; |
1716
|
234
|
|
|
|
|
278
|
|
1717
|
|
|
|
|
|
|
# Match Print_Area and Print_Titles xlnm types. |
1718
|
234
|
|
|
|
|
461
|
if ( $name =~ /^_xlnm\.(.*)$/ ) { |
1719
|
|
|
|
|
|
|
my $xlnm_type = $1; |
1720
|
|
|
|
|
|
|
$name = $sheet_name . '!' . $xlnm_type; |
1721
|
|
|
|
|
|
|
} |
1722
|
|
|
|
|
|
|
elsif ( $index != -1 ) { |
1723
|
|
|
|
|
|
|
$name = $sheet_name . '!' . $name; |
1724
|
24
|
|
|
24
|
|
31
|
} |
1725
|
|
|
|
|
|
|
|
1726
|
24
|
|
|
|
|
40
|
push @named_ranges, $name; |
1727
|
24
|
|
|
|
|
40
|
} |
1728
|
|
|
|
|
|
|
} |
1729
|
24
|
|
|
|
|
40
|
|
1730
|
|
|
|
|
|
|
return \@named_ranges; |
1731
|
|
|
|
|
|
|
} |
1732
|
|
|
|
|
|
|
|
1733
|
|
|
|
|
|
|
|
1734
|
|
|
|
|
|
|
############################################################################### |
1735
|
|
|
|
|
|
|
# |
1736
|
|
|
|
|
|
|
# _prepare_drawings() |
1737
|
|
|
|
|
|
|
# |
1738
|
|
|
|
|
|
|
# Iterate through the worksheets and set up any chart or image drawings. |
1739
|
|
|
|
|
|
|
# |
1740
|
|
|
|
|
|
|
|
1741
|
|
|
|
|
|
|
my $self = shift; |
1742
|
894
|
|
|
894
|
|
5421
|
my $chart_ref_id = 0; |
1743
|
894
|
|
|
|
|
1652
|
my $image_ref_id = 0; |
1744
|
|
|
|
|
|
|
my $drawing_id = 0; |
1745
|
|
|
|
|
|
|
my $ref_id = 0; |
1746
|
894
|
|
|
|
|
2456
|
my %image_ids = (); |
1747
|
|
|
|
|
|
|
my %header_image_ids = (); |
1748
|
76
|
|
|
|
|
123
|
my %background_ids = (); |
1749
|
76
|
|
|
|
|
106
|
|
1750
|
76
|
|
|
|
|
103
|
for my $sheet ( @{ $self->{_worksheets} } ) { |
1751
|
|
|
|
|
|
|
|
1752
|
|
|
|
|
|
|
my $chart_count = scalar @{ $sheet->{_charts} }; |
1753
|
76
|
100
|
|
|
|
171
|
my $image_count = scalar @{ $sheet->{_images} }; |
1754
|
|
|
|
|
|
|
my $shape_count = scalar @{ $sheet->{_shapes} }; |
1755
|
|
|
|
|
|
|
|
1756
|
64
|
100
|
|
|
|
253
|
my $header_image_count = scalar @{ $sheet->{_header_images} }; |
1757
|
57
|
|
|
|
|
138
|
my $footer_image_count = scalar @{ $sheet->{_footer_images} }; |
1758
|
|
|
|
|
|
|
my $has_background = $sheet->{_background_image}; |
1759
|
|
|
|
|
|
|
my $has_drawing = 0; |
1760
|
57
|
100
|
|
|
|
179
|
|
|
|
100
|
|
|
|
|
|
1761
|
18
|
|
|
|
|
48
|
|
1762
|
18
|
|
|
|
|
53
|
# Check that some image or drawing needs to be processed. |
1763
|
|
|
|
|
|
|
if ( !$chart_count |
1764
|
|
|
|
|
|
|
&& !$image_count |
1765
|
16
|
|
|
|
|
28
|
&& !$shape_count |
1766
|
|
|
|
|
|
|
&& !$header_image_count |
1767
|
|
|
|
|
|
|
&& !$footer_image_count |
1768
|
57
|
|
|
|
|
128
|
&& !$has_background ) |
1769
|
|
|
|
|
|
|
{ |
1770
|
|
|
|
|
|
|
next; |
1771
|
|
|
|
|
|
|
} |
1772
|
894
|
|
|
|
|
2567
|
|
1773
|
|
|
|
|
|
|
# Don't increase the drawing_id header/footer images. |
1774
|
|
|
|
|
|
|
if ( $chart_count || $image_count || $shape_count ) { |
1775
|
|
|
|
|
|
|
$drawing_id++; |
1776
|
|
|
|
|
|
|
$has_drawing = 1; |
1777
|
|
|
|
|
|
|
} |
1778
|
|
|
|
|
|
|
|
1779
|
|
|
|
|
|
|
# Prepare the background images. |
1780
|
|
|
|
|
|
|
if ( $has_background ) { |
1781
|
|
|
|
|
|
|
|
1782
|
|
|
|
|
|
|
my $filename = $sheet->{_background_image}; |
1783
|
|
|
|
|
|
|
|
1784
|
892
|
|
|
892
|
|
1914
|
my ( $type, $width, $height, $name, $x_dpi, $y_dpi, $md5 ) = |
1785
|
892
|
|
|
|
|
1713
|
$self->_get_image_properties( $filename ); |
1786
|
892
|
|
|
|
|
1639
|
|
1787
|
892
|
|
|
|
|
1671
|
if ( exists $background_ids{$md5} ) { |
1788
|
892
|
|
|
|
|
1681
|
$ref_id = $background_ids{$md5}; |
1789
|
892
|
|
|
|
|
2002
|
} |
1790
|
892
|
|
|
|
|
1685
|
else { |
1791
|
892
|
|
|
|
|
1683
|
$ref_id = ++$image_ref_id; |
1792
|
|
|
|
|
|
|
$background_ids{$md5} = $ref_id; |
1793
|
892
|
|
|
|
|
1728
|
push @{ $self->{_images} }, [ $filename, $type ]; |
|
892
|
|
|
|
|
2561
|
|
1794
|
|
|
|
|
|
|
} |
1795
|
1041
|
|
|
|
|
1677
|
|
|
1041
|
|
|
|
|
2555
|
|
1796
|
1041
|
|
|
|
|
1904
|
$sheet->_prepare_background($ref_id, $type); |
|
1041
|
|
|
|
|
2348
|
|
1797
|
1041
|
|
|
|
|
1676
|
} |
|
1041
|
|
|
|
|
2262
|
|
1798
|
|
|
|
|
|
|
|
1799
|
1041
|
|
|
|
|
1695
|
# Prepare the worksheet images. |
|
1041
|
|
|
|
|
2326
|
|
1800
|
1041
|
|
|
|
|
1801
|
for my $index ( 0 .. $image_count - 1 ) { |
|
1041
|
|
|
|
|
2307
|
|
1801
|
1041
|
|
|
|
|
2257
|
|
1802
|
1041
|
|
|
|
|
1828
|
my $filename = $sheet->{_images}->[$index]->[2]; |
1803
|
|
|
|
|
|
|
|
1804
|
|
|
|
|
|
|
my ( $type, $width, $height, $name, $x_dpi, $y_dpi, $md5 ) = |
1805
|
|
|
|
|
|
|
$self->_get_image_properties( $filename ); |
1806
|
1041
|
100
|
100
|
|
|
8803
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
1807
|
|
|
|
|
|
|
if ( exists $image_ids{$md5} ) { |
1808
|
|
|
|
|
|
|
$ref_id = $image_ids{$md5}; |
1809
|
|
|
|
|
|
|
} |
1810
|
|
|
|
|
|
|
else { |
1811
|
|
|
|
|
|
|
$ref_id = ++$image_ref_id; |
1812
|
|
|
|
|
|
|
$image_ids{$md5} = $ref_id; |
1813
|
494
|
|
|
|
|
1281
|
push @{ $self->{_images} }, [ $filename, $type ]; |
1814
|
|
|
|
|
|
|
} |
1815
|
|
|
|
|
|
|
|
1816
|
|
|
|
|
|
|
$sheet->_prepare_image( |
1817
|
547
|
100
|
100
|
|
|
2460
|
$index, $ref_id, $drawing_id, $width, $height, |
|
|
|
100
|
|
|
|
|
1818
|
523
|
|
|
|
|
913
|
$name, $type, $x_dpi, $y_dpi, $md5 |
1819
|
523
|
|
|
|
|
970
|
); |
1820
|
|
|
|
|
|
|
} |
1821
|
|
|
|
|
|
|
|
1822
|
|
|
|
|
|
|
# Prepare the worksheet charts. |
1823
|
547
|
100
|
|
|
|
1725
|
for my $index ( 0 .. $chart_count - 1 ) { |
1824
|
|
|
|
|
|
|
$chart_ref_id++; |
1825
|
8
|
|
|
|
|
12
|
$sheet->_prepare_chart( $index, $chart_ref_id, $drawing_id ); |
1826
|
|
|
|
|
|
|
} |
1827
|
8
|
|
|
|
|
34
|
|
1828
|
|
|
|
|
|
|
# Prepare the worksheet shapes. |
1829
|
|
|
|
|
|
|
for my $index ( 0 .. $shape_count - 1 ) { |
1830
|
8
|
100
|
|
|
|
30
|
$sheet->_prepare_shape( $index, $drawing_id ); |
1831
|
1
|
|
|
|
|
2
|
} |
1832
|
|
|
|
|
|
|
|
1833
|
|
|
|
|
|
|
# Prepare the header images. |
1834
|
7
|
|
|
|
|
12
|
for my $index ( 0 .. $header_image_count - 1 ) { |
1835
|
7
|
|
|
|
|
14
|
|
1836
|
7
|
|
|
|
|
10
|
my $filename = $sheet->{_header_images}->[$index]->[0]; |
|
7
|
|
|
|
|
22
|
|
1837
|
|
|
|
|
|
|
my $position = $sheet->{_header_images}->[$index]->[1]; |
1838
|
|
|
|
|
|
|
|
1839
|
8
|
|
|
|
|
31
|
my ( $type, $width, $height, $name, $x_dpi, $y_dpi, $md5 ) = |
1840
|
|
|
|
|
|
|
$self->_get_image_properties( $filename ); |
1841
|
|
|
|
|
|
|
|
1842
|
|
|
|
|
|
|
if ( exists $header_image_ids{$md5} ) { |
1843
|
547
|
|
|
|
|
2274
|
$ref_id = $header_image_ids{$md5}; |
1844
|
|
|
|
|
|
|
} |
1845
|
126
|
|
|
|
|
298
|
else { |
1846
|
|
|
|
|
|
|
$ref_id = ++$image_ref_id; |
1847
|
126
|
|
|
|
|
681
|
$header_image_ids{$md5} = $ref_id; |
1848
|
|
|
|
|
|
|
push @{ $self->{_images} }, [ $filename, $type ]; |
1849
|
|
|
|
|
|
|
} |
1850
|
126
|
100
|
|
|
|
465
|
|
1851
|
12
|
|
|
|
|
22
|
$sheet->_prepare_header_image( |
1852
|
|
|
|
|
|
|
$ref_id, $width, $height, $name, $type, |
1853
|
|
|
|
|
|
|
$position, $x_dpi, $y_dpi, $md5 |
1854
|
114
|
|
|
|
|
236
|
); |
1855
|
114
|
|
|
|
|
255
|
} |
1856
|
114
|
|
|
|
|
171
|
|
|
114
|
|
|
|
|
380
|
|
1857
|
|
|
|
|
|
|
# Prepare the footer images. |
1858
|
|
|
|
|
|
|
for my $index ( 0 .. $footer_image_count - 1 ) { |
1859
|
126
|
|
|
|
|
576
|
|
1860
|
|
|
|
|
|
|
my $filename = $sheet->{_footer_images}->[$index]->[0]; |
1861
|
|
|
|
|
|
|
my $position = $sheet->{_footer_images}->[$index]->[1]; |
1862
|
|
|
|
|
|
|
|
1863
|
|
|
|
|
|
|
my ( $type, $width, $height, $name, $x_dpi, $y_dpi, $md5 ) = |
1864
|
|
|
|
|
|
|
$self->_get_image_properties( $filename ); |
1865
|
|
|
|
|
|
|
|
1866
|
547
|
|
|
|
|
1664
|
if ( exists $header_image_ids{$md5} ) { |
1867
|
426
|
|
|
|
|
724
|
$ref_id = $header_image_ids{$md5}; |
1868
|
426
|
|
|
|
|
2174
|
} |
1869
|
|
|
|
|
|
|
else { |
1870
|
|
|
|
|
|
|
$ref_id = ++$image_ref_id; |
1871
|
|
|
|
|
|
|
$header_image_ids{$md5} = $ref_id; |
1872
|
547
|
|
|
|
|
1830
|
push @{ $self->{_images} }, [ $filename, $type ]; |
1873
|
41
|
|
|
|
|
103
|
} |
1874
|
|
|
|
|
|
|
|
1875
|
|
|
|
|
|
|
$sheet->_prepare_header_image( |
1876
|
|
|
|
|
|
|
$ref_id, $width, $height, $name, $type, |
1877
|
547
|
|
|
|
|
1674
|
$position, $x_dpi, $y_dpi, $md5 |
1878
|
|
|
|
|
|
|
); |
1879
|
34
|
|
|
|
|
93
|
} |
1880
|
34
|
|
|
|
|
70
|
|
1881
|
|
|
|
|
|
|
|
1882
|
34
|
|
|
|
|
138
|
if ( $has_drawing ) { |
1883
|
|
|
|
|
|
|
my $drawing = $sheet->{_drawing}; |
1884
|
|
|
|
|
|
|
push @{ $self->{_drawings} }, $drawing; |
1885
|
34
|
100
|
|
|
|
126
|
} |
1886
|
4
|
|
|
|
|
9
|
} |
1887
|
|
|
|
|
|
|
|
1888
|
|
|
|
|
|
|
|
1889
|
30
|
|
|
|
|
50
|
# Remove charts that were created but not inserted into worksheets. |
1890
|
30
|
|
|
|
|
67
|
my @chart_data; |
1891
|
30
|
|
|
|
|
41
|
|
|
30
|
|
|
|
|
101
|
|
1892
|
|
|
|
|
|
|
for my $chart ( @{ $self->{_charts} } ) { |
1893
|
|
|
|
|
|
|
if ( $chart->{_id} != -1 ) { |
1894
|
34
|
|
|
|
|
165
|
push @chart_data, $chart; |
1895
|
|
|
|
|
|
|
} |
1896
|
|
|
|
|
|
|
} |
1897
|
|
|
|
|
|
|
|
1898
|
|
|
|
|
|
|
# Sort the workbook charts references into the order that the were |
1899
|
|
|
|
|
|
|
# written from the worksheets above. |
1900
|
|
|
|
|
|
|
@chart_data = sort { $a->{_id} <=> $b->{_id} } @chart_data; |
1901
|
547
|
|
|
|
|
1621
|
|
1902
|
|
|
|
|
|
|
$self->{_charts} = \@chart_data; |
1903
|
12
|
|
|
|
|
36
|
$self->{_drawing_count} = $drawing_id; |
1904
|
12
|
|
|
|
|
22
|
} |
1905
|
|
|
|
|
|
|
|
1906
|
12
|
|
|
|
|
32
|
|
1907
|
|
|
|
|
|
|
############################################################################### |
1908
|
|
|
|
|
|
|
# |
1909
|
12
|
100
|
|
|
|
329
|
# _prepare_vml_objects() |
1910
|
8
|
|
|
|
|
14
|
# |
1911
|
|
|
|
|
|
|
# Iterate through the worksheets and set up the VML objects. |
1912
|
|
|
|
|
|
|
# |
1913
|
4
|
|
|
|
|
9
|
|
1914
|
4
|
|
|
|
|
8
|
my $self = shift; |
1915
|
4
|
|
|
|
|
6
|
my $comment_id = 0; |
|
4
|
|
|
|
|
13
|
|
1916
|
|
|
|
|
|
|
my $vml_drawing_id = 0; |
1917
|
|
|
|
|
|
|
my $vml_data_id = 1; |
1918
|
12
|
|
|
|
|
44
|
my $vml_header_id = 0; |
1919
|
|
|
|
|
|
|
my $vml_shape_id = 1024; |
1920
|
|
|
|
|
|
|
my $vml_files = 0; |
1921
|
|
|
|
|
|
|
my $comment_files = 0; |
1922
|
|
|
|
|
|
|
|
1923
|
|
|
|
|
|
|
for my $sheet ( @{ $self->{_worksheets} } ) { |
1924
|
|
|
|
|
|
|
|
1925
|
547
|
100
|
|
|
|
1771
|
next if !$sheet->{_has_vml} and !$sheet->{_has_header_vml}; |
1926
|
523
|
|
|
|
|
1273
|
$vml_files = 1; |
1927
|
523
|
|
|
|
|
956
|
|
|
523
|
|
|
|
|
1907
|
|
1928
|
|
|
|
|
|
|
|
1929
|
|
|
|
|
|
|
if ( $sheet->{_has_vml} ) { |
1930
|
|
|
|
|
|
|
|
1931
|
|
|
|
|
|
|
if ( $sheet->{_has_comments} ) { |
1932
|
|
|
|
|
|
|
$comment_files++; |
1933
|
892
|
|
|
|
|
1894
|
$comment_id++; |
1934
|
|
|
|
|
|
|
$self->{_has_comments} = 1; |
1935
|
892
|
|
|
|
|
1945
|
} |
|
892
|
|
|
|
|
2758
|
|
1936
|
437
|
100
|
|
|
|
1675
|
|
1937
|
426
|
|
|
|
|
1144
|
$vml_drawing_id++; |
1938
|
|
|
|
|
|
|
|
1939
|
|
|
|
|
|
|
my $count = |
1940
|
|
|
|
|
|
|
$sheet->_prepare_vml_objects( $vml_data_id, $vml_shape_id, |
1941
|
|
|
|
|
|
|
$vml_drawing_id, $comment_id ); |
1942
|
|
|
|
|
|
|
|
1943
|
892
|
|
|
|
|
2465
|
# Each VML file should start with a shape id incremented by 1024. |
|
64
|
|
|
|
|
139
|
|
1944
|
|
|
|
|
|
|
$vml_data_id += 1 * int( ( 1024 + $count ) / 1024 ); |
1945
|
892
|
|
|
|
|
2324
|
$vml_shape_id += 1024 * int( ( 1024 + $count ) / 1024 ); |
1946
|
892
|
|
|
|
|
2761
|
|
1947
|
|
|
|
|
|
|
} |
1948
|
|
|
|
|
|
|
|
1949
|
|
|
|
|
|
|
if ( $sheet->{_has_header_vml} ) { |
1950
|
|
|
|
|
|
|
$vml_header_id++; |
1951
|
|
|
|
|
|
|
$vml_drawing_id++; |
1952
|
|
|
|
|
|
|
$sheet->_prepare_header_vml_objects( $vml_header_id, |
1953
|
|
|
|
|
|
|
$vml_drawing_id ); |
1954
|
|
|
|
|
|
|
} |
1955
|
|
|
|
|
|
|
|
1956
|
|
|
|
|
|
|
} |
1957
|
|
|
|
|
|
|
|
1958
|
892
|
|
|
892
|
|
1885
|
$self->{_num_vml_files} = $vml_files; |
1959
|
892
|
|
|
|
|
1830
|
$self->{_num_comment_files} = $comment_files; |
1960
|
892
|
|
|
|
|
1679
|
|
1961
|
892
|
|
|
|
|
1855
|
} |
1962
|
892
|
|
|
|
|
1804
|
|
1963
|
892
|
|
|
|
|
1747
|
|
1964
|
892
|
|
|
|
|
1799
|
############################################################################### |
1965
|
892
|
|
|
|
|
1966
|
# |
1966
|
|
|
|
|
|
|
# _prepare_tables() |
1967
|
892
|
|
|
|
|
1774
|
# |
|
892
|
|
|
|
|
2838
|
|
1968
|
|
|
|
|
|
|
# Set the table ids for the worksheet tables. |
1969
|
1041
|
100
|
100
|
|
|
7306
|
# |
1970
|
80
|
|
|
|
|
140
|
|
1971
|
|
|
|
|
|
|
my $self = shift; |
1972
|
|
|
|
|
|
|
my $table_id = 0; |
1973
|
80
|
100
|
|
|
|
238
|
my $seen = {}; |
1974
|
|
|
|
|
|
|
|
1975
|
58
|
100
|
|
|
|
176
|
for my $sheet ( @{ $self->{_worksheets} } ) { |
1976
|
44
|
|
|
|
|
82
|
|
1977
|
44
|
|
|
|
|
71
|
my $table_count = scalar @{ $sheet->{_tables} }; |
1978
|
44
|
|
|
|
|
79
|
|
1979
|
|
|
|
|
|
|
next unless $table_count; |
1980
|
|
|
|
|
|
|
|
1981
|
58
|
|
|
|
|
90
|
$sheet->_prepare_tables( $table_id + 1, $seen ); |
1982
|
|
|
|
|
|
|
|
1983
|
58
|
|
|
|
|
257
|
$table_id += $table_count; |
1984
|
|
|
|
|
|
|
} |
1985
|
|
|
|
|
|
|
} |
1986
|
|
|
|
|
|
|
|
1987
|
|
|
|
|
|
|
|
1988
|
58
|
|
|
|
|
198
|
############################################################################### |
1989
|
58
|
|
|
|
|
182
|
# |
1990
|
|
|
|
|
|
|
# _prepare_metadata() |
1991
|
|
|
|
|
|
|
# |
1992
|
|
|
|
|
|
|
# Set the metadata rel link. |
1993
|
80
|
100
|
|
|
|
265
|
# |
1994
|
24
|
|
|
|
|
47
|
|
1995
|
24
|
|
|
|
|
40
|
my $self = shift; |
1996
|
24
|
|
|
|
|
109
|
|
1997
|
|
|
|
|
|
|
for my $sheet ( @{ $self->{_worksheets} } ) { |
1998
|
|
|
|
|
|
|
if ($sheet->{_has_dynamic_arrays}) { |
1999
|
|
|
|
|
|
|
$self->{_has_metadata} = 1; |
2000
|
|
|
|
|
|
|
} |
2001
|
|
|
|
|
|
|
} |
2002
|
892
|
|
|
|
|
2490
|
} |
2003
|
892
|
|
|
|
|
2291
|
|
2004
|
|
|
|
|
|
|
|
2005
|
|
|
|
|
|
|
############################################################################### |
2006
|
|
|
|
|
|
|
# |
2007
|
|
|
|
|
|
|
# _add_chart_data() |
2008
|
|
|
|
|
|
|
# |
2009
|
|
|
|
|
|
|
# Add "cached" data to charts to provide the numCache and strCache data for |
2010
|
|
|
|
|
|
|
# series and title/axis ranges. |
2011
|
|
|
|
|
|
|
# |
2012
|
|
|
|
|
|
|
|
2013
|
|
|
|
|
|
|
my $self = shift; |
2014
|
|
|
|
|
|
|
my %worksheets; |
2015
|
|
|
|
|
|
|
my %seen_ranges; |
2016
|
892
|
|
|
892
|
|
1912
|
my @charts; |
2017
|
892
|
|
|
|
|
1672
|
|
2018
|
892
|
|
|
|
|
1920
|
# Map worksheet names to worksheet objects. |
2019
|
|
|
|
|
|
|
for my $worksheet ( @{ $self->{_worksheets} } ) { |
2020
|
892
|
|
|
|
|
1906
|
$worksheets{ $worksheet->{_name} } = $worksheet; |
|
892
|
|
|
|
|
2716
|
|
2021
|
|
|
|
|
|
|
} |
2022
|
1041
|
|
|
|
|
1811
|
|
|
1041
|
|
|
|
|
2535
|
|
2023
|
|
|
|
|
|
|
# Build an array of the worksheet charts including any combined charts. |
2024
|
1041
|
100
|
|
|
|
4106
|
for my $chart ( @{ $self->{_charts} } ) { |
2025
|
|
|
|
|
|
|
push @charts, $chart; |
2026
|
28
|
|
|
|
|
196
|
|
2027
|
|
|
|
|
|
|
if ($chart->{_combined}) { |
2028
|
28
|
|
|
|
|
86
|
push @charts, $chart->{_combined}; |
2029
|
|
|
|
|
|
|
} |
2030
|
|
|
|
|
|
|
} |
2031
|
|
|
|
|
|
|
|
2032
|
|
|
|
|
|
|
|
2033
|
|
|
|
|
|
|
CHART: |
2034
|
|
|
|
|
|
|
for my $chart ( @charts ) { |
2035
|
|
|
|
|
|
|
|
2036
|
|
|
|
|
|
|
RANGE: |
2037
|
|
|
|
|
|
|
while ( my ( $range, $id ) = each %{ $chart->{_formula_ids} } ) { |
2038
|
|
|
|
|
|
|
|
2039
|
|
|
|
|
|
|
# Skip if the series has user defined data. |
2040
|
|
|
|
|
|
|
if ( defined $chart->{_formula_data}->[$id] ) { |
2041
|
892
|
|
|
892
|
|
1831
|
if ( !exists $seen_ranges{$range} |
2042
|
|
|
|
|
|
|
|| !defined $seen_ranges{$range} ) |
2043
|
892
|
|
|
|
|
1802
|
{ |
|
892
|
|
|
|
|
2539
|
|
2044
|
1041
|
100
|
|
|
|
3851
|
my $data = $chart->{_formula_data}->[$id]; |
2045
|
1
|
|
|
|
|
2
|
$seen_ranges{$range} = $data; |
2046
|
|
|
|
|
|
|
} |
2047
|
|
|
|
|
|
|
next RANGE; |
2048
|
|
|
|
|
|
|
} |
2049
|
|
|
|
|
|
|
|
2050
|
|
|
|
|
|
|
# Check to see if the data is already cached locally. |
2051
|
|
|
|
|
|
|
if ( exists $seen_ranges{$range} ) { |
2052
|
|
|
|
|
|
|
$chart->{_formula_data}->[$id] = $seen_ranges{$range}; |
2053
|
|
|
|
|
|
|
next RANGE; |
2054
|
|
|
|
|
|
|
} |
2055
|
|
|
|
|
|
|
|
2056
|
|
|
|
|
|
|
# Convert the range formula to a sheet name and cell range. |
2057
|
|
|
|
|
|
|
my ( $sheetname, @cells ) = $self->_get_chart_range( $range ); |
2058
|
|
|
|
|
|
|
|
2059
|
|
|
|
|
|
|
# Skip if we couldn't parse the formula. |
2060
|
892
|
|
|
892
|
|
2093
|
next RANGE if !defined $sheetname; |
2061
|
892
|
|
|
|
|
3180
|
|
2062
|
|
|
|
|
|
|
# Handle non-contiguous ranges: (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5). |
2063
|
892
|
|
|
|
|
0
|
# We don't try to parse the ranges. We just return an empty list. |
2064
|
|
|
|
|
|
|
if ( $sheetname =~ m/^\([^,]+,/ ) { |
2065
|
|
|
|
|
|
|
$chart->{_formula_data}->[$id] = []; |
2066
|
892
|
|
|
|
|
1696
|
$seen_ranges{$range} = []; |
|
892
|
|
|
|
|
2658
|
|
2067
|
1041
|
|
|
|
|
3108
|
next RANGE; |
2068
|
|
|
|
|
|
|
} |
2069
|
|
|
|
|
|
|
|
2070
|
|
|
|
|
|
|
# Die if the name is unknown since it indicates a user error in |
2071
|
892
|
|
|
|
|
2052
|
# a chart series formula. |
|
892
|
|
|
|
|
2572
|
|
2072
|
426
|
|
|
|
|
897
|
if ( !exists $worksheets{$sheetname} ) { |
2073
|
|
|
|
|
|
|
die "Unknown worksheet reference '$sheetname' in range " |
2074
|
426
|
100
|
|
|
|
1748
|
. "'$range' passed to add_series().\n"; |
2075
|
10
|
|
|
|
|
23
|
} |
2076
|
|
|
|
|
|
|
|
2077
|
|
|
|
|
|
|
# Find the worksheet object based on the sheet name. |
2078
|
|
|
|
|
|
|
my $worksheet = $worksheets{$sheetname}; |
2079
|
|
|
|
|
|
|
|
2080
|
|
|
|
|
|
|
# Get the data from the worksheet table. |
2081
|
892
|
|
|
|
|
2630
|
my @data = $worksheet->_get_range_data( @cells ); |
2082
|
|
|
|
|
|
|
|
2083
|
|
|
|
|
|
|
# Convert shared string indexes to strings. |
2084
|
436
|
|
|
|
|
993
|
for my $token ( @data ) { |
|
1639
|
|
|
|
|
6285
|
|
2085
|
|
|
|
|
|
|
if ( ref $token ) { |
2086
|
|
|
|
|
|
|
$token = $self->{_str_array}->[ $token->{sst_id} ]; |
2087
|
1203
|
100
|
|
|
|
3031
|
|
2088
|
16
|
50
|
33
|
|
|
46
|
# Ignore rich strings for now. Deparse later if necessary. |
2089
|
|
|
|
|
|
|
if ( $token =~ m{^<r>} && $token =~ m{</r>$} ) { |
2090
|
|
|
|
|
|
|
$token = ''; |
2091
|
16
|
|
|
|
|
28
|
} |
2092
|
16
|
|
|
|
|
25
|
} |
2093
|
|
|
|
|
|
|
} |
2094
|
16
|
|
|
|
|
35
|
|
2095
|
|
|
|
|
|
|
# Add the data to the chart. |
2096
|
|
|
|
|
|
|
$chart->{_formula_data}->[$id] = \@data; |
2097
|
|
|
|
|
|
|
|
2098
|
1187
|
100
|
|
|
|
2608
|
# Store range data locally to avoid lookup if seen again. |
2099
|
20
|
|
|
|
|
28
|
$seen_ranges{$range} = \@data; |
2100
|
20
|
|
|
|
|
38
|
} |
2101
|
|
|
|
|
|
|
} |
2102
|
|
|
|
|
|
|
} |
2103
|
|
|
|
|
|
|
|
2104
|
1167
|
|
|
|
|
4151
|
|
2105
|
|
|
|
|
|
|
############################################################################### |
2106
|
|
|
|
|
|
|
# |
2107
|
1167
|
50
|
|
|
|
2638
|
# _get_chart_range() |
2108
|
|
|
|
|
|
|
# |
2109
|
|
|
|
|
|
|
# Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name and cell |
2110
|
|
|
|
|
|
|
# range such as ( 'Sheet1', 0, 1, 4, 1 ). |
2111
|
1167
|
50
|
|
|
|
2958
|
# |
2112
|
0
|
|
|
|
|
0
|
|
2113
|
0
|
|
|
|
|
0
|
my $self = shift; |
2114
|
0
|
|
|
|
|
0
|
my $range = shift; |
2115
|
|
|
|
|
|
|
my $cell_1; |
2116
|
|
|
|
|
|
|
my $cell_2; |
2117
|
|
|
|
|
|
|
my $sheetname; |
2118
|
|
|
|
|
|
|
my $cells; |
2119
|
1167
|
50
|
|
|
|
2827
|
|
2120
|
0
|
|
|
|
|
0
|
# Split the range formula into sheetname and cells at the last '!'. |
2121
|
|
|
|
|
|
|
my $pos = rindex $range, '!'; |
2122
|
|
|
|
|
|
|
if ( $pos > 0 ) { |
2123
|
|
|
|
|
|
|
$sheetname = substr $range, 0, $pos; |
2124
|
|
|
|
|
|
|
$cells = substr $range, $pos + 1; |
2125
|
1167
|
|
|
|
|
1963
|
} |
2126
|
|
|
|
|
|
|
else { |
2127
|
|
|
|
|
|
|
return undef; |
2128
|
1167
|
|
|
|
|
3759
|
} |
2129
|
|
|
|
|
|
|
|
2130
|
|
|
|
|
|
|
# Split the cell range into 2 cells or else use single cell for both. |
2131
|
1167
|
|
|
|
|
2142
|
if ( $cells =~ ':' ) { |
2132
|
5525
|
100
|
|
|
|
8587
|
( $cell_1, $cell_2 ) = split /:/, $cells; |
2133
|
28
|
|
|
|
|
60
|
} |
2134
|
|
|
|
|
|
|
else { |
2135
|
|
|
|
|
|
|
( $cell_1, $cell_2 ) = ( $cells, $cells ); |
2136
|
28
|
50
|
33
|
|
|
121
|
} |
2137
|
0
|
|
|
|
|
0
|
|
2138
|
|
|
|
|
|
|
# Remove leading/trailing apostrophes and convert escaped quotes to single. |
2139
|
|
|
|
|
|
|
$sheetname =~ s/^'//g; |
2140
|
|
|
|
|
|
|
$sheetname =~ s/'$//g; |
2141
|
|
|
|
|
|
|
$sheetname =~ s/''/'/g; |
2142
|
|
|
|
|
|
|
|
2143
|
1167
|
|
|
|
|
2348
|
my ( $row_start, $col_start ) = xl_cell_to_rowcol( $cell_1 ); |
2144
|
|
|
|
|
|
|
my ( $row_end, $col_end ) = xl_cell_to_rowcol( $cell_2 ); |
2145
|
|
|
|
|
|
|
|
2146
|
1167
|
|
|
|
|
3204
|
# Check that we have a 1D range only. |
2147
|
|
|
|
|
|
|
if ( $row_start != $row_end && $col_start != $col_end ) { |
2148
|
|
|
|
|
|
|
return undef; |
2149
|
|
|
|
|
|
|
} |
2150
|
|
|
|
|
|
|
|
2151
|
|
|
|
|
|
|
return ( $sheetname, $row_start, $col_start, $row_end, $col_end ); |
2152
|
|
|
|
|
|
|
} |
2153
|
|
|
|
|
|
|
|
2154
|
|
|
|
|
|
|
|
2155
|
|
|
|
|
|
|
############################################################################### |
2156
|
|
|
|
|
|
|
# |
2157
|
|
|
|
|
|
|
# _store_externs() |
2158
|
|
|
|
|
|
|
# |
2159
|
|
|
|
|
|
|
# Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for |
2160
|
|
|
|
|
|
|
# the NAME records. |
2161
|
1174
|
|
|
1174
|
|
1874
|
# |
2162
|
1174
|
|
|
|
|
1655
|
|
2163
|
1174
|
|
|
|
|
3392
|
my $self = shift; |
2164
|
|
|
|
|
|
|
|
2165
|
1174
|
|
|
|
|
0
|
} |
2166
|
1174
|
|
|
|
|
0
|
|
2167
|
|
|
|
|
|
|
|
2168
|
|
|
|
|
|
|
############################################################################### |
2169
|
1174
|
|
|
|
|
2287
|
# |
2170
|
1174
|
100
|
|
|
|
2935
|
# _store_names() |
2171
|
1173
|
|
|
|
|
2433
|
# |
2172
|
1173
|
|
|
|
|
2266
|
# Write the NAME record to define the print area and the repeat rows and cols. |
2173
|
|
|
|
|
|
|
# |
2174
|
|
|
|
|
|
|
|
2175
|
1
|
|
|
|
|
6
|
my $self = shift; |
2176
|
|
|
|
|
|
|
|
2177
|
|
|
|
|
|
|
} |
2178
|
|
|
|
|
|
|
|
2179
|
1173
|
100
|
|
|
|
3591
|
|
2180
|
1142
|
|
|
|
|
3479
|
############################################################################### |
2181
|
|
|
|
|
|
|
# |
2182
|
|
|
|
|
|
|
# _quote_sheetname() |
2183
|
31
|
|
|
|
|
80
|
# |
2184
|
|
|
|
|
|
|
# Sheetnames used in references should be quoted if they contain any spaces, |
2185
|
|
|
|
|
|
|
# special characters or if the look like something that isn't a sheet name. |
2186
|
|
|
|
|
|
|
# TODO. We need to handle more special cases. |
2187
|
1173
|
|
|
|
|
2350
|
# |
2188
|
1173
|
|
|
|
|
1876
|
|
2189
|
1173
|
|
|
|
|
1824
|
my $self = shift; |
2190
|
|
|
|
|
|
|
my $sheetname = $_[0]; |
2191
|
1173
|
|
|
|
|
3612
|
|
2192
|
1173
|
|
|
|
|
2766
|
if ( $sheetname =~ /^Sheet\d+$/ ) { |
2193
|
|
|
|
|
|
|
return $sheetname; |
2194
|
|
|
|
|
|
|
} |
2195
|
1173
|
100
|
100
|
|
|
5087
|
else { |
2196
|
1
|
|
|
|
|
5
|
return qq('$sheetname'); |
2197
|
|
|
|
|
|
|
} |
2198
|
|
|
|
|
|
|
} |
2199
|
1172
|
|
|
|
|
3607
|
|
2200
|
|
|
|
|
|
|
|
2201
|
|
|
|
|
|
|
############################################################################### |
2202
|
|
|
|
|
|
|
# |
2203
|
|
|
|
|
|
|
# _get_image_properties() |
2204
|
|
|
|
|
|
|
# |
2205
|
|
|
|
|
|
|
# Extract information from the image file such as dimension, type, filename, |
2206
|
|
|
|
|
|
|
# and extension. Also keep track of previously seen images to optimise out |
2207
|
|
|
|
|
|
|
# any duplicates. |
2208
|
|
|
|
|
|
|
# |
2209
|
|
|
|
|
|
|
|
2210
|
|
|
|
|
|
|
my $self = shift; |
2211
|
|
|
|
|
|
|
my $filename = shift; |
2212
|
0
|
|
|
0
|
|
0
|
|
2213
|
|
|
|
|
|
|
my $type; |
2214
|
|
|
|
|
|
|
my $width; |
2215
|
|
|
|
|
|
|
my $height; |
2216
|
|
|
|
|
|
|
my $x_dpi = 96; |
2217
|
|
|
|
|
|
|
my $y_dpi = 96; |
2218
|
|
|
|
|
|
|
my $image_name; |
2219
|
|
|
|
|
|
|
|
2220
|
|
|
|
|
|
|
|
2221
|
|
|
|
|
|
|
( $image_name ) = fileparse( $filename ); |
2222
|
|
|
|
|
|
|
|
2223
|
|
|
|
|
|
|
# Open the image file and import the data. |
2224
|
|
|
|
|
|
|
my $fh = FileHandle->new( $filename ); |
2225
|
0
|
|
|
0
|
|
0
|
croak "Couldn't import $filename: $!" unless defined $fh; |
2226
|
|
|
|
|
|
|
binmode $fh; |
2227
|
|
|
|
|
|
|
|
2228
|
|
|
|
|
|
|
# Slurp the file into a string and do some size calcs. |
2229
|
|
|
|
|
|
|
my $data = do { local $/; <$fh> }; |
2230
|
|
|
|
|
|
|
my $size = length $data; |
2231
|
|
|
|
|
|
|
my $md5 = md5_hex($data); |
2232
|
|
|
|
|
|
|
|
2233
|
|
|
|
|
|
|
if ( unpack( 'x A3', $data ) eq 'PNG' ) { |
2234
|
|
|
|
|
|
|
|
2235
|
|
|
|
|
|
|
# Test for PNGs. |
2236
|
|
|
|
|
|
|
( $type, $width, $height, $x_dpi, $y_dpi ) = |
2237
|
|
|
|
|
|
|
$self->_process_png( $data, $filename ); |
2238
|
|
|
|
|
|
|
|
2239
|
|
|
|
|
|
|
$self->{_image_types}->{png} = 1; |
2240
|
0
|
|
|
0
|
|
0
|
} |
2241
|
0
|
|
|
|
|
0
|
elsif ( unpack( 'n', $data ) == 0xFFD8 ) { |
2242
|
|
|
|
|
|
|
|
2243
|
0
|
0
|
|
|
|
0
|
# Test for JPEG files. |
2244
|
0
|
|
|
|
|
0
|
( $type, $width, $height, $x_dpi, $y_dpi ) = |
2245
|
|
|
|
|
|
|
$self->_process_jpg( $data, $filename ); |
2246
|
|
|
|
|
|
|
|
2247
|
0
|
|
|
|
|
0
|
$self->{_image_types}->{jpeg} = 1; |
2248
|
|
|
|
|
|
|
} |
2249
|
|
|
|
|
|
|
elsif ( unpack( 'A4', $data ) eq 'GIF8' ) { |
2250
|
|
|
|
|
|
|
|
2251
|
|
|
|
|
|
|
# Test for GIFs. |
2252
|
|
|
|
|
|
|
( $type, $width, $height, $x_dpi, $y_dpi ) = |
2253
|
|
|
|
|
|
|
$self->_process_gif( $data, $filename ); |
2254
|
|
|
|
|
|
|
|
2255
|
|
|
|
|
|
|
$self->{_image_types}->{gif} = 1; |
2256
|
|
|
|
|
|
|
} |
2257
|
|
|
|
|
|
|
elsif ( unpack( 'A2', $data ) eq 'BM' ) { |
2258
|
|
|
|
|
|
|
|
2259
|
|
|
|
|
|
|
# Test for BMPs. |
2260
|
|
|
|
|
|
|
( $type, $width, $height ) = $self->_process_bmp( $data, $filename ); |
2261
|
|
|
|
|
|
|
|
2262
|
180
|
|
|
180
|
|
345
|
$self->{_image_types}->{bmp} = 1; |
2263
|
180
|
|
|
|
|
296
|
} |
2264
|
|
|
|
|
|
|
else { |
2265
|
180
|
|
|
|
|
450
|
croak "Unsupported image format for file: $filename\n"; |
2266
|
|
|
|
|
|
|
} |
2267
|
180
|
|
|
|
|
0
|
|
2268
|
180
|
|
|
|
|
294
|
# Set a default dpi for images with 0 dpi. |
2269
|
180
|
|
|
|
|
281
|
$x_dpi = 96 if $x_dpi == 0; |
2270
|
180
|
|
|
|
|
277
|
$y_dpi = 96 if $y_dpi == 0; |
2271
|
|
|
|
|
|
|
|
2272
|
|
|
|
|
|
|
$fh->close; |
2273
|
180
|
|
|
|
|
4172
|
|
2274
|
|
|
|
|
|
|
return ( $type, $width, $height, $image_name, $x_dpi, $y_dpi, $md5 ); |
2275
|
|
|
|
|
|
|
} |
2276
|
180
|
|
|
|
|
1221
|
|
2277
|
180
|
50
|
|
|
|
12566
|
|
2278
|
180
|
|
|
|
|
531
|
############################################################################### |
2279
|
|
|
|
|
|
|
# |
2280
|
|
|
|
|
|
|
# _process_png() |
2281
|
180
|
|
|
|
|
276
|
# |
|
180
|
|
|
|
|
605
|
|
|
180
|
|
|
|
|
5506
|
|
2282
|
180
|
|
|
|
|
470
|
# Extract width and height information from a PNG file. |
2283
|
180
|
|
|
|
|
1701
|
# |
2284
|
|
|
|
|
|
|
|
2285
|
180
|
100
|
|
|
|
1381
|
my $self = shift; |
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
2286
|
|
|
|
|
|
|
my $data = $_[0]; |
2287
|
|
|
|
|
|
|
my $filename = $_[1]; |
2288
|
97
|
|
|
|
|
720
|
|
2289
|
|
|
|
|
|
|
my $type = 'png'; |
2290
|
|
|
|
|
|
|
my $width = 0; |
2291
|
97
|
|
|
|
|
305
|
my $height = 0; |
2292
|
|
|
|
|
|
|
my $x_dpi = 96; |
2293
|
|
|
|
|
|
|
my $y_dpi = 96; |
2294
|
|
|
|
|
|
|
|
2295
|
|
|
|
|
|
|
my $offset = 8; |
2296
|
80
|
|
|
|
|
421
|
my $data_length = length $data; |
2297
|
|
|
|
|
|
|
|
2298
|
|
|
|
|
|
|
# Search through the image data to read the height and width in the |
2299
|
80
|
|
|
|
|
223
|
# IHDR element. Also read the DPI in the pHYs element. |
2300
|
|
|
|
|
|
|
while ( $offset < $data_length ) { |
2301
|
|
|
|
|
|
|
|
2302
|
|
|
|
|
|
|
my $length = unpack "N", substr $data, $offset + 0, 4; |
2303
|
|
|
|
|
|
|
my $type = unpack "A4", substr $data, $offset + 4, 4; |
2304
|
2
|
|
|
|
|
14
|
|
2305
|
|
|
|
|
|
|
if ( $type eq "IHDR" ) { |
2306
|
|
|
|
|
|
|
$width = unpack "N", substr $data, $offset + 8, 4; |
2307
|
2
|
|
|
|
|
7
|
$height = unpack "N", substr $data, $offset + 12, 4; |
2308
|
|
|
|
|
|
|
} |
2309
|
|
|
|
|
|
|
|
2310
|
|
|
|
|
|
|
if ( $type eq "pHYs" ) { |
2311
|
|
|
|
|
|
|
my $x_ppu = unpack "N", substr $data, $offset + 8, 4; |
2312
|
1
|
|
|
|
|
7
|
my $y_ppu = unpack "N", substr $data, $offset + 12, 4; |
2313
|
|
|
|
|
|
|
my $units = unpack "C", substr $data, $offset + 16, 1; |
2314
|
1
|
|
|
|
|
4
|
|
2315
|
|
|
|
|
|
|
if ( $units == 1 ) { |
2316
|
|
|
|
|
|
|
$x_dpi = $x_ppu * 0.0254; |
2317
|
0
|
|
|
|
|
0
|
$y_dpi = $y_ppu * 0.0254; |
2318
|
|
|
|
|
|
|
} |
2319
|
|
|
|
|
|
|
} |
2320
|
|
|
|
|
|
|
|
2321
|
180
|
100
|
|
|
|
527
|
$offset = $offset + $length + 12; |
2322
|
180
|
100
|
|
|
|
432
|
|
2323
|
|
|
|
|
|
|
last if $type eq "IEND"; |
2324
|
180
|
|
|
|
|
802
|
} |
2325
|
|
|
|
|
|
|
|
2326
|
180
|
|
|
|
|
3581
|
if ( not defined $height ) { |
2327
|
|
|
|
|
|
|
croak "$filename: no size data found in png image.\n"; |
2328
|
|
|
|
|
|
|
} |
2329
|
|
|
|
|
|
|
|
2330
|
|
|
|
|
|
|
return ( $type, $width, $height, $x_dpi, $y_dpi ); |
2331
|
|
|
|
|
|
|
} |
2332
|
|
|
|
|
|
|
|
2333
|
|
|
|
|
|
|
|
2334
|
|
|
|
|
|
|
############################################################################### |
2335
|
|
|
|
|
|
|
# |
2336
|
|
|
|
|
|
|
# _process_bmp() |
2337
|
|
|
|
|
|
|
# |
2338
|
97
|
|
|
97
|
|
210
|
# Extract width and height information from a BMP file. |
2339
|
97
|
|
|
|
|
193
|
# |
2340
|
97
|
|
|
|
|
159
|
# Most of the checks came from old Spredsheet::WriteExcel code. |
2341
|
|
|
|
|
|
|
# |
2342
|
97
|
|
|
|
|
192
|
|
2343
|
97
|
|
|
|
|
177
|
my $self = shift; |
2344
|
97
|
|
|
|
|
166
|
my $data = $_[0]; |
2345
|
97
|
|
|
|
|
150
|
my $filename = $_[1]; |
2346
|
97
|
|
|
|
|
153
|
my $type = 'bmp'; |
2347
|
|
|
|
|
|
|
|
2348
|
97
|
|
|
|
|
170
|
|
2349
|
97
|
|
|
|
|
184
|
# Check that the file is big enough to be a bitmap. |
2350
|
|
|
|
|
|
|
if ( length $data <= 0x36 ) { |
2351
|
|
|
|
|
|
|
croak "$filename doesn't contain enough data."; |
2352
|
|
|
|
|
|
|
} |
2353
|
97
|
|
|
|
|
328
|
|
2354
|
|
|
|
|
|
|
|
2355
|
571
|
|
|
|
|
1056
|
# Read the bitmap width and height. Verify the sizes. |
2356
|
571
|
|
|
|
|
1034
|
my ( $width, $height ) = unpack "x18 V2", $data; |
2357
|
|
|
|
|
|
|
|
2358
|
571
|
100
|
|
|
|
964
|
if ( $width > 0xFFFF ) { |
2359
|
97
|
|
|
|
|
273
|
croak "$filename: largest image width $width supported is 65k."; |
2360
|
97
|
|
|
|
|
284
|
} |
2361
|
|
|
|
|
|
|
|
2362
|
|
|
|
|
|
|
if ( $height > 0xFFFF ) { |
2363
|
571
|
100
|
|
|
|
944
|
croak "$filename: largest image height supported is 65k."; |
2364
|
11
|
|
|
|
|
29
|
} |
2365
|
11
|
|
|
|
|
24
|
|
2366
|
11
|
|
|
|
|
35
|
# Read the bitmap planes and bpp data. Verify them. |
2367
|
|
|
|
|
|
|
my ( $planes, $bitcount ) = unpack "x26 v2", $data; |
2368
|
11
|
50
|
|
|
|
24
|
|
2369
|
11
|
|
|
|
|
24
|
if ( $bitcount != 24 ) { |
2370
|
11
|
|
|
|
|
23
|
croak "$filename isn't a 24bit true color bitmap."; |
2371
|
|
|
|
|
|
|
} |
2372
|
|
|
|
|
|
|
|
2373
|
|
|
|
|
|
|
if ( $planes != 1 ) { |
2374
|
571
|
|
|
|
|
719
|
croak "$filename: only 1 plane supported in bitmap image."; |
2375
|
|
|
|
|
|
|
} |
2376
|
571
|
100
|
|
|
|
1162
|
|
2377
|
|
|
|
|
|
|
|
2378
|
|
|
|
|
|
|
# Read the bitmap compression. Verify compression. |
2379
|
97
|
50
|
|
|
|
267
|
my $compression = unpack "x30 V", $data; |
2380
|
0
|
|
|
|
|
0
|
|
2381
|
|
|
|
|
|
|
if ( $compression != 0 ) { |
2382
|
|
|
|
|
|
|
croak "$filename: compression not supported in bitmap image."; |
2383
|
97
|
|
|
|
|
343
|
} |
2384
|
|
|
|
|
|
|
|
2385
|
|
|
|
|
|
|
return ( $type, $width, $height ); |
2386
|
|
|
|
|
|
|
} |
2387
|
|
|
|
|
|
|
|
2388
|
|
|
|
|
|
|
|
2389
|
|
|
|
|
|
|
############################################################################### |
2390
|
|
|
|
|
|
|
# |
2391
|
|
|
|
|
|
|
# _process_jpg() |
2392
|
|
|
|
|
|
|
# |
2393
|
|
|
|
|
|
|
# Extract width and height information from a JPEG file. |
2394
|
|
|
|
|
|
|
# |
2395
|
|
|
|
|
|
|
|
2396
|
|
|
|
|
|
|
my $self = shift; |
2397
|
1
|
|
|
1
|
|
2
|
my $data = $_[0]; |
2398
|
1
|
|
|
|
|
2
|
my $filename = $_[1]; |
2399
|
1
|
|
|
|
|
2
|
my $type = 'jpeg'; |
2400
|
1
|
|
|
|
|
1
|
my $x_dpi = 96; |
2401
|
|
|
|
|
|
|
my $y_dpi = 96; |
2402
|
|
|
|
|
|
|
my $width; |
2403
|
|
|
|
|
|
|
my $height; |
2404
|
1
|
50
|
|
|
|
4
|
|
2405
|
0
|
|
|
|
|
0
|
my $offset = 2; |
2406
|
|
|
|
|
|
|
my $data_length = length $data; |
2407
|
|
|
|
|
|
|
|
2408
|
|
|
|
|
|
|
# Search through the image data to read the JPEG markers. |
2409
|
|
|
|
|
|
|
while ( $offset < $data_length ) { |
2410
|
1
|
|
|
|
|
4
|
|
2411
|
|
|
|
|
|
|
my $marker = unpack "n", substr $data, $offset + 0, 2; |
2412
|
1
|
50
|
|
|
|
4
|
my $length = unpack "n", substr $data, $offset + 2, 2; |
2413
|
0
|
|
|
|
|
0
|
|
2414
|
|
|
|
|
|
|
# Read the height and width in the 0xFFCn elements (except C4, C8 and |
2415
|
|
|
|
|
|
|
# CC which aren't SOF markers). |
2416
|
1
|
50
|
|
|
|
7
|
if ( ( $marker & 0xFFF0 ) == 0xFFC0 |
2417
|
0
|
|
|
|
|
0
|
&& $marker != 0xFFC4 |
2418
|
|
|
|
|
|
|
&& $marker != 0xFFCC ) |
2419
|
|
|
|
|
|
|
{ |
2420
|
|
|
|
|
|
|
$height = unpack "n", substr $data, $offset + 5, 2; |
2421
|
1
|
|
|
|
|
5
|
$width = unpack "n", substr $data, $offset + 7, 2; |
2422
|
|
|
|
|
|
|
} |
2423
|
1
|
50
|
|
|
|
3
|
|
2424
|
0
|
|
|
|
|
0
|
# Read the DPI in the 0xFFE0 element. |
2425
|
|
|
|
|
|
|
if ( $marker == 0xFFE0 ) { |
2426
|
|
|
|
|
|
|
my $units = unpack "C", substr $data, $offset + 11, 1; |
2427
|
1
|
50
|
|
|
|
4
|
my $x_density = unpack "n", substr $data, $offset + 12, 2; |
2428
|
0
|
|
|
|
|
0
|
my $y_density = unpack "n", substr $data, $offset + 14, 2; |
2429
|
|
|
|
|
|
|
|
2430
|
|
|
|
|
|
|
if ( $units == 1 ) { |
2431
|
|
|
|
|
|
|
$x_dpi = $x_density; |
2432
|
|
|
|
|
|
|
$y_dpi = $y_density; |
2433
|
1
|
|
|
|
|
2
|
} |
2434
|
|
|
|
|
|
|
|
2435
|
1
|
50
|
|
|
|
3
|
if ( $units == 2 ) { |
2436
|
0
|
|
|
|
|
0
|
$x_dpi = $x_density * 2.54; |
2437
|
|
|
|
|
|
|
$y_dpi = $y_density * 2.54; |
2438
|
|
|
|
|
|
|
} |
2439
|
1
|
|
|
|
|
3
|
} |
2440
|
|
|
|
|
|
|
|
2441
|
|
|
|
|
|
|
$offset = $offset + $length + 2; |
2442
|
|
|
|
|
|
|
last if $marker == 0xFFDA; |
2443
|
|
|
|
|
|
|
} |
2444
|
|
|
|
|
|
|
|
2445
|
|
|
|
|
|
|
if ( not defined $height ) { |
2446
|
|
|
|
|
|
|
croak "$filename: no size data found in jpeg image.\n"; |
2447
|
|
|
|
|
|
|
} |
2448
|
|
|
|
|
|
|
|
2449
|
|
|
|
|
|
|
return ( $type, $width, $height, $x_dpi, $y_dpi ); |
2450
|
|
|
|
|
|
|
} |
2451
|
80
|
|
|
80
|
|
146
|
|
2452
|
80
|
|
|
|
|
130
|
|
2453
|
80
|
|
|
|
|
139
|
############################################################################### |
2454
|
80
|
|
|
|
|
135
|
# |
2455
|
80
|
|
|
|
|
114
|
# _process_gif() |
2456
|
80
|
|
|
|
|
108
|
# |
2457
|
80
|
|
|
|
|
147
|
# Extract width and height information from a GIF file. |
2458
|
|
|
|
|
|
|
# |
2459
|
|
|
|
|
|
|
|
2460
|
80
|
|
|
|
|
130
|
my $self = shift; |
2461
|
80
|
|
|
|
|
116
|
my $data = $_[0]; |
2462
|
|
|
|
|
|
|
my $filename = $_[1]; |
2463
|
|
|
|
|
|
|
|
2464
|
80
|
|
|
|
|
239
|
my $type = 'gif'; |
2465
|
|
|
|
|
|
|
my $x_dpi = 96; |
2466
|
751
|
|
|
|
|
1220
|
my $y_dpi = 96; |
2467
|
751
|
|
|
|
|
1041
|
|
2468
|
|
|
|
|
|
|
my $width = unpack "v", substr $data, 6, 2; |
2469
|
|
|
|
|
|
|
my $height = unpack "v", substr $data, 8, 2; |
2470
|
|
|
|
|
|
|
print join ", ", ( $type, $width, $height, $x_dpi, $y_dpi, "\n" ); |
2471
|
751
|
100
|
100
|
|
|
1793
|
|
|
|
|
66
|
|
|
|
|
2472
|
|
|
|
|
|
|
if ( not defined $height ) { |
2473
|
|
|
|
|
|
|
croak "$filename: no size data found in gif image.\n"; |
2474
|
|
|
|
|
|
|
} |
2475
|
80
|
|
|
|
|
161
|
|
2476
|
80
|
|
|
|
|
160
|
return ( $type, $width, $height, $x_dpi, $y_dpi ); |
2477
|
|
|
|
|
|
|
} |
2478
|
|
|
|
|
|
|
|
2479
|
|
|
|
|
|
|
|
2480
|
751
|
100
|
|
|
|
1050
|
############################################################################### |
2481
|
79
|
|
|
|
|
179
|
# |
2482
|
79
|
|
|
|
|
160
|
# _get_sheet_index() |
2483
|
79
|
|
|
|
|
158
|
# |
2484
|
|
|
|
|
|
|
# Convert a sheet name to its index. Return undef otherwise. |
2485
|
79
|
100
|
|
|
|
180
|
# |
2486
|
67
|
|
|
|
|
92
|
|
2487
|
67
|
|
|
|
|
98
|
my $self = shift; |
2488
|
|
|
|
|
|
|
my $sheetname = shift; |
2489
|
|
|
|
|
|
|
my $sheet_index = undef; |
2490
|
79
|
50
|
|
|
|
184
|
|
2491
|
0
|
|
|
|
|
0
|
$sheetname =~ s/^'//; |
2492
|
0
|
|
|
|
|
0
|
$sheetname =~ s/'$//; |
2493
|
|
|
|
|
|
|
|
2494
|
|
|
|
|
|
|
if ( exists $self->{_sheetnames}->{$sheetname} ) { |
2495
|
|
|
|
|
|
|
return $self->{_sheetnames}->{$sheetname}->{_index}; |
2496
|
751
|
|
|
|
|
866
|
} |
2497
|
751
|
100
|
|
|
|
1292
|
else { |
2498
|
|
|
|
|
|
|
return undef; |
2499
|
|
|
|
|
|
|
} |
2500
|
80
|
50
|
|
|
|
183
|
} |
2501
|
0
|
|
|
|
|
0
|
|
2502
|
|
|
|
|
|
|
|
2503
|
|
|
|
|
|
|
############################################################################### |
2504
|
80
|
|
|
|
|
258
|
# |
2505
|
|
|
|
|
|
|
# set_optimization() |
2506
|
|
|
|
|
|
|
# |
2507
|
|
|
|
|
|
|
# Set the speed/memory optimisation level. |
2508
|
|
|
|
|
|
|
# |
2509
|
|
|
|
|
|
|
|
2510
|
|
|
|
|
|
|
my $self = shift; |
2511
|
|
|
|
|
|
|
my $level = defined $_[0] ? $_[0] : 1; |
2512
|
|
|
|
|
|
|
|
2513
|
|
|
|
|
|
|
croak "set_optimization() must be called before add_worksheet()" |
2514
|
|
|
|
|
|
|
if $self->sheets(); |
2515
|
|
|
|
|
|
|
|
2516
|
2
|
|
|
2
|
|
4
|
$self->{_optimization} = $level; |
2517
|
2
|
|
|
|
|
4
|
} |
2518
|
2
|
|
|
|
|
3
|
|
2519
|
|
|
|
|
|
|
|
2520
|
2
|
|
|
|
|
3
|
############################################################################### |
2521
|
2
|
|
|
|
|
3
|
# |
2522
|
2
|
|
|
|
|
3
|
# Deprecated methods for backwards compatibility. |
2523
|
|
|
|
|
|
|
# |
2524
|
2
|
|
|
|
|
5
|
############################################################################### |
2525
|
2
|
|
|
|
|
6
|
|
2526
|
2
|
|
|
|
|
556
|
# No longer required by Excel::Writer::XLSX. |
2527
|
|
|
|
|
|
|
|
2528
|
2
|
50
|
|
|
|
10
|
|
2529
|
0
|
|
|
|
|
0
|
############################################################################### |
2530
|
|
|
|
|
|
|
# |
2531
|
|
|
|
|
|
|
# XML writing methods. |
2532
|
2
|
|
|
|
|
16
|
# |
2533
|
|
|
|
|
|
|
############################################################################### |
2534
|
|
|
|
|
|
|
|
2535
|
|
|
|
|
|
|
|
2536
|
|
|
|
|
|
|
############################################################################### |
2537
|
|
|
|
|
|
|
# |
2538
|
|
|
|
|
|
|
# _write_workbook() |
2539
|
|
|
|
|
|
|
# |
2540
|
|
|
|
|
|
|
# Write <workbook> element. |
2541
|
|
|
|
|
|
|
# |
2542
|
|
|
|
|
|
|
|
2543
|
|
|
|
|
|
|
my $self = shift; |
2544
|
9
|
|
|
9
|
|
14
|
my $schema = 'http://schemas.openxmlformats.org'; |
2545
|
9
|
|
|
|
|
11
|
my $xmlns = $schema . '/spreadsheetml/2006/main'; |
2546
|
9
|
|
|
|
|
10
|
my $xmlns_r = $schema . '/officeDocument/2006/relationships'; |
2547
|
|
|
|
|
|
|
|
2548
|
9
|
|
|
|
|
18
|
my @attributes = ( |
2549
|
9
|
|
|
|
|
30
|
'xmlns' => $xmlns, |
2550
|
|
|
|
|
|
|
'xmlns:r' => $xmlns_r, |
2551
|
9
|
50
|
|
|
|
22
|
); |
2552
|
9
|
|
|
|
|
19
|
|
2553
|
|
|
|
|
|
|
$self->xml_start_tag( 'workbook', @attributes ); |
2554
|
|
|
|
|
|
|
} |
2555
|
0
|
|
|
|
|
0
|
|
2556
|
|
|
|
|
|
|
|
2557
|
|
|
|
|
|
|
############################################################################### |
2558
|
|
|
|
|
|
|
# |
2559
|
|
|
|
|
|
|
# write_file_version() |
2560
|
|
|
|
|
|
|
# |
2561
|
|
|
|
|
|
|
# Write the <fileVersion> element. |
2562
|
|
|
|
|
|
|
# |
2563
|
|
|
|
|
|
|
|
2564
|
|
|
|
|
|
|
my $self = shift; |
2565
|
|
|
|
|
|
|
my $app_name = 'xl'; |
2566
|
|
|
|
|
|
|
my $last_edited = 4; |
2567
|
|
|
|
|
|
|
my $lowest_edited = 4; |
2568
|
10
|
|
|
10
|
0
|
101
|
my $rup_build = 4505; |
2569
|
10
|
50
|
|
|
|
33
|
|
2570
|
|
|
|
|
|
|
my @attributes = ( |
2571
|
10
|
50
|
|
|
|
78
|
'appName' => $app_name, |
2572
|
|
|
|
|
|
|
'lastEdited' => $last_edited, |
2573
|
|
|
|
|
|
|
'lowestEdited' => $lowest_edited, |
2574
|
10
|
|
|
|
|
28
|
'rupBuild' => $rup_build, |
2575
|
|
|
|
|
|
|
); |
2576
|
|
|
|
|
|
|
|
2577
|
|
|
|
|
|
|
if ( $self->{_vba_project} ) { |
2578
|
|
|
|
|
|
|
push @attributes, codeName => '{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}'; |
2579
|
|
|
|
|
|
|
} |
2580
|
|
|
|
|
|
|
|
2581
|
|
|
|
|
|
|
$self->xml_empty_tag( 'fileVersion', @attributes ); |
2582
|
|
|
|
|
|
|
} |
2583
|
|
|
|
|
|
|
|
2584
|
|
|
|
|
|
|
|
2585
|
|
|
|
0
|
0
|
|
############################################################################## |
2586
|
|
|
|
0
|
0
|
|
# |
2587
|
|
|
|
|
|
|
# _write_file_sharing() |
2588
|
|
|
|
|
|
|
# |
2589
|
|
|
|
|
|
|
# Write the <fileSharing> element. |
2590
|
|
|
|
|
|
|
# |
2591
|
|
|
|
|
|
|
|
2592
|
|
|
|
|
|
|
my $self = shift; |
2593
|
|
|
|
|
|
|
|
2594
|
|
|
|
|
|
|
return if !$self->{_read_only}; |
2595
|
|
|
|
|
|
|
|
2596
|
|
|
|
|
|
|
my @attributes = ( 'readOnlyRecommended' => 1, ); |
2597
|
|
|
|
|
|
|
|
2598
|
|
|
|
|
|
|
$self->xml_empty_tag( 'fileSharing', @attributes ); |
2599
|
|
|
|
|
|
|
} |
2600
|
|
|
|
|
|
|
|
2601
|
|
|
|
|
|
|
|
2602
|
|
|
|
|
|
|
############################################################################### |
2603
|
|
|
|
|
|
|
# |
2604
|
896
|
|
|
896
|
|
2057
|
# _write_workbook_pr() |
2605
|
896
|
|
|
|
|
2100
|
# |
2606
|
896
|
|
|
|
|
2999
|
# Write <workbookPr> element. |
2607
|
896
|
|
|
|
|
2560
|
# |
2608
|
|
|
|
|
|
|
|
2609
|
896
|
|
|
|
|
3280
|
my $self = shift; |
2610
|
|
|
|
|
|
|
my $date_1904 = $self->{_date_1904}; |
2611
|
|
|
|
|
|
|
my $show_ink_annotation = 0; |
2612
|
|
|
|
|
|
|
my $auto_compress_pictures = 0; |
2613
|
|
|
|
|
|
|
my $default_theme_version = 124226; |
2614
|
896
|
|
|
|
|
5309
|
my $codename = $self->{_vba_codename}; |
2615
|
|
|
|
|
|
|
my @attributes; |
2616
|
|
|
|
|
|
|
|
2617
|
|
|
|
|
|
|
push @attributes, ( 'codeName' => $codename ) if $codename; |
2618
|
|
|
|
|
|
|
push @attributes, ( 'date1904' => 1 ) if $date_1904; |
2619
|
|
|
|
|
|
|
push @attributes, ( 'defaultThemeVersion' => $default_theme_version ); |
2620
|
|
|
|
|
|
|
|
2621
|
|
|
|
|
|
|
$self->xml_empty_tag( 'workbookPr', @attributes ); |
2622
|
|
|
|
|
|
|
} |
2623
|
|
|
|
|
|
|
|
2624
|
|
|
|
|
|
|
|
2625
|
|
|
|
|
|
|
############################################################################### |
2626
|
896
|
|
|
896
|
|
1798
|
# |
2627
|
896
|
|
|
|
|
1744
|
# _write_book_views() |
2628
|
896
|
|
|
|
|
1731
|
# |
2629
|
896
|
|
|
|
|
1839
|
# Write <bookViews> element. |
2630
|
896
|
|
|
|
|
1778
|
# |
2631
|
|
|
|
|
|
|
|
2632
|
896
|
|
|
|
|
4126
|
my $self = shift; |
2633
|
|
|
|
|
|
|
|
2634
|
|
|
|
|
|
|
$self->xml_start_tag( 'bookViews' ); |
2635
|
|
|
|
|
|
|
$self->_write_workbook_view(); |
2636
|
|
|
|
|
|
|
$self->xml_end_tag( 'bookViews' ); |
2637
|
|
|
|
|
|
|
} |
2638
|
|
|
|
|
|
|
|
2639
|
896
|
100
|
|
|
|
3561
|
############################################################################### |
2640
|
6
|
|
|
|
|
22
|
# |
2641
|
|
|
|
|
|
|
# _write_workbook_view() |
2642
|
|
|
|
|
|
|
# |
2643
|
896
|
|
|
|
|
5158
|
# Write <workbookView> element. |
2644
|
|
|
|
|
|
|
# |
2645
|
|
|
|
|
|
|
|
2646
|
|
|
|
|
|
|
my $self = shift; |
2647
|
|
|
|
|
|
|
my $x_window = $self->{_x_window}; |
2648
|
|
|
|
|
|
|
my $y_window = $self->{_y_window}; |
2649
|
|
|
|
|
|
|
my $window_width = $self->{_window_width}; |
2650
|
|
|
|
|
|
|
my $window_height = $self->{_window_height}; |
2651
|
|
|
|
|
|
|
my $tab_ratio = $self->{_tab_ratio}; |
2652
|
|
|
|
|
|
|
my $active_tab = $self->{_activesheet}; |
2653
|
|
|
|
|
|
|
my $first_sheet = $self->{_firstsheet}; |
2654
|
|
|
|
|
|
|
|
2655
|
895
|
|
|
895
|
|
1903
|
my @attributes = ( |
2656
|
|
|
|
|
|
|
'xWindow' => $x_window, |
2657
|
895
|
100
|
|
|
|
3693
|
'yWindow' => $y_window, |
2658
|
|
|
|
|
|
|
'windowWidth' => $window_width, |
2659
|
1
|
|
|
|
|
3
|
'windowHeight' => $window_height, |
2660
|
|
|
|
|
|
|
); |
2661
|
1
|
|
|
|
|
4
|
|
2662
|
|
|
|
|
|
|
# Store the tabRatio attribute when it isn't the default. |
2663
|
|
|
|
|
|
|
push @attributes, ( tabRatio => $tab_ratio ) if $tab_ratio != 600; |
2664
|
|
|
|
|
|
|
|
2665
|
|
|
|
|
|
|
# Store the firstSheet attribute when it isn't the default. |
2666
|
|
|
|
|
|
|
push @attributes, ( firstSheet => $first_sheet + 1 ) if $first_sheet > 0; |
2667
|
|
|
|
|
|
|
|
2668
|
|
|
|
|
|
|
# Store the activeTab attribute when it isn't the first sheet. |
2669
|
|
|
|
|
|
|
push @attributes, ( activeTab => $active_tab ) if $active_tab > 0; |
2670
|
|
|
|
|
|
|
|
2671
|
|
|
|
|
|
|
$self->xml_empty_tag( 'workbookView', @attributes ); |
2672
|
|
|
|
|
|
|
} |
2673
|
896
|
|
|
896
|
|
1845
|
|
2674
|
896
|
|
|
|
|
2038
|
############################################################################### |
2675
|
896
|
|
|
|
|
1757
|
# |
2676
|
896
|
|
|
|
|
1754
|
# _write_sheets() |
2677
|
896
|
|
|
|
|
1671
|
# |
2678
|
896
|
|
|
|
|
2056
|
# Write <sheets> element. |
2679
|
896
|
|
|
|
|
1598
|
# |
2680
|
|
|
|
|
|
|
|
2681
|
896
|
100
|
|
|
|
2936
|
my $self = shift; |
2682
|
896
|
100
|
|
|
|
3219
|
my $id_num = 1; |
2683
|
896
|
|
|
|
|
2563
|
|
2684
|
|
|
|
|
|
|
$self->xml_start_tag( 'sheets' ); |
2685
|
896
|
|
|
|
|
3603
|
|
2686
|
|
|
|
|
|
|
for my $worksheet ( @{ $self->{_worksheets} } ) { |
2687
|
|
|
|
|
|
|
$self->_write_sheet( $worksheet->{_name}, $id_num++, |
2688
|
|
|
|
|
|
|
$worksheet->{_hidden} ); |
2689
|
|
|
|
|
|
|
} |
2690
|
|
|
|
|
|
|
|
2691
|
|
|
|
|
|
|
$self->xml_end_tag( 'sheets' ); |
2692
|
|
|
|
|
|
|
} |
2693
|
|
|
|
|
|
|
|
2694
|
|
|
|
|
|
|
|
2695
|
|
|
|
|
|
|
############################################################################### |
2696
|
|
|
|
|
|
|
# |
2697
|
896
|
|
|
896
|
|
2012
|
# _write_sheet() |
2698
|
|
|
|
|
|
|
# |
2699
|
896
|
|
|
|
|
3345
|
# Write <sheet> element. |
2700
|
896
|
|
|
|
|
5016
|
# |
2701
|
896
|
|
|
|
|
4582
|
|
2702
|
|
|
|
|
|
|
my $self = shift; |
2703
|
|
|
|
|
|
|
my $name = shift; |
2704
|
|
|
|
|
|
|
my $sheet_id = shift; |
2705
|
|
|
|
|
|
|
my $hidden = shift; |
2706
|
|
|
|
|
|
|
my $r_id = 'rId' . $sheet_id; |
2707
|
|
|
|
|
|
|
|
2708
|
|
|
|
|
|
|
my @attributes = ( |
2709
|
|
|
|
|
|
|
'name' => $name, |
2710
|
|
|
|
|
|
|
'sheetId' => $sheet_id, |
2711
|
|
|
|
|
|
|
); |
2712
|
908
|
|
|
908
|
|
2004
|
|
2713
|
908
|
|
|
|
|
2256
|
push @attributes, ( 'state' => 'hidden' ) if $hidden; |
2714
|
908
|
|
|
|
|
2079
|
push @attributes, ( 'r:id' => $r_id ); |
2715
|
908
|
|
|
|
|
2001
|
|
2716
|
908
|
|
|
|
|
2150
|
|
2717
|
908
|
|
|
|
|
1813
|
$self->xml_empty_tag( 'sheet', @attributes ); |
2718
|
908
|
|
|
|
|
1866
|
} |
2719
|
908
|
|
|
|
|
1934
|
|
2720
|
|
|
|
|
|
|
|
2721
|
908
|
|
|
|
|
3816
|
############################################################################### |
2722
|
|
|
|
|
|
|
# |
2723
|
|
|
|
|
|
|
# _write_calc_pr() |
2724
|
|
|
|
|
|
|
# |
2725
|
|
|
|
|
|
|
# Write <calcPr> element. |
2726
|
|
|
|
|
|
|
# |
2727
|
|
|
|
|
|
|
|
2728
|
|
|
|
|
|
|
my $self = shift; |
2729
|
908
|
100
|
|
|
|
3465
|
my $calc_id = $self->{_calc_id}; |
2730
|
|
|
|
|
|
|
my $concurrent_calc = 0; |
2731
|
|
|
|
|
|
|
|
2732
|
908
|
100
|
|
|
|
3293
|
my @attributes = ( calcId => $calc_id ); |
2733
|
|
|
|
|
|
|
|
2734
|
|
|
|
|
|
|
if ( $self->{_calc_mode} eq 'manual' ) { |
2735
|
908
|
100
|
|
|
|
3168
|
push @attributes, 'calcMode' => 'manual'; |
2736
|
|
|
|
|
|
|
push @attributes, 'calcOnSave' => 0; |
2737
|
908
|
|
|
|
|
3640
|
} |
2738
|
|
|
|
|
|
|
elsif ( $self->{_calc_mode} eq 'autoNoTable' ) { |
2739
|
|
|
|
|
|
|
push @attributes, calcMode => 'autoNoTable'; |
2740
|
|
|
|
|
|
|
} |
2741
|
|
|
|
|
|
|
|
2742
|
|
|
|
|
|
|
if ( $self->{_calc_on_load} ) { |
2743
|
|
|
|
|
|
|
push @attributes, 'fullCalcOnLoad' => 1; |
2744
|
|
|
|
|
|
|
} |
2745
|
|
|
|
|
|
|
|
2746
|
|
|
|
|
|
|
|
2747
|
|
|
|
|
|
|
$self->xml_empty_tag( 'calcPr', @attributes ); |
2748
|
896
|
|
|
896
|
|
1974
|
} |
2749
|
896
|
|
|
|
|
1745
|
|
2750
|
|
|
|
|
|
|
|
2751
|
896
|
|
|
|
|
3138
|
############################################################################### |
2752
|
|
|
|
|
|
|
# |
2753
|
896
|
|
|
|
|
1655
|
# _write_ext_lst() |
|
896
|
|
|
|
|
2902
|
|
2754
|
|
|
|
|
|
|
# |
2755
|
1047
|
|
|
|
|
6024
|
# Write <extLst> element. |
2756
|
|
|
|
|
|
|
# |
2757
|
|
|
|
|
|
|
|
2758
|
896
|
|
|
|
|
3213
|
my $self = shift; |
2759
|
|
|
|
|
|
|
|
2760
|
|
|
|
|
|
|
$self->xml_start_tag( 'extLst' ); |
2761
|
|
|
|
|
|
|
$self->_write_ext(); |
2762
|
|
|
|
|
|
|
$self->xml_end_tag( 'extLst' ); |
2763
|
|
|
|
|
|
|
} |
2764
|
|
|
|
|
|
|
|
2765
|
|
|
|
|
|
|
|
2766
|
|
|
|
|
|
|
############################################################################### |
2767
|
|
|
|
|
|
|
# |
2768
|
|
|
|
|
|
|
# _write_ext() |
2769
|
|
|
|
|
|
|
# |
2770
|
1050
|
|
|
1050
|
|
2316
|
# Write <ext> element. |
2771
|
1050
|
|
|
|
|
2060
|
# |
2772
|
1050
|
|
|
|
|
2107
|
|
2773
|
1050
|
|
|
|
|
1884
|
my $self = shift; |
2774
|
1050
|
|
|
|
|
2890
|
my $xmlns_mx = 'http://schemas.microsoft.com/office/mac/excel/2008/main'; |
2775
|
|
|
|
|
|
|
my $uri = 'http://schemas.microsoft.com/office/mac/excel/2008/main'; |
2776
|
1050
|
|
|
|
|
3646
|
|
2777
|
|
|
|
|
|
|
my @attributes = ( |
2778
|
|
|
|
|
|
|
'xmlns:mx' => $xmlns_mx, |
2779
|
|
|
|
|
|
|
'uri' => $uri, |
2780
|
|
|
|
|
|
|
); |
2781
|
1050
|
100
|
|
|
|
3388
|
|
2782
|
1050
|
|
|
|
|
3169
|
$self->xml_start_tag( 'ext', @attributes ); |
2783
|
|
|
|
|
|
|
$self->_write_mx_arch_id(); |
2784
|
|
|
|
|
|
|
$self->xml_end_tag( 'ext' ); |
2785
|
1050
|
|
|
|
|
3759
|
} |
2786
|
|
|
|
|
|
|
|
2787
|
|
|
|
|
|
|
############################################################################### |
2788
|
|
|
|
|
|
|
# |
2789
|
|
|
|
|
|
|
# _write_mx_arch_id() |
2790
|
|
|
|
|
|
|
# |
2791
|
|
|
|
|
|
|
# Write <mx:ArchID> element. |
2792
|
|
|
|
|
|
|
# |
2793
|
|
|
|
|
|
|
|
2794
|
|
|
|
|
|
|
my $self = shift; |
2795
|
|
|
|
|
|
|
my $Flags = 2; |
2796
|
|
|
|
|
|
|
|
2797
|
899
|
|
|
899
|
|
1952
|
my @attributes = ( 'Flags' => $Flags, ); |
2798
|
899
|
|
|
|
|
2182
|
|
2799
|
899
|
|
|
|
|
1756
|
$self->xml_empty_tag( 'mx:ArchID', @attributes ); |
2800
|
|
|
|
|
|
|
} |
2801
|
899
|
|
|
|
|
2846
|
|
2802
|
|
|
|
|
|
|
|
2803
|
899
|
100
|
|
|
|
5995
|
############################################################################## |
|
|
100
|
|
|
|
|
|
2804
|
2
|
|
|
|
|
3
|
# |
2805
|
2
|
|
|
|
|
4
|
# _write_defined_names() |
2806
|
|
|
|
|
|
|
# |
2807
|
|
|
|
|
|
|
# Write the <definedNames> element. |
2808
|
2
|
|
|
|
|
4
|
# |
2809
|
|
|
|
|
|
|
|
2810
|
|
|
|
|
|
|
my $self = shift; |
2811
|
899
|
100
|
|
|
|
3357
|
|
2812
|
897
|
|
|
|
|
2405
|
return unless @{ $self->{_defined_names} }; |
2813
|
|
|
|
|
|
|
|
2814
|
|
|
|
|
|
|
$self->xml_start_tag( 'definedNames' ); |
2815
|
|
|
|
|
|
|
|
2816
|
899
|
|
|
|
|
3315
|
for my $aref ( @{ $self->{_defined_names} } ) { |
2817
|
|
|
|
|
|
|
$self->_write_defined_name( $aref ); |
2818
|
|
|
|
|
|
|
} |
2819
|
|
|
|
|
|
|
|
2820
|
|
|
|
|
|
|
$self->xml_end_tag( 'definedNames' ); |
2821
|
|
|
|
|
|
|
} |
2822
|
|
|
|
|
|
|
|
2823
|
|
|
|
|
|
|
|
2824
|
|
|
|
|
|
|
############################################################################## |
2825
|
|
|
|
|
|
|
# |
2826
|
|
|
|
|
|
|
# _write_defined_name() |
2827
|
|
|
|
|
|
|
# |
2828
|
1
|
|
|
1
|
|
19
|
# Write the <definedName> element. |
2829
|
|
|
|
|
|
|
# |
2830
|
1
|
|
|
|
|
11
|
|
2831
|
1
|
|
|
|
|
7
|
my $self = shift; |
2832
|
1
|
|
|
|
|
4
|
my $data = shift; |
2833
|
|
|
|
|
|
|
|
2834
|
|
|
|
|
|
|
my $name = $data->[0]; |
2835
|
|
|
|
|
|
|
my $id = $data->[1]; |
2836
|
|
|
|
|
|
|
my $range = $data->[2]; |
2837
|
|
|
|
|
|
|
my $hidden = $data->[3]; |
2838
|
|
|
|
|
|
|
|
2839
|
|
|
|
|
|
|
my @attributes = ( 'name' => $name ); |
2840
|
|
|
|
|
|
|
|
2841
|
|
|
|
|
|
|
push @attributes, ( 'localSheetId' => $id ) if $id != -1; |
2842
|
|
|
|
|
|
|
push @attributes, ( 'hidden' => 1 ) if $hidden; |
2843
|
|
|
|
|
|
|
|
2844
|
2
|
|
|
2
|
|
28
|
$self->xml_data_element( 'definedName', $range, @attributes ); |
2845
|
2
|
|
|
|
|
7
|
} |
2846
|
2
|
|
|
|
|
5
|
|
2847
|
|
|
|
|
|
|
|
2848
|
2
|
|
|
|
|
7
|
1; |
2849
|
|
|
|
|
|
|
|
2850
|
|
|
|
|
|
|
|
2851
|
|
|
|
|
|
|
|
2852
|
|
|
|
|
|
|
|
2853
|
2
|
|
|
|
|
14
|
=head1 NAME |
2854
|
2
|
|
|
|
|
13
|
|
2855
|
2
|
|
|
|
|
13
|
Workbook - A class for writing Excel Workbooks. |
2856
|
|
|
|
|
|
|
|
2857
|
|
|
|
|
|
|
=head1 SYNOPSIS |
2858
|
|
|
|
|
|
|
|
2859
|
|
|
|
|
|
|
See the documentation for L<Excel::Writer::XLSX> |
2860
|
|
|
|
|
|
|
|
2861
|
|
|
|
|
|
|
=head1 DESCRIPTION |
2862
|
|
|
|
|
|
|
|
2863
|
|
|
|
|
|
|
This module is used in conjunction with L<Excel::Writer::XLSX>. |
2864
|
|
|
|
|
|
|
|
2865
|
|
|
|
|
|
|
=head1 AUTHOR |
2866
|
3
|
|
|
3
|
|
27
|
|
2867
|
3
|
|
|
|
|
7
|
John McNamara jmcnamara@cpan.org |
2868
|
|
|
|
|
|
|
|
2869
|
3
|
|
|
|
|
9
|
=head1 COPYRIGHT |
2870
|
|
|
|
|
|
|
|
2871
|
3
|
|
|
|
|
24
|
(c) MM-MMXXI, John McNamara. |
2872
|
|
|
|
|
|
|
|
2873
|
|
|
|
|
|
|
All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself. |