The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
=head1 NAME

Charts and Spreadsheet::WriteExcel - A short introduction on how to include externally generated charts into a Spreadsheet::WriteExcel file.

=head1 DESCRIPTION

This document explains how to import Excel charts into a C<Spreadsheet::WriteExcel> file.

B<Note>: This feature is semi-deprecated. The preferred method of adding charts is to use the Workbook C<add_chart()> method to create a "native" chart. See the L<Spreadsheet::WriteExcel::Chart> module for more information. This older method of using external charts is still supported for backwards compatibility and for certain chart types or features that aren't supported by the native charts.

Also note that this feature was/is experimental. It may not work in all cases and it is best to start with a simple Excel file and gradually add complexity.


=head1 METHODOLOGY

The general methodology is to create a chart in Excel, extract the chart from the binary file, import it into C<Spreadsheet::WriteExcel> and add new data to the series that the chart uses.

The steps involved are as follows:

=over

=item 1. Create a new workbook in Excel or with Spreadsheet::WriteExcel. The file should be in Excel 97 or later format.

=item 2. Add one or more worksheets with sample data of the type and format that you would like to have in the final version.

=item 3. Create a chart on a new chart sheet that refers to a data range in one of the worksheets. Charts embedded in worksheets are also supported. See the C<add_chart_ext()> section of the main Spreadsheet::WriteExcel documentation.

=item 4. Extend the chart data series to cover a sufficient range for any additional data that might be added. For example, if you initially have only 10 data points but you think that you may add up to 2000 at a later stage then increase the chart data series to 2000 points. In this case you should probably also leave the axes on automatic scaling.

=item 5. Format the chart as you would like it to appear in the final version.

=item 6. Save the workbook.

=item 7. Using the C<chartex>* or C<chartex.pl> utility extract the chart(s) from the Excel file:

    chartex file.xls

    or

    perl chartex.pl file.xls

* If you performed a normal installation then the C<chartex> utility should be installed to your C<perl/bin> directory and should be available from the command line.

=item 8. Create a new C<Spreadsheet::WriteExcel> file with the same worksheets as the original file.

=item 9. Add the external chart data to the C<Spreadsheet::WriteExcel> file:

    my $chart = $workbook->add_chart_ext('chart01.bin', 'Chart1');

In this case the C<chart01.bin> file is the chart data that was extracted in the Step 7.

=item 10. Create a link between the chart and the target worksheet using a dummy formula, this is discussed in more detail below:

    $worksheet->store_formula('=Sheet1!A1');

=item 11. Add 3 or more additional formats to match any formats used in the chart, for example in the axis labels or the title. You may also have to adjust  some of the font properties such as C<bold> or C<italic> to obtain the required font formats in the final chart.

    $workbook->add_format(color => $_, bold => 1) for 1 ..5;

If you do not supply enough additional formats then you may see the following error when you open the file in Excel: B<File error: data may have been lost.> The file will still load but some formatting will have been lost.

=item 12. Add new data to the data ranges defined in the chart using the standard Spreadsheet::WriteExcel interface.

=back


=head1 EXAMPLE

This following is a short example which uses line chart to display some X-Y data:

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;

    my $workbook  = Spreadsheet::WriteExcel->new("demo01.xls");
    my $worksheet = $workbook->add_worksheet();

    my $chart     = $workbook->add_chart_ext('chart01.bin', 'Chart1');

    $worksheet->store_formula('=Sheet1!A1');

    $workbook->add_format(color => 1);
    $workbook->add_format(color => 2, bold => 1);
    $workbook->add_format(color => 3);

    my @nums    = (0, 1, 2, 3, 4,  5,  6,  7,  8,  9,  10 );
    my @squares = (0, 1, 4, 9, 16, 25, 36, 49, 64, 81, 100);

    $worksheet->write_col('A1', \@nums   );
    $worksheet->write_col('B1', \@squares);



This can be viewed in terms of the steps outlined above:

Steps 1-6. Create a workbook with a chart based on data in the first worksheet. Otherwise use the C<Chart1.xls> file in the C<external_charts> directory of the distro as a template.

Step 7. Extract the chart data:

    perl chartex.pl file.xls

    Extracting "Chart1" to chart01.bin

    ============================================================
    Add the following near the start of your program.
    Change variable name $worksheet if required.

        $worksheet->store_formula("=Sheet1!A1");


Step 8. Create the new C<Spreadsheet::WriteExcel> file with the same worksheets as the original file.

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;

    my $workbook  = Spreadsheet::WriteExcel->new("demo01.xls");
    my $worksheet = $workbook->add_worksheet();


Step 9. Add the external chart data to the C<Spreadsheet::WriteExcel> file:

    my $chart     = $workbook->add_chart_ext('chart01.bin', 'Chart1');


Step 10. Create a link between the chart and the worksheet using a dummy formula:

    $worksheet->store_formula('=Sheet1!A1');

Step 11. Add 3 or more additional formats to match any formats used in the chart.

    $workbook->add_format(color => 1);
    $workbook->add_format(color => 2, bold => 1);
    $workbook->add_format(color => 3);

Step 12. Add new data to the data ranges defined in the chart.

    my @nums    = (0, 1, 2, 3, 4,  5,  6,  7,  8,  9,  10 );
    my @squares = (0, 1, 4, 9, 16, 25, 36, 49, 64, 81, 100);

    $worksheet->write_col('A1', \@nums   );
    $worksheet->write_col('B1', \@squares);

See also the C<demo1.pl>, C<demo2.pl> and C<demo3.pl> example programs in the C<external_charts> directory of the distro.


=head1 LINKING CHARTS AND DATA

Excel maintains links between charts and their data using references. For example C<Sheet3> in the following chart series would be stored internally in a reference table using a zero-based integer.

    =SERIES(,Sheet3!$A$2:$A$100,Sheet3!$B$2:$B$100,1)

These references are also shared with formulas that refer to sheetnames. For example the following would share the same reference as the previous chart series:

    =Sheet3!A1

Therefore, we can simulate the link between the chart and the worksheet data using a dummy formula:

    $worksheet->store_formula('=Sheet3!A1');

When you run the C<chartex> program it will suggest the required links:

    ============================================================
    Add the following near the start of your program.
    Change variable name $worksheet if required.

        $worksheet->store_formula("=Sheet3!A1");


=head1 SEE ALSO

The L<Spreadsheet::WriteExcel> L<Spreadsheet::WriteExcel::Chart> documentation.

The C<demo1.pl>, C<demo2.pl> and C<demo3.pl> example programs in the C<external_charts> directory of the distro.


=head1 BUGS

If you wish to submit a bug report run the C<bug_report.pl> program in the C<examples> directory of the distro.


=head1 AUTHOR

John McNamara jmcnamara@cpan.org


=head1 COPYRIGHT

© MMV-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.