OpenOffice Calc/Spreadsheets & Win32::OLE

dallen16 has asked for the wisdom of the Perl Monks concerning the following question:

Has anyone been able to write Perl scripts with Win32::OLE that create well formatted, non-trivial spreadsheets using OpenOffice.org or derivatives such as Lotus Symphony 3? ... or by any other means?

I have years of experience creating "all sorts" of complex MS Excel spreadsheets using ActiveState Perl, Win32::OLE and the MS Excel object model. I've been "challenged" to migrate some of this work to OpenOffice 3.x. My initial impression is that it's much more complicated with OpenOffice than with MS Excel. And even some of the basics don't seem to work... like setting the visible / hidden parameter on file open call as such... (or maybe I'm just being dense). Conversely, setting Excel to invisible is commonplace and trivial.

Anyone want to collaborate on developing and posting non-trivial Perl scripts that create and manipulate OpenOffice spreadsheets?

use strict;
use warnings;

use Win32::OLE;
use Win32::OLE::Variant;
use Win32::OLE::Const;

Win32::OLE->Option(Warn => 3); # Turn on warnings for easier debugging

my $objServiceManager = Win32::OLE->new("com.sun.star.ServiceManager")
+ || die "CreateObject: $!"; # create a new process
my $desktop = $objServiceManager->createInstance("com.sun.star.frame.D
+esktop");

my @propValue = ();
$propValue[0] = $objServiceManager->Bridge_GetStruct("com.sun.star.bea
+ns.PropertyValue");
$propValue[0]->{Name} = 'Hidden'; # This does not work!
$propValue[0]->{Value} = 1;

my $calc = $desktop->loadComponentfromUrl("file:///C:/My Documents/Sum
+mary-20101031.ods", "MyCalc", 0, \@propValue );
[download]

Comment on OpenOffice Calc/Spreadsheets & Win32::OLE Download Code

Replies are listed 'Best First'.
Re: OpenOffice Calc/Spreadsheets & Win32::OLE by bluescreen (Friar) on Nov 01, 2010 at 22:21 UTC
Please look at the Spreadsheet::WriteExcel, it's 100% perl module to create .xls files. The good thing is that your code with it is portable to other platforms and you don't depend on com objects - meaning you don't need OpenOffice or MS Excel installed. Unless you need to use a functionality only specific to both software I'd definitely use Spreadsheet::WriteExcel, it even has charts :).	[reply]
Re^2: OpenOffice Calc/Spreadsheets & Win32::OLE by dallen16 (Sexton) on Nov 02, 2010 at 01:24 UTC
I've used Spreadsheet::WriteExcel as well as Win32:OLE to create Excel spreadsheet files (.xls). And over time, I've grown to prefer Win32::OLE to Spreadsheet::WriteExcel. For example, with Win32::OLE, you can use "AutoFit" to set the column width which is not available in Spreadsheet::WriteExcel. In Win32::OLE, you can set "AutoFilter"... The challenge is to create OpenOffice / StarOffice / Lotus Symphony format ".ods" files directly. Yes, it's certainly possible to create .xls files and then convert them using OpenOffice / StarOffice / Symphony to it's native ".ods" format but that's kinda cheating isn't it?	[reply]
Re^3: OpenOffice Calc/Spreadsheets & Win32::OLE by jmcnamara (Monsignor) on Nov 02, 2010 at 09:46 UTC
Spreadsheet::WriteExcel isn't suitable for your task but to set the record straight you can use it to Autofit and Autofilter. -- John.	[reply]
Re^3: OpenOffice Calc/Spreadsheets & Win32::OLE by choroba (Cardinal) on Nov 02, 2010 at 08:52 UTC
A .ods file is just a zip file, containing several files. One of them is an XML document containing the actual spreadsheet. Maybe it would be easier to read the documentation of the XML format or study some examples, and then create the XML file directly using some XML modules?	[reply]
Re: OpenOffice Calc/Spreadsheets & Win32::OLE by Sinistral (Monsignor) on Nov 02, 2010 at 12:38 UTC
I don't have any experience with OO.o automation either, but a relatively quick Googling came up with an example showing how to hide the application. I also came across a VBA example that might help (but I bet you've already found). The Win32::OLE for OO.o surely does seem very different from Excel (I've dabbbled with that myself, because at the time Win32::OLE was much faster than Spreadsheet::WriteExcel). Good luck with your project! Aside: It won't help your project, but I came across an excellent presentation on translating VBA to Perl Win32::OLE. I wonder if that was done by Corion?	[reply]
Re: OpenOffice Calc/Spreadsheets & Win32::OLE by locked_user sundialsvc4 (Abbot) on Nov 02, 2010 at 12:52 UTC
Writing an XML file is often the easiest way. Start with an existing spreadsheet template, with named cell-ranges and so on, formatted “just the way you like it” except for data. Save this template as XML. Now, use standard XML/XPath techniques. Locate the named cell-ranges in this way and update them as needed. I advise strongly that you should write your code in this way, using a “beautiful empty spreadsheet” as its input, rather than writing complex Perl code to generate those extras. Someone should be able to change the appearance of the spreadsheet without changing your code, ’cuz you can guarantee that this sort of thing will happen constantly. (“Dress for success.” Management tends to care a whole lot more for how the spreadsheet looks than for ...) If you save (or send) the XML data with the usual `.XLS` file extension, It Just Works.™
Re: OpenOffice Calc/Spreadsheets & Win32::OLE by thargas (Deacon) on Nov 02, 2010 at 13:33 UTC
Or perhaps you could use OpenOffice::OOCBuilder which looks to be intended for exactly that purpose. It's not using OLE, but is that a real requirement? <disclaimer> I've not used it, just looked it up on CPAN</disclaimer>	[reply]
Re: OpenOffice Calc/Spreadsheets & Win32::OLE by angiehope (Pilgrim) on Nov 02, 2010 at 13:19 UTC
Hi! How about Spreadsheet::Read? Best wishes	[reply]
Re: OpenOffice Calc/Spreadsheets & Win32::OLE by starrychloe (Initiate) on Jun 24, 2013 at 21:41 UTC
I see you found my code! http://www.oooforum.org/forum/viewtopic.phtml?t=100205 - How to program OpenOffice Calc using Perl API w/ OLE and DDE http://stackoverflow.com/questions/278090/how-can-i-create-or-read-openoffice-spreadsheets-from-perl/2780194#2780194 - How can I create or read OpenOffice spreadsheets from Perl? If you look down a bit more, you'll see how to hide the window: `# How to hide, as loading the document hidden does not work. $calc->getCurrentController->getFrame->getContainerWindow()->setVisibl +e(0);` [download]	[reply] [d/l]