Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

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
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?
Re: Excel Chart
by Don Coyote (Monk) 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 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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (9)
As of 2014-09-16 23:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (53 votes), past polls