If you have Excel, an easy way is to take the spreadsheet, open it in Excel,
start recording a macro, build the chart, the stop the macro recording.
Then go Tools-Macro, select the macro and edit. This will show the code in
VB. Translate it to Perl.
Here is a sample of what I am using to generate MS Exchange Statistics and a
3D Bar chart.
my $FALSE = 0;
my $TRUE = ! $FALSE;
$Worksheet->Range("C3:H6")->Select;
$Excel->Charts->Add;
$Graph = $Excel->ActiveChart;
$Chart = new Win32::OLE("MSGraph.Application") || die "GO Away. Can not
create\n";
$Chart->{Visible} = 1;
$Graph->{HasLegend} = 0;
$Graph->{ChartType} = xl3DColumn;
#
$Graph->SetSourceData({
Source=>$Excel->Sheets("Msg Worksheet")->Range("C3:H6"),
PlotBy=>xlRows});
#This gives the labels for the Categories on the 'x' axis
$Graph->SeriesCollection(1)->{XValues} = "='Msg Worksheet'!R2C3:R2C7";
# This gives the label for the server name on the 'y' axis
$Graph->SeriesCollection(1)->{Name} = "='Msg Worksheet'!R3C1";
$Graph->SeriesCollection(2)->{Name} = "='Msg Worksheet'!R4C1";
$Graph->SeriesCollection(3)->{Name} = "='Msg Worksheet'!R5C1";
$Graph->SeriesCollection(4)->{Name} = "='Msg Worksheet'!R6C1";
$Graph->Location({
Where=>xlLocationAsObject,
Name=>"Graph1"});
$Graph = $Excel->ActiveChart;
$Graph->{HasTitle} = $TRUE;
$Graph->ChartTitle->Characters->{Text} = "Exchange Email Internal Traffic";
$Graph->Axes(xlCategory)->{HasTitle} = $FALSE;
#$Graph->Axes(xlCategory)->AxisTitle->Characters->{Text} = $timeframe;
$Graph->Axes(xlSeries)->{HasTitle} = $TRUE;
$Graph->Axes(xlSeries)->AxisTitle->Characters->{Text} = $timeframe;
$Graph->Axes(xlValue)->{HasTitle} = $TRUE;
$Graph->Axes(xlValue)->AxisTitle->Characters->{Text} = "Number of Messages";
$Graph->ChartTitle->Select;
$Excel->Selection->{AutoScaleFont}= 0;
$Excel->Selection->Font->{Size} = 14;
$Graph->Axes(xlCategory)->{HasMajorGridlines} = 1;
$Graph->Axes(xlCategory)->{HasMinorGridlines} = 0;
$Graph->Axes(xlSeries)->{HasMajorGridlines} = 1;
$Graph->Axes(xlSeries)->{HasMinorGridlines} = 0;
$Graph->Axes(xlValue)->{HasMajorGridlines} = 1;
$Graph->Axes(xlValue)->{HasMinorGridlines} = 0;
$Graph->Axes(xlValue)->{WallsAndGridlines2D} = 0;
$Graph->{HasLegend} = 0;
$Graph->{Elevation} = 13;
$Graph->{Perspective} = 35;
$Graph->{Rotation} = 54;
$Graph->{RightAngleAxes} = $FALSE;
$Graph->{HeightPercent} = 100;
$Graph->{AutoScaling} = $TRUE;
undef $Graph;
-----Original Message-----
From: Richard Fernandez [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 12, 2003 8:55 AM
To: [EMAIL PROTECTED]
Subject: Excel spreadsheet: creating charts
We have a manual process in place to create Excel worksheets that contain
bar charts. I'm trying to automate this process using perl. The problem is
that I don't know how to access the underlying formulas, in Excel, that
generate the charts. Our current process is as follows:
1) Generate a space separated list of stats on Sun box using sar(1).
2) cut and paste this list into a pre-existing Excel spreadsheet which then
automatically
re-draws the charts based on the new data.
What I need help with is figuring out how to create a sheet with the
necessary charts. Is this possible to do using Spreadsheet::WriteExcel? Is
there a way to determine form the existing worksheet what the formula is for
the charts.
I'm not opposed to doing this a different/better way if anyone has any
suggestions, but I'd like to avoid generating csv files since this would
require a manual "import" into Excel.
Any help is much appreciated!
Rich
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
