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

DESCRIPTION
    This document explains how to import Excel charts into a
    'Spreadsheet::WriteExcel' file.

    Note: This feature is semi-deprecated. The preferred method of adding
    charts is to use the Workbook "add_chart()" method to create a "native"
    chart. See the 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.

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

    The steps involved are as follows:

    1. Create a new workbook in Excel or with Spreadsheet::WriteExcel. The file should be in Excel 97 or later format.
    2. Add one or more worksheets with sample data of the type and format that you would like to have in the final version.
    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 'add_chart_ext()' section of the main Spreadsheet::WriteExcel documentation.
    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.
    5. Format the chart as you would like it to appear in the final version.
    6. Save the workbook.
    7. Using the 'chartex'* or '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 'chartex'
        utility should be installed to your 'perl/bin' directory and
        should be available from the command line.

    8. Create a new 'Spreadsheet::WriteExcel' file with the same worksheets as the original file.
    9. Add the external chart data to the 'Spreadsheet::WriteExcel' file:
            my $chart = $workbook->add_chart_ext('chart01.bin', 'Chart1');

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

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

    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 'bold' or '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:
        File error: data may have been lost. The file will still
        load but some formatting will have been lost.

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

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 'Chart1.xls' file in the
    '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 '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
    '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 'demo1.pl', 'demo2.pl' and 'demo3.pl' example
    programs in the 'external_charts' directory of the distro.

LINKING CHARTS AND DATA
    Excel maintains links between charts and their data using
    references. For example '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 '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");

SEE ALSO
    The Spreadsheet::WriteExcel documentation.

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

BUGS
    If you wish to submit a bug report run the 'bug_report.pl'
    program in the 'examples' directory of the distro.

AUTHOR
    John McNamara jmcnamara@cpan.org

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