Charts and Spreadsheet::WriteExcel - A short introduction on how to include externally generated charts into a Spreadsheet::WriteExcel file.
This document explains how to import Excel charts into a Spreadsheet::WriteExcel file.
Spreadsheet::WriteExcel
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.
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:
chartex
chartex.pl
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.
perl/bin
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.
chart01.bin
$worksheet->store_formula('=Sheet1!A1');
bold
italic
$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.
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.
Chart1.xls
charts
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:
Step 10. Create a link between the chart and the worksheet using a dummy formula:
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.
demo1.pl
demo2.pl
demo3.pl
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.
Sheet3
=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.
The Spreadsheet::WriteExcel documentation.
The demo1.pl, demo2.pl and demo3.pl example programs in the charts directory of the distro.
If you wish to submit a bug report run the bug_report.pl program in the examples directory of the distro.
bug_report.pl
examples
John McNamara jmcnamara@cpan.org
© 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.
1 POD Error
The following errors were encountered while parsing the POD:
Non-ASCII character seen before =encoding in '©'. Assuming CP1252
To install Spreadsheet::WriteExcel, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::WriteExcel
CPAN shell
perl -MCPAN -e shell install Spreadsheet::WriteExcel
For more information on module installation, please visit the detailed CPAN module installation guide.