In my monthly Win32 Perl column at www.winscriptingsolutions.com I wrote
about doing just this: creating charts in Excel and saving them as a GIF
file. The article is
http://www.winscriptingsolutions.com/Articles/Index.cfm?ArticleID=22049 (you
need a subscription to read this, however columns older than a year are
publicly available).
The code from the script is as follows:
use Win32::OLE qw( with in );
use Win32::OLE::Const "Microsoft Graph";
my $TIME = time();
my $VISIBLE = 1;
my %ChartOptions = (
width => 640,
height => 400,
haslegend => 0,
type => xl3DLine,
perspective => 30,
rotation => 20,
autoscaling => 1,
rightangleaxes => 1,
title => "Our test graph",
);
my( @CELLS ) = ( 'a'..'zz' );
my $File = "C:\\temp\\test.gif";
my @Data = GetData();
my $DAY = 60 * 60 * 24;
# BEGIN CALLOUT A
my $ChartApp = new Win32::OLE( "MSGraph.Application", "Quit" ) ||
die "Cannot create object\n";
# END CALLOUT A
$ChartApp->{Visible} = $VISIBLE;
# BEGIN CALLOUT B
my $DataSheet = $ChartApp->DataSheet();
my $Chart = $ChartApp->Chart();
# END CALLOUT B
foreach my $Option ( keys( %ChartOptions ) )
{
$Chart->{$Option} = $ChartOptions{$Option};
}
# BEGIN CALLOUT C
my $iTotal = scalar @Data;
my $iIndex;
foreach my $iIndex ( 0 .. $iTotal - 1 )
{
my $Datum = $Data[$iIndex];
my @Time = localtime( $TIME + $DAY * $iIndex );
my $Percent = int( ( $iIndex + 1 ) * 100 / $iTotal );
my $Date = sprintf( "%04d.%02d.%02d",
$Time[5] + 1900,
$Time[4] + 1,
$Time[3] );
$DataSheet->Range( "$CELLS[$iIndex]0" )->{Value} = $Date;
$DataSheet->Range( "$CELLS[$iIndex]1" )->{Value} = $Data[$iIndex];
print "\rPlotting: $Percent%";
}
# END CALLOUT C
print "\n";
# Configure the X axis.
if( my $Axis = $Chart->Axes( xlCategory ) )
{
$Axis->{HasMajorGridlines} = 0;
$Axis->{TickLabels}->{orientation} = xlUpward;
with( $Axis->{TickLabels}->{Font},
Name => "Tahoma",
Bold => 0,
Italic => 0
);
}
# Configure the Y axis.
if( my $Axis = $Chart->Axes( xlValue ) )
{
$Axis->{HasMajorGridlines} = 1;
$Axis->{MajorGridlines}->{Border}->{Weight} = 1;
$Axis->{MajorGridlines}->{Border}->{ColorIndex} = 48;
$Axis->{MajorGridlines}->{Border}->{LineStyle} = xlContinuous;
with( $Chart->Axes( xlValue )->{TickLabels}->{Font},
Name => "Tahoma",
Bold => 0,
Italic => 0
);
}
# BEGIN CALLOUT D
# Configure data-point labels.
$Chart->SeriesCollection( 1 )->{HasDataLabels} = 1;
if( my $Labels = $Chart->SeriesCollection(1)->DataLabels() )
{
with( $Labels,
NumberFormat => "#.0",
Type => xlDataLabelsShowValue
);
with( $Labels->{Font},
Name => "Tahoma",
Bold => 0,
Italic => 0,
);
}
if( defined $ChartOptions{title} )
{
$Chart->{HasTitle} = 1;
$Chart->{ChartTitle}->{Text} = $ChartOptions{title};
$Chart->{ChartTitle}->{Font}->{Name} = "Tahoma";
$Chart->{ChartTitle}->{Font}->{Size} = 18;
}
# Remove consecutive redundant data-point labels.
$iTotal = $Chart->SeriesCollection( 1 )->Points()->{Count};
$iIndex = 0;
my $PrevText = "";
foreach my $Point (in( $Chart->SeriesCollection( 1 )->Points()))
{
my $Percent = int( ++$iIndex * 100 / $iTotal );
my $Text = $Point->{DataLabel}->{Text};
$Point->{MarkerStyle} = xlMarkerStyleDot;
$Point->{DataLabel}->{Font}->{Background} = xlBackgroundOpaque;
$Point->{DataLabel}->{Top} -= 12;
$Point->{HasDataLabel} = 0 if( $Text eq $PrevText );
$PrevText = $Text;
print "\rFormatting: $Percent%";
}
# END CALLOUT D
print "\n";
print "Exporting to GIF file: $File\n";
# BEGIN CALLOUT E
$Chart->Export( $File, "GIF", 0 );
# END CALLOUT E
`start "" "$File"`;
sub GetData
{
my @Data;
my $iIndex = 10;
srand( time() );
while( --$iIndex )
{
push( @Data, rand( 100 ) );
}
return( @Data );
}
-----Original Message-----
From: Richard Lippmann [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 02, 2002 11:51 AM
To: [EMAIL PROTECTED]
Subject: OLE/Excelgraphics to BMP in Perl?
I want to create some nice graphics in Excel 97 and save these grahpics as
GIF, BMP or so. Creating the graphics with OLE is easy when I use an
existing document, I only have to change the values in the excel-sheet, the
graphic which depends on the cells changes immediately.
use Win32::OLE;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application');
my $Sheet = $Excel->Application->ActiveSheet();
my $Range = $Sheet->Range("B6:E6");
$Range->{Value} = [ 23, 28, 30, 34 ];
The problem is that I cannot save the graphics in the Excelsheet. Maybe a
way is to copy the graphics into the buffer (ctrl-c) and paste it to some
graphics-application which could save the graphics.
This sounds very strange but I think I can use a lot of windows programms to
do the dirty work and get good graphical good results (diagrams of
temperature, generating nice-looking pdf-files from excel, election-charts
from excel).
From Bavaria, Germany
Horshack
--
http://lena.franken.de
_______________________________________________
Perl-Win32-Admin mailing list [EMAIL PROTECTED]
To unsubscribe:
http://listserv.ActiveState.com/mailman/listinfo/perl-win32-admin
_______________________________________________
Perl-Win32-Admin mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/listinfo/perl-win32-admin
