Spreadsheet::WriteExceUserhContributed Perl DSpreadsheet::WriteExcel::Chart(3)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:
· "area": Creates an Area (filled line) style chart. See
Spreadsheet::WriteExcel::Chart::Area.
· "bar": Creates a Bar style (transposed histogram) chart. See
Spreadsheet::WriteExcel::Chart::Bar.
· "column": Creates a column style (histogram) chart. See
Spreadsheet::WriteExcel::Chart::Column.
· "line": Creates a Line style chart. See
Spreadsheet::WriteExcel::Chart::Line.
· "pie": Creates an Pie style chart. See
Spreadsheet::WriteExcel::Chart::Pie.
· "scatter": Creates an Scatter style chart. See
Spreadsheet::WriteExcel::Chart::Scatter.
· "stock": Creates an Stock style chart. See
Spreadsheet::WriteExcel::Chart::Stock.
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 METHODSadd_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:
· "values"
This is the most important property of a series and must be set for
every chart object. It links the chart with the worksheet data that
it displays. Note the format that should be used for the formula.
See "Working with Cell Ranges".
· "categories"
This sets the chart category labels. The category is more or less
the same as the X-axis. In most chart types the "categories"
property is optional and the chart will just assume a sequential
series from "1 .. n".
· "name"
Set the name for the series. The name is displayed in the chart
legend and in the formula bar. The name property is optional and if
it isn't supplied will default to "Series 1 .. n".
· "name_formula"
Optional, can be used to link the name to a worksheet cell. See
"Chart names and links".
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:
· "name"
Set the name (title or caption) for the axis. The name is displayed
below the X axis. This property is optional. The default is to have
no axis name.
· "name_formula"
Optional, can be used to link the name to a worksheet cell. See
"Chart names and links".
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:
· "name"
Set the name (title or caption) for the axis. The name is displayed
to the left of the Y axis. This property is optional. The default
is to have no axis name.
· "name_formula"
Optional, can be used to link the name to a worksheet cell. See
"Chart names and links".
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:
· "name"
Set the name (title) for the chart. The name is displayed above the
chart. This property is optional. The default is to have no chart
title.
· "name_formula"
Optional, can be used to link the name to a worksheet cell. See
"Chart names and links".
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:
· "position"
Set the position of the chart legend.
$chart->set_legend( position => 'none' );
The default legend position is "bottom". The currently supported
chart positions are:
none
bottom
The other legend positions will be added soon.
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:
· "color"
Set the colour of the chart area. The Excel default chart area
color is 'white', index 9. See "Chart object colours".
· "line_color"
Set the colour of the chart area border line. The Excel default
border line colour is 'black', index 9. See "Chart object
colours".
· "line_pattern"
Set the pattern of the of the chart area border line. The Excel
default pattern is 'none', index 0 for a chart sheet and 'solid',
index 1, for an embedded chart. See "Chart line patterns".
· "line_weight"
Set the weight of the of the chart area border line. The Excel
default weight is 'narrow', index 2. See "Chart line weights".
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 is 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:
· "visible"
Set the visibility of the plot area. The default is 1 for visible.
Set to 0 to hide the plot area and have the same colour as the
background chart area.
· "color"
Set the colour of the plot area. The Excel default plot area color
is 'silver', index 23. See "Chart object colours".
· "line_color"
Set the colour of the plot area border line. The Excel default
border line colour is 'gray', index 22. See "Chart object colours".
· "line_pattern"
Set the pattern of the of the plot area border line. The Excel
default pattern is 'solid', index 1. See "Chart line patterns".
· "line_weight"
Set the weight of the of the plot area border line. The Excel
default weight is 'narrow', index 2. See "Chart line weights".
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__
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
<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:
· Chart sub-types.
· Colours and formatting options. For now you will have to make do
with the default Excel colours and formats.
· Axis controls, gridlines.
· 3D charts.
· Embedded data in charts for third party application support. See
Known Issues.
· Additional chart types such as Bubble and Radar. Send an email if
you are interested in other types and they will be added to the
queue.
If you are interested in sponsoring a feature let me know.
KNOWN ISSUES
· Currently charts don't contain embedded data from which the charts
can be rendered. Excel and most other third party applications
ignore this and read the data via the links that have been
specified. However, some applications may complain or not render
charts correctly. The preview option in Mac OS X is an known
example. This will be fixed in a later release.
· When there are several charts with titles set in a workbook some of
the titles may display at a font size of 10 instead of the default
12 until another chart with the title set is viewed.
· Stock (and other) charts should have the X-axis dates aligned at an
angle for clarity. This will be fixed at a later stage.
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.
perl v5.14.2 2010-02-02 Spreadsheet::WriteExcel::Chart(3)
