NAME

Spreadsheet::WriteExcel::Styler - Styles for formatting generated Excel files

SYNOPSIS

  use Spreadsheet::WriteExcel; # or use Excel::Writer::XLSX
  use Spreadsheet::WriteExcel::Styler;

  # Create an Excel workbook and worksheet
  my $workbook = Spreadsheet::WriteExcel->new('output.xls');
               # or Excel::Writer::XLSX->new('output.xls');
  $worksheet = $workbook->add_worksheet();

  # Create a styler with some styles 
  my $styler = Spreadsheet::WriteExcel::Styler->new($workbook);
  $styler->add_styles(
    title        => {align       => "center",
                     border      => 1,
                     bold        => 1,
                     color       => 'white',
                     bg_color    => 'blue'},
    right_border => {right       => 6,         # double line
                     right_color => 'blue'},
    highlighted  => {bg_color    => 'silver'},
    rotated      => {rotation    => 90},
  );

  # Write data into a cell, with a list of cumulated styles
  $worksheet->write($row, $col, $data, 
                    $styler->(qw/highlighted right_border/));

  # same thing, but styles are expressed as toggles in a hashref
  $worksheet->write($row, $col, $data,
                    $styler->({ highlighted  => 1,
                                rotated      => 0,
                                right_border => should_border($row, $col) }));

DESCRIPTION

This is a small utility to help formatting cells while creating Excel workbooks through Spreadsheet::WriteExcel or Excel::Writer::XLSX.

When working interactively within the Excel application, users often change one format feature at a time (highlight a row, add a border to a column, etc.); these changes do not affect other format features (for example if you change the background color, it does not affect fonts, borders, or cell alignment). By contrast, when generating a workbook programmatically through Spreadsheet::WriteExcel or Excel::Writer::XLSX, formats express complete sets of features, and they cannot be combined together. This means that the programmer has to prepare in advance all formats for all possible combinations of format features, and has to invent a way of cataloguing those combinations.

Styler objects from the current module come to the rescue: they hold a catalogue of styles, where each style is a collection of format features. Then, for any combination of styles, the styler generates a Spreadsheet::WriteExcel::Format or Excel::Writer::XLSX::Format on the fly, or, if a similar combination was already encountered, retrieves the format from its internal cache.

METHODS

new

  my $styler = Spreadsheet::WriteExcel::Styler->new($workbook);

Creates a styler object, associated to a given workbook.

add_styles

  $styler->add_styles(
    $style_name_1 => \%format_properties_1,
    $style_name_2 => \%format_properties_2,
    ...
   );

Defines a number of styles within the styler. Each style has a name and a hashref containing format properties (like for Spreadsheet::WriteExcel's add_format method).

format or function dereferencing operator

  # explicit calls to the 'format()' method
  my $format = $styler->format($style_name_1, $style_name_2, ...);
  my $format = $styler->format({$style_name_1 => $toggle_1,
                                $style_name_2 => $toggle_2,
                                ...});

  # same as above, but in shorthand notation
  my $format = $styler->($style_name_1, $style_name_2, ...);
  my $format = $styler->({$style_name_1 => $toggle_1,
                          $style_name_2 => $toggle_2,
                          ...});

The format method can be invoked either as a regular method call, or, in shorthand notation, as a simple coderef call (arrow operator and parentheses). It returns a Format object, either retrieved from cache, or created on the fly, that can then be passed as argument to any of the worksheet's write methods.

Arguments to format() can be either :

• a list of style names

• a single arrayref containing a list of style names

• a single hashref where keys are style names and values are boolean toggles that specify whether that style should be applied or not

The array form is useful when one knows statically the list of styles to apply. The hashref form is useful when decisions about styles depend on the context, as for example in :

  my $format = $styler->({
    highlighted  => ($row % 2),
    right_border => $is_end_of_group{$col},
    emphasized   => is_very_important($data),
   });

workbook

Returns the workbook to which this styler is bound.

styles

Returns the list of style names defined in this styler.

style

  my $props_hashref = $styler->style($style_name);

Returns the hashref of format properties that were defined for the given $style_name through a previous call to the "add_styles" method.

AUTHOR

Laurent Dami, <laurent.dami AT etat ge ch>

BUGS

Please report any bugs or feature requests to bug-spreadsheet-writeexcel-styler at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Spreadsheet-WriteExcel-Styler. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.

    perldoc Spreadsheet::WriteExcel::Styler

You can also look for information at:

RT: CPAN's request tracker

http://rt.cpan.org/NoAuth/Bugs.html?Dist=Spreadsheet-WriteExcel-Styler

AnnoCPAN: Annotated CPAN documentation

http://annocpan.org/dist/Spreadsheet-WriteExcel-Styler

CPAN Ratings

http://cpanratings.perl.org/d/Spreadsheet-WriteExcel-Styler

METACPAN

https://metacpan.org/dist/Spreadsheet-WriteExcel-Styler/

ACKNOWLEDGEMENTS

Thanks to John McNamara and to all other contributors for the wonderful Spreadsheet::WriteExcel and Excel::Writer::XLSX modules.

LICENSE AND COPYRIGHT

Copyright 2010, 2012 Laurent Dami.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

cpanm Spreadsheet::WriteExcel::Styler

perl -MCPAN -e shell
install Spreadsheet::WriteExcel::Styler