Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

OpenOffice Calc/Spreadsheets & Win32::OLE

by dallen16 (Sexton)
on Nov 01, 2010 at 19:58 UTC ( #868874=perlquestion: print w/replies, xml ) Need Help??
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 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("") + || die "CreateObject: $!"; # create a new process my $desktop = $objServiceManager->createInstance(" +esktop"); my @propValue = (); $propValue[0] = $objServiceManager->Bridge_GetStruct(" +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 );

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 :).

      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?

        Spreadsheet::WriteExcel isn't suitable for your task but to set the record straight you can use it to Autofit and Autofilter.


        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?
Re: OpenOffice Calc/Spreadsheets & Win32::OLE
by 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 Sinistral (Prior) on Nov 02, 2010 at 12:38 UTC
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>
Re: OpenOffice Calc/Spreadsheets & Win32::OLE
by angiehope (Pilgrim) on Nov 02, 2010 at 13:19 UTC
Re: OpenOffice Calc/Spreadsheets & Win32::OLE
by starrychloe (Initiate) on Jun 24, 2013 at 21:41 UTC
    I see you found my code! - How to program OpenOffice Calc using Perl API w/ OLE and DDE - 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);

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://868874]
Approved by NiJo
Front-paged by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (8)
As of 2017-03-26 00:55 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (313 votes). Check out past polls.