Syntactic Confectionery Delight | |
PerlMonks |
comment on |
( [id://3333]=superdoc: print w/replies, xml ) | Need Help?? |
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
ResourcesYou 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 here). Mine was conveniently located in:
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 jeffa and dws Update - Added more snippets regarding inserting sheets in a specific order. The CodeFirst, 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 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. Now, we'll add a new WorkBook to the collection of our very own and immediately save it with our predeclared file name Of Course, we could just as easily opened an existing file to work with like so: 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. 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. 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.
And now for a brief example using a Variant data type and it's methods. Update: Added other variant as per example in this node Now for the SnippetsIn an earlier thread I suggested that the monk 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
'IN' Example
Finding the last Column and RowKnowing 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.
Adding a Chart ObjectYou have all this data, what if you want to create some dynamic Charts?
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.
Running a MacroAlthough I would rewrite all of the VBA Marcos in Perl (*grin*) if you want to leave old sleeping code dogs lie.Fitting to one landscaped page So, you say your boss wants to see it all on one page. *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:In the first spot: After (or before) a specific sheet - Note, you can do this by index or by sheet name: 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-. In reply to Using Win32::OLE and Excel - Tips and Tricks by cacharbe
|
|