1#!/usr/bin/perl -w
2
3###############################################################################
4#
5# Examples of formatting using the Excel::Writer::XLSX module.
6#
7# This program demonstrates almost all possible formatting options. It is worth
8# running this program and viewing the output Excel file if you are interested
9# in the various formatting possibilities.
10#
11# Copyright 2000-2021, John McNamara, jmcnamara@cpan.org
12#
13
14use strict;
15use Excel::Writer::XLSX;
16
17my $workbook = Excel::Writer::XLSX->new( 'formats.xlsx' );
18
19# Some common formats
20my $center = $workbook->add_format( align => 'center' );
21my $heading = $workbook->add_format( align => 'center', bold => 1 );
22
23# The named colors
24my %colors = (
25    0x08, 'black',
26    0x0C, 'blue',
27    0x10, 'brown',
28    0x0F, 'cyan',
29    0x17, 'gray',
30    0x11, 'green',
31    0x0B, 'lime',
32    0x0E, 'magenta',
33    0x12, 'navy',
34    0x35, 'orange',
35    0x21, 'pink',
36    0x14, 'purple',
37    0x0A, 'red',
38    0x16, 'silver',
39    0x09, 'white',
40    0x0D, 'yellow',
41
42);
43
44# Call these subroutines to demonstrate different formatting options
45intro();
46fonts();
47named_colors();
48standard_colors();
49numeric_formats();
50borders();
51patterns();
52alignment();
53misc();
54
55# Note: this is required
56$workbook->close();
57
58
59######################################################################
60#
61# Intro.
62#
63sub intro {
64
65    my $worksheet = $workbook->add_worksheet( 'Introduction' );
66
67    $worksheet->set_column( 0, 0, 60 );
68
69    my $format = $workbook->add_format();
70    $format->set_bold();
71    $format->set_size( 14 );
72    $format->set_color( 'blue' );
73    $format->set_align( 'center' );
74
75    my $format2 = $workbook->add_format();
76    $format2->set_bold();
77    $format2->set_color( 'blue' );
78
79    my $format3 = $workbook->add_format(
80        color     => 'blue',
81        underline => 1,
82    );
83
84    $worksheet->write( 2, 0, 'This workbook demonstrates some of', $format );
85    $worksheet->write( 3, 0, 'the formatting options provided by', $format );
86    $worksheet->write( 4, 0, 'the Excel::Writer::XLSX module.',    $format );
87    $worksheet->write( 'A7', 'Sections:', $format2 );
88
89    $worksheet->write( 'A8', "internal:Fonts!A1", 'Fonts', $format3 );
90
91    $worksheet->write( 'A9', "internal:'Named colors'!A1",
92        'Named colors', $format3 );
93
94    $worksheet->write(
95        'A10',
96        "internal:'Standard colors'!A1",
97        'Standard colors', $format3
98    );
99
100    $worksheet->write(
101        'A11',
102        "internal:'Numeric formats'!A1",
103        'Numeric formats', $format3
104    );
105
106    $worksheet->write( 'A12', "internal:Borders!A1", 'Borders', $format3 );
107    $worksheet->write( 'A13', "internal:Patterns!A1", 'Patterns', $format3 );
108    $worksheet->write( 'A14', "internal:Alignment!A1", 'Alignment', $format3 );
109    $worksheet->write( 'A15', "internal:Miscellaneous!A1", 'Miscellaneous',
110        $format3 );
111
112}
113
114
115######################################################################
116#
117# Demonstrate the named colors.
118#
119sub named_colors {
120
121    my $worksheet = $workbook->add_worksheet( 'Named colors' );
122
123    $worksheet->set_column( 0, 3, 15 );
124
125    $worksheet->write( 0, 0, "Index", $heading );
126    $worksheet->write( 0, 1, "Index", $heading );
127    $worksheet->write( 0, 2, "Name",  $heading );
128    $worksheet->write( 0, 3, "Color", $heading );
129
130    my $i = 1;
131
132    while ( my ( $index, $color ) = each %colors ) {
133        my $format = $workbook->add_format(
134            bg_color => $color,
135            pattern  => 1,
136            border   => 1
137        );
138
139        $worksheet->write( $i + 1, 0, $index, $center );
140        $worksheet->write( $i + 1, 1, sprintf( "0x%02X", $index ), $center );
141        $worksheet->write( $i + 1, 2, $color, $center );
142        $worksheet->write( $i + 1, 3, '',     $format );
143        $i++;
144    }
145}
146
147
148######################################################################
149#
150# Demonstrate the standard Excel colors in the range 8..63.
151#
152sub standard_colors {
153
154    my $worksheet = $workbook->add_worksheet( 'Standard colors' );
155
156    $worksheet->set_column( 0, 3, 15 );
157
158    $worksheet->write( 0, 0, "Index", $heading );
159    $worksheet->write( 0, 1, "Index", $heading );
160    $worksheet->write( 0, 2, "Color", $heading );
161    $worksheet->write( 0, 3, "Name",  $heading );
162
163    for my $i ( 8 .. 63 ) {
164        my $format = $workbook->add_format(
165            bg_color => $i,
166            pattern  => 1,
167            border   => 1
168        );
169
170        $worksheet->write( ( $i - 7 ), 0, $i, $center );
171        $worksheet->write( ( $i - 7 ), 1, sprintf( "0x%02X", $i ), $center );
172        $worksheet->write( ( $i - 7 ), 2, '', $format );
173
174        # Add the  color names
175        if ( exists $colors{$i} ) {
176            $worksheet->write( ( $i - 7 ), 3, $colors{$i}, $center );
177
178        }
179    }
180}
181
182
183######################################################################
184#
185# Demonstrate the standard numeric formats.
186#
187sub numeric_formats {
188
189    my $worksheet = $workbook->add_worksheet( 'Numeric formats' );
190
191    $worksheet->set_column( 0, 4, 15 );
192    $worksheet->set_column( 5, 5, 45 );
193
194    $worksheet->write( 0, 0, "Index",       $heading );
195    $worksheet->write( 0, 1, "Index",       $heading );
196    $worksheet->write( 0, 2, "Unformatted", $heading );
197    $worksheet->write( 0, 3, "Formatted",   $heading );
198    $worksheet->write( 0, 4, "Negative",    $heading );
199    $worksheet->write( 0, 5, "Format",      $heading );
200
201    #<<<
202    my @formats;
203    push @formats, [ 0x00, 1234.567,   0,         'General' ];
204    push @formats, [ 0x01, 1234.567,   0,         '0' ];
205    push @formats, [ 0x02, 1234.567,   0,         '0.00' ];
206    push @formats, [ 0x03, 1234.567,   0,         '#,##0' ];
207    push @formats, [ 0x04, 1234.567,   0,         '#,##0.00' ];
208    push @formats, [ 0x05, 1234.567,   -1234.567, '($#,##0_);($#,##0)' ];
209    push @formats, [ 0x06, 1234.567,   -1234.567, '($#,##0_);[Red]($#,##0)' ];
210    push @formats, [ 0x07, 1234.567,   -1234.567, '($#,##0.00_);($#,##0.00)' ];
211    push @formats, [ 0x08, 1234.567,   -1234.567, '($#,##0.00_);[Red]($#,##0.00)' ];
212    push @formats, [ 0x09, 0.567,      0,         '0%' ];
213    push @formats, [ 0x0a, 0.567,      0,         '0.00%' ];
214    push @formats, [ 0x0b, 1234.567,   0,         '0.00E+00' ];
215    push @formats, [ 0x0c, 0.75,       0,         '# ?/?' ];
216    push @formats, [ 0x0d, 0.3125,     0,         '# ??/??' ];
217    push @formats, [ 0x0e, 36892.521,  0,         'm/d/yy' ];
218    push @formats, [ 0x0f, 36892.521,  0,         'd-mmm-yy' ];
219    push @formats, [ 0x10, 36892.521,  0,         'd-mmm' ];
220    push @formats, [ 0x11, 36892.521,  0,         'mmm-yy' ];
221    push @formats, [ 0x12, 36892.521,  0,         'h:mm AM/PM' ];
222    push @formats, [ 0x13, 36892.521,  0,         'h:mm:ss AM/PM' ];
223    push @formats, [ 0x14, 36892.521,  0,         'h:mm' ];
224    push @formats, [ 0x15, 36892.521,  0,         'h:mm:ss' ];
225    push @formats, [ 0x16, 36892.521,  0,         'm/d/yy h:mm' ];
226    push @formats, [ 0x25, 1234.567,   -1234.567, '(#,##0_);(#,##0)' ];
227    push @formats, [ 0x26, 1234.567,   -1234.567, '(#,##0_);[Red](#,##0)' ];
228    push @formats, [ 0x27, 1234.567,   -1234.567, '(#,##0.00_);(#,##0.00)' ];
229    push @formats, [ 0x28, 1234.567,   -1234.567, '(#,##0.00_);[Red](#,##0.00)' ];
230    push @formats, [ 0x29, 1234.567,   -1234.567, '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)' ];
231    push @formats, [ 0x2a, 1234.567,   -1234.567, '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)' ];
232    push @formats, [ 0x2b, 1234.567,   -1234.567, '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)' ];
233    push @formats, [ 0x2c, 1234.567,   -1234.567, '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)' ];
234    push @formats, [ 0x2d, 36892.521,  0,         'mm:ss' ];
235    push @formats, [ 0x2e, 3.0153,     0,         '[h]:mm:ss' ];
236    push @formats, [ 0x2f, 36892.521,  0,         'mm:ss.0' ];
237    push @formats, [ 0x30, 1234.567,   0,         '##0.0E+0' ];
238    push @formats, [ 0x31, 1234.567,   0,         '@' ];
239    #>>>
240
241    my $i;
242    foreach my $format ( @formats ) {
243        my $style = $workbook->add_format();
244        $style->set_num_format( $format->[0] );
245
246        $i++;
247        $worksheet->write( $i, 0, $format->[0], $center );
248        $worksheet->write( $i, 1, sprintf( "0x%02X", $format->[0] ), $center );
249        $worksheet->write( $i, 2, $format->[1], $center );
250        $worksheet->write( $i, 3, $format->[1], $style );
251
252        if ( $format->[2] ) {
253            $worksheet->write( $i, 4, $format->[2], $style );
254        }
255
256        $worksheet->write_string( $i, 5, $format->[3] );
257    }
258}
259
260
261######################################################################
262#
263# Demonstrate the font options.
264#
265sub fonts {
266
267    my $worksheet = $workbook->add_worksheet( 'Fonts' );
268
269    $worksheet->set_column( 0, 0, 30 );
270    $worksheet->set_column( 1, 1, 10 );
271
272    $worksheet->write( 0, 0, "Font name", $heading );
273    $worksheet->write( 0, 1, "Font size", $heading );
274
275    my @fonts;
276    push @fonts, [ 10, 'Arial' ];
277    push @fonts, [ 12, 'Arial' ];
278    push @fonts, [ 14, 'Arial' ];
279    push @fonts, [ 12, 'Arial Black' ];
280    push @fonts, [ 12, 'Arial Narrow' ];
281    push @fonts, [ 12, 'Century Schoolbook' ];
282    push @fonts, [ 12, 'Courier' ];
283    push @fonts, [ 12, 'Courier New' ];
284    push @fonts, [ 12, 'Garamond' ];
285    push @fonts, [ 12, 'Impact' ];
286    push @fonts, [ 12, 'Lucida Handwriting' ];
287    push @fonts, [ 12, 'Times New Roman' ];
288    push @fonts, [ 12, 'Symbol' ];
289    push @fonts, [ 12, 'Wingdings' ];
290    push @fonts, [ 12, 'A font that doesn\'t exist' ];
291
292    my $i;
293    foreach my $font ( @fonts ) {
294        my $format = $workbook->add_format();
295
296        $format->set_size( $font->[0] );
297        $format->set_font( $font->[1] );
298
299        $i++;
300        $worksheet->write( $i, 0, $font->[1], $format );
301        $worksheet->write( $i, 1, $font->[0], $format );
302    }
303
304}
305
306
307######################################################################
308#
309# Demonstrate the standard Excel border styles.
310#
311sub borders {
312
313    my $worksheet = $workbook->add_worksheet( 'Borders' );
314
315    $worksheet->set_column( 0, 4, 10 );
316    $worksheet->set_column( 5, 5, 40 );
317
318    $worksheet->write( 0, 0, "Index",                                $heading );
319    $worksheet->write( 0, 1, "Index",                                $heading );
320    $worksheet->write( 0, 3, "Style",                                $heading );
321    $worksheet->write( 0, 5, "The style is highlighted in red for ", $heading );
322    $worksheet->write( 1, 5, "emphasis, the default color is black.",
323        $heading );
324
325    for my $i ( 0 .. 13 ) {
326        my $format = $workbook->add_format();
327        $format->set_border( $i );
328        $format->set_border_color( 'red' );
329        $format->set_align( 'center' );
330
331        $worksheet->write( ( 2 * ( $i + 1 ) ), 0, $i, $center );
332        $worksheet->write( ( 2 * ( $i + 1 ) ),
333            1, sprintf( "0x%02X", $i ), $center );
334
335        $worksheet->write( ( 2 * ( $i + 1 ) ), 3, "Border", $format );
336    }
337
338    $worksheet->write( 30, 0, "Diag type",              $heading );
339    $worksheet->write( 30, 1, "Index",                  $heading );
340    $worksheet->write( 30, 3, "Style",                  $heading );
341    $worksheet->write( 30, 5, "Diagonal Border styles", $heading );
342
343    for my $i ( 1 .. 3 ) {
344        my $format = $workbook->add_format();
345        $format->set_diag_type( $i );
346        $format->set_diag_border( 1 );
347        $format->set_diag_color( 'red' );
348        $format->set_align( 'center' );
349
350        $worksheet->write( ( 2 * ( $i + 15 ) ), 0, $i, $center );
351        $worksheet->write( ( 2 * ( $i + 15 ) ),
352            1, sprintf( "0x%02X", $i ), $center );
353
354        $worksheet->write( ( 2 * ( $i + 15 ) ), 3, "Border", $format );
355    }
356}
357
358
359######################################################################
360#
361# Demonstrate the standard Excel cell patterns.
362#
363sub patterns {
364
365    my $worksheet = $workbook->add_worksheet( 'Patterns' );
366
367    $worksheet->set_column( 0, 4, 10 );
368    $worksheet->set_column( 5, 5, 50 );
369
370    $worksheet->write( 0, 0, "Index",   $heading );
371    $worksheet->write( 0, 1, "Index",   $heading );
372    $worksheet->write( 0, 3, "Pattern", $heading );
373
374    $worksheet->write( 0, 5, "The background colour has been set to silver.",
375        $heading );
376    $worksheet->write( 1, 5, "The foreground colour has been set to green.",
377        $heading );
378
379    for my $i ( 0 .. 18 ) {
380        my $format = $workbook->add_format();
381
382        $format->set_pattern( $i );
383        $format->set_bg_color( 'silver' );
384        $format->set_fg_color( 'green' );
385        $format->set_align( 'center' );
386
387        $worksheet->write( ( 2 * ( $i + 1 ) ), 0, $i, $center );
388        $worksheet->write( ( 2 * ( $i + 1 ) ),
389            1, sprintf( "0x%02X", $i ), $center );
390
391        $worksheet->write( ( 2 * ( $i + 1 ) ), 3, "Pattern", $format );
392
393        if ( $i == 1 ) {
394            $worksheet->write( ( 2 * ( $i + 1 ) ),
395                5, "This is solid colour, the most useful pattern.", $heading );
396        }
397    }
398}
399
400
401######################################################################
402#
403# Demonstrate the standard Excel cell alignments.
404#
405sub alignment {
406
407    my $worksheet = $workbook->add_worksheet( 'Alignment' );
408
409    $worksheet->set_column( 0, 7, 12 );
410    $worksheet->set_row( 0, 40 );
411    $worksheet->set_selection( 7, 0 );
412
413    my $format01 = $workbook->add_format();
414    my $format02 = $workbook->add_format();
415    my $format03 = $workbook->add_format();
416    my $format04 = $workbook->add_format();
417    my $format05 = $workbook->add_format();
418    my $format06 = $workbook->add_format();
419    my $format07 = $workbook->add_format();
420    my $format08 = $workbook->add_format();
421    my $format09 = $workbook->add_format();
422    my $format10 = $workbook->add_format();
423    my $format11 = $workbook->add_format();
424    my $format12 = $workbook->add_format();
425    my $format13 = $workbook->add_format();
426    my $format14 = $workbook->add_format();
427    my $format15 = $workbook->add_format();
428    my $format16 = $workbook->add_format();
429    my $format17 = $workbook->add_format();
430
431    $format02->set_align( 'top' );
432    $format03->set_align( 'bottom' );
433    $format04->set_align( 'vcenter' );
434    $format05->set_align( 'vjustify' );
435    $format06->set_text_wrap();
436
437    $format07->set_align( 'left' );
438    $format08->set_align( 'right' );
439    $format09->set_align( 'center' );
440    $format10->set_align( 'fill' );
441    $format11->set_align( 'justify' );
442    $format12->set_merge();
443
444    $format13->set_rotation( 45 );
445    $format14->set_rotation( -45 );
446    $format15->set_rotation( 270 );
447
448    $format16->set_shrink();
449    $format17->set_indent( 1 );
450
451    $worksheet->write( 0, 0, 'Vertical',   $heading );
452    $worksheet->write( 0, 1, 'top',        $format02 );
453    $worksheet->write( 0, 2, 'bottom',     $format03 );
454    $worksheet->write( 0, 3, 'vcenter',    $format04 );
455    $worksheet->write( 0, 4, 'vjustify',   $format05 );
456    $worksheet->write( 0, 5, "text\nwrap", $format06 );
457
458    $worksheet->write( 2, 0, 'Horizontal', $heading );
459    $worksheet->write( 2, 1, 'left',       $format07 );
460    $worksheet->write( 2, 2, 'right',      $format08 );
461    $worksheet->write( 2, 3, 'center',     $format09 );
462    $worksheet->write( 2, 4, 'fill',       $format10 );
463    $worksheet->write( 2, 5, 'justify',    $format11 );
464
465    $worksheet->write( 3, 1, 'merge', $format12 );
466    $worksheet->write( 3, 2, '',      $format12 );
467
468    $worksheet->write( 3, 3, 'Shrink ' x 3, $format16 );
469    $worksheet->write( 3, 4, 'Indent',      $format17 );
470
471
472    $worksheet->write( 5, 0, 'Rotation',   $heading );
473    $worksheet->write( 5, 1, 'Rotate 45',  $format13 );
474    $worksheet->write( 6, 1, 'Rotate -45', $format14 );
475    $worksheet->write( 7, 1, 'Rotate 270', $format15 );
476}
477
478
479######################################################################
480#
481# Demonstrate other miscellaneous features.
482#
483sub misc {
484
485    my $worksheet = $workbook->add_worksheet( 'Miscellaneous' );
486
487    $worksheet->set_column( 2, 2, 25 );
488
489    my $format01 = $workbook->add_format();
490    my $format02 = $workbook->add_format();
491    my $format03 = $workbook->add_format();
492    my $format04 = $workbook->add_format();
493    my $format05 = $workbook->add_format();
494    my $format06 = $workbook->add_format();
495    my $format07 = $workbook->add_format();
496
497    $format01->set_underline( 0x01 );
498    $format02->set_underline( 0x02 );
499    $format03->set_underline( 0x21 );
500    $format04->set_underline( 0x22 );
501    $format05->set_font_strikeout();
502    $format06->set_font_outline();
503    $format07->set_font_shadow();
504
505    $worksheet->write( 1,  2, 'Underline  0x01',          $format01 );
506    $worksheet->write( 3,  2, 'Underline  0x02',          $format02 );
507    $worksheet->write( 5,  2, 'Underline  0x21',          $format03 );
508    $worksheet->write( 7,  2, 'Underline  0x22',          $format04 );
509    $worksheet->write( 9,  2, 'Strikeout',                $format05 );
510    $worksheet->write( 11, 2, 'Outline (Macintosh only)', $format06 );
511    $worksheet->write( 13, 2, 'Shadow (Macintosh only)',  $format07 );
512}
513
514
515$workbook->close();
516
517__END__
518