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