http://www.perlmonks.org?node_id=1004530

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;

[download]

Replies are listed 'Best First'.
Re: Excel Chart
by 2teez (Vicar) 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

    [download]
    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
[reply]
[d/l]
Re: Excel Chart
by Don Coyote (Hermit) 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',
);

    [download]
[reply]
[d/l]
      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?
[reply]

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

[reply]

        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.

        Regards,

        John Davies

[reply]
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?
[reply]

Back to Seekers of Perl Wisdom