This might help - it's some basic code you can use to do a whole host of
things with Excel via Win32::OLE. It's not a script, just a collection of
one-liners that you can past in to your script and modify to do what you
want. Most of it works, but I'm not giving any guarantees :-)
Another way to find out much more is to look through the module browser in
Excel itself. From Excel, hit Alt-F11 to get the VB project window, then F2
to bring up the module browser. That lists all the
methods/properties/constants/general stuff that Win32::OLE will allow you to
do in Excel; the help function usually lists sample code (NB this is in VB,
not Perl, but you can mostly translate the Excel calls from VB to Perl just
by changin '.' to '->')
Play around with it, be impressed at the power Win32::OLE gives you over
Windows apps, explore more into other apps, become a guru on OLE via Perl,
then come back and tell me how to deal with mails attached to mails in
Outlook, 'cos no-one else seems to be able to!
-----Original Message-----
From: Timothy Johnson [mailto:[EMAIL PROTECTED]]
Sent: 31 January 2002 03:20
To: [EMAIL PROTECTED]; Perl-Win32-Admin (E-mail)
Subject: RE: How to transform data into .xls format?
If you are using ActiveState's ActivePerl, check the documentation on
Win32::OLE. It will give you a basic understanding of using OLE to make an
Excel spreadsheet. To find out how to do the rest, record a macro, and then
look at the "source". You should be able to figure out how to do whatever
it is you want to do.
-----Original Message-----
From: jrunnke [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 7:20 PM
To: Perl-Win32-Admin (E-mail)
Subject: How to transform data into .xls format?
Hi
I want to write a perl program that can transform the .txt
data (which seperate each column in comma sign) into .xls
which module i can use,or maybe there are similar codes
I can get??
and also,is it possible to write a perl program that can even
do the excel graph thing??
thanx for your help!
_______________________________________________
Perl-Win32-Admin mailing list
[EMAIL PROTECTED]
To unsubscribe:
http://listserv.ActiveState.com/mailman/listinfo/perl-win32-admin
----------------------------------------------------------------------------
----
This email may contain confidential and privileged
material for the sole use of the intended recipient.
If you are not the intended recipient, please contact
the sender and delete all copies.
_______________________________________________
Perl-Win32-Admin mailing list
[EMAIL PROTECTED]
To unsubscribe:
http://listserv.ActiveState.com/mailman/listinfo/perl-win32-admin
------------------------------------------------------------------------------
This message is intended only for the personal and confidential use of the designated
recipient(s) named above. If you are not the intended recipient of this message you
are hereby notified that any review, dissemination, distribution or copying of this
message is strictly prohibited. This communication is for information purposes only
and should not be regarded as an offer to sell or as a solicitation of an offer to buy
any financial product, an official confirmation of any transaction, or as an official
statement of Lehman Brothers. Email transmission cannot be guaranteed to be secure or
error-free. Therefore, we do not represent that this information is complete or
accurate and it should not be relied upon as such. All information is subject to
change without notice.
##use appropriate modules
use Win32::OLE;
##File handling
#use existing instance of Excel or open new instance
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
#add workbook
$book = $ex->Workbooks->Add;
#add worksheet
$sheet = $book->Worksheets(1);
$sheet->{Name} = "Worksheet No. 1"
#save object
$sheet->SaveAs( "$filename" );
##assigning values
#write to cell
$sheet->Range("A1")->{Value}="1";
#write same value to multiple cells
$sheet->Range("B1:C1")->{Value}="same value";
#write array to range of cells
@array=("North", "South", "East", "West");
$sheet->Range("A2:D2")->{Value}=\@array;
#nb this only works for a 'horizontal' range in Excel (e.g. A2:D2). Writing an array
to a vertical range
# (e.g. A1:A4) causes the first element of the array to be written to each cell
##Size matters
$sheet->Range("B9")->CurrentRegion->Rows->Count;
$sheet->Range("B9")->CurrentRegion->Column->Count;
$LastRow = $sheet->Cells(1,1)->End(xlDown)->{Row};
##pretty it up
$sheet->Columns(2)->{NumberFormat} = '#0';
$sheet->Columns(3)->{NumberFormat} = '#0.0';
$sheet->Range("A1")->Columns->AutoFit;
$sheet->Range("A1")->Font->{ColorIndex} = 3;
$sheet->Range("A1")->Font->{Italic} = TRUE;
$sheet->Range("A1")->Font->{Bold} =TRUE;
$sheet->Range("B1:D1")->{Orientation} = 90;
##miscellaneous
#add comment to cells
$sheet->Range("A1")->AddComment("test");
#subroutine to convert a number into an Excel column (so 0=A, 1=B, 26=AA, 27=AB, 52 =
BA, etc.)
sub xlcolconv {
($xlcolnum)=@_;
$xlcolalph=0;
@alphabet=(A..Z,undef);
while ($xlcolnum > 25) {
$xlcolalph += 1;
$xlcolnum -=26;
}
$xlcol="$alphabet[$xlcolalph-1]"."$alphabet[$xlcolnum]";
}
