Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

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;

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', );
      Instead of hard coding as " categories => '='."$worksheet".'!$B$1:$E$1' ", Can we make it dynamic to select the range of cells to plot the chart? Any idea how to make it dynamic?

        Making the range dynamic is as easy as constructing the appropriate string for the range.

        Have a look at Re^3: Win32::Ole excel external data range, in which I show the use of dynamic ranges. It does other things too (it's answering a different question), but I think it's reasonably clear.


        John Davies

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 studying the Monastery: (8)
As of 2016-10-27 17:54 GMT
Find Nodes?
    Voting Booth?
    How many different varieties (color, size, etc) of socks do you have in your sock drawer?

    Results (366 votes). Check out past polls.