Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

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 drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2016-05-25 00:39 GMT
Find Nodes?
    Voting Booth?