3
######################################################################
5
# Examples of formatting using the Spreadsheet::WriteExcel module
7
# reverse('�'), September 2002, John McNamara, jmcnamara@cpan.org
11
use Spreadsheet::WriteExcel;
13
my $workbook = Spreadsheet::WriteExcel->new('formats.xls');
16
my $center = $workbook->add_format(align => 'center');
17
my $heading = $workbook->add_format(align => 'center', bold => 1);
38
# Call these subroutines to demonstrate different formatting options
49
# Note: this is required
53
######################################################################
59
my $worksheet = $workbook->add_worksheet('Introduction');
61
$worksheet->set_column(0, 0, 60);
63
my $format = $workbook->add_format();
65
$format->set_size(14);
66
$format->set_color('blue');
67
$format->set_align('center');
69
my $format2 = $workbook->add_format();
71
$format2->set_color('blue');
73
$worksheet->write(2, 0, 'This workbook demonstrates some of', $format);
74
$worksheet->write(3, 0, 'the formatting options provided by', $format);
75
$worksheet->write(4, 0, 'the Spreadsheet::WriteExcel module.', $format);
77
$worksheet->write('A7', 'Sections:', $format2);
78
$worksheet->write('A8', "internal:Fonts!A1", 'Fonts' );
79
$worksheet->write('A9', "internal:'Named colors'!A1", 'Named colors' );
80
$worksheet->write('A10', "internal:'Standard colors'!A1", 'Standard colors');
81
$worksheet->write('A11', "internal:'Numeric formats'!A1", 'Numeric formats');
82
$worksheet->write('A12', "internal:Borders!A1", 'Borders' );
83
$worksheet->write('A13', "internal:Patterns!A1", 'Patterns' );
84
$worksheet->write('A14', "internal:Alignment!A1", 'Alignment' );
85
$worksheet->write('A15', "internal:Miscellaneous!A1", 'Miscellaneous' );
90
######################################################################
92
# Demonstrate the named colors.
96
my $worksheet = $workbook->add_worksheet('Named colors');
98
$worksheet->set_column(0, 3, 15);
100
$worksheet->write(0, 0, "Index", $heading);
101
$worksheet->write(0, 1, "Index", $heading);
102
$worksheet->write(0, 2, "Name", $heading);
103
$worksheet->write(0, 3, "Color", $heading);
107
while (my($index, $color) = each %colors) {
108
my $format = $workbook->add_format(
114
$worksheet->write($i+1, 0, $index, $center);
115
$worksheet->write($i+1, 1, sprintf("0x%02X", $index), $center);
116
$worksheet->write($i+1, 2, $color, $center);
117
$worksheet->write($i+1, 3, '', $format);
123
######################################################################
125
# Demonstrate the standard Excel colors in the range 8..63.
127
sub standard_colors {
129
my $worksheet = $workbook->add_worksheet('Standard colors');
131
$worksheet->set_column(0, 3, 15);
133
$worksheet->write(0, 0, "Index", $heading);
134
$worksheet->write(0, 1, "Index", $heading);
135
$worksheet->write(0, 2, "Color", $heading);
136
$worksheet->write(0, 3, "Name", $heading);
139
my $format = $workbook->add_format(
145
$worksheet->write(($i -7), 0, $i, $center);
146
$worksheet->write(($i -7), 1, sprintf("0x%02X", $i), $center);
147
$worksheet->write(($i -7), 2, '', $format);
149
# Add the color names
150
if (exists $colors{$i}) {
151
$worksheet->write(($i -7), 3, $colors{$i}, $center);
158
######################################################################
160
# Demonstrate the standard numeric formats.
162
sub numeric_formats {
164
my $worksheet = $workbook->add_worksheet('Numeric formats');
166
$worksheet->set_column(0, 4, 15);
167
$worksheet->set_column(5, 5, 45);
169
$worksheet->write(0, 0, "Index", $heading);
170
$worksheet->write(0, 1, "Index", $heading);
171
$worksheet->write(0, 2, "Unformatted", $heading);
172
$worksheet->write(0, 3, "Formatted", $heading);
173
$worksheet->write(0, 4, "Negative", $heading);
174
$worksheet->write(0, 5, "Format", $heading);
177
push @formats, [ 0x00, 1234.567, 0, 'General' ];
178
push @formats, [ 0x01, 1234.567, 0, '0' ];
179
push @formats, [ 0x02, 1234.567, 0, '0.00' ];
180
push @formats, [ 0x03, 1234.567, 0, '#,##0' ];
181
push @formats, [ 0x04, 1234.567, 0, '#,##0.00' ];
182
push @formats, [ 0x05, 1234.567, -1234.567, '($#,##0_);($#,##0)' ];
183
push @formats, [ 0x06, 1234.567, -1234.567, '($#,##0_);[Red]($#,##0)' ];
184
push @formats, [ 0x07, 1234.567, -1234.567, '($#,##0.00_);($#,##0.00)' ];
185
push @formats, [ 0x08, 1234.567, -1234.567, '($#,##0.00_);[Red]($#,##0.00)' ];
186
push @formats, [ 0x09, 0.567, 0, '0%' ];
187
push @formats, [ 0x0a, 0.567, 0, '0.00%' ];
188
push @formats, [ 0x0b, 1234.567, 0, '0.00E+00' ];
189
push @formats, [ 0x0c, 0.75, 0, '# ?/?' ];
190
push @formats, [ 0x0d, 0.3125, 0, '# ??/??' ];
191
push @formats, [ 0x0e, 36892.521, 0, 'm/d/yy' ];
192
push @formats, [ 0x0f, 36892.521, 0, 'd-mmm-yy' ];
193
push @formats, [ 0x10, 36892.521, 0, 'd-mmm' ];
194
push @formats, [ 0x11, 36892.521, 0, 'mmm-yy' ];
195
push @formats, [ 0x12, 36892.521, 0, 'h:mm AM/PM' ];
196
push @formats, [ 0x13, 36892.521, 0, 'h:mm:ss AM/PM' ];
197
push @formats, [ 0x14, 36892.521, 0, 'h:mm' ];
198
push @formats, [ 0x15, 36892.521, 0, 'h:mm:ss' ];
199
push @formats, [ 0x16, 36892.521, 0, 'm/d/yy h:mm' ];
200
push @formats, [ 0x25, 1234.567, -1234.567, '(#,##0_);(#,##0)' ];
201
push @formats, [ 0x26, 1234.567, -1234.567, '(#,##0_);[Red](#,##0)' ];
202
push @formats, [ 0x27, 1234.567, -1234.567, '(#,##0.00_);(#,##0.00)' ];
203
push @formats, [ 0x28, 1234.567, -1234.567, '(#,##0.00_);[Red](#,##0.00)' ];
204
push @formats, [ 0x29, 1234.567, -1234.567, '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)' ];
205
push @formats, [ 0x2a, 1234.567, -1234.567, '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)' ];
206
push @formats, [ 0x2b, 1234.567, -1234.567, '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)' ];
207
push @formats, [ 0x2c, 1234.567, -1234.567, '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)' ];
208
push @formats, [ 0x2d, 36892.521, 0, 'mm:ss' ];
209
push @formats, [ 0x2e, 3.0153, 0, '[h]:mm:ss' ];
210
push @formats, [ 0x2f, 36892.521, 0, 'mm:ss.0' ];
211
push @formats, [ 0x30, 1234.567, 0, '##0.0E+0' ];
212
push @formats, [ 0x31, 1234.567, 0, '@' ];
215
foreach my $format (@formats){
216
my $style = $workbook->add_format();
217
$style->set_num_format($$format[0]);
220
$worksheet->write($i, 0, $$format[0], $center);
221
$worksheet->write($i, 1, sprintf("0x%02X", $$format[0]), $center);
222
$worksheet->write($i, 2, $$format[1], $center);
223
$worksheet->write($i, 3, $$format[1], $style);
226
$worksheet->write($i, 4, $$format[2], $style);
229
$worksheet->write_string($i, 5, $$format[3]);
234
######################################################################
236
# Demonstrate the font options.
240
my $worksheet = $workbook->add_worksheet('Fonts');
242
$worksheet->set_column(0, 0, 30);
243
$worksheet->set_column(1, 1, 10);
245
$worksheet->write(0, 0, "Font name", $heading);
246
$worksheet->write(0, 1, "Font size", $heading);
249
push @fonts, [ 10, 'Arial' ];
250
push @fonts, [ 12, 'Arial' ];
251
push @fonts, [ 14, 'Arial' ];
252
push @fonts, [ 12, 'Arial Black' ];
253
push @fonts, [ 12, 'Arial Narrow' ];
254
push @fonts, [ 12, 'Century Schoolbook' ];
255
push @fonts, [ 12, 'Courier' ];
256
push @fonts, [ 12, 'Courier New' ];
257
push @fonts, [ 12, 'Garamond' ];
258
push @fonts, [ 12, 'Impact' ];
259
push @fonts, [ 12, 'Lucida Handwriting'] ;
260
push @fonts, [ 12, 'Times New Roman' ];
261
push @fonts, [ 12, 'Symbol' ];
262
push @fonts, [ 12, 'Wingdings' ];
263
push @fonts, [ 12, 'A font that doesnt exist' ];
266
foreach my $font (@fonts){
267
my $format = $workbook->add_format();
269
$format->set_size($$font[0]);
270
$format->set_font($$font[1]);
273
$worksheet->write($i, 0, $$font[1], $format);
274
$worksheet->write($i, 1, $$font[0], $format);
280
######################################################################
282
# Demonstrate the standard Excel border styles.
286
my $worksheet = $workbook->add_worksheet('Borders');
288
$worksheet->set_column(0, 4, 10);
289
$worksheet->set_column(5, 5, 40);
291
$worksheet->write(0, 0, "Index", $heading);
292
$worksheet->write(0, 1, "Index", $heading);
293
$worksheet->write(0, 3, "Style", $heading);
294
$worksheet->write(0, 5, "The style is highlighted in red for ", $heading);
295
$worksheet->write(1, 5, "emphasis, the default color is black.", $heading);
298
my $format = $workbook->add_format();
299
$format->set_border($i);
300
$format->set_border_color('red');
301
$format->set_align('center');
303
$worksheet->write((2*($i+1)), 0, $i, $center);
304
$worksheet->write((2*($i+1)), 1, sprintf("0x%02X", $i), $center);
306
$worksheet->write((2*($i+1)), 3, "Border", $format);
309
$worksheet->write(18, 0, "Diag type", $heading);
310
$worksheet->write(18, 1, "Index", $heading);
311
$worksheet->write(18, 3, "Style", $heading);
314
my $format = $workbook->add_format();
315
$format->set_diag_type($i);
316
$format->set_diag_border(1);
317
$format->set_diag_color('red');
318
$format->set_align('center');
320
$worksheet->write((2*($i+9)), 0, $i, $center);
321
$worksheet->write((2*($i+9)), 1, sprintf("0x%02X", 1), $center);
323
$worksheet->write((2*($i+9)), 3, "Border", $format);
329
######################################################################
331
# Demonstrate the standard Excel cell patterns.
335
my $worksheet = $workbook->add_worksheet('Patterns');
337
$worksheet->set_column(0, 4, 10);
338
$worksheet->set_column(5, 5, 50);
340
$worksheet->write(0, 0, "Index", $heading);
341
$worksheet->write(0, 1, "Index", $heading);
342
$worksheet->write(0, 3, "Pattern", $heading);
344
$worksheet->write(0, 5, "The background colour has been set to silver.", $heading);
345
$worksheet->write(1, 5, "The foreground colour has been set to green.", $heading);
348
my $format = $workbook->add_format();
350
$format->set_pattern($i);
351
$format->set_bg_color('silver');
352
$format->set_fg_color('green');
353
$format->set_align('center');
355
$worksheet->write((2*($i+1)), 0, $i, $center);
356
$worksheet->write((2*($i+1)), 1, sprintf("0x%02X", $i), $center);
358
$worksheet->write((2*($i+1)), 3, "Pattern", $format);
361
$worksheet->write((2*($i+1)), 5, "This is solid colour, the most useful pattern.", $heading);
367
######################################################################
369
# Demonstrate the standard Excel cell alignments.
373
my $worksheet = $workbook->add_worksheet('Alignment');
375
$worksheet->set_column(0, 7, 12);
376
$worksheet->set_row(0, 40);
377
$worksheet->set_selection(7, 0);
379
my $format01 = $workbook->add_format();
380
my $format02 = $workbook->add_format();
381
my $format03 = $workbook->add_format();
382
my $format04 = $workbook->add_format();
383
my $format05 = $workbook->add_format();
384
my $format06 = $workbook->add_format();
385
my $format07 = $workbook->add_format();
386
my $format08 = $workbook->add_format();
387
my $format09 = $workbook->add_format();
388
my $format10 = $workbook->add_format();
389
my $format11 = $workbook->add_format();
390
my $format12 = $workbook->add_format();
391
my $format13 = $workbook->add_format();
392
my $format14 = $workbook->add_format();
393
my $format15 = $workbook->add_format();
394
my $format16 = $workbook->add_format();
395
my $format17 = $workbook->add_format();
397
$format02->set_align('top');
398
$format03->set_align('bottom');
399
$format04->set_align('vcenter');
400
$format05->set_align('vjustify');
401
$format06->set_text_wrap();
403
$format07->set_align('left');
404
$format08->set_align('right');
405
$format09->set_align('center');
406
$format10->set_align('fill');
407
$format11->set_align('justify');
408
$format12->set_merge();
410
$format13->set_rotation(45);
411
$format14->set_rotation(-45);
412
$format15->set_rotation(270);
414
$format16->set_shrink();
415
$format17->set_indent(1);
417
$worksheet->write(0, 0, 'Vertical', $heading);
418
$worksheet->write(0, 1, 'top', $format02);
419
$worksheet->write(0, 2, 'bottom', $format03);
420
$worksheet->write(0, 3, 'vcenter', $format04);
421
$worksheet->write(0, 4, 'vjustify', $format05);
422
$worksheet->write(0, 5, "text\nwrap", $format06);
424
$worksheet->write(2, 0, 'Horizontal', $heading);
425
$worksheet->write(2, 1, 'left', $format07);
426
$worksheet->write(2, 2, 'right', $format08);
427
$worksheet->write(2, 3, 'center', $format09);
428
$worksheet->write(2, 4, 'fill', $format10);
429
$worksheet->write(2, 5, 'justify', $format11);
431
$worksheet->write(3, 1, 'merge', $format12);
432
$worksheet->write(3, 2, '', $format12);
434
$worksheet->write(2, 3, 'Shrink ' x 3, $format16);
435
$worksheet->write(2, 4, 'Indent', $format17);
438
$worksheet->write(5, 0, 'Rotation', $heading);
439
$worksheet->write(5, 1, 'Rotate 45', $format13);
440
$worksheet->write(6, 1, 'Rotate -45', $format14);
441
$worksheet->write(7, 1, 'Rotate 270', $format15);
445
######################################################################
447
# Demonstrate other miscellaneous features.
451
my $worksheet = $workbook->add_worksheet('Miscellaneous');
453
$worksheet->set_column(2, 2, 25);
455
my $format01 = $workbook->add_format();
456
my $format02 = $workbook->add_format();
457
my $format03 = $workbook->add_format();
458
my $format04 = $workbook->add_format();
459
my $format05 = $workbook->add_format();
460
my $format06 = $workbook->add_format();
461
my $format07 = $workbook->add_format();
463
$format01->set_underline(0x01);
464
$format02->set_underline(0x02);
465
$format03->set_underline(0x21);
466
$format04->set_underline(0x22);
467
$format05->set_font_strikeout();
468
$format06->set_font_outline();
469
$format07->set_font_shadow();
471
$worksheet->write(1, 2, 'Underline 0x01', $format01);
472
$worksheet->write(3, 2, 'Underline 0x02', $format02);
473
$worksheet->write(5, 2, 'Underline 0x21', $format03);
474
$worksheet->write(7, 2, 'Underline 0x22', $format04);
475
$worksheet->write(9, 2, 'Strikeout', $format05);
476
$worksheet->write(11, 2, 'Outline (Macintosh only)', $format06);
477
$worksheet->write(13, 2, 'Shadow (Macintosh only)', $format07);