Using Win32::OLE and Excel - Tips and Tricks

Using Win32::OLE and Excel - Tips and Tricks
by
on Mar 22, 2002 at 04:56 UTC ( #=perltutorial:  ,  )

My goal with this tutorial is to answer the same repetitive questions dealing with Win32::OLE and Excel that I see so frequently on Perl Monks, as well as the various Perl lists that I frequent. As always, it's a snap-shot of work in progress, and suggestions for improvement or functions you would like to see are always welcome.

I'm going to cover many topics, ranging from dealing with dates and implementing borders, to sizing columns, turning off those pesky warning dialogs, iterating through a collection of worksheets and shading and formatting cells and their contents.

Before I go any further, though, there are some tools and texts that I have found indispensable in my work with Perl and OLE, and I think that I should mention them right from the top.

Books

• by
• by

Both of these books attack the art of using perl in the Win32 environment much more succinctly than I ever could (thank you, Dave Roth), and for a much more in-depth discussion on the "how's", "why's" and "where's", I point you to them and Dave's website, .

Resources You need a good Object Explorer to map the proper methods, objects and properties of each interface. Since I happen to have VB and VC++ on my machine as well, I use the Object Browser that comes standard with VB, and associate the appropriate reference to a project (I actually created an empty project with references to all of the office objects for an easy reference). But for those without those tools, never fear, you still have a chance.

If you are using the ActiveState flavor of Perl, I recommend the "Win32::OLE - Type Library Browser" written by Jan Dubois (who also wrote a nice little tutorial ). Mine was conveniently located in:

\html\site\lib\Win32\OLE\Browswer\Browser.html

What follows is an "Out of the Box" piece of code that should run (well, it does on my machine) with one small change (the path information to reflect your current environment near the CHANGE ME comment). To get the most from the code, feel free to play with the lines that I have commented out, including inserting a graphic into a worksheet.

At the end I have some snippets that didn't really fit in with the complete code, but they are useful in the everyday use of Perl and Excel.

Update - I'm working on a rewrite that removes the comments from the code and puts them in plain text surrounding the lines being discussed, as per a CB conversation with and
UpdateII - Done
UpdateIII - [LN];257757 is a nice resource regarding using OLE in a web environement and some hazards you might face (I'm not sure how to make that a link, given the internal use of square brackets, guidance welcome)

Update - Added more snippets regarding inserting sheets in a specific order.

The Code

#!c:\perl\bin\ use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::Variant; use Win32::OLE::NLS qw(:LOCALE :DATE); $Win32::OLE::Warn = 3; # Die on Errors. # ::Warn = 2; throws the errors, but # # expects that the programmer deals # my $excelfile = '\perltut.xls';

First, we need an excel object to work with, so if there isn't an open one, we create a new one, and we define how the object is going to exit

my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');

For the sake of this program, we'll turn off all those pesky alert boxes, such as the SaveAs response "This file already exists", etc. using the DisplayAlerts property.

$Excel->{DisplayAlerts}=0;

Now, we'll add a new WorkBook to the collection of our very own and immediately save it with our predeclared file name

my $Book = $Excel->Workbooks->Add(); $Book->SaveAs($excelfile); #Good habit when working with OLE, save +often.

Of Course, we could just as easily opened an existing file to work with like so:

my $Book = $Excel->Workbooks->Open($excelfile);

Now we create a reference to a worksheet object and activate the sheet to give it focus so that actions taken on the workbook or application objects occur on this sheet unless otherwise specified.

my $Sheet = $Book->Worksheets("Sheet1"); $Sheet->Activate(); $Sheet->{Name} = "DidItInPerl";

As a first example of inserting data into a WorkSheet and as a preamble to using variants later on I thought we'd use a date value and a piece of text longer than the unformatted cell width.

my ($mday,$mon,$year) = (localtime(time))[3,4,5]; $year += 1900; my $str = $mon.'/'.$mday.'/'.$year; $Sheet->Range("a1")->{Value} = $str; $Sheet->Range("c1")->{Value} = "This is a long piece of text";

This is a study in a few things. The first is iterating over a range and the second is exploring the colors available to us for the interior background color. There are 56 colors in the indexed color palette. If you want a larger palette, you can use the ->{Color} property instead, and pass it an integer value as generated by the VBA function RGB (RGB(Red,Green,Blue)), an exercise I leave to the reader.

Following the iteration, I look at changing the style of a cells text by changing the font and font alignment, as well as changing the widths of the columns and adding gridlines using the WITH method.

foreach my $y(1..56){ my $range = 'b'.$y; $Sheet->Range($range)->Interior->{ColorIndex} =$y; $Sheet->Range($range)->{Value} = $y; } my $range = "A1"; $Sheet->Range($range)->Interior->{ColorIndex} =27; $Sheet->Range($range)->Font->{FontStyle}="Bold"; $Sheet->Range($range)->{HorizontalAlignment} = xlHAlignCenter; my @columnheaders = qw(A:B); foreach my $range(@columnheaders){ $Sheet->Columns($range)->AutoFit(); } $Sheet->Columns("c")->{ColumnWidth}=56; my @edges = qw (xlEdgeBottom xlEdgeLeft xlEdgeRight xlEdgeTop xlI +nsideHorizontal xlInsideVertical); $range = "b1:c56"; foreach my $edge (@edges){ with (my $Borders = $Sheet->Range($range)->Borders(eval($edge)), LineStyle =>xlContinuous, Weight => xlThin , ColorIndex => 1); } #$Excel->ActiveSheet->Pictures->Insert();

And now for a brief example using a Variant data type and it's methods. Update: Added other variant as per example in node

my $dt = Variant(VT_DATE, $Sheet->Range("a1")->{Value}); print "$dt\n"; print $dt->Date(DATE_LONGDATE), "\n"; $Sheet->Range("a2")->{Value} = $dt->Date(DATE_LONGDATE); print $dt->Date("ddd',' MMM dd yy"), "\n"; $Sheet->Range("a3")->{Value}= $dt->Date("ddd',' MMM dd yy"); #Some Properties Take Variant ints or longs - #in this case the Zoom property MUST be set for the #FitToPages(Wide|Tall) to be acknowledged. my $vtfalse = Variant(VT_BOOL, 0); my $vtpages = Variant(VT_I4, 1); with ($Sheet->PageSetup, 'FitToPagesWide'=>$vtpages, 'FitToPagesTall'=>$vtpages, 'Zoom'=>$vtfalse, 'PrintGridlines'=>0, 'LeftHeader'=> "Using Perl and Excel", 'CenterHeader' => "My First Script", 'RightHeader' => "Test", 'LeftFooter' => "Done", 'CenterFooter' => $dt->Date("ddd',' MMM dd yy"), 'Orientation' => xlLandscape, 'RightFooter' => "", 'PrintHeadings'=>0, 'FirstPageNumber'=> xlAutomatic, 'PrintTitleRows' => "1:1"); #$Book->Save(); #$Book = $Excel->Workbooks->Close();

Now for the Snippets In an earlier I suggested that the iterate through the worsheets object using the ordinal postion of each sheet in the collection, however you can also do this by using the 'in' method of the OLE object. I will show both here for examples sake. Range Example

my $sheetcnt = $Book->Worksheets->Count(); foreach (1..$sheetcnt){ print "\t" .$Book->Worksheets($_)->{Name} ."\n"; }

'IN' Example

foreach my $Sheet(in $Book->{Worksheets}){ print "\t" .$Sheet->{Name} ."\n"; }

Finding the last Column and Row Knowing where your data ends is useful. If you want to find the outer edges of the data in your spreadsheet, the following is what I recommend.

my $LastRow = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row}; my $LastCol = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->{Column};

Adding a Chart Object You have all this data, what if you want to create some dynamic Charts?

# Create the chart by dimension # my $Chart = $Sheet->ChartObjects->Add(200, 200, 200, 200);

We're going to use the beauty of the ChartWizard to initialize the data object within the chart object and just give a dummy range. Then, we'll add the bar graphs in after the data object is created.

$Chart->Chart->ChartWizard({Source =>$Sheet->Cells(1)}); $Chart->Chart->SeriesCollection(1)->{Values}= [19,3,24,56,34, 33 +, 16, 10, 3, 100];

Running a Macro Although I would rewrite all of the VBA Marcos in Perl (*grin*) if you want to leave old sleeping code dogs lie.

$Excel->Run($MacroName);

Fitting to one landscaped page

So, you say your boss wants to see it all on one page.

with ($Sheet->PageSetup, Zoom => Variant(VT_BOOL, 0), FitToPagesTall => 1, FitToPagesWide => 1, Orientation => xlLandscape);

*new*Using Before and After with 'Add'

Here are some examples of using before and after to get the sheet exactly where you want it.

In the last spot:

my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets($Book->Wo +rksheets->{Count})}) or die Win32::OLE->LastError();

In the first spot:

my $Sheet = $Book->Worksheets->Add({Before=>$Book->Worksheets(1)}) or +die Win32::OLE->LastError();

After (or before) a specific sheet - Note, you can do this by index or by sheet name:

my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets("Sheet1") +}) or die Win32::OLE->LastError();

I've covered most of the 'Basic' skills I found necessary to get my projects done around the office, but I will attempt to add things as I think of them, or am asked about how they work. Suggestions welcome.

C-.