1#!/usr/bin/perl -w
2
3###############################################################################
4#
5# Testcases for Spreadsheet::WriteExcel.
6#
7# Tests for Workbook property_sets() interface.
8#
9# reverse('�'), Auguest 2008, John McNamara, jmcnamara@cpan.org
10#
11
12
13use strict;
14use Carp;
15
16use Spreadsheet::WriteExcel;
17use Spreadsheet::WriteExcel::Properties ':testing';
18use Time::Local 'timegm';
19use Test::More tests => 17;
20
21
22###############################################################################
23#
24# Tests setup
25#
26my $test_file   = "temp_test_file.xls";
27my $workbook    = Spreadsheet::WriteExcel->new($test_file);
28my $worksheet   = $workbook->add_worksheet();
29
30my $target;
31my $result;
32my $caption;
33my $string;
34my $codepage;
35my $smiley      = chr 0x263A;
36my $filetime;
37my @properties;
38my %params;
39my @strings;
40
41
42###############################################################################
43#
44# Test 1. _get_property_set_codepage() for default latin1 strings.
45#
46%params =   (
47                title       => 'Title',
48                subject     => 'Subject',
49                author      => 'Author',
50                keywords    => 'Keywords',
51                comments    => 'Comments',
52                last_author => 'Username',
53            );
54
55@strings = qw(title subject author keywords comments last_author);
56
57
58$caption    = " \t_get_property_set_codepage('latin1')";
59$target     = 0x04E4;
60
61$result     = $workbook->_get_property_set_codepage(\%params, \@strings);
62is($result, $target, $caption);
63
64
65###############################################################################
66#
67# Test 2. _get_property_set_codepage() for manual utf8 strings.
68#
69
70%params =   (
71                title       => 'Title',
72                subject     => 'Subject',
73                author      => 'Author',
74                keywords    => 'Keywords',
75                comments    => 'Comments',
76                last_author => 'Username',
77                utf8        => 1,
78            );
79
80@strings = qw(title subject author keywords comments last_author);
81
82
83$caption    = " \t_get_property_set_codepage('utf8')";
84$target     = 0xFDE9;
85
86$result     = $workbook->_get_property_set_codepage(\%params, \@strings);
87is($result, $target, $caption);
88
89
90###############################################################################
91#
92# Test 3. _get_property_set_codepage() for perl 5.8 utf8 strings.
93#
94SKIP: {
95
96skip " \t_get_property_set_codepage('utf8'). Requires Perl 5.8 Unicode.", 1
97     if $] < 5.008;
98
99%params =   (
100                title       => 'Title' . $smiley,
101                subject     => 'Subject',
102                author      => 'Author',
103                keywords    => 'Keywords',
104                comments    => 'Comments',
105                last_author => 'Username',
106            );
107
108@strings = qw(title subject author keywords comments last_author);
109
110
111$caption    = " \t_get_property_set_codepage('utf8')";
112$target     = 0xFDE9;
113
114$result     = $workbook->_get_property_set_codepage(\%params, \@strings);
115is($result, $target, $caption);
116}
117
118
119###############################################################################
120#
121# Note, the "created => undef" parameters in some of the following tests is
122# used to avoid adding the default date to the property sets.
123
124
125###############################################################################
126#
127# Test 4. Codepage only.
128#
129
130$workbook->set_properties(
131                            created     => undef,
132                         );
133
134$caption    = " \tset_properties(codepage)";
135$target     = join " ",  qw(
136                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
137                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
138                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
139                            18 00 00 00 01 00 00 00 01 00 00 00 10 00 00 00
140                            02 00 00 00 E4 04 00 00
141                           );
142
143$result     = unpack_record( $workbook->{summary} );
144is($result, $target, $caption);
145
146
147###############################################################################
148#
149# Test 5. Same as previous + Title.
150#
151
152$workbook->set_properties(
153                            title       => 'Title',
154                            created     => undef,
155                         );
156
157$caption    = " \tset_properties('Title')";
158$target     = join " ",  qw(
159                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
160                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
161                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
162                            30 00 00 00 02 00 00 00 01 00 00 00 18 00 00 00
163                            02 00 00 00 20 00 00 00 02 00 00 00 E4 04 00 00
164                            1E 00 00 00 06 00 00 00 54 69 74 6C 65 00 00 00
165                           );
166
167$result     = unpack_record( $workbook->{summary} );
168is($result, $target, $caption);
169
170
171###############################################################################
172#
173# Test 6. Same as previous + Subject.
174#
175
176$workbook->set_properties(
177                            title       => 'Title',
178                            subject     => 'Subject',
179                            created     => undef,
180                         );
181
182$caption    = " \tset_properties('+ Subject')";
183$target     = join " ",  qw(
184                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
185                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
186                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
187                            48 00 00 00 03 00 00 00 01 00 00 00 20 00 00 00
188                            02 00 00 00 28 00 00 00 03 00 00 00 38 00 00 00
189                            02 00 00 00 E4 04 00 00 1E 00 00 00 06 00 00 00
190                            54 69 74 6C 65 00 00 00 1E 00 00 00 08 00 00 00
191                            53 75 62 6A 65 63 74 00
192                           );
193
194$result     = unpack_record( $workbook->{summary} );
195is($result, $target, $caption);
196
197
198###############################################################################
199#
200# Test 7. Same as previous + Author.
201#
202
203$workbook->set_properties(
204                            title       => 'Title',
205                            subject     => 'Subject',
206                            author      => 'Author',
207                            created     => undef,
208                         );
209
210$caption    = " \tset_properties('+ Author')";
211$target     = join " ",  qw(
212                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
213                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
214                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
215                            60 00 00 00 04 00 00 00 01 00 00 00 28 00 00 00
216                            02 00 00 00 30 00 00 00 03 00 00 00 40 00 00 00
217                            04 00 00 00 50 00 00 00 02 00 00 00 E4 04 00 00
218                            1E 00 00 00 06 00 00 00 54 69 74 6C 65 00 00 00
219                            1E 00 00 00 08 00 00 00 53 75 62 6A 65 63 74 00
220                            1E 00 00 00 07 00 00 00 41 75 74 68 6F 72 00 00
221                           );
222
223$result     = unpack_record( $workbook->{summary} );
224is($result, $target, $caption);
225
226
227###############################################################################
228#
229# Test 8. Same as previous + Keywords.
230#
231
232$workbook->set_properties(
233                            title       => 'Title',
234                            subject     => 'Subject',
235                            author      => 'Author',
236                            keywords    => 'Keywords',
237                            created     => undef,
238                         );
239
240$caption    = " \tset_properties('+ Keywords')";
241$target     = join " ",  qw(
242                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
243                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
244                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
245                            7C 00 00 00 05 00 00 00 01 00 00 00 30 00 00 00
246                            02 00 00 00 38 00 00 00 03 00 00 00 48 00 00 00
247                            04 00 00 00 58 00 00 00 05 00 00 00 68 00 00 00
248                            02 00 00 00 E4 04 00 00 1E 00 00 00 06 00 00 00
249                            54 69 74 6C 65 00 00 00 1E 00 00 00 08 00 00 00
250                            53 75 62 6A 65 63 74 00 1E 00 00 00 07 00 00 00
251                            41 75 74 68 6F 72 00 00 1E 00 00 00 09 00 00 00
252                            4B 65 79 77 6F 72 64 73 00 00 00 00
253                           );
254
255$result     = unpack_record( $workbook->{summary} );
256is($result, $target, $caption);
257
258
259###############################################################################
260#
261# Test 9. Same as previous + Comments.
262#
263
264$workbook->set_properties(
265                            title       => 'Title',
266                            subject     => 'Subject',
267                            author      => 'Author',
268                            keywords    => 'Keywords',
269                            comments    => 'Comments',
270                            created     => undef,
271                         );
272
273$caption    = " \tset_properties('+ Comments')";
274$target     = join " ",  qw(
275                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
276                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
277                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
278                            98 00 00 00 06 00 00 00 01 00 00 00 38 00 00 00
279                            02 00 00 00 40 00 00 00 03 00 00 00 50 00 00 00
280                            04 00 00 00 60 00 00 00 05 00 00 00 70 00 00 00
281                            06 00 00 00 84 00 00 00 02 00 00 00 E4 04 00 00
282                            1E 00 00 00 06 00 00 00 54 69 74 6C 65 00 00 00
283                            1E 00 00 00 08 00 00 00 53 75 62 6A 65 63 74 00
284                            1E 00 00 00 07 00 00 00 41 75 74 68 6F 72 00 00
285                            1E 00 00 00 09 00 00 00 4B 65 79 77 6F 72 64 73
286                            00 00 00 00 1E 00 00 00 09 00 00 00 43 6F 6D 6D
287                            65 6E 74 73 00 00 00 00
288                           );
289
290$result     = unpack_record( $workbook->{summary} );
291is($result, $target, $caption);
292
293
294###############################################################################
295#
296# Test 10. Same as previous + Last author.
297#
298
299$workbook->set_properties(
300                            title       => 'Title',
301                            subject     => 'Subject',
302                            author      => 'Author',
303                            keywords    => 'Keywords',
304                            comments    => 'Comments',
305                            last_author => 'Username',
306                            created     => undef,
307                         );
308
309$caption    = " \tset_properties('+ Last author')";
310$target     = join " ",  qw(
311                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
312                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
313                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
314                            B4 00 00 00 07 00 00 00 01 00 00 00 40 00 00 00
315                            02 00 00 00 48 00 00 00 03 00 00 00 58 00 00 00
316                            04 00 00 00 68 00 00 00 05 00 00 00 78 00 00 00
317                            06 00 00 00 8C 00 00 00 08 00 00 00 A0 00 00 00
318                            02 00 00 00 E4 04 00 00 1E 00 00 00 06 00 00 00
319                            54 69 74 6C 65 00 00 00 1E 00 00 00 08 00 00 00
320                            53 75 62 6A 65 63 74 00 1E 00 00 00 07 00 00 00
321                            41 75 74 68 6F 72 00 00 1E 00 00 00 09 00 00 00
322                            4B 65 79 77 6F 72 64 73 00 00 00 00 1E 00 00 00
323                            09 00 00 00 43 6F 6D 6D 65 6E 74 73 00 00 00 00
324                            1E 00 00 00 09 00 00 00 55 73 65 72 6E 61 6D 65
325                            00 00 00 00
326                           );
327
328$result     = unpack_record( $workbook->{summary} );
329is($result, $target, $caption);
330
331
332###############################################################################
333#
334# Test 11. Same as previous + Creation date.
335#
336
337# Wed Aug 20 00:20:13 2008
338# $sec,$min,$hour,$mday,$mon,$year
339# We normalise the time using timegm() so that the tests don't fail due to
340# different timezones.
341$filetime   = [localtime(timegm(13, 20, 23, 19, 7, 108))];
342
343$workbook->set_properties(
344                            title       => 'Title',
345                            subject     => 'Subject',
346                            author      => 'Author',
347                            keywords    => 'Keywords',
348                            comments    => 'Comments',
349                            last_author => 'Username',
350                            created     => $filetime,
351                         );
352
353$caption    = " \tset_properties('+ Creation date')";
354$target     = join " ",  qw(
355                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
356                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
357                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
358                            C8 00 00 00 08 00 00 00 01 00 00 00 48 00 00 00
359                            02 00 00 00 50 00 00 00 03 00 00 00 60 00 00 00
360                            04 00 00 00 70 00 00 00 05 00 00 00 80 00 00 00
361                            06 00 00 00 94 00 00 00 08 00 00 00 A8 00 00 00
362                            0C 00 00 00 BC 00 00 00 02 00 00 00 E4 04 00 00
363                            1E 00 00 00 06 00 00 00 54 69 74 6C 65 00 00 00
364                            1E 00 00 00 08 00 00 00 53 75 62 6A 65 63 74 00
365                            1E 00 00 00 07 00 00 00 41 75 74 68 6F 72 00 00
366                            1E 00 00 00 09 00 00 00 4B 65 79 77 6F 72 64 73
367                            00 00 00 00 1E 00 00 00 09 00 00 00 43 6F 6D 6D
368                            65 6E 74 73 00 00 00 00 1E 00 00 00 09 00 00 00
369                            55 73 65 72 6E 61 6D 65 00 00 00 00 40 00 00 00
370                            80 74 89 21 52 02 C9 01
371                          );
372
373$result     = unpack_record( $workbook->{summary} );
374is($result, $target, $caption);
375
376
377###############################################################################
378#
379# Test 12. Same as previous. Date set at the workbook level.
380#
381
382# Wed Aug 20 00:20:13 2008
383# $sec,$min,$hour,$mday,$mon,$year
384# We normalise the time using timegm() so that the tests don't fail due to
385# different timezones.
386$workbook->{_localtime}  = [localtime(timegm(13, 20, 23, 19, 7, 108))];
387
388$workbook->set_properties(
389                            title       => 'Title',
390                            subject     => 'Subject',
391                            author      => 'Author',
392                            keywords    => 'Keywords',
393                            comments    => 'Comments',
394                            last_author => 'Username',
395                         );
396
397$caption    = " \tset_properties('+ Creation date')";
398$target     = join " ",  qw(
399                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
400                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
401                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
402                            C8 00 00 00 08 00 00 00 01 00 00 00 48 00 00 00
403                            02 00 00 00 50 00 00 00 03 00 00 00 60 00 00 00
404                            04 00 00 00 70 00 00 00 05 00 00 00 80 00 00 00
405                            06 00 00 00 94 00 00 00 08 00 00 00 A8 00 00 00
406                            0C 00 00 00 BC 00 00 00 02 00 00 00 E4 04 00 00
407                            1E 00 00 00 06 00 00 00 54 69 74 6C 65 00 00 00
408                            1E 00 00 00 08 00 00 00 53 75 62 6A 65 63 74 00
409                            1E 00 00 00 07 00 00 00 41 75 74 68 6F 72 00 00
410                            1E 00 00 00 09 00 00 00 4B 65 79 77 6F 72 64 73
411                            00 00 00 00 1E 00 00 00 09 00 00 00 43 6F 6D 6D
412                            65 6E 74 73 00 00 00 00 1E 00 00 00 09 00 00 00
413                            55 73 65 72 6E 61 6D 65 00 00 00 00 40 00 00 00
414                            80 74 89 21 52 02 C9 01
415                          );
416
417$result     = unpack_record( $workbook->{summary} );
418is($result, $target, $caption);
419
420
421###############################################################################
422#
423# Test 13. Same as 11  but params passed as a hashref.
424#
425
426# Wed Aug 20 00:20:13 2008
427# $sec,$min,$hour,$mday,$mon,$year
428# We normalise the time using timegm() so that the tests don't fail due to
429# different timezones.
430$filetime   = [localtime(timegm(13, 20, 23, 19, 7, 108))];
431
432$workbook->set_properties({
433                            title       => 'Title',
434                            subject     => 'Subject',
435                            author      => 'Author',
436                            keywords    => 'Keywords',
437                            comments    => 'Comments',
438                            last_author => 'Username',
439                            created     => $filetime,
440                         });
441
442$caption    = " \tset_properties({hash})";
443$target     = join " ",  qw(
444                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
445                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
446                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
447                            C8 00 00 00 08 00 00 00 01 00 00 00 48 00 00 00
448                            02 00 00 00 50 00 00 00 03 00 00 00 60 00 00 00
449                            04 00 00 00 70 00 00 00 05 00 00 00 80 00 00 00
450                            06 00 00 00 94 00 00 00 08 00 00 00 A8 00 00 00
451                            0C 00 00 00 BC 00 00 00 02 00 00 00 E4 04 00 00
452                            1E 00 00 00 06 00 00 00 54 69 74 6C 65 00 00 00
453                            1E 00 00 00 08 00 00 00 53 75 62 6A 65 63 74 00
454                            1E 00 00 00 07 00 00 00 41 75 74 68 6F 72 00 00
455                            1E 00 00 00 09 00 00 00 4B 65 79 77 6F 72 64 73
456                            00 00 00 00 1E 00 00 00 09 00 00 00 43 6F 6D 6D
457                            65 6E 74 73 00 00 00 00 1E 00 00 00 09 00 00 00
458                            55 73 65 72 6E 61 6D 65 00 00 00 00 40 00 00 00
459                            80 74 89 21 52 02 C9 01
460                          );
461
462$result     = unpack_record( $workbook->{summary} );
463is($result, $target, $caption);
464
465
466###############################################################################
467#
468# Test 14. UTF-8 string used.
469#
470SKIP: {
471
472skip " \tset_properties(utf8). Test requires Perl 5.8 Unicode support.", 1
473     if $] < 5.008;
474
475$workbook->set_properties(
476                            title       => 'Title' . $smiley,
477                            created     => undef,
478                         );
479
480$caption    = " \tset_properties(utf8)";
481$target     = join " ",  qw(
482                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
483                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
484                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
485                            34 00 00 00 02 00 00 00 01 00 00 00 18 00 00 00
486                            02 00 00 00 20 00 00 00 02 00 00 00 E9 FD 00 00
487                            1E 00 00 00 09 00 00 00 54 69 74 6C 65 E2 98 BA
488                            00 00 00 00
489                           );
490
491$result     = unpack_record( $workbook->{summary} );
492is($result, $target, $caption);
493}
494
495
496###############################################################################
497#
498# Test 15. Manual UTF-8 string used..
499#
500
501my $smiley_manual = pack 'H*', 'E298BA';
502
503$workbook->set_properties(
504                            title       => 'Title' . $smiley_manual,
505                            subject     => 'Subject',
506                            created     => undef,
507                            utf8        => 1,
508                         );
509
510$caption    = " \tset_properties(utf8)";
511$target     = join " ",  qw(
512                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
513                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
514                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
515                            4C 00 00 00 03 00 00 00 01 00 00 00 20 00 00 00
516                            02 00 00 00 28 00 00 00 03 00 00 00 3C 00 00 00
517                            02 00 00 00 E9 FD 00 00 1E 00 00 00 09 00 00 00
518                            54 69 74 6C 65 E2 98 BA 00 00 00 00 1E 00 00 00
519                            08 00 00 00 53 75 62 6A 65 63 74 00
520                           );
521
522$result     = unpack_record( $workbook->{summary} );
523is($result, $target, $caption);
524
525
526###############################################################################
527#
528# Test 16. UTF-8 string used.
529#
530SKIP: {
531
532skip " \tset_properties(utf8). Test requires Perl 5.8 Unicode support.", 1
533     if $] < 5.008;
534
535$workbook->set_properties(
536                            title       => 'Title' . $smiley,
537                            subject     => 'Subject',
538                            created     => undef,
539                         );
540
541$caption    = " \tset_properties(utf8)";
542$target     = join " ",  qw(
543                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
544                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
545                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
546                            4C 00 00 00 03 00 00 00 01 00 00 00 20 00 00 00
547                            02 00 00 00 28 00 00 00 03 00 00 00 3C 00 00 00
548                            02 00 00 00 E9 FD 00 00 1E 00 00 00 09 00 00 00
549                            54 69 74 6C 65 E2 98 BA 00 00 00 00 1E 00 00 00
550                            08 00 00 00 53 75 62 6A 65 63 74 00
551                           );
552
553$result     = unpack_record( $workbook->{summary} );
554is($result, $target, $caption);
555}
556
557
558###############################################################################
559#
560# Test 17. UTF-8 string used.
561#
562SKIP: {
563
564skip " \tset_properties(utf8). Test requires Perl 5.8 Unicode support.", 1
565     if $] < 5.008;
566
567$workbook->set_properties(
568                            title       => 'Title',
569                            subject     => 'Subject' . $smiley,
570                            created     => undef,
571                         );
572
573$caption    = " \tset_properties(utf8)";
574$target     = join " ",  qw(
575                            FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00
576                            00 00 00 00 00 00 00 00 01 00 00 00 E0 85 9F F2
577                            F9 4F 68 10 AB 91 08 00 2B 27 B3 D9 30 00 00 00
578                            4C 00 00 00 03 00 00 00 01 00 00 00 20 00 00 00
579                            02 00 00 00 28 00 00 00 03 00 00 00 38 00 00 00
580                            02 00 00 00 E9 FD 00 00 1E 00 00 00 06 00 00 00
581                            54 69 74 6C 65 00 00 00 1E 00 00 00 0B 00 00 00
582                            53 75 62 6A 65 63 74 E2 98 BA 00 00
583                           );
584
585$result     = unpack_record( $workbook->{summary} );
586is($result, $target, $caption);
587}
588
589
590###############################################################################
591#
592# Unpack the binary data into a format suitable for printing in tests.
593#
594sub unpack_record {
595    return join ' ', map {sprintf "%02X", $_} unpack "C*", $_[0];
596}
597
598
599# Cleanup
600$workbook->close();
601unlink $test_file;
602
603
604__END__
605