Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Re: Excel module

by dimmesdale (Friar)
on Jul 24, 2002 at 18:59 UTC ( [id://184983] : note . print w/replies, xml ) Need Help??

in reply to Excel module

You can use Win32::OLE.

I have found this module rather helpful for working with Excel. If you want to find out how to do anything, just make an excel macro and look at it -- the VB can be translated to perl VERY easily.

#here's the macro Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess +, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xl +Guess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

If you know the Excel object model you can use that to help. Just take the . and make them method calls, i.e., a.b to a->b. The := assignments are mapped to hashes. So a.b c:=d to a->b( {c => d}).

There're a lot of good resources you can find for this -- I enjoyed the OLE Browser (standard with active perl, I think it's <perl-path>/html/ole-browser


Here's a quick start:

my $file_name = 'FILE_NAME.xls'; my $xl = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "hlpful err msg"; my $book = $xl->Workbooks->Add(); my $sheet1 = $book->Worksheets(1); $xl->{DisplayAlerts} = 0; $sheet1->{Name} = 'YOUR_SHEET_NAME'; $book->SaveAs($file_name);