RE: Excel spreadsheet: creating charts

[Richard Fernandez]

Thanks! This looks like just what I was looking for. I'll give it a try and
see how it goes.

-----Original Message-----
From: Thomson Steven R Contr AFRL/VSIO [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 12, 2003 1:03 PM
To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE: Excel spreadsheet: creating charts


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]