The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
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.