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
* orchartex.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 yourperl/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
oritalic
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 Spreadsheet::WriteExcel::Chart 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
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.