Tk / Spreadsheet::WriteExcel Charts problem

marcinski has asked for the wisdom of the Perl Monks concerning the following question:

Hello Perl Monks!

I'm trying to generate charts from a newly created xls table. I use Spreadsheet::WriteExcel add_chart command. I tested the short code provided with the documentation and it worked perfectly. So the package is installed correctly.

However, after putting it into my code I get an error:

Tk::Error: Can't locate object method "add_chart" via package "Spreads
+heet::WriteExcel::Worksheet" at C:\perl\KDC_v3charts.pl line 1352.
main::analysis at C:\perl\KDC_v3charts.pl line 1352
Tk callback for .button4
Tk:__ANON__ at C:/Perl/site/lib/Tk.pm line 250
Tk::Button::butUp at C:/Perl/site/lib/Tk/Button.pm line 175
<ButtonRelease-1>
<command bound to event>
[download]

My program looks generally like this (rough frame was created by Zooz):

#!/usr/bin/perl

use strict;
use warnings;

use Tk; #GUI
use Tk::DirTree; #GUI
use Cwd; #Current working dir
use Tk::NumEntry; #GUI
use Spreadsheet::ParseExcel; #Excel I/O
use Statistics::Descriptive; #Statistics
use Spreadsheet::WriteExcel; #Excel I/O
use Spreadsheet::WriteExcel:Worksheet;


listed global variables


Tk MainLoop, where there is main window created for user input, multip
+le variables, and the  submit button:

$ZWIDGETS{'Button3'} = $MW->Button(
   -text => 'SUBMIT FOR ANALYSIS',
   -command      => 'main::analysis',
  )->grid(
   -row    => 15,
   -column => 0,
   -sticky => 'w',
   -padx   => 5,
   -pady   => 5,
  )


sub analysis {

importing data from an input file

multiple statistical calculations

creating a new output xls file via

my $EXworkbook = Spreadsheet::WriteExcel->new("output.xls")

my $EXworksheet = $EXworkbook->add_worksheet('Worksheet1')

writing statistical calculations into worksheet

}
[download]

Works great! But... Then I tried to add new charts via

my $chart = $EXworksheet->add_chart( type => 'column', embedded => 1 )
[download]

If I try to embedd this into the main 'analysis' sub I get the above error.

If I try to place it as a separate sub (either within main sub or outside).... nothing happens

The package should be ok, since the standalone code generates charts. Any thoughts? Thanks.

Comment on Tk / Spreadsheet::WriteExcel Charts problem Select or Download Code

Replies are listed 'Best First'.
Re: Tk / Spreadsheet::WriteExcel Charts problem by jmcnamara (Monsignor) on Feb 08, 2011 at 19:57 UTC
The `add_chart()` method is a Workbook method but you are accidentally calling it on a Worksheet object. Once you have created the chart object you can add it to a Worksheet using the insert_chart() method. Something like the following: `sub analysis { my $EXworkbook = Spreadsheet::WriteExcel->new( "output.xls" ); my $EXworksheet = $EXworkbook->add_worksheet( 'Worksheet1' ); my $chart = $EXworkbook->add_chart( type => 'column', embedded => +1 ); # Insert the chart into the a worksheet. $EXworksheet->insert_chart( 'E2', $chart ); }` [download] Note, you won't be able to create a Workbook object in one sub and populate it in another unless you are careful with the scope of the objects that you are using. See also the `close()` section of the WriteExcel docs for further information on the last point. -- John.	[reply] [d/l]

Replies are listed 'Best First'.

Re: Tk / Spreadsheet::WriteExcel Charts problem
by jmcnamara (Monsignor) on Feb 08, 2011 at 19:57 UTC

add_chart()

Once you have created the chart object you can add it to a Worksheet using the insert_chart() method. Something like the following:


sub analysis {

    my $EXworkbook  = Spreadsheet::WriteExcel->new( "output.xls" );
    my $EXworksheet = $EXworkbook->add_worksheet( 'Worksheet1' );

    my $chart = $EXworkbook->add_chart( type => 'column', embedded => 
+1 );

    # Insert the chart into the a worksheet.
    $EXworksheet->insert_chart( 'E2', $chart );
}
[download]

Note, you won't be able to create a Workbook object in one sub and populate it in another unless you are careful with the scope of the objects that you are using.

See also the close() section of the WriteExcel docs for further information on the last point.

--
John.

[reply]
[d/l]