examples/formats.pl - metacpan.org

#!/usr/bin/perl -w

###############################################################################
#
# Examples of formatting using the Spreadsheet::WriteExcel module.
#
# This program demonstrates almost all possible formatting options. It is worth
# running this program and viewing the output Excel file if you are interested
# in the various formatting possibilities.
#
# reverse('©'), September 2002, John McNamara, jmcnamara@cpan.org
#

use strict;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('formats.xls');

# Some common formats
my $center  = $workbook->add_format(align => 'center');
my $heading = $workbook->add_format(align => 'center', bold => 1);

# The named colors
my %colors = (
                0x08, 'black',
                0x0C, 'blue',
                0x10, 'brown',
                0x0F, 'cyan',
                0x17, 'gray',
                0x11, 'green',
                0x0B, 'lime',
                0x0E, 'magenta',
                0x12, 'navy',
                0x35, 'orange',
                0x21, 'pink',
                0x14, 'purple',
                0x0A, 'red',
                0x16, 'silver',
                0x09, 'white',
                0x0D, 'yellow',
             );

# Call these subroutines to demonstrate different formatting options
intro();
fonts();
named_colors();
standard_colors();
numeric_formats();
borders();
patterns();
alignment();
misc();

# Note: this is required
$workbook->close();


######################################################################
#
# Intro.
#
sub intro {

    my $worksheet = $workbook->add_worksheet('Introduction');

    $worksheet->set_column(0, 0, 60);

    my $format = $workbook->add_format();
    $format->set_bold();
    $format->set_size(14);
    $format->set_color('blue');
    $format->set_align('center');

    my $format2 = $workbook->add_format();
    $format2->set_bold();
    $format2->set_color('blue');

    $worksheet->write(2, 0, 'This workbook demonstrates some of',  $format);
    $worksheet->write(3, 0, 'the formatting options provided by',  $format);
    $worksheet->write(4, 0, 'the Spreadsheet::WriteExcel module.', $format);

    $worksheet->write('A7',  'Sections:', $format2);
    $worksheet->write('A8',  "internal:Fonts!A1",             'Fonts'          );
    $worksheet->write('A9',  "internal:'Named colors'!A1",    'Named colors'   );
    $worksheet->write('A10', "internal:'Standard colors'!A1", 'Standard colors');
    $worksheet->write('A11', "internal:'Numeric formats'!A1", 'Numeric formats');
    $worksheet->write('A12', "internal:Borders!A1",           'Borders'        );
    $worksheet->write('A13', "internal:Patterns!A1",          'Patterns'       );
    $worksheet->write('A14', "internal:Alignment!A1",         'Alignment'      );
    $worksheet->write('A15', "internal:Miscellaneous!A1",     'Miscellaneous'  );

}


######################################################################
#
# Demonstrate the named colors.
#
sub named_colors {

    my $worksheet = $workbook->add_worksheet('Named colors');

    $worksheet->set_column(0, 3, 15);

    $worksheet->write(0, 0, "Index", $heading);
    $worksheet->write(0, 1, "Index", $heading);
    $worksheet->write(0, 2, "Name",  $heading);
    $worksheet->write(0, 3, "Color", $heading);

    my $i = 1;

    while (my($index, $color) = each %colors) {
        my $format = $workbook->add_format(
                                            bg_color => $color,
                                            pattern  => 1,
                                            border   => 1
                                         );

        $worksheet->write($i+1, 0, $index,                    $center);
        $worksheet->write($i+1, 1, sprintf("0x%02X", $index), $center);
        $worksheet->write($i+1, 2, $color,                    $center);
        $worksheet->write($i+1, 3, '',                        $format);
        $i++;
    }
}


######################################################################
#
# Demonstrate the standard Excel colors in the range 8..63.
#
sub standard_colors {

    my $worksheet = $workbook->add_worksheet('Standard colors');

    $worksheet->set_column(0, 3, 15);

    $worksheet->write(0, 0, "Index", $heading);
    $worksheet->write(0, 1, "Index", $heading);
    $worksheet->write(0, 2, "Color", $heading);
    $worksheet->write(0, 3, "Name",  $heading);

    for my $i (8..63) {
        my $format = $workbook->add_format(
                                            bg_color => $i,
                                            pattern  => 1,
                                            border   => 1
                                         );

        $worksheet->write(($i -7), 0, $i,                    $center);
        $worksheet->write(($i -7), 1, sprintf("0x%02X", $i), $center);
        $worksheet->write(($i -7), 2, '',                    $format);

        # Add the  color names
        if (exists $colors{$i}) {
            $worksheet->write(($i -7), 3, $colors{$i}, $center);

        }
    }
}


######################################################################
#
# Demonstrate the standard numeric formats.
#
sub numeric_formats {

    my $worksheet = $workbook->add_worksheet('Numeric formats');

    $worksheet->set_column(0, 4, 15);
    $worksheet->set_column(5, 5, 45);

    $worksheet->write(0, 0, "Index",       $heading);
    $worksheet->write(0, 1, "Index",       $heading);
    $worksheet->write(0, 2, "Unformatted", $heading);
    $worksheet->write(0, 3, "Formatted",   $heading);
    $worksheet->write(0, 4, "Negative",    $heading);
    $worksheet->write(0, 5, "Format",      $heading);

    my @formats;
    push @formats, [ 0x00, 1234.567,   0,         'General' ];
    push @formats, [ 0x01, 1234.567,   0,         '0' ];
    push @formats, [ 0x02, 1234.567,   0,         '0.00' ];
    push @formats, [ 0x03, 1234.567,   0,         '#,##0' ];
    push @formats, [ 0x04, 1234.567,   0,         '#,##0.00' ];
    push @formats, [ 0x05, 1234.567,   -1234.567, '($#,##0_);($#,##0)' ];
    push @formats, [ 0x06, 1234.567,   -1234.567, '($#,##0_);[Red]($#,##0)' ];
    push @formats, [ 0x07, 1234.567,   -1234.567, '($#,##0.00_);($#,##0.00)' ];
    push @formats, [ 0x08, 1234.567,   -1234.567, '($#,##0.00_);[Red]($#,##0.00)' ];
    push @formats, [ 0x09, 0.567,      0,         '0%' ];
    push @formats, [ 0x0a, 0.567,      0,         '0.00%' ];
    push @formats, [ 0x0b, 1234.567,   0,         '0.00E+00' ];
    push @formats, [ 0x0c, 0.75,       0,         '# ?/?' ];
    push @formats, [ 0x0d, 0.3125,     0,         '# ??/??' ];
    push @formats, [ 0x0e, 36892.521,  0,         'm/d/yy' ];
    push @formats, [ 0x0f, 36892.521,  0,         'd-mmm-yy' ];
    push @formats, [ 0x10, 36892.521,  0,         'd-mmm' ];
    push @formats, [ 0x11, 36892.521,  0,         'mmm-yy' ];
    push @formats, [ 0x12, 36892.521,  0,         'h:mm AM/PM' ];
    push @formats, [ 0x13, 36892.521,  0,         'h:mm:ss AM/PM' ];
    push @formats, [ 0x14, 36892.521,  0,         'h:mm' ];
    push @formats, [ 0x15, 36892.521,  0,         'h:mm:ss' ];
    push @formats, [ 0x16, 36892.521,  0,         'm/d/yy h:mm' ];
    push @formats, [ 0x25, 1234.567,   -1234.567, '(#,##0_);(#,##0)' ];
    push @formats, [ 0x26, 1234.567,   -1234.567, '(#,##0_);[Red](#,##0)' ];
    push @formats, [ 0x27, 1234.567,   -1234.567, '(#,##0.00_);(#,##0.00)' ];
    push @formats, [ 0x28, 1234.567,   -1234.567, '(#,##0.00_);[Red](#,##0.00)' ];
    push @formats, [ 0x29, 1234.567,   -1234.567, '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)' ];
    push @formats, [ 0x2a, 1234.567,   -1234.567, '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)' ];
    push @formats, [ 0x2b, 1234.567,   -1234.567, '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)' ];
    push @formats, [ 0x2c, 1234.567,   -1234.567, '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)' ];
    push @formats, [ 0x2d, 36892.521,  0,         'mm:ss' ];
    push @formats, [ 0x2e, 3.0153,     0,         '[h]:mm:ss' ];
    push @formats, [ 0x2f, 36892.521,  0,         'mm:ss.0' ];
    push @formats, [ 0x30, 1234.567,   0,         '##0.0E+0' ];
    push @formats, [ 0x31, 1234.567,   0,         '@' ];

    my $i;
    foreach my $format (@formats){
        my $style = $workbook->add_format();
        $style->set_num_format($format->[0]);

        $i++;
        $worksheet->write($i, 0, $format->[0],                    $center);
        $worksheet->write($i, 1, sprintf("0x%02X", $format->[0]), $center);
        $worksheet->write($i, 2, $format->[1],                    $center);
        $worksheet->write($i, 3, $format->[1],                    $style);

        if ($format->[2]) {
            $worksheet->write($i, 4, $format->[2], $style);
        }

        $worksheet->write_string($i, 5, $format->[3]);
    }
}


######################################################################
#
# Demonstrate the font options.
#
sub fonts {

    my $worksheet = $workbook->add_worksheet('Fonts');

    $worksheet->set_column(0, 0, 30);
    $worksheet->set_column(1, 1, 10);

    $worksheet->write(0, 0, "Font name",   $heading);
    $worksheet->write(0, 1, "Font size",   $heading);

    my @fonts;
    push @fonts, [ 10, 'Arial' ];
    push @fonts, [ 12, 'Arial' ];
    push @fonts, [ 14, 'Arial' ];
    push @fonts, [ 12, 'Arial Black' ];
    push @fonts, [ 12, 'Arial Narrow' ];
    push @fonts, [ 12, 'Century Schoolbook' ];
    push @fonts, [ 12, 'Courier' ];
    push @fonts, [ 12, 'Courier New' ];
    push @fonts, [ 12, 'Garamond' ];
    push @fonts, [ 12, 'Impact' ];
    push @fonts, [ 12, 'Lucida Handwriting'] ;
    push @fonts, [ 12, 'Times New Roman' ];
    push @fonts, [ 12, 'Symbol' ];
    push @fonts, [ 12, 'Wingdings' ];
    push @fonts, [ 12, 'A font that doesn\'t exist' ];

    my $i;
    foreach my $font (@fonts){
        my $format = $workbook->add_format();

        $format->set_size($font->[0]);
        $format->set_font($font->[1]);

        $i++;
        $worksheet->write($i, 0, $font->[1], $format);
        $worksheet->write($i, 1, $font->[0], $format);
    }

}


######################################################################
#
# Demonstrate the standard Excel border styles.
#
sub borders {

    my $worksheet = $workbook->add_worksheet('Borders');

    $worksheet->set_column(0, 4, 10);
    $worksheet->set_column(5, 5, 40);

    $worksheet->write(0, 0, "Index", $heading);
    $worksheet->write(0, 1, "Index", $heading);
    $worksheet->write(0, 3, "Style", $heading);
    $worksheet->write(0, 5, "The style is highlighted in red for ", $heading);
    $worksheet->write(1, 5, "emphasis, the default color is black.", $heading);

    for my $i (0..13){
        my $format = $workbook->add_format();
        $format->set_border($i);
        $format->set_border_color('red');
        $format->set_align('center');

        $worksheet->write((2*($i+1)), 0, $i,                    $center);
        $worksheet->write((2*($i+1)), 1, sprintf("0x%02X", $i), $center);

        $worksheet->write((2*($i+1)), 3, "Border", $format);
    }

    $worksheet->write(30, 0, "Diag type", $heading);
    $worksheet->write(30, 1, "Index", $heading);
    $worksheet->write(30, 3, "Style", $heading);
    $worksheet->write(30, 5, "Diagonal Boder styles", $heading);

    for my $i (1..3){
        my $format = $workbook->add_format();
        $format->set_diag_type($i);
        $format->set_diag_border(1);
        $format->set_diag_color('red');
        $format->set_align('center');

        $worksheet->write((2*($i+15)), 0, $i,                     $center);
        $worksheet->write((2*($i+15)), 1, sprintf("0x%02X", $i),  $center);

        $worksheet->write((2*($i+15)), 3, "Border", $format);
    }
}



######################################################################
#
# Demonstrate the standard Excel cell patterns.
#
sub patterns {

    my $worksheet = $workbook->add_worksheet('Patterns');

    $worksheet->set_column(0, 4, 10);
    $worksheet->set_column(5, 5, 50);

    $worksheet->write(0, 0, "Index", $heading);
    $worksheet->write(0, 1, "Index", $heading);
    $worksheet->write(0, 3, "Pattern", $heading);

    $worksheet->write(0, 5, "The background colour has been set to silver.", $heading);
    $worksheet->write(1, 5, "The foreground colour has been set to green.",  $heading);

    for my $i (0..18){
        my $format = $workbook->add_format();

        $format->set_pattern($i);
        $format->set_bg_color('silver');
        $format->set_fg_color('green');
        $format->set_align('center');

        $worksheet->write((2*($i+1)), 0, $i,                    $center);
        $worksheet->write((2*($i+1)), 1, sprintf("0x%02X", $i), $center);

        $worksheet->write((2*($i+1)), 3, "Pattern", $format);

        if ($i == 1) {
            $worksheet->write((2*($i+1)), 5, "This is solid colour, the most useful pattern.", $heading);
        }
    }
}


######################################################################
#
# Demonstrate the standard Excel cell alignments.
#
sub alignment {

    my $worksheet = $workbook->add_worksheet('Alignment');

    $worksheet->set_column(0, 7, 12);
    $worksheet->set_row(0, 40);
    $worksheet->set_selection(7, 0);

    my $format01 = $workbook->add_format();
    my $format02 = $workbook->add_format();
    my $format03 = $workbook->add_format();
    my $format04 = $workbook->add_format();
    my $format05 = $workbook->add_format();
    my $format06 = $workbook->add_format();
    my $format07 = $workbook->add_format();
    my $format08 = $workbook->add_format();
    my $format09 = $workbook->add_format();
    my $format10 = $workbook->add_format();
    my $format11 = $workbook->add_format();
    my $format12 = $workbook->add_format();
    my $format13 = $workbook->add_format();
    my $format14 = $workbook->add_format();
    my $format15 = $workbook->add_format();
    my $format16 = $workbook->add_format();
    my $format17 = $workbook->add_format();

    $format02->set_align('top');
    $format03->set_align('bottom');
    $format04->set_align('vcenter');
    $format05->set_align('vjustify');
    $format06->set_text_wrap();

    $format07->set_align('left');
    $format08->set_align('right');
    $format09->set_align('center');
    $format10->set_align('fill');
    $format11->set_align('justify');
    $format12->set_merge();

    $format13->set_rotation(45);
    $format14->set_rotation(-45);
    $format15->set_rotation(270);

    $format16->set_shrink();
    $format17->set_indent(1);

    $worksheet->write(0, 0, 'Vertical',     $heading);
    $worksheet->write(0, 1, 'top',          $format02);
    $worksheet->write(0, 2, 'bottom',       $format03);
    $worksheet->write(0, 3, 'vcenter',      $format04);
    $worksheet->write(0, 4, 'vjustify',     $format05);
    $worksheet->write(0, 5, "text\nwrap",   $format06);

    $worksheet->write(2, 0, 'Horizontal',   $heading);
    $worksheet->write(2, 1, 'left',         $format07);
    $worksheet->write(2, 2, 'right',        $format08);
    $worksheet->write(2, 3, 'center',       $format09);
    $worksheet->write(2, 4, 'fill',         $format10);
    $worksheet->write(2, 5, 'justify',      $format11);

    $worksheet->write(3, 1, 'merge',        $format12);
    $worksheet->write(3, 2, '',             $format12);

    $worksheet->write(3, 3, 'Shrink ' x 3,  $format16);
    $worksheet->write(3, 4, 'Indent',       $format17);


    $worksheet->write(5, 0, 'Rotation',     $heading);
    $worksheet->write(5, 1, 'Rotate 45',    $format13);
    $worksheet->write(6, 1, 'Rotate -45',   $format14);
    $worksheet->write(7, 1, 'Rotate 270',   $format15);
}


######################################################################
#
# Demonstrate other miscellaneous features.
#
sub misc {

    my $worksheet = $workbook->add_worksheet('Miscellaneous');

    $worksheet->set_column(2, 2, 25);

    my $format01 = $workbook->add_format();
    my $format02 = $workbook->add_format();
    my $format03 = $workbook->add_format();
    my $format04 = $workbook->add_format();
    my $format05 = $workbook->add_format();
    my $format06 = $workbook->add_format();
    my $format07 = $workbook->add_format();

    $format01->set_underline(0x01);
    $format02->set_underline(0x02);
    $format03->set_underline(0x21);
    $format04->set_underline(0x22);
    $format05->set_font_strikeout();
    $format06->set_font_outline();
    $format07->set_font_shadow();

    $worksheet->write(1,  2, 'Underline  0x01',          $format01);
    $worksheet->write(3,  2, 'Underline  0x02',          $format02);
    $worksheet->write(5,  2, 'Underline  0x21',          $format03);
    $worksheet->write(7,  2, 'Underline  0x22',          $format04);
    $worksheet->write(9,  2, 'Strikeout',                $format05);
    $worksheet->write(11, 2, 'Outline (Macintosh only)', $format06);
    $worksheet->write(13, 2, 'Shadow (Macintosh only)',  $format07);
}

__END__
	Global
`s`	Focus search bar
`?`	Bring up this help dialog
	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)
	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse
	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)