File Coverage

blib/lib/PDL/IO/XLSX/Writer.pm
Criterion Covered Total %
statement 183 579 31.6
branch 0 84 0.0
condition 0 68 0.0
subroutine 65 101 64.3
pod 0 5 0.0
total 248 837 29.6


line stmt bran cond sub pod time code
1             package PDL::IO::XLSX::Writer::Base;
2 3     3   80 use 5.010;
  3         12  
3 3     3   14 use strict;
  3         5  
  3         70  
4 3     3   11 use warnings;
  3         5  
  3         99  
5 3     3   14 use Carp;
  3         3  
  3         202  
6              
7 3     3   14 use File::Path 'mkpath';
  3         5  
  3         165  
8 3     3   13 use File::Basename 'dirname';
  3         3  
  3         2837  
9              
10             my %XML = (
11             '&' => '&',
12             '<' => '<',
13             '>' => '>',
14             '"' => '"',
15             '\'' => ''',
16             "\n" => ' ',
17             );
18              
19             sub _xml_escape {
20 0   0 0     my $str = shift // '';
21 0           $str =~ s/([&<>"'\n])/$XML{$1}/ge;
  0            
22 0           return $str;
23             }
24              
25             sub _xml_excape_data {
26 0   0 0     my $str = shift // '';
27 0           $str =~ s/([&<>])/$XML{$1}/ge;
  0            
28 0           return $str;
29             }
30              
31             sub new {
32 0     0     my ($class, %args) = @_;
33 0   0       $args{excel_version} //= '2007';
34 0 0         croak "undefined parent" unless ref $args{parent};
35 0           bless \%args, $class;
36             }
37              
38 0     0     sub sheets { shift->{parent}{sheets} }
39 0     0     sub strings { shift->{parent}{strings} }
40 0     0     sub styles { shift->{parent}{styles} }
41 0     0     sub tmpdir { shift->{parent}{tmpdir} }
42              
43             sub open_xml {
44 0     0     my ($self, $file) = @_;
45 0           my $fullname = $self->tmpdir . "/$file";
46 0 0 0       croak if -f $fullname || -d $fullname;
47 0           my $dirname = dirname($fullname);
48 0 0         mkpath($dirname) unless -d $dirname;
49 0 0         open my $fh, '>:encoding(UTF-8)', $fullname or croak "cannot open '$fullname': $!";
50 0           print $fh '' . "\n";
51 0           return $fh;
52             }
53              
54             sub write_xml_start_tag {
55 0     0     my $self = shift;
56 0           my $fh = shift;
57 0           my $tag = shift;
58 0           while (@_) {
59 0           my $key = shift @_;
60 0           my $value = shift @_;
61 0           $value = _xml_escape($value);
62 0           $tag .= qq( $key="$value");
63             }
64 0           print $fh "<$tag>";
65 0           return $self;
66             }
67              
68             sub write_xml_end_tag {
69 0     0     my $self = shift;
70 0           my $fh = shift;
71 0           my $tag = shift;
72 0           print $fh "";
73             }
74              
75             sub write_xml_empty_tag {
76 0     0     my $self = shift;
77 0           my $fh = shift;
78 0           my $tag = shift;
79 0           while (@_) {
80 0           my $key = shift @_;
81 0           my $value = shift @_;
82 0           $value = _xml_escape($value);
83 0           $tag .= qq( $key="$value");
84             }
85 0           print $fh "<$tag/>";
86 0           return $self;
87             }
88              
89             sub write_xml_data_element {
90 0     0     my $self = shift;
91 0           my $fh = shift;
92 0           my $tag = shift;
93 0           my $data = shift;
94 0           my $closetag = "";
95 0           while (@_) {
96 0           my $key = shift @_;
97 0           my $value = shift @_;
98 0           $value = _xml_escape($value);
99 0           $tag .= qq( $key="$value");
100             }
101 0   0       $data //= '';
102 0 0         if ($data ne '') {
103 0           print $fh "<$tag>" . _xml_excape_data($data) . $closetag;
104             }
105             else {
106 0           print $fh "<$tag/>";
107             }
108 0           return $self;
109             }
110              
111             package PDL::IO::XLSX::Writer::SharedStrings;
112 3     3   61 use 5.010;
  3         8  
113 3     3   13 use strict;
  3         5  
  3         64  
114 3     3   11 use warnings;
  3         5  
  3         93  
115 3     3   19 use Carp;
  3         4  
  3         152  
116              
117 3     3   12 use base 'PDL::IO::XLSX::Writer::Base';
  3         3  
  3         2102  
118              
119             sub count {
120 0     0     my $self = shift;
121 0           return scalar keys %{$self->{_ss_hash}};
  0            
122             }
123              
124             sub get_sstring_id {
125 0     0     my $self = shift;
126 0           my $string = shift;
127 0 0         croak "get_sstring_id: undefined string" unless defined $string;
128 0 0         if (!defined $self->{_ss_hash}{$string}) {
129 0           $self->{_ss_hash}{$string} = keys %{$self->{_ss_hash}}; # 0-based index
  0            
130             }
131 0           return $self->{_ss_hash}{$string};
132             }
133              
134             sub save {
135 0     0     my $self = shift;
136 0           my @sorted_ss = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [$_, $self->{_ss_hash}{$_}] } keys %{$self->{_ss_hash}};
  0            
  0            
  0            
  0            
137 0 0         return unless @sorted_ss > 0;
138 0           my $fh = $self->open_xml('xl/sharedStrings.xml');
139 0           my $count = @sorted_ss;
140 0           $self->write_xml_start_tag($fh, 'sst',
141             'xmlns' => "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
142             'count' => $count,
143             'uniqueCount' => $count,
144             );
145 0           for (@sorted_ss) {
146 0           $self->write_xml_start_tag($fh, 'si');
147 0           $self->write_xml_data_element($fh, 't', $_);
148 0           $self->write_xml_end_tag($fh, 'si');
149             }
150 0           $self->write_xml_end_tag($fh, 'sst');
151 0           close $fh;
152             }
153              
154             package PDL::IO::XLSX::Writer::RelRoot;
155 3     3   57 use 5.010;
  3         10  
156 3     3   11 use strict;
  3         3  
  3         59  
157 3     3   9 use warnings;
  3         4  
  3         69  
158 3     3   11 use Carp;
  3         4  
  3         187  
159              
160 3     3   14 use base 'PDL::IO::XLSX::Writer::Base';
  3         5  
  3         1301  
161              
162             sub save {
163 0     0     my $self = shift;
164 0           my $fh = $self->open_xml('_rels/.rels');
165 0           $self->write_xml_start_tag($fh, 'Relationships',
166             'xmlns' => "http://schemas.openxmlformats.org/package/2006/relationships",
167             );
168 0           $self->write_xml_empty_tag($fh, 'Relationship',
169             Id => "rId1",
170             Type => "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" ,
171             Target => "xl/workbook.xml",
172             );
173 0           $self->write_xml_empty_tag($fh, 'Relationship',
174             Id => "rId2",
175             Type => "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties",
176             Target => "docProps/core.xml",
177             );
178 0           $self->write_xml_empty_tag($fh, 'Relationship',
179             Id => "rId3",
180             Type => "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties",
181             Target => "docProps/app.xml",
182             );
183 0           $self->write_xml_end_tag($fh, 'Relationships');
184 0           close $fh;
185             }
186              
187             package PDL::IO::XLSX::Writer::RelWorkbook;
188 3     3   49 use 5.010;
  3         7  
189 3     3   11 use strict;
  3         4  
  3         52  
190 3     3   9 use warnings;
  3         3  
  3         71  
191 3     3   13 use Carp;
  3         4  
  3         183  
192              
193 3     3   12 use base 'PDL::IO::XLSX::Writer::Base';
  3         6  
  3         1317  
194              
195             sub save {
196 0     0     my $self = shift;
197 0           my $fh = $self->open_xml('xl/_rels/workbook.xml.rels');
198 0           $self->write_xml_start_tag($fh, 'Relationships',
199             'xmlns' => "http://schemas.openxmlformats.org/package/2006/relationships",
200             );
201 0           my $i = 1;
202 0           for my $id ($self->sheets->list_id) {
203 0 0         croak "inconsistent sheets i=$i id=$id" if $i != $id;
204 0           $self->write_xml_empty_tag($fh, 'Relationship',
205             Id => "rId$i",
206             Type => "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet",
207             Target => "worksheets/sheet$i.xml",
208             );
209 0           $i++;
210             }
211 0           $self->write_xml_empty_tag($fh, 'Relationship',
212             Id => "rId" . $i++,
213             Type => "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" ,
214             Target => "theme/theme1.xml",
215             );
216 0           $self->write_xml_empty_tag($fh, 'Relationship',
217             Id => "rId" . $i++,
218             Type => "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles",
219             Target => "styles.xml",
220             );
221 0 0         if ($self->strings->count > 0) {
222 0           $self->write_xml_empty_tag($fh, 'Relationship',
223             Id => "rId" . $i++,
224             Type => "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings",
225             Target => "sharedStrings.xml",
226             );
227             }
228 0           $self->write_xml_end_tag($fh, 'Relationships');
229 0           close $fh;
230             }
231              
232             package PDL::IO::XLSX::Writer::PropsCore;
233 3     3   44 use 5.010;
  3         8  
234 3     3   11 use strict;
  3         4  
  3         58  
235 3     3   12 use warnings;
  3         3  
  3         116  
236 3     3   58 use Carp;
  3         12  
  3         183  
237              
238 3     3   13 use base 'PDL::IO::XLSX::Writer::Base';
  3         2  
  3         951  
239              
240 3     3   1706 use Time::Moment;
  3         4466  
  3         561  
241              
242             sub save {
243 0     0     my $self = shift;
244 0           my $fh = $self->open_xml('docProps/core.xml');
245 0           $self->write_xml_start_tag($fh, 'cp:coreProperties',
246             'xmlns:cp' => "http://schemas.openxmlformats.org/package/2006/metadata/core-properties",
247             'xmlns:dc' => "http://purl.org/dc/elements/1.1/",
248             'xmlns:dcterms' => "http://purl.org/dc/terms/",
249             'xmlns:dcmitype' => "http://purl.org/dc/dcmitype/",
250             'xmlns:xsi' => "http://www.w3.org/2001/XMLSchema-instance",
251             );
252 0           my $now = Time::Moment->now_utc->strftime("%Y-%m-%dT%H:%M:%SZ"); # 2016-12-05T13:54:42Z
253 0           $self->write_xml_data_element($fh, 'dc:title', $self->{title});
254 0           $self->write_xml_data_element($fh, 'dc:subject', $self->{subject});
255 0           $self->write_xml_data_element($fh, 'dc:creator', $self->{author});
256 0           $self->write_xml_data_element($fh, 'cp:lastModifiedBy', $self->{author});
257 0           $self->write_xml_data_element($fh, 'dcterms:created', $now, 'xsi:type' => "dcterms:W3CDTF");
258 0           $self->write_xml_data_element($fh, 'dcterms:modified', $now, 'xsi:type' => "dcterms:W3CDTF");
259 0           $self->write_xml_end_tag($fh, 'cp:coreProperties');
260 0           close $fh;
261             }
262              
263             package PDL::IO::XLSX::Writer::PropsApp;
264 3     3   66 use 5.010;
  3         8  
265 3     3   37 use strict;
  3         5  
  3         92  
266 3     3   14 use warnings;
  3         4  
  3         89  
267 3     3   12 use Carp;
  3         3  
  3         206  
268              
269 3     3   14 use base 'PDL::IO::XLSX::Writer::Base';
  3         22  
  3         1757  
270              
271             sub save {
272 0     0     my $self = shift;
273 0           my $sheets = $self->sheets->count;
274 0           my $fh = $self->open_xml('docProps/app.xml');
275 0           $self->write_xml_start_tag($fh, 'Properties',
276             'xmlns' => "http://schemas.openxmlformats.org/officeDocument/2006/extended-properties",
277             'xmlns:vt' => "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes",
278             );
279 0           $self->write_xml_data_element($fh, 'Application', 'Microsoft Excel');
280 0           $self->write_xml_data_element($fh, 'DocSecurity', '0');
281 0           $self->write_xml_data_element($fh, 'ScaleCrop', 'false');
282 0           $self->write_xml_start_tag($fh, 'HeadingPairs');
283 0           $self->write_xml_start_tag($fh, 'vt:vector', size => 2, baseType => "variant");
284 0           $self->write_xml_start_tag($fh, 'vt:variant');
285 0           $self->write_xml_data_element($fh, 'vt:lpstr', 'Worksheets');
286 0           $self->write_xml_end_tag($fh, 'vt:variant');
287 0           $self->write_xml_start_tag($fh, 'vt:variant');
288 0           $self->write_xml_data_element($fh, 'vt:i4', $sheets);
289 0           $self->write_xml_end_tag($fh, 'vt:variant');
290 0           $self->write_xml_end_tag($fh, 'vt:vector');
291 0           $self->write_xml_end_tag($fh, 'HeadingPairs');
292 0           $self->write_xml_start_tag($fh, 'TitlesOfParts');
293 0           $self->write_xml_start_tag($fh, 'vt:vector', size => $sheets, baseType => "lpstr");
294 0           for my $name ($self->sheets->list_name) {
295 0           $self->write_xml_data_element($fh, 'vt:lpstr', $name);
296             }
297 0           $self->write_xml_end_tag($fh, 'vt:vector');
298 0           $self->write_xml_end_tag($fh, 'TitlesOfParts');
299 0           $self->write_xml_data_element($fh, 'Company', $self->{company});
300 0           $self->write_xml_data_element($fh, 'LinksUpToDate', 'false');
301 0           $self->write_xml_data_element($fh, 'SharedDoc', 'false');
302 0           $self->write_xml_data_element($fh, 'HyperlinksChanged', 'false');
303             #Excel 2007 (v12.0), Excel 2010 (v14.0), Excel 2013 (v15.0), Excel 2016 (v16.0)
304 0 0         my $ver = $self->{excel_version} eq '2010' ? '14.0000' : '12.0000';
305 0           $self->write_xml_data_element($fh, 'AppVersion', $ver);
306 0           $self->write_xml_end_tag($fh, 'Properties');
307 0           close $fh;
308             }
309              
310             package PDL::IO::XLSX::Writer::Styles;
311 3     3   50 use 5.010;
  3         8  
312 3     3   14 use strict;
  3         4  
  3         67  
313 3     3   13 use warnings;
  3         3  
  3         98  
314 3     3   12 use Carp;
  3         3  
  3         179  
315              
316 3     3   13 use base 'PDL::IO::XLSX::Writer::Base';
  3         6  
  3         3013  
317              
318             my %builtin = (
319             '0' => 1, # 'int'
320             '0.00' => 2, # 'float'
321             '#,##0' => 3, # 'float'
322             '#,##0.00' => 4, # 'float'
323             '0%' => 9, # 'int'
324             '0.00%' => 10, # 'float'
325             '0.00E+00' => 11, # 'float'
326              
327             );
328              
329             sub get_style_attr {
330 0     0     my $self = shift;
331 0           my $style = shift;
332 0 0 0       return '' if ($style//'') eq '';
333              
334 0 0         if (defined $self->{_style_hash}{$style}) {
335 0           return qq( s="$self->{_style_hash}{$style}{seqid}"); # must start with a space
336             }
337 0           $self->{_style_hash}{$style}{seqid} = 1 + keys %{$self->{_style_hash}}; # 1-based index
  0            
338 0 0         if (defined $builtin{$style}) {
339 0           $self->{_style_hash}{$style}{builtin} = 1;
340 0           $self->{_style_hash}{$style}{fmtid} = $builtin{$style};
341             }
342             else {
343 0   0       $self->{_style_next_custom_fmtid} //= 164; # numFmtId less than 164 are "built-in"
344 0           $self->{_style_hash}{$style}{fmtid} = $self->{_style_next_custom_fmtid}++;
345             }
346 0           $self->{_style_hash}{$style}{format} = $style;
347 0           return qq( s="$self->{_style_hash}{$style}{seqid}"); # must start with a space
348             }
349              
350             sub save {
351 0     0     my $self = shift;
352 0           my @sorted_formats = sort { $a->{seqid} <=> $b->{seqid} } values %{$self->{_style_hash}};
  0            
  0            
353 0           my @custom_formats = grep { !$_->{builtin} } @sorted_formats;
  0            
354 0           my $fh = $self->open_xml('xl/styles.xml');
355 0           $self->write_xml_start_tag($fh, 'styleSheet', xmlns => "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
356 0 0         if (@custom_formats > 0) {
357 0           $self->write_xml_start_tag($fh, 'numFmts', count => scalar(@custom_formats));
358 0           $self->write_xml_empty_tag($fh, 'numFmt', formatCode => $_->{format}, numFmtId => $_->{fmtid}) for (@custom_formats);
359 0           $self->write_xml_end_tag($fh, 'numFmts');
360             }
361 0           $self->write_xml_start_tag($fh, 'fonts', count => 1);
362 0           $self->write_xml_start_tag($fh, 'font');
363 0           $self->write_xml_empty_tag($fh, 'sz', val => "11");
364 0           $self->write_xml_empty_tag($fh, 'color', theme => "1");
365 0           $self->write_xml_empty_tag($fh, 'name', val => "Calibri");
366 0           $self->write_xml_empty_tag($fh, 'family', val => "2");
367 0           $self->write_xml_empty_tag($fh, 'scheme', val => "minor");
368 0           $self->write_xml_end_tag($fh, 'font');
369 0           $self->write_xml_end_tag($fh, 'fonts');
370 0           $self->write_xml_start_tag($fh, 'fills', count => 2);
371 0           $self->write_xml_start_tag($fh, 'fill');
372 0           $self->write_xml_empty_tag($fh, 'patternFill', patternType => "none");
373 0           $self->write_xml_end_tag($fh, 'fill');
374 0           $self->write_xml_start_tag($fh, 'fill');
375 0           $self->write_xml_empty_tag($fh, 'patternFill', patternType => "gray125");
376 0           $self->write_xml_end_tag($fh, 'fill');
377              
378             ### header style - gray background XXX-TODO
379             #$self->write_xml_start_tag($fh, 'fill');
380             #$self->write_xml_start_tag($fh, 'patternFill', patternType => "solid");
381             #$self->write_xml_empty_tag($fh, 'fgColor', theme => "0", tint => "-0.14999847407452621");
382             #$self->write_xml_empty_tag($fh, 'bgColor', indexed => "64");
383             #$self->write_xml_end_tag($fh, 'patternFill');
384             #$self->write_xml_end_tag($fh, 'fill');
385              
386 0           $self->write_xml_end_tag($fh, 'fills');
387 0           $self->write_xml_start_tag($fh, 'borders', count => 1);
388 0           $self->write_xml_start_tag($fh, 'border');
389 0           $self->write_xml_empty_tag($fh, 'left');
390 0           $self->write_xml_empty_tag($fh, 'right');
391 0           $self->write_xml_empty_tag($fh, 'top');
392 0           $self->write_xml_empty_tag($fh, 'bottom');
393 0           $self->write_xml_empty_tag($fh, 'diagonal');
394 0           $self->write_xml_end_tag($fh, 'border');
395 0           $self->write_xml_end_tag($fh, 'borders');
396 0           $self->write_xml_start_tag($fh, 'cellStyleXfs', count => "1");
397 0           $self->write_xml_empty_tag($fh, 'xf', numFmtId => "0", fontId => "0", fillId => "0", borderId => "0");
398 0           $self->write_xml_end_tag($fh, 'cellStyleXfs');
399 0           $self->write_xml_start_tag($fh, 'cellXfs', count => 1 + @sorted_formats);
400 0           $self->write_xml_empty_tag($fh, 'xf', numFmtId => "0", fontId => "0", fillId => "0", borderId => "0", xfId => "0");
401 0           for (@sorted_formats) {
402 0           $self->write_xml_empty_tag($fh, 'xf', numFmtId => $_->{fmtid}, fontId => "0", fillId => "0", borderId => "0", xfId => "0", applyNumberFormat => "1");
403             }
404 0           $self->write_xml_end_tag($fh, 'cellXfs');
405 0           $self->write_xml_start_tag($fh, 'cellStyles', count => "1");
406 0           $self->write_xml_empty_tag($fh, 'cellStyle', name => "Normal", xfId => "0", builtinId => "0");
407 0           $self->write_xml_end_tag($fh, 'cellStyles');
408 0           $self->write_xml_empty_tag($fh, 'dxfs', count => "0");
409 0           $self->write_xml_empty_tag($fh, 'tableStyles', count => "0", defaultTableStyle => "TableStyleMedium9", defaultPivotStyle => "PivotStyleLight16");
410 0           $self->write_xml_end_tag($fh, 'styleSheet');
411 0           close $fh;
412             }
413              
414             package PDL::IO::XLSX::Writer::Theme;
415 3     3   62 use 5.010;
  3         11  
416 3     3   9 use strict;
  3         6  
  3         58  
417 3     3   9 use warnings;
  3         5  
  3         75  
418 3     3   10 use Carp;
  3         3  
  3         159  
419              
420 3     3   11 use base 'PDL::IO::XLSX::Writer::Base';
  3         3  
  3         1418  
421              
422             sub save {
423 0     0     my $self = shift;
424 0           my $fh = $self->open_xml('xl/theme/theme1.xml');
425             # hardcoded for now
426 0           print $fh '';
427 0           close $fh;
428             }
429              
430             package PDL::IO::XLSX::Writer::ContentTypes;
431 3     3   70 use 5.010;
  3         8  
432 3     3   9 use strict;
  3         5  
  3         76  
433 3     3   12 use warnings;
  3         4  
  3         97  
434 3     3   11 use Carp;
  3         11  
  3         192  
435              
436 3     3   16 use base 'PDL::IO::XLSX::Writer::Base';
  3         3  
  3         1699  
437              
438             sub save {
439 0     0     my $self = shift;
440 0           my $fh = $self->open_xml('[Content_Types].xml');
441 0           $self->write_xml_start_tag($fh, 'Types',
442             'xmlns' => "http://schemas.openxmlformats.org/package/2006/content-types",
443             );
444 0           $self->write_xml_empty_tag($fh, 'Default',
445             Extension => "rels",
446             ContentType => "application/vnd.openxmlformats-package.relationships+xml",
447             );
448 0           $self->write_xml_empty_tag($fh, 'Default',
449             Extension => "xml",
450             ContentType => "application/xml",
451             );
452 0           $self->write_xml_empty_tag($fh, 'Override',
453             PartName => "/docProps/app.xml",
454             ContentType => "application/vnd.openxmlformats-officedocument.extended-properties+xml",
455             );
456 0           $self->write_xml_empty_tag($fh, 'Override',
457             PartName => "/docProps/core.xml",
458             ContentType => "application/vnd.openxmlformats-package.core-properties+xml",
459             );
460 0           $self->write_xml_empty_tag($fh, 'Override',
461             PartName => "/xl/styles.xml",
462             ContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml",
463             );
464 0           $self->write_xml_empty_tag($fh, 'Override',
465             PartName => "/xl/theme/theme1.xml",
466             ContentType => "application/vnd.openxmlformats-officedocument.theme+xml",
467             );
468 0           $self->write_xml_empty_tag($fh, 'Override',
469             PartName => "/xl/workbook.xml",
470             ContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml",
471             );
472 0           for my $id ($self->sheets->list_id) {
473 0           $self->write_xml_empty_tag($fh, 'Override',
474             PartName => "/xl/worksheets/sheet$id.xml",
475             ContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml",
476             );
477             }
478 0 0         if ($self->strings->count > 0) {
479 0           $self->write_xml_empty_tag($fh, 'Override',
480             PartName => "/xl/sharedStrings.xml",
481             ContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml",
482             );
483             }
484 0           $self->write_xml_end_tag($fh, 'Types');
485 0           close $fh;
486             }
487              
488             package PDL::IO::XLSX::Writer::Workbook;
489 3     3   44 use 5.010;
  3         8  
490 3     3   13 use strict;
  3         5  
  3         71  
491 3     3   12 use warnings;
  3         17  
  3         69  
492 3     3   10 use Carp;
  3         4  
  3         196  
493              
494 3     3   14 use base 'PDL::IO::XLSX::Writer::Base';
  3         4  
  3         1617  
495              
496             sub save {
497 0     0     my $self = shift;
498 0           my $fh = $self->open_xml('xl/workbook.xml');
499             ##
500 0           $self->write_xml_start_tag($fh, 'workbook',
501             'xmlns' => 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
502             'xmlns:r' => 'http://schemas.openxmlformats.org/officeDocument/2006/relationships',
503             );
504             ##
505 0           $self->write_xml_empty_tag($fh, 'fileVersion',
506             'appName' => 'xl',
507             'lastEdited' => 4,
508             'lowestEdited' => 4,
509             'rupBuild' => 4505,
510             );
511             ##
512 0           $self->write_xml_empty_tag($fh, 'workbookPr',
513             'defaultThemeVersion' => 124226,
514             );
515             ##
516 0           $self->write_xml_start_tag($fh, 'bookViews');
517             ##
518 0           $self->write_xml_empty_tag($fh, 'workbookView',
519             'xWindow' => 384,
520             'yWindow' => 84,
521             'windowWidth' => 18180,
522             'windowHeight' => 7176,
523             );
524             ##
525 0           $self->write_xml_end_tag($fh, 'bookViews');
526             ##
527 0           $self->write_xml_start_tag($fh, 'sheets' );
528 0           for ($self->sheets->list_id_name) {
529 0           my ($id, $name) = @$_;
530             ##
531 0           $self->write_xml_empty_tag($fh, 'sheet',
532             'r:id' => "rId$id",
533             'name' => $name,
534             'sheetId' => $id,
535             );
536             }
537             ##
538 0           $self->write_xml_end_tag($fh, 'sheets');
539             ##
540 0           $self->write_xml_empty_tag($fh, 'calcPr',
541             'calcId' => 124519,
542             'fullCalcOnLoad' => 1,
543             );
544             #
545 0           $self->write_xml_end_tag($fh, 'workbook');
546 0           close $fh;
547             }
548              
549             package PDL::IO::XLSX::Writer::Sheets;
550 3     3   45 use 5.010;
  3         8  
551 3     3   12 use strict;
  3         7  
  3         78  
552 3     3   10 use warnings;
  3         2  
  3         71  
553 3     3   9 use Carp;
  3         3  
  3         203  
554              
555 3     3   11 use base 'PDL::IO::XLSX::Writer::Base';
  3         4  
  3         920  
556              
557 3     3   21 use Scalar::Util qw(looks_like_number);
  3         5  
  3         30083  
558              
559             my @row2letter = ( 'A' .. 'XFD' );
560              
561             sub count {
562 0     0     my $self = shift;
563 0   0       return scalar @{$self->{_sheet_list}//[]};
  0            
564             }
565              
566             sub list_id {
567 0     0     my $self = shift;
568 0   0       return map { $_->[0] } @{$self->{_sheet_list}//[]};
  0            
  0            
569             }
570              
571             sub list_name {
572 0     0     my $self = shift;
573 0   0       return map { $_->[1] } @{$self->{_sheet_list}//[]};
  0            
  0            
574             }
575              
576             sub list_id_name {
577 0     0     my $self = shift;
578 0   0       return map { [$_->[0], $_->[1]] } @{$self->{_sheet_list}//[]};
  0            
  0            
579             }
580              
581             sub start {
582 0     0     my ($self, $sheet_name, $width_hash, $format_array) = @_;
583 0 0 0       croak "you must call save() before start()" if defined $self->{_sheet_fh} || defined $self->{_sheet_id};
584 0           my $sheet_id = $self->count + 1;
585 0           my $fh = $self->open_xml("xl/worksheets/sheet$sheet_id.xml");
586 0           $self->{_sheet_id} = $sheet_id;
587 0           $self->{_sheet_name} = $sheet_name;
588 0           $self->{_sheet_rows} = 0;
589 0           $self->{_sheet_cols} = 0;
590 0           $self->{_sheet_fh} = $fh;
591 0   0       $self->{_sheet_colfmt} = $format_array // [];
592 0           my @ex2010 = (
593             'xmlns:mc' => 'http://schemas.openxmlformats.org/markup-compatibility/2006',
594             'xmlns:x14ac' => 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac',
595             'mc:Ignorable' => 'x14ac',
596             );
597             $self->write_xml_start_tag($fh, 'worksheet',
598             'xmlns' => "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
599             'xmlns:r' => "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
600 0 0         ( $self->{excel_version} eq '2010' ? (@ex2010) : () ),
601             );
602 0           $self->write_xml_empty_tag($fh, 'dimension', ref => "A1");
603 0           $self->write_xml_start_tag($fh, 'sheetViews');
604 0 0         $self->write_xml_empty_tag($fh, 'sheetView', tabSelected => ($sheet_id == 1 ? 1 : 0), workbookViewId => 0);
605 0           $self->write_xml_end_tag($fh, 'sheetViews');
606             $self->write_xml_empty_tag($fh, 'sheetFormatPr',
607             'defaultRowHeight' => 15,
608 0 0         ( $self->{excel_version} eq '2010' ? ('x14ac:dyDescent' => '0.25') : () ),
609             );
610             # handle custom column width
611 0 0         if (my @wkeys = (keys %$width_hash)) {
612 0           my @tmp;
613 0           for my $k (@wkeys) {
614 0           my $width = $width_hash->{$k};
615 0           my ($min,undef,$max) = $k =~ /^(\d+)(-(\d+))?$/; # accept "123" as well as "123-130"
616 0   0       $max //= $min;
617 0 0 0       croak "custom-width: invalid column definition '$k'" unless defined $min && defined $max;
618 0 0         croak "custom-width: invalid width '$width'" unless looks_like_number($width);
619 0           push @tmp, [ $min, $max, $width ];
620             }
621 0           @tmp = sort { $a->[0] <=> $b->[0] } @tmp; # sort by min
  0            
622 0           $self->write_xml_start_tag($fh, 'cols');
623 0           $self->write_xml_empty_tag($fh, 'col', min=>$_->[0], max=>$_->[1], width=>$_->[2], customWidth=>"1") for (@tmp);
624 0           $self->write_xml_end_tag($fh, 'cols');
625             }
626             # open tag - it will be closed in save()
627 0           $self->write_xml_start_tag($fh, 'sheetData');
628             }
629              
630             sub add_row {
631 0     0     my $self = shift;
632 0   0       my $data = shift // [];
633 0   0       my $format = shift // $self->{_sheet_colfmt} // [];
      0        
634              
635 0           my $cols = scalar @{$data};
  0            
636 0           my $r = $self->{_sheet_rows} + 1;
637              
638             # Excel2010 limitations - https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
639 0 0         if ($r > 1048576) {
640 0 0         carp "rows with index above 1048576 will be ignored" if !$self->{_warn_rows};
641 0           $self->{_warn_rows} = 1;
642 0           return;
643             }
644 0 0         if ($cols > 16384) {
645 0 0         carp "columns with index above 16384 will be ignored" if !$self->{_warn_cols};
646 0           $self->{_warn_cols} = 1;
647 0           $cols = 16384;
648             }
649              
650 0           $self->{_sheet_rows} = $r;
651 0 0         $self->{_sheet_cols} = $cols if $self->{_sheet_cols} < $cols;
652 0 0         my $xmlcells = $self->{excel_version} eq '2010' ? sprintf('', $r, 1, $cols)
653             : sprintf('', $r, 1, $cols);
654 0           my @s_attr = map { $self->styles->get_style_attr($_) } @$format;
  0            
655 0           for(my $c = 1; $c <= $cols; $c++) {
656 0           my $val = $data->[$c-1];
657 0 0 0       if (looks_like_number($val)) {
    0          
658 0 0         if ($s_attr[$c-1]) {
659             # add format/style attribute s="?"
660 0           $xmlcells .= sprintf('%s', $row2letter[$c-1] . $r, $s_attr[$c-1], $val);
661             }
662             else {
663             # no format/style attribute s="?"
664 0           $xmlcells .= sprintf('%s', $row2letter[$c-1] . $r, $val);
665             }
666             }
667             elsif (($val//'') ne '') {
668 0           my $id = $self->strings->get_sstring_id($val);
669 0           $xmlcells .= sprintf('%s', $row2letter[$c-1] . $r, $id);
670             }
671             }
672 0           $xmlcells .= '';
673 0           print { $self->{_sheet_fh} } $xmlcells;
  0            
674             }
675              
676             sub save {
677 0     0     my $self = shift;
678             croak "incomplete data" unless defined $self->{_sheet_id} && defined $self->{_sheet_name} &&
679             defined $self->{_sheet_rows} && defined $self->{_sheet_cols} &&
680 0 0 0       defined $self->{_sheet_fh};
      0        
      0        
      0        
681 0           my $fh = $self->{_sheet_fh};
682 0           $self->write_xml_end_tag($fh, 'sheetData');
683 0           $self->write_xml_empty_tag($fh, 'pageMargins', left => "0.7", right => "0.7", top => "0.75", bottom => "0.75", header => "0.3", footer => "0.3");
684 0           $self->write_xml_end_tag($fh, 'worksheet');
685 0           close $self->{_sheet_fh};
686 0           push @{ $self->{_sheet_list} }, [ $self->{_sheet_id}, $self->{_sheet_name}, $self->{_sheet_rows}, $self->{_sheet_cols} ];
  0            
687 0           $self->{_sheet_id} = undef;
688 0           $self->{_sheet_name} = undef;
689 0           $self->{_sheet_rows} = undef;
690 0           $self->{_sheet_cols} = undef;
691 0           $self->{_sheet_colfmt} = undef;
692 0           $self->{_sheet_colwidth} = undef;
693 0           $self->{_sheet_fh} = undef;
694             }
695              
696             package PDL::IO::XLSX::Writer;
697 3     3   105 use 5.010;
  3         12  
698 3     3   17 use strict;
  3         7  
  3         96  
699 3     3   14 use warnings;
  3         5  
  3         123  
700 3     3   16 use Carp;
  3         6  
  3         296  
701              
702 3     3   17 use File::Temp;
  3         4  
  3         287  
703 3     3   3446 use Archive::Zip;
  3         238379  
  3         302  
704 3     3   38 use Scalar::Util qw(openhandle looks_like_number);
  3         6  
  3         2947  
705              
706             sub new {
707 0     0 0   my ($class, %args) = @_;
708 0           my $tmp_dir = delete $args{tmp_dir};
709 0 0 0       my $tmp_cleanup = delete $args{tmp_cleanup} // 1 ? 1 : 0;
710 0           my $compression = delete $args{compression};
711              
712 0 0         if (defined $compression) {
713 0 0 0       croak "compression must be 0..9" unless looks_like_number($compression) && $compression >= 0 && $compression <= 9;
      0        
714             }
715              
716 0 0 0       my $tmp = $tmp_dir && -d $tmp_dir ? File::Temp->newdir( "xlsx_writer_XXXXXX", DIR => $tmp_dir, CLEANUP => $tmp_cleanup )
717             : File::Temp->newdir( "xlsx_writer_XXXXXX", TMPDIR => 1, CLEANUP => $tmp_cleanup );
718 0   0       my $self = bless {
719             extra_args => \%args,
720             compression => $compression // 6, # 6 = default, 0 = none, 1 = fastest, 9 = best
721             tmpdir => $tmp,
722             }, $class;
723 0           $self->{styles} = PDL::IO::XLSX::Writer::Styles->new(%{$self->{extra_args}}, parent => $self);
  0            
724 0           $self->{strings} = PDL::IO::XLSX::Writer::SharedStrings->new(%{$self->{extra_args}}, parent => $self);
  0            
725 0           $self->{sheets} = PDL::IO::XLSX::Writer::Sheets->new(%{$self->{extra_args}}, parent => $self);
  0            
726 0           return $self;
727             }
728              
729 0     0 0   sub sheets { shift->{sheets} }
730 0     0 0   sub strings { shift->{strings} }
731 0     0 0   sub styles { shift->{styles} }
732              
733             sub xlsx_save {
734 0     0 0   my $self = shift;
735 0           my $filename_or_fh = shift;
736              
737 0 0         croak "no sheets" if $self->sheets->count == 0;
738              
739 0           my $fh;
740 0 0         if (!defined $filename_or_fh) {
    0          
741 0           $fh = \*STDOUT;
742             }
743             elsif (openhandle($filename_or_fh)) {
744 0           $fh = $filename_or_fh;
745             }
746             else {
747 0 0         open $fh, ">", $filename_or_fh or croak "$filename_or_fh: $!";
748             }
749              
750             #$self->sheets->save; #XXX-TODO detect unsaved sheets
751              
752 0           PDL::IO::XLSX::Writer::Workbook ->new(%{$self->{extra_args}}, parent => $self)->save;
  0            
753 0           PDL::IO::XLSX::Writer::PropsApp ->new(%{$self->{extra_args}}, parent => $self)->save;
  0            
754 0           PDL::IO::XLSX::Writer::PropsCore ->new(%{$self->{extra_args}}, parent => $self)->save;
  0            
755 0           PDL::IO::XLSX::Writer::Theme ->new(%{$self->{extra_args}}, parent => $self)->save;
  0            
756 0           PDL::IO::XLSX::Writer::ContentTypes ->new(%{$self->{extra_args}}, parent => $self)->save;
  0            
757 0           PDL::IO::XLSX::Writer::RelWorkbook ->new(%{$self->{extra_args}}, parent => $self)->save;
  0            
758 0           PDL::IO::XLSX::Writer::RelRoot ->new(%{$self->{extra_args}}, parent => $self)->save;
  0            
759              
760 0           $self->styles->save;
761 0           $self->strings->save;
762              
763 0           my $zip = Archive::Zip->new;
764 0           my $dir_member = $zip->addTree($self->{tmpdir}, '', undef, $self->{compression});
765 0 0         croak 'ZIP write error' unless $zip->writeToFileHandle($fh) == Archive::Zip::AZ_OK;
766 0           return $self;
767             }
768              
769             1;
770              
771             __END__