PERL excell scripting

Shell Programming and Scripting
1

Hi,
At the minute I am using a script to gather weekly SAR stats and put them into an excel scpreadsheet using perl.
I then get that spreadsheet and manually add in a chart on a new worksheet, Can i add this step into the script?
and if so how?

here is the part of my script that creates the spreadsheet

#
# Generate Excel Spreadsheet
#
my $workbook = Spreadsheet::WriteExcel->new("$filename");
my $worksheet = $workbook->add_worksheet('cpu_stats');
# 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);
# Close workbook
$workbook->close;

---------- Post updated at 02:03 PM ---------- Previous update was at 02:01 PM ----------

just for reference the chart needs to be a line chart which shows each of the stats with time along the x-axis and % alonf the y-axis

2

Take a look at: Spreadsheet::WriteExcel::Chart - search.cpan.org

3

Cheers, i will have a look and if i have any problems ill let you know

4

Below is my script at the moment:

 
#
# Generate Excel Spreadsheet
#
my $workbook = Spreadsheet::WriteExcel->new("$filename");
my $worksheet = $workbook->add_worksheet('cpu_stats');
my $worksheet2 = $workbook->add_worksheet('chart1');
my $chart = $workbook->add_chart(type => 'line', embedded =>1);
# 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',
 name       => 'chart1',
);
# Add chart labels
$chart->set_title (name=> '$host Performance Chart for Week Ending $week_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;

I know the issue is "

values => '=$worksheet

" as i get the following when i try to run the script:

 
Couldn't parse formula: =$worksheet! at sar_chart.tst line 83

The thing is I need to have 2 worksheets, 1 with all the data and 1 with the chart,
Can anyone help?

5

Try this:

 categories => '=cpu_stats!$B$1:$E$1',
 values     => '=cpu_stats',
6

That worked great, thanks:
Does anyone know if its possible to remove the markers on a line chart using perl?
and secondly my script runs on a number of servers so Im trying to get the script to name the chart with the hostname,
Here is what im doing:

#Hostname
my $host = hostname;
 
$chart->set_title (name=> print "$host" 'Performance Chart' );
$chart->set_x_axis (name=> 'Time "$(HR)');
$chart->set_y_axis (name=> 'Percentage');

I can't seem to get it to actually print the hostname, it just prints whatever i type after name=>

7

I don't know what markers you are talking about (maybe post some screenshot). As for the chart name, this should work:

$chart->set_title (name=> "$host Performance Chart" );
8

Thank ive got everything sorted now except the markers,
I think they are technically called data points,
There are boxes or an 'X' at every point along the line, I want to remove these so it is
just a smooth line

9

Did you take a look at this? Spreadsheet::WriteExcel::Chart - search.cpan.org

10

yeh i had a look at it but doesnt seem to change the actual chart data lines