The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Excel-Writer-XLSX-0.35
River stage two • 26 direct dependents • 33 total dependents
64 ++
/ Excel::Writer::XLSX::Chart

NAME

Chart - A class for writing 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:

CHART METHODS

Methods that are common to all chart types are documented below. See the documentation for each of the above chart modules 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 formatting 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.
        values     => '=Sheet1!$B$2:$B$10', # Required.
        line       => { color => 'blue' },
    );

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 it will default to Series 1 .. n.

  • line

    Set the properties of the series line type such as colour and width. See the "CHART FORMATTING" section below.

  • border

    Set the border properties of the series such as colour and style. See the "CHART FORMATTING" section below.

  • fill

    Set the fill properties of the series such as colour. See the "CHART FORMATTING" section below.

  • marker

    Set the properties of the series marker such as style and color. See the "CHART FORMATTING" section below.

  • trendline

    Set the properties of the series trendline such as linear, polynomial and moving average types. See the "CHART FORMATTING" section below.

  • data_labels

    Set data labels for the series. See the "CHART FORMATTING" section below.

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 Excel chart will be the same as the order in which that are added in Excel::Writer::XLSX.

    # 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. Same categories. Different range values.
    $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.

  • reverse

    Reverse the order of the X axis categories or values.

        $chart->set_x_axis( reverse => 1 );

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.

  • reverse

    Reverse the order of the Y axis categories or values.

        $chart->set_y_axis( reverse => 1 );

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:

        none
    top
    bottom
    left
    right
    overlay_left
    overlay_right

  • delete_series

    This allows you to remove 1 or more series from the the legend (the series will still display on the chart). This property takes an array ref as an argument and the series are zero indexed:

        # Delete/hide series index 0 and 2 from the legend.
    $chart->set_legend( delete_series => [0, 2] );

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.

CHART FORMATTING

The following chart formatting properties can be set for any chart object that they apply to (and that are supported by Excel::Writer::XLSX) such as chart lines, column fill areas, plot area borders, markers and other chart elements documented above.

    line
    border
    fill
    marker
    trendline
    data_labels

Chart formatting properties are generally set using hash refs.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        line       => { color => 'blue' },
    );

In some cases the format properties can be nested. For example a marker may contain border and fill sub-properties.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        line       => { color => 'blue' },
        marker     => {
            type    => 'square',
            size    => 5,
            border  => { color => 'red' },
            fill    => { color => 'yellow' },
        },
    );

Line

The line format is used to specify properties of line objects that appear in a chart such as a plotted line on a chart or a border.

The following properties can be set for line formats in a chart.

    none
    color
    width
    dash_type

The none property is uses to turn the line off (it is always on by default except in Scatter charts). This is useful if you wish to plot a series with markers but without a line.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        line       => { none => 1 },
    );

The color property sets the color of the line.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        line       => { color => 'red' },
    );

The available colors are shown in the main Excel::Writer::XLSX documentation. It is also possible to set the color of a line with a HTML style RGB color:

    $chart->add_series(
        line       => { color => '#FF0000' },
    );

The width property sets the width of the line. It should be specified in increments of 0.25 of a point as in Excel.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        line       => { width => 3.25 },
    );

The dash_type property sets the dash style of the line.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        line       => { dash_type => 'dash_dot' },
    );

The following dash_type values are available. They are shown in the order that they appear in the Excel dialog.

    solid
    round_dot
    square_dot
    dash
    dash_dot
    long_dash
    long_dash_dot
    long_dash_dot_dot

The default line style is solid.

More than one line property can be specified at time:

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        line       => {
            color     => 'red',
            width     => 1.25,
            dash_type => 'square_dot',
        },
    );

Border

The border property is a synonym for line.

It can be used as a descriptive substitute for line in chart types such as Bar and Column that have a border and fill style rather than a line style. In general chart objects with a border property will also have a fill property.

Fill

The fill format is used to specify filled areas of chart objects such as the interior of a column or the background of the chart itself.

The following properties can be set for fill formats in a chart.

    none
    color

The none property is uses to turn the fill property off (it is generally on by default).

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        fill       => { none => 1 },
    );

The color property sets the color of the fill area.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        fill       => { color => 'red' },
    );

The available colors are shown in the main Excel::Writer::XLSX documentation. It is also possible to set the color of a fill with a HTML style RGB color:

    $chart->add_series(
        fill       => { color => '#FF0000' },
    );

The fill format is generally used in conjunction with a border format which has the same properties as a line format.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        border     => { color => 'red' },
        fill       => { color => 'yellow' },
    );

Marker

The marker format specifies the properties of the markers used to distinguish series on a chart. In general only Line and Scatter chart types and trendlines use markers.

The following properties can be set for marker formats in a chart.

    type
    size
    border
    fill

The type property sets the type of marker that is used with a series.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        marker     => { type => 'diamond' },
    );

The following type properties can be set for marker formats in a chart. These are shown in the same order as in the Excel format dialog.

    automatic
    none
    square
    diamond
    triangle
    x
    star
    short_dash
    long_dash
    circle
    plus

The automatic type is a special case which turns on a marker using the default marker style for the particular series number.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        marker     => { type => 'automatic' },
    );

If automatic is on then other marker properties such as size, border or fill cannot be set.

The size property sets the size of the marker and is generally used in conjunction with type.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        marker     => { type => 'diamond', size => 7 },
    );

Nested border and fill properties can also be set for a marker. These have the same sub-properties as shown above.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        marker     => {
            type    => 'square',
            size    => 5,
            border  => { color => 'red' },
            fill    => { color => 'yellow' },
        },
    );

Trendline

A trendline can be added to a chart series to indicate trends in the data such as a moving average or a polynomial fit.

The following properties can be set for trendline formats in a chart.

    type
    order       (for polynomial trends)
    period      (for moving average)
    forward     (for all except moving average)
    backward    (for all except moving average)
    name
    line

The type property sets the type of trendline in the series.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        trendline  => { type => 'linear' },
    );

The available trendline types are:

    exponential
    linear
    log
    moving_average
    polynomial
    power

A polynomial trendline can also specify the order of the polynomial. The default value is 2.

    $chart->add_series(
        values    => '=Sheet1!$B$1:$B$5',
        trendline => {
            type  => 'polynomial',
            order => 3,
        },
    );

A moving_average trendline can also the period of the moving average. The default value is 2.

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        trendline  => {
            type   => 'moving_average',
            period => 3,
        },
    );

The forward and backward properties set the forecast period of the trendline.

    $chart->add_series(
        values    => '=Sheet1!$B$1:$B$5',
        trendline => {
            type     => 'linear',
            forward  => 0.5,
            backward => 0.5,
        },
    );

The name property sets an optional name for the trendline that will appear in the chart legend. If it isn't specified the Excel default name will be displayed. This is usually a combination of the trendline type and the series name.

    $chart->add_series(
        values    => '=Sheet1!$B$1:$B$5',
        trendline => {
            type => 'linear',
            name => 'Interpolated trend',
        },
    );

Several of these properties can be set in one go:

    $chart->add_series(
        values     => '=Sheet1!$B$1:$B$5',
        trendline  => {
            type     => 'linear',
            name     => 'My trend name',
            forward  => 0.5,
            backward => 0.5,
            line     => {
                color     => 'red',
                width     => 1,
                dash_type => 'long_dash',
            },
        },
    );

Trendlines cannot be added to series in a stacked chart or pie chart or (when implemented) to 3-D, radar, surface, or doughnut charts.

Data Labels

Data labels can be added to a chart series to indicate the values of the plotted data points.

The following properties can be set for data_labels formats in a chart.

    value
    category
    series_name

The value property turns on the Value data label for a series.

    $chart->add_series(
        values      => '=Sheet1!$B$1:$B$5',
        data_labels => { value => 1 },
    );

The category property turns on the Category Name data label for a series.

    $chart->add_series(
        values      => '=Sheet1!$B$1:$B$5',
        data_labels => { category => 1 },
    );

The series_name property turns on the Series Name data label for a series.

    $chart->add_series(
        values      => '=Sheet1!$B$1:$B$5',
        data_labels => { series_name => 1 },
    );

Other formatting options

Other formatting options will be added in time. If there is a feature that you would like to see included drop me a line.

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 second series. Note alternative use of 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:

Chart example.

TODO

The chart feature 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:

  • Add more chart sub-types.

  • Additional 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

Copyright MM-MMXI, 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.