CONTENTS

NAME

Chart - A writer class for Excel Charts.

SYNOPSIS

To create a simple Excel file with a chart using Spreadsheet::WriteExcel:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

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

my $chart     = $workbook->add_chart( type => 'column' );

# Configure the chart.
$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

# Add the worksheet data the chart refers to.
my $data = [
    [ 'Category', 2, 3, 4, 5, 6, 7 ],
    [ 'Value',    1, 4, 5, 2, 1, 5 ],
];

$worksheet->write( 'A1', $data );

__END__

DESCRIPTION

The Chart module is an abstract base class for modules that implement charts in Spreadsheet::WriteExcel. The information below is applicable to all of the available subclasses.

The Chart module isn't used directly, a chart object is created via the Workbook add_chart() method where the chart type is specified:

my $chart = $workbook->add_chart( type => 'column' );

Currently the supported chart types are:

More charts and sub-types will be supported in time. See the "TODO" section.

Methods that are common to all chart types are documented below.

CHART METHODS

add_series()

In an Excel chart a "series" is a collection of information such as values, x-axis labels and the name that define which data is plotted. These settings are displayed when you select the Chart -> Source Data... menu option.

With a Spreadsheet::WriteExcel chart object the add_series() method is used to set the properties for a series:

$chart->add_series(
    categories    => '=Sheet1!$A$2:$A$10',
    values        => '=Sheet1!$B$2:$B$10',
    name          => 'Series name',
    name_formula  => '=Sheet1!$B$1',
);

The properties that can be set are:

You can add more than one series to a chart, in fact some chart types such as stock require it. The series numbering and order in the final chart is the same as the order in which that are added.

# Add the first series.
$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
    name       => 'Test data series 1',
);

# Add another series. Category is the same but values are different.
$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$C$2:$C$7',
    name       => 'Test data series 2',
);

set_x_axis()

The set_x_axis() method is used to set properties of the X axis.

$chart->set_x_axis( name => 'Sample length (m)' );

The properties that can be set are:

Additional axis properties such as range, divisions and ticks will be made available in later releases. See the "TODO" section.

set_y_axis()

The set_y_axis() method is used to set properties of the Y axis.

$chart->set_y_axis( name => 'Sample weight (kg)' );

The properties that can be set are:

Additional axis properties such as range, divisions and ticks will be made available in later releases. See the "TODO" section.

set_title()

The set_title() method is used to set properties of the chart title.

$chart->set_title( name => 'Year End Results' );

The properties that can be set are:

set_legend()

The set_legend() method is used to set properties of the chart legend.

$chart->set_legend( position => 'none' );

The properties that can be set are:

set_chartarea()

The set_chartarea() method is used to set the properties of the chart area. In Excel the chart area is the background area behind the chart.

The properties that can be set are:

Here is an example of setting several properties:

$chart->set_chartarea(
    color        => 'red',
    line_color   => 'black',
    line_pattern => 2,
    line_weight  => 3,
);

Note, for chart sheets the chart area border is off by default. For embedded charts this is on by default.

set_plotarea()

The set_plotarea() method is used to set properties of the plot area of a chart. In Excel the plot area is the area between the axes on which the chart series are plotted.

The properties that can be set are:

Here is an example of setting several properties:

$chart->set_plotarea(
    color        => 'red',
    line_color   => 'black',
    line_pattern => 2,
    line_weight  => 3,
);

WORKSHEET METHODS

In Excel a chart sheet (i.e, a chart that isn't embedded) shares properties with data worksheets such as tab selection, headers, footers, margins and print properties.

In Spreadsheet::WriteExcel you can set chart sheet properties using the same methods that are used for Worksheet objects.

The following Worksheet methods are also available through a non-embedded Chart object:

get_name()
activate()
select()
hide()
set_first_sheet()
protect()
set_zoom()
set_tab_color()

set_landscape()
set_portrait()
set_paper()
set_margins()
set_header()
set_footer()

See Spreadsheet::WriteExcel for a detailed explanation of these methods.

EXAMPLE

Here is a complete example that demonstrates some of the available features when creating a chart.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

my $workbook  = Spreadsheet::WriteExcel->new( 'chart_area.xls' );
my $worksheet = $workbook->add_worksheet();
my $bold      = $workbook->add_format( bold => 1 );

# Add the worksheet data that the charts will refer to.
my $headings = [ 'Number', 'Sample 1', 'Sample 2' ];
my $data = [
    [ 2, 3, 4, 5, 6, 7 ],
    [ 1, 4, 5, 2, 1, 5 ],
    [ 3, 6, 7, 5, 4, 3 ],
];

$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );

# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'area', embedded => 1 );

# Configure the first series. (Sample 1)
$chart->add_series(
    name       => 'Sample 1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

# Configure the second series. (Sample 2)
$chart->add_series(
    name       => 'Sample 2',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$C$2:$C$7',
);

# Add a chart title and some axis labels.
$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Test number' );
$chart->set_y_axis( name => 'Sample length (cm)' );

# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart, 25, 10 );

__END__

This will produce a chart that looks like this:

Chart example.

Chart object colours

Many of the chart objects supported by Spreadsheet::WriteExcl allow the default colours to be changed. Excel provides a palette of 56 colours and in Spreadsheet::WriteExcel these colours are accessed via their palette index in the range 8..63.

The most commonly used colours can be accessed by name or index.

black   =>   8,    green    =>  17,    navy     =>  18,
white   =>   9,    orange   =>  53,    pink     =>  33,
red     =>  10,    gray     =>  23,    purple   =>  20,
blue    =>  12,    lime     =>  11,    silver   =>  22,
yellow  =>  13,    cyan     =>  15,
brown   =>  16,    magenta  =>  14,

For example the following are equivalent.

$chart->set_plotarea( color => 10    );
$chart->set_plotarea( color => 'red' );

The colour palette is shown in palette.html in the docs directory of the distro. An Excel version of the palette can be generated using colors.pl in the examples directory.

User defined colours can be set using the set_custom_color() workbook method. This and other aspects of using colours are discussed in the "Colours in Excel" section of the main Spreadsheet::WriteExcel documentation: http://search.cpan.org/dist/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#COLOURS_IN_EXCEL.

Chart line patterns

Chart lines patterns can be set using either an index or a name:

$chart->set_plotarea( weight => 2      );
$chart->set_plotarea( weight => 'dash' );

Chart lines have 9 possible patterns are follows:

'none'         => 0,
'solid'        => 1,
'dash'         => 2,
'dot'          => 3,
'dash-dot'     => 4,
'dash-dot-dot' => 5,
'medium-gray'  => 6,
'dark-gray'    => 7,
'light-gray'   => 8,

The patterns 1-8 are shown in order in the drop down dialog boxes in Excel. The default pattern is 'solid', index 1.

Chart line weights

Chart lines weights can be set using either an index or a name:

$chart->set_plotarea( weight => 1          );
$chart->set_plotarea( weight => 'hairline' );

Chart lines have 4 possible weights are follows:

'hairline' => 1,
'narrow'   => 2,
'medium'   => 3,
'wide'     => 4,

The weights 1-4 are shown in order in the drop down dialog boxes in Excel. The default weight is 'narrow', index 2.

Chart names and links

The add_series()), set_x_axis(), set_y_axis() and set_title() methods all support a name property. In general these names can be either a static string or a link to a worksheet cell. If you choose to use the name_formula property to specify a link then you should also the name property. This isn't strictly required by Excel but some third party applications expect it to be present.

$chart->set_title(
    name          => 'Year End Results',
    name_formula  => '=Sheet1!$C$1',
);

These links should be used sparingly since they aren't commonly used in Excel charts.

Chart names and Unicode

The add_series()), set_x_axis(), set_y_axis() and set_title() methods all support a name property. These names can be UTF8 strings if you are using perl 5.8+.

# perl 5.8+ example:
my $smiley = "\x{263A}";

$chart->set_title( name => "Best. Results. Ever! $smiley" );

For older perls you write Unicode strings as UTF-16BE by adding a name_encoding property:

# perl 5.005 example:
my $utf16be_name = pack 'n', 0x263A;

$chart->set_title(
    name          => $utf16be_name,
    name_encoding => 1,
);

This methodology is explained in the "UNICODE IN EXCEL" section of Spreadsheet::WriteExcel but is semi-deprecated. If you are using Unicode the easiest option is to just use UTF8 in perl 5.8+.

Working with Cell Ranges

In the section on add_series() it was noted that the series must be defined using a range formula:

$chart->add_series( values => '=Sheet1!$B$2:$B$10' );

The worksheet name must be specified (even for embedded charts) and the cell references must be "absolute" references, i.e., they must contain $ signs. This is the format that is required by Excel for chart references.

Since it isn't very convenient to work with this type of string programmatically the Spreadsheet::WriteExcel::Utility module, which is included with Spreadsheet::WriteExcel, provides a function called xl_range_formula() to convert from zero based row and column cell references to an A1 style formula string.

The syntax is:

xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)

If you include it in your program, using the standard import syntax, you can use the function as follows:

# Include the Utility module or just the function you need.
use Spreadsheet::WriteExcel::Utility qw( xl_range_formula );
...

# Then use it as required.
$chart->add_series(
    categories    => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
    values        => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
);

# Which is the same as:
$chart->add_series(
    categories    => '=Sheet1!$A$2:$A$10',
    values        => '=Sheet1!$B$2:$B$10',
);

See Spreadsheet::WriteExcel::Utility for more details.

TODO

Charts in Spreadsheet::WriteExcel are a work in progress. More chart types and features will be added in time. Please be patient. Even a small feature can take a week or more to implement, test and document.

Features that are on the TODO list and will be added are:

If you are interested in sponsoring a feature let me know.

KNOWN ISSUES

AUTHOR

John McNamara jmcnamara@cpan.org

COPYRIGHT

Copyright MM-MMX, John McNamara.

All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.