Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Excel Chart

by bdoydie (Initiate)
on Nov 19, 2012 at 12:59 UTC ( #1004530=perlquestion: print w/ replies, xml ) Need Help??
bdoydie has asked for the wisdom of the Perl Monks concerning the following question:

Hi I have a perl script at the moment which gathers the sar stats for last week and writes them into an excel spreadsheet, i then manually turn these into a chart, I am trying to automate this so the perl script creates the chart aswell, I need to have 1 worksheet with the data and 1 worksheet with the chart, here is what I have so far but its not working:
# # Generate Excel Spreadsheet # my $workbook = Spreadsheet::WriteExcel->new("$filename"); my $worksheet = $workbook->add_worksheet('cpu_stats'); my $worksheet2 = $workbook->add_worksheet('chart 1'); my $chart = $workbook->add_chart(type => 'line'); # Write header into worksheet my @header = ('00:00:00','%usr','%sys','%wio','%idle'); $worksheet->write_row(0,0,\@header); # Write data into worksheet $worksheet->write('A2',\@data); # Add worksheet data to the chart $chart->add_series( categories => '=$worksheet!$B$1:$E$1', values => '=$worksheet!$A$1:$E$1450', name => 'chart1', ); # Add chart labels $chart->set_title (name=> '$host Performance Chart for Week Ending $we +ek_ending'); $chart->set_x_axis (name=> 'Time 24(HR)'); $chart->set_y_axis (name=> 'Percentage'); # Insert chart into worksheet2 $worksheet2->insert_chart ('A1', $chart); # Close workbook $workbook->close;

Comment on Excel Chart
Download Code
Replies are listed 'Best First'.
Re: Excel Chart
by 2teez (Priest) on Nov 19, 2012 at 14:22 UTC

    Hi bdoydie,

    Something like this, for a headup:

    #!/usr/bin/perl use warnings; use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new('data.xls'); # Add a worksheet my $worksheet = $workbook->add_worksheet('data'); $worksheet->write_row( $. - 1, 0, [split] ) while <DATA>; my $chart5 = $workbook->add_chart( type => 'line', embedded => 1 ); # Configure the series. $chart5->add_series( categories => '=data!$A$2:$A$7', values => '=data!$B$2:$B$7', name => 'Test data series 1', ); # Add some labels. $chart5->set_title( name => 'Results of sample analysis' ); $chart5->set_x_axis( name => 'Sample number' ); $chart5->set_y_axis( name => 'Sample length (cm)' ); # Insert the chart into the main worksheet. $worksheet->insert_chart( 'E2', $chart5 ); __DATA__ Value1 Value2 1 5 2 0 3 30 4 15 5 75
    Please, take your time to check Spreadsheet::WriteExcel::Examples for more details, it will answer your questions.

    If you tell me, I'll forget.
    If you show me, I'll remember.
    if you involve me, I'll understand.
    --- Author unknown to me
Re: Excel Chart
by Don Coyote (Pilgrim) on Nov 19, 2012 at 14:01 UTC

    For your initial problem, I think you may need to concatenate and interpolate your '$worksheet!' hash values.

    $chart->add_series( categories => '='."$worksheet".'!$B$1:$E$1', values => '='."$worksheet".'!$A$1:$E$1450', name => 'chart1', );
Re: Excel Chart
by bdoydie (Initiate) on Nov 19, 2012 at 13:34 UTC
    I also want wanting to know is there a way to edit the properties of the chart? I want a line chart but without markers so it is easier to read can this be done?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1004530]
Approved by frozenwithjoy
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (11)
As of 2016-04-29 19:30 GMT
Find Nodes?
    Voting Booth?
    :nehw tseb si esrever ni gnitirW

    Results (441 votes). Check out past polls.