add_chart() is one of the WORKBOOK METHODS. Try code like this:
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('perl.xls');
$worksheet = $workbook->add_worksheet();
$worksheet->write('A1', 'Hi Chart!');
my $chart = $workbook->add_chart( type => 'line', embedded => 1, name => 'pended graph' );
# Insert the chart into the a worksheet.
$worksheet->insert_chart( 'E2', $chart );
Update
The problem is that excel is very hard to update with perl.
An Excel file is a binary file within a binary file. It contains
several interlinked checksums and changing even one byte can cause it
to become corrupted.
As such you cannot simply append or update an Excel file. The only way
to achieve this is to read the entire file into memory, make the
required changes or additions and then write the file out again.
Spreadsheet::ParseExcel will read in existing excel files:
my $parser = Spreadsheet::ParseExcel->new();
# $workbook is a Spreadsheet::ParseExcel::Workbook object
my $workbook = $parser->Parse('blablabla.xls');
What you really want is Spreadsheet::ParseExcel::SaveParser, which is a combination of Spreadsheet::ParseExcel and Spreadsheet::WriteExcel.
Here is an example.
Summing it up, I would suggest you to read the excel data in and then try either of the following:
- Create another xls file and use the Spreadsheet::WriteExcel::Chart
library.
- Create a xlsx file and use the Excel::Writer::XLSX::Chart library.
- Another close option would be to read the excel in with
Spreadsheet::ParseExcel::SaveParser and then add the chart and save
it, but with this module all original charts are lost.
If you are on a Windows machine you may try to use Win32::OLE.
Here is the example from Win32::OLE's own documentation:
use Win32::OLE;
# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
# get a new workbook
$book = $ex->Workbooks->Add;
# write to a particular cell
$sheet = $book->Worksheets(1);
$sheet->Cells(1,1)->{Value} = "foo";
# write a 2 rows by 3 columns range
$sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
[ 42, 'Perl', 3.1415 ]];
# print "XyzzyPerl"
$array = $sheet->Range("A8:C9")->{Value};
for (@$array) {
for (@$_) {
print defined($_) ? "$_|" : "<undef>|";
}
print "\n";
}
# save and exit
$book->SaveAs( 'test.xls' );
undef $book;
undef $ex;
UPDATE@2
Here is an example code:
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new( 'chart_column.xls' );
my $worksheet = $workbook->add_worksheet();
my $bold = $workbook->add_format( bold => 1 );
# Add the worksheet data that the charts will refer to.
my $headings = [ 'Category', 'Values 1', 'Values 2' ];
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
[ 1, 4, 5, 2, 1, 5 ],
[ 3, 6, 7, 5, 4, 3 ],
];
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );
###############################################################################
#
# Example 1. A minimal chart.
#
my $chart1 = $workbook->add_chart( type => 'column', embedded => 1 );
# Add values only. Use the default categories.
$chart1->add_series( values => '=Sheet1!$B$2:$B$7' );
# Insert the chart into the main worksheet.
$worksheet->insert_chart( 'E2', $chart1 );
###############################################################################
#
# Example 2. One more chart
#
my $chart2 = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the chart. # change the categories if required change the values as required
$chart2->add_series(
categories => '=Sheet1!$A$4:$A$7',
values => '=Sheet1!$B$4:$B$7',
);
$worksheet->insert_chart( 'N1', $chart2, 3, 3 );
Also,
If you don't mind xlsx over xls, you may use Excel::Writer::XLSX. It is more actively maintained.
