Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: OLE Excel.Application Vs Spreadsheet::WriteExcel

by furry_marmot (Pilgrim)
on Aug 19, 2010 at 01:01 UTC ( [id://855945]=note: print w/replies, xml ) Need Help??


in reply to OLE Excel.Application Vs Spreadsheet::WriteExcel

OLE is a specific Microsoft technology. It is based on and extends the older DDE protocols. It works on Windows only. Unless it works in WINE and is callable from a Unix/Linux process, you will not be able to use it.

I have used Spreadsheet::WriteExcel a lot, but not to create graphs. Just read the docs for starters.

If that is not sufficient for your needs, consider writing two spreadsheets. One is a pre-created template that you copy and rename as necessary. The other is a data file. When you load the template, it will have a VBA script to load the pre-formatted-by-your-Perl-script data, which will automatically update the associated graph.

I hope this helps

--marmot

Update: I've decided my reply might not have been as clear as I meant it, and I see no one else has quite hit this point, so here it is: OLE is a Windows API. It doesn't work on non-Windows systems. So that should help clarify what you can use right there.

OLE, with respect to Excel, is used as a form of automation. When creating a spreadsheet via Excel, it looks like magic hands open Excel, enter data, create graphs, etc. It's pretty easy, and VERY impressive to newbs and managers. :-) You can actually hide the updates while it's running, and that runs faster, but it's not as cool-looking.

For what it's worth, it's pretty cool. Excel becomes an OLE server -- you send it commands and it does what you tell it. Technically, you create a Workbook object, then a Worksheet object inside that, then address cells, entering data or formatting them, creating charts, etc. You have absolute control over Excel. The easiest way to get started is to record a macro in Excel, doing whatever you need to do, and then save it as a Visual Basic for Applications script. It's very straightforward to convert it to Perl. You can enter data into cells, say A10-C10 through A20-C20, then find the relevant lines in the VBA script. Copy one of those into your script and convert it to A$row-C$row and write a loop to fit your current data.

You end up writing a Perl wrapper around a VBA script, which is only a drawback if you want to do something more Perlish. If you're working on Windows and you're using Perl to generate your data, it's a fine way to go, especially if you're getting your feet wet with Perl but are more familiar with VBA. The other potential drawback is, again, that it's Windows only.

Spreadsheet::WriteExcel creates a binary file and doesn't care what platform it's on. It's mature, well-tested, and very solid. It creates an Office 97 .xls file that you can open in current versions Excel or OpenOffice. I use Office 2007 with a variety of different .xls and .xlsx formats people send me, and it's completely transparent; thus you shouldn't need to worry about that.

From the docs, it looks like it can insert charts, with control over the type of chart, data rows and columns, chart and axis titles, etc. It should do what you need. Try some of the examples.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://855945]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2024-03-29 14:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found