John McNamara > Excel-Writer-XLSX > Excel::Writer::XLSX::Chart

Download:
Excel-Writer-XLSX-0.79.tar.gz

Dependencies

Annotate this POD

Website

View/Report Bugs
Module Version: 0.79   Source  

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 subtypes are also supported in some cases:

    $workbook->add_chart( type => 'bar', subtype => 'stacked' );

The currently available subtypes are:

    area
        stacked
        percent_stacked

    bar
        stacked
        percent_stacked

    column
        stacked
        percent_stacked

    scatter
        straight_with_markers
        straight
        smooth_with_markers
        smooth

    radar
        with_markers
        filled

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

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 they 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',
    );

It is also possible to specify non-contiguous ranges:

    $chart->add_series(
        categories      => '=(Sheet1!$A$1:$A$9,Sheet1!$A$14:$A$25)',
        values          => '=(Sheet1!$B$1:$B$9,Sheet1!$B$14:$B$25)',
    );

set_x_axis()

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

    $chart->set_x_axis( name => 'Quarterly results' );

The properties that can be set are:

    name
    name_font
    name_layout
    num_font
    num_format
    min
    max
    minor_unit
    major_unit
    interval_unit
    crossing
    reverse
    position_axis
    log_base
    label_position
    major_gridlines
    minor_gridlines
    visible
    date_axis
    minor_unit_type
    major_unit_type

These are explained below. Some properties are only applicable to value or category axes, as indicated. See "Value and Category Axes" for an explanation of Excel's distinction between the axis types.

More than one property can be set in a call to set_x_axis():

    $chart->set_x_axis(
        name => 'Quarterly results',
        min  => 10,
        max  => 80,
    );

set_y_axis()

The set_y_axis() method is used to set properties of the Y axis. The properties that can be set are the same as for set_x_axis, see above.

set_x2_axis()

The set_x2_axis() method is used to set properties of the secondary X axis. The properties that can be set are the same as for set_x_axis, see above. The default properties for this axis are:

    label_position => 'none',
    crossing       => 'max',
    visible        => 0,

set_y2_axis()

The set_y2_axis() method is used to set properties of the secondary Y axis. The properties that can be set are the same as for set_x_axis, see above. The default properties for this axis are:

    major_gridlines => { visible => 0 }

set_size()

The set_size() method is used to set the dimensions of the chart. The size properties that can be set are:

     width
     height
     x_scale
     y_scale
     x_offset
     y_offset

The width and height are in pixels. The default chart width is 480 pixels and the default height is 288 pixels. The size of the chart can be modified by setting the width and height or by setting the x_scale and y_scale:

    $chart->set_size( width => 720, height => 576 );

    # Same as:

    $chart->set_size( x_scale => 1.5, y_scale => 2 );

The x_offset and y_offset position the top left corner of the chart in the cell that it is inserted into.

Note: the x_scale, y_scale, x_offset and y_offset parameters can also be set via the insert_chart() method:

    $worksheet->insert_chart( 'E2', $chart, 2, 4, 1.5, 2 );

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.

The properties that can be set are:

set_chartarea()

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

    $chart->set_chartarea(
        border => { none  => 1 },
        fill   => { color => 'red' }
    );

The properties that can be set are:

set_plotarea()

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

    $chart->set_plotarea(
        border => { color => 'yellow', width => 1, dash_type => 'dash' },
        fill   => { color => '#92D050' }
    );

The properties that can be set are:

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.

set_table()

The set_table() method adds a data table below the horizontal axis with the data used to plot the chart.

    $chart->set_table();

The available options, with default values are:

    vertical   => 1,    # Display vertical lines in the table.
    horizontal => 1,    # Display horizontal lines in the table.
    outline    => 1,    # Display an outline in the table.
    show_keys  => 0     # Show the legend keys with the table data.

The data table can only be shown with Bar, Column, Line, Area and stock charts.

set_up_down_bars

The set_up_down_bars() method adds Up-Down bars to Line charts to indicate the difference between the first and last data series.

    $chart->set_up_down_bars();

It is possible to format the up and down bars to add fill and border properties if required. See the "CHART FORMATTING" section below.

    $chart->set_up_down_bars(
        up   => { fill => { color => 'green' } },
        down => { fill => { color => 'red' } },
    );

Up-down bars can only be applied to Line charts and to Stock charts (by default).

set_drop_lines

The set_drop_lines() method adds Drop Lines to charts to show the Category value of points in the data.

    $chart->set_drop_lines();

It is possible to format the Drop Line line properties if required. See the "CHART FORMATTING" section below.

    $chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );

Drop Lines are only available in Line, Area and Stock charts.

set_high_low_lines

The set_high_low_lines() method adds High-Low lines to charts to show the maximum and minimum values of points in a Category.

    $chart->set_high_low_lines();

It is possible to format the High-Low Line line properties if required. See the "CHART FORMATTING" section below.

    $chart->set_high_low_lines( line => { color => 'red' } );

High-Low Lines are only available in Line and Stock charts.

show_blanks_as()

The show_blanks_as() method controls how blank data is displayed in a chart.

    $chart->show_blanks_as( 'span' );

The available options are:

        gap    # Blank data is shown as a gap. The default.
        zero   # Blank data is displayed as zero.
        span   # Blank data is connected with a line.

show_hidden_data()

Display data in hidden rows or columns on the chart.

    $chart->show_hidden_data();

SERIES OPTIONS ^

This section details the following properties of add_series() in more detail:

    marker
    trendline
    y_error_bars
    x_error_bars
    data_labels
    points
    smooth

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. See the "CHART FORMATTING" section below.

    $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 trendlines in a chart series.

    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 specify 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, radar chart, doughtnut or (when implemented) to 3D, or surface charts.

Error Bars

Error bars can be added to a chart series to indicate error bounds in the data. The error bars can be vertical y_error_bars (the most common type) or horizontal x_error_bars (for Bar and Scatter charts only).

The following properties can be set for error bars in a chart series.

    type
    value        (for all types except standard error and custom)
    plus_values  (for custom only)
    minus_values (for custom only)
    direction
    end_style
    line

The type property sets the type of error bars in the series.

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

The available error bars types are available:

    fixed
    percentage
    standard_deviation
    standard_error
    custom

All error bar types, except for standard_error and custom must also have a value associated with it for the error bounds:

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

The custom error bar type must specify plus_values and minus_values which should either by a Sheet1!$A$1:$A$5 type range formula or an arrayref of values:

    $chart->add_series(
        categories   => '=Sheet1!$A$1:$A$5',
        values       => '=Sheet1!$B$1:$B$5',
        y_error_bars => {
            type         => 'custom',
            plus_values  => '=Sheet1!$C$1:$C$5',
            minus_values => '=Sheet1!$D$1:$D$5',
        },
    );

    # or


    $chart->add_series(
        categories   => '=Sheet1!$A$1:$A$5',
        values       => '=Sheet1!$B$1:$B$5',
        y_error_bars => {
            type         => 'custom',
            plus_values  => [1, 1, 1, 1, 1],
            minus_values => [2, 2, 2, 2, 2],
        },
    );

Note, as in Excel the items in the minus_values do not need to be negative.

The direction property sets the direction of the error bars. It should be one of the following:

    plus    # Positive direction only.
    minus   # Negative direction only.
    both    # Plus and minus directions, The default.

The end_style property sets the style of the error bar end cap. The options are 1 (the default) or 0 (for no end cap):

    $chart->add_series(
        values       => '=Sheet1!$B$1:$B$5',
        y_error_bars => {
            type      => 'fixed',
            value     => 2,
            end_style => 0,
            direction => 'minus'
        },
    );

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
    position
    leader_lines
    percentage

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 },
    );

The position property is used to position the data label for a series.

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

Valid positions are:

    center
    right
    left
    top
    bottom
    above           # Same as top
    below           # Same as bottom
    inside_base     # Mainly for Column/Bar charts.
    inside_end      # Pie chart mainly.
    outside_end     # Pie chart mainly.
    best_fit        # Pie chart mainly.

The percentage property is used to turn on the display of data labels as a Percentage for a series. It is mainly used for pie and doughnut charts.

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

The leader_lines property is used to turn on Leader Lines for the data label for a series. It is mainly used for pie charts.

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

Note: Even when leader lines are turned on they aren't automatically visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or design) leader lines only appear if the data label is moved manually or if the data labels are very close and need to be adjusted automatically.

Points

In general formatting is applied to an entire series in a chart. However, it is occasionally required to format individual points in a series. In particular this is required for Pie and Doughnut charts where each segment is represented by a point.

In these cases it is possible to use the points property of add_series():

    $chart->add_series(
        values => '=Sheet1!$A$1:$A$3',
        points => [
            { fill => { color => '#FF0000' } },
            { fill => { color => '#CC0000' } },
            { fill => { color => '#990000' } },
        ],
    );

The points property takes an array ref of format options (see the "CHART FORMATTING" section below). To assign default properties to points in a series pass undef values in the array ref:

    # Format point 3 of 3 only.
    $chart->add_series(
        values => '=Sheet1!$A$1:$A$3',
        points => [
            undef,
            undef,
            { fill => { color => '#990000' } },
        ],
    );

    # Format the first point only.
    $chart->add_series(
        values => '=Sheet1!$A$1:$A$3',
        points => [ { fill => { color => '#FF0000' } } ],
    );

Smooth

The smooth option is used to set the smooth property of a line series. It is only applicable to the Line and Scatter chart types.

    $chart->add_series( values => '=Sheet1!$C$1:$C$5',
                        smooth => 1 );

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, gridlines and other chart elements documented above.

    line
    border
    fill

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 colours are shown in the main Excel::Writer::XLSX documentation. It is also possible to set the colour of a line with a HTML style RGB colour:

    $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 a 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 used 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 colour of the fill area.

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

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

    $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' },
    );

CHART FONTS ^

The following font properties can be set for any chart object that they apply to (and that are supported by Excel::Writer::XLSX) such as chart titles, axis labels and axis numbering. They correspond to the equivalent Worksheet cell Format object properties. See "FORMAT_METHODS" in Excel::Writer::XLSX for more information.

    name
    size
    bold
    italic
    underline
    rotation
    color

The following explains the available font properties:

Here is an example of Font formatting in a Chart program:

    # Format the chart title.
    $chart->set_title(
        name      => 'Sales Results Chart',
        name_font => {
            name  => 'Calibri',
            color => 'yellow',
        },
    );

    # Format the X-axis.
    $chart->set_x_axis(
        name      => 'Month',
        name_font => {
            name  => 'Arial',
            color => '#92D050'
        },
        num_font => {
            name  => 'Courier New',
            color => '#00B0F0',
        },
    );

    # Format the Y-axis.
    $chart->set_y_axis(
        name      => 'Sales (1000 units)',
        name_font => {
            name      => 'Century',
            underline => 1,
            color     => 'red'
        },
        num_font => {
            bold   => 1,
            italic => 1,
            color  => '#7030A0',
        },
    );

CHART LAYOUT ^

The position of the chart in the worksheet is controlled by the set_size() method shown above.

It is also possible to change the layout of the following chart sub-objects:

    plotarea
    legend
    title
    x_axis caption
    y_axis caption

Here are some examples:

    $chart->set_plotarea(
        layout => {
            x      => 0.35,
            y      => 0.26,
            width  => 0.62,
            height => 0.50,
        }
    );

    $chart->set_legend(
        layout => {
            x      => 0.80,
            y      => 0.37,
            width  => 0.12,
            height => 0.25,
        }
    );

    $chart->set_title(
        name   => 'Title',
        layout => {
            x => 0.42,
            y => 0.14,
        }
    );

    $chart->set_x_axis(
        name        => 'X axis',
        name_layout => {
            x => 0.34,
            y => 0.85,
        }
    );

Note that it is only possible to change the width and height for the plotarea and legend objects. For the other text based objects the width and height are changed by the font dimensions.

The layout units must be a float in the range 0 < x <= 1 and are expressed as a percentage of the chart dimensions as shown below:

Chart object layout.

From this the layout units are calculated as follows:

    layout:
        width  = w / W
        height = h / H
        x      = a / W
        y      = b / H

These units are slightly cumbersome but are required by Excel so that the chart object positions remain relative to each other if the chart is resized by the user.

Note that for plotarea the origin is the top left corner in the plotarea itself and does not take into account the axes.

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.

Value and Category Axes ^

Excel differentiates between a chart axis that is used for series categories and an axis that is used for series values.

In the example above the X axis is the category axis and each of the values is evenly spaced. The Y axis (in this case) is the value axis and points are displayed according to their value.

Since Excel treats the axes differently it also handles their formatting differently and exposes different properties for each.

As such some of Excel::Writer::XLSX axis properties can be set for a value axis, some can be set for a category axis and some properties can be set for both.

For example the min and max properties can only be set for value axes and reverse can be set for both. The type of axis that a property applies to is shown in the set_x_axis() section of the documentation above.

Some charts such as Scatter and Stock have two value axes.

Date Axes are a special type of category axis which are explained below.

Date Category Axes ^

Date Category Axes are category axes that display time or date information. In Excel::Writer::XLSX Date Category Axes are set using the date_axis option:

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

In general you should also specify a number format for a date axis although Excel will usually default to the same format as the data being plotted:

    $chart->set_x_axis(
        date_axis         => 1,
        num_format        => 'dd/mm/yyyy',
    );

Excel doesn't normally allow minimum and maximum values to be set for category axes. However, date axes are an exception. The min and max values should be set as Excel times or dates:

    $chart->set_x_axis(
        date_axis         => 1,
        min               => $worksheet->convert_date_time('2013-01-02T'),
        max               => $worksheet->convert_date_time('2013-01-09T'),
        num_format        => 'dd/mm/yyyy',
    );

For date axes it is also possible to set the type of the major and minor units:

    $chart->set_x_axis(
        date_axis         => 1,
        minor_unit        => 4,
        minor_unit_type   => 'months',
        major_unit        => 1,
        major_unit_type   => 'years',
        num_format        => 'dd/mm/yyyy',
    );

Secondary Axes ^

It is possible to add a secondary axis of the same type to a chart by setting the y2_axis or x2_axis property of the series:

    #!/usr/bin/perl

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

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

    # Add the worksheet data that the charts will refer to.
    my $data = [
        [ 2,  3,  4,  5,  6,  7 ],
        [ 10, 40, 50, 20, 10, 50 ],

    ];

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

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

    # Configure a series with a secondary axis
    $chart->add_series(
        values  => '=Sheet1!$A$1:$A$6',
        y2_axis => 1,
    );

    $chart->add_series(
        values => '=Sheet1!$B$1:$B$6',
    );


    # Insert the chart into the worksheet.
    $worksheet->insert_chart( 'D2', $chart );

    __END__

Note, it isn't currently possible to add a secondary axis of a different chart type (for example line and column).

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:

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-MMXIIII, 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.

syntax highlighting: