/ 
Spreadsheet-WriteExcel-Extended-FitColumnWidth-1.02
River stage zero No dependents
/ Spreadsheet::WriteExcel::Extended::FitColumnWidth

NAME

Spreadsheet::WriteExcel::Extended::FitColumnWidth - Extends Spreadsheet::WriteExcel with autofit of columns and a few other nice things

SYNOPSIS

use Spreadsheet::WriteExcel::Extended::FitColumnWidth where you would otherwise use Spreadsheet::WriteExcel except that the call to new has been enhanced and there are a number of things done by default, like autofit of columns, setup of header line, pre defined formats.

use warnings;
use strict;
use Spreadsheet::WriteExcel::Extended::FitColumnWidth;

my @headings = qw{ Fruit Colour Price/Kg };
my $workbook = Spreadsheet::WriteExcel::Extended::FitColumnWidth->new({
       filename  => 'test.xls',
       sheets    => [ { name => 'Test Data', headings => \@headings}, ],
       font      => '/myfonts/arial.ttf'    # optional, defaults to 'c:\windows\fonts\arial.ttf'
       font_bold => '/myfonts/arialbd.ttf'  # optional, defaults to 'c:\windows\fonts\arialbd.ttf'
       });
my $worksheet = $workbook->{__extended_sheets__}[0];
my $row = 1;  # First row after the header row

$worksheet->write_row($row++, 0, [ 'Apple - Pink Lady', 'Red', '3.25' ], $workbook->get_format('red'));
$worksheet->write_row($row++, 0, [ 'Apple - Granny Smith', 'Green', '2.95' ], $workbook->{__extended_format_green__});
# Note:  The autofit does not currently take bold fonts into account, bit is may soon :)
$worksheet->write_row($row++, 0, [ 'Original Carrot', 'Purple', '5.95' ], $workbook->{__extended_format_purple_bold__});
$worksheet->write_row($row++, 0, [ 'Orange', 'Orange', '6.15' ], $workbook->{__extended_format_orange_bg__});

$workbook->close();

You MUST call close();

Note that the default font is assumed to be Arial 10pt

METHODS

new

my $workbook = Spreadsheet::WriteExcel::Extended::FitColumnWidth->new({
      filename => 'filename.xls',
      sheets   => [
          { name => 'Test Data', headings => \@headings},
          { name => 'Sheet Number 2', headings => [ 'Component', 'Component Description' ]},
          ...
          ],
      font      => 'path/to/default/ttf'      # optional, defaults to 'c:\windows\fonts\arial.ttf'
      font_bold => 'path/to/header_row/ttf'   # optional, defaults to 'c:\windows\fonts\arialbd.ttf'
      });

The main difference here is that you pre-define the sheets you want and what heading they should have. The headings are added with a format of:

$format_heading->set_bold();
$format_heading->set_bg_color('silver');
$format_heading->set_color('blue');
$format_heading->set_align('center');

Which is also stored as: $workbook->{__extended_format_heading__} = $format_heading;

close

$workbook->close();

Don't call this and you will not have any autofit!

get_format

Get one of the predefined formats eg $workbook->get_format('blue');

Note that the name provided does not include the prefix '__extended_format_' or suffix '__'

get_worksheets_extended

my @sheets = $workbook->get_worksheets_extended();

Returns an array of Spreadsheet::WriteExcel::Worksheet objects in the order they were originally defined in the call to new

PRE DEFINED FORMATS

The pre defined formats are listed below (as returned by get_formats())

get_formats

The following formats are pre defined and accessable as $workbook->{format_name_blow}:

__extended_format_blue__
__extended_format_blue_bg__
__extended_format_blue_bold__
__extended_format_bold__
__extended_format_brown__
__extended_format_brown_bg__
__extended_format_brown_bold__
__extended_format_cyan__
__extended_format_cyan_bg__
__extended_format_cyan_bold__
__extended_format_gray__
__extended_format_gray_bg__
__extended_format_gray_bold__
__extended_format_green__
__extended_format_green_bg__
__extended_format_green_bold__
__extended_format_heading__
__extended_format_lightblue__
__extended_format_lightblue_bg__
__extended_format_lightblue_bold__
__extended_format_lightgray__
__extended_format_lightgray_bg__
__extended_format_lightgreen__
__extended_format_lightgreen_bg__
__extended_format_lightgreen_bold__
__extended_format_lightpurple__
__extended_format_lightpurple_bg__
__extended_format_lightpurple_bold__
__extended_format_lightyellow__
__extended_format_lightyellow_bg__
__extended_format_lightyellow_bold__
__extended_format_lime__
__extended_format_lime_bg__
__extended_format_lime_bold__
__extended_format_magenta__
__extended_format_magenta_bg__
__extended_format_magenta_bold__
__extended_format_navy__
__extended_format_navy_bg__
__extended_format_navy_bold__
__extended_format_orange__
__extended_format_orange_bg__
__extended_format_orange_bold__
__extended_format_pink__
__extended_format_pink_bg__
__extended_format_pink_bold__
__extended_format_purple__
__extended_format_purple_bg__
__extended_format_purple_bold__
__extended_format_red__
__extended_format_red_bg__
__extended_format_red_bold__
__extended_format_silver__
__extended_format_silver_bg__
__extended_format_silver_bold__
__extended_format_white__
__extended_format_white_bg__
__extended_format_white_bold__
__extended_format_yellow__
__extended_format_yellow_bg__
__extended_format_yellow_bold__

This list can be generated using:

print "Formats:\n", join("\n", $workbook->get_formats()), "\n";

get_number_sheets

$workbook->get_number_sheets(); returns the number of sheets defined in call to new.

INTERAL USE ONLY

extended_autofit_columns

extended_store_string_widths

string_width_fancy

string_width_simple

KNOWN ISSUES

None

SEE ALSO

Spreadsheet::WriteExcel

The fantastic module by John McNamara (jmcnamara @ cpan.org) which is the basis of this module. The autofit code is also based on the example code that John put together.

TODO

- Change autofit to cater for Bold fonts in general (ie other than the header line) - Allow for different font sizes (currently assumes Arial 10pt) - Better approach to finding the arial.ttf to allow the use of font metrics (ie with out having to specify a location)

CVS ID

$Id: FitColumnWidth.pm,v 1.2 2012/04/11 11:49:17 Greg Exp $

CVS LOG

$Log: FitColumnWidth.pm,v $
Revision 1.2  2012/04/11 11:49:17  Greg
- Minor but annoying correction

Revision 1.1  2012/04/10 10:46:29  Greg
Initial development

AUTHOR

Greg George, IT Technology Solutions P/L,
Email: gng@cpan.org

BUGS

Please report any bugs or feature requests to bug-spreadsheet-writeexcel-extended-fitcolumnwidth at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Spreadsheet-WriteExcel-Extended-FitColumnWidth. 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::Extended::FitColumnWidth

You can also look for information at:

ACKNOWLEDGEMENTS

John McNamara the creator of Spreadsheet::WriteExcel and who defined the basis of this auto column fit code

COPYRIGHT & LICENSE

Copyright 2012 Greg George.

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.