/ 
Spreadsheet-WriteExcel-2.11
River stage two • 35 direct dependents • 49 total dependents
/ Charts

NAME

Charts and Spreadsheet::WriteExcel - A short introduction to 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.

Please note that this feature 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. Note, charts embedded in worksheets aren't currently supported.
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.

$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 if 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 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 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");

This method is a workaround and will hopefully be made more transparent in a future release.

SEE ALSO

The Spreadsheet::WriteExcel documentation.

The demo1.pl, demo2.pl and demo3.pl example programs in the 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

© MMIV, 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.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 191:

Non-ASCII character seen before =encoding in '©'. Assuming CP1252