NAME

Chart - A writer class for Excel Charts.

SYNOPSIS

To create a simple Excel file with a chart using Excel::Writer::XLSX:

    #!/usr/bin/perl

    use strict;
    use warnings;
    use Excel::Writer::XLSX;

    my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' );
    my $worksheet = $workbook->add_worksheet();

    # 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 );

    # Add a worksheet chart.
    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',
    );

    __END__

DESCRIPTION

The Chart module is an abstract base class for modules that implement charts in Excel::Writer::XLSX. 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 Excel::Writer::XLSX::Chart::Area.
bar

Creates a Bar style (transposed histogram) chart. See Excel::Writer::XLSX::Chart::Bar.
column

Creates a column style (histogram) chart. See Excel::Writer::XLSX::Chart::Column.
line

Creates a Line style chart. See Excel::Writer::XLSX::Chart::Line.
pie

Creates an Pie style chart. See Excel::Writer::XLSX::Chart::Pie.
scatter

Creates an Scatter style chart. See Excel::Writer::XLSX::Chart::Scatter.
stock

Creates an Stock style chart. See Excel::Writer::XLSX::Chart::Stock.
...

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

CHART METHODS

Methods that are common to all chart types are documented below. See the documentation for each sub class for chart specific information.

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.

With a Excel::Writer::XLSX chart object the add_series() method is used to set the properties for a series:

    $chart->add_series(
        categories => '=Sheet1!$A$2:$A$10', # Optional, depending on chart type.
        values     => '=Sheet1!$B$2:$B$10', # Required for all chart types.
        name       => 'Series name',        # Optional.
    );

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. A formula or array ref can be used for the data range, see below.
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.

The categories and values can take either a range formula such as =Sheet1!$A$2:$A$7 or, more usefully when generating the range programmatically, an array ref with zero indexed row/column values:

     [ $sheetname, $row_start, $row_end, $col_start, $col_end ]

The following are equivalent:

    $chart->add_series( categories => '=Sheet1!$A$2:$A$7'      ); # Same as ...
    $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.

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. The name can also be a formula such as =Sheet1!$A$1. The name property is optional. The default is to have no axis name.

Additional axis properties such as range, divisions and ticks will be made available in later releases.

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. The name can also be a formula such as =Sheet1!$A$1. The name property is optional. The default is to have no axis name.

Additional axis properties such as range, divisions and ticks will be made available in later releases.

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. The name can also be a formula such as =Sheet1!$A$1. The name property is optional. The default is to have no chart title.

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 => 'bottom' );

The default legend position is right. The available positions are:

    right
    left
    top
    bottom
    none
    overlay_right
    overlay_left

set_chartarea()

The set_chartarea() method is used to set the properties of the chart area.

This method isn't implemented yet and is only available in Spreadsheet::WriteExcel. However, it can be simulated using the set_style() method, see below.

set_plotarea()

The set_plotarea() method is used to set properties of the plot area of a chart.

This method isn't implemented yet and is only available in Spreadsheet::WriteExcel. However, it can be simulated using the set_style() method, see below.

set_style()

The set_style() method is used to set the style of the chart to one of the 42 built-in styles available on the 'Design' tab in Excel:

    $chart->set_style( 4 );

The default style is 2.

WORKSHEET METHODS

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

In Excel::Writer::XLSX you can set chartsheet 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 Excel::Writer::XLSX 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

    use strict;
    use warnings;
    use Excel::Writer::XLSX;

    my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' );
    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', 'Batch 1', 'Batch 2' ];
    my $data = [
        [ 2,  3,  4,  5,  6,  7 ],
        [ 10, 40, 50, 20, 10, 50 ],
        [ 30, 60, 70, 50, 40, 30 ],

    ];

    $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 => 'column', embedded => 1 );

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

    # Configure the second series. Note the use of the array ref to define
    # ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
    $chart->add_series(
        name       => '=Sheet1!$C$1',
        categories => [ 'Sheet1', 1, 6, 0, 0 ],
        values     => [ 'Sheet1', 1, 6, 2, 2 ],
    );

    # 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 (mm)' );

    # Set an Excel chart style. Blue colors with white outline and shadow.
    $chart->set_style( 11 );

    # 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:

TODO

Charts in Excel::Writer::XLSX is under active development. More chart types and features will be added in time.

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

Chart sub-types such as stacked and percent stacked.
Colours and formatting options. For now try the set_style() method.
Axis controls, range limits, gridlines.
3D charts.
Additional chart types such as Bubble and Radar.

If you are interested in sponsoring a feature to have it implemented or expedited let me know.

AUTHOR

John McNamara jmcnamara@cpan.org

COPYRIGHT

To install Excel::Writer::XLSX, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Excel::Writer::XLSX

CPAN shell

perl -MCPAN -e shell
install Excel::Writer::XLSX

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

CHART METHODS

add_series()

set_x_axis()

set_y_axis()

set_title()

set_legend()

set_chartarea()

set_plotarea()

set_style()

WORKSHEET METHODS

EXAMPLE

TODO

AUTHOR

COPYRIGHT

Module Install Instructions