NAME

SYNOPSIS

    #!/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 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 METHODS

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

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

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

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

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

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

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

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

WORKSHEET METHODS

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

    #!/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

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

    $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

    # 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

    $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

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

COPYRIGHT

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