Python and Open Office

[Maric Michaud]

Le Wednesday 10 September 2008 22:04:57 Greg Lindstrom, vous avez écrit :
> Hello,
>
> I would like to create and manipulate Open Office documents using Python. 
> I have found then UNO Python page and odfpy modules which seem to be
> exactly what I need.  The odfpy manual is, to me, a confusing list of
> objects and methods (it's an impressive list!), but does not have much in
> the way of how to use them.  For example, I can open a spreadsheet and
> create new pages (there's a nice example near the back of the manual) but I
> can't figure out how to open an existing spreadsheet and list the names of
> the individual sheets ("tabs").
>
> I have written an application that access Microsoft Excel and creates
> reports for work, but would like to create an Open Source version using
> Open Office and release it to the community (and maybe get a talk at PyCon
> :-).
>
> Is there someone here who can help me out, or is there an appropriate
> mailing list for me to join?
>
> Thanks
>
> --greg


I don't like the UNO solution, first, UNO API is not that practical, second 
you'll need to have a running instance of openoffice, which I wouldn't want 
to manage for a server application in term of performance, reliability and 
security.

I had to produce text document within a zope application once, and did this by 
replacing variables values in a template directly in the xml, the function 
was no more than twenty lines long, and used only stdlib modules (see below). 
A far better approach IMHO.

Of course for big manipultaion it could be more error prone and it's a good 
news that odfpy exists (I didn't use for my example because it was too 
simple), so I give it a try :

>>>[1]: import odf.opendocument

>>>[2]: import odf.table

>>>[3]: new = odf.opendocument.OpenDocumentSpreadsheet()

>>>[4]: for n in ('titi', 'tata', 'toto') : 
new.spreadsheet.addElement(odf.table.Table(name=n))
   ...:

>>>[5]: new.save("toto.ods")

>>>[6]:
maric at redflag1 17:10:27:~/odfpy-0.8$ ooffice toto.ods # document ok !
maric at redflag1 17:10:31:~/odfpy-0.8$ ipython

>>>[12]: import odf.table

>>>[13]: import odf.opendocument

>>>[14]: for t in old.spreadsheet.getElementsByType(odf.table.Table) :
    print t.getAttribute('name')
   ....:
   ....:
titi
tata
toto

Great !


This was the function I use (from a local copy of the product I backed up, I 
have not the application working nor the svn server so I didn't test this 
version, but it's mainly the idea that is interesting) :
    
    def updateVariablesInSXW(self, variables, sxw) :
        from zipfile import ZipFile
        from StringIO import StringIO
        from xml.dom.minidom import parseString
        sxw = StringIO(sxw)
        zip = ZipFile(sxw, 'a')
        dom_document = parseString(zip.read('content.xml'))
        dom_styles = parseString(zip.read('styles.xml'))
        def update_variables(node) :
            if node.nodeName in ('text:variable-set','text:variable-get') :
                variable = node.attributes['text:name'].value
                if variable in variables :
                    value = variables[variable].decode('utf-8')
                    if node.attributes.has_key('text:value') :
                        node.attributes['text:value'].value = value
                    if node.attributes.has_key('text:string-value') :
                        node.attributes['text:string-value'].value = value
                    if node.firstChild is not None : 
                        node.firstChild.nodeValue = value
            for i in node.childNodes :
                update_variables(i)
        for i in (dom_document, dom_styles) :
            update_variables(i)
        zip.writestr('content.xml', dom_document.toxml().encode('utf-8'))
        zip.writestr('styles.xml', dom_styles.toxml().encode('utf-8'))
        sxw.seek(0)
        return zip.read()


and a sample use (code frome a Zope product, facture is french term for 
invoice) :

    security.declarePrivate('prepareFacture')
    def prepareFacture(self) :
        import math
        price, devise = self.getProgramme().getPrice().split()
        end = self.getProgramme().end()
        start = self.getProgramme().start()
        subscriber = self.getSubscriberInfo()
        variables = {
            'fullname' : subscriber.fullname,
            'address' : subscriber.address,
            'company' : subscriber.company,
            'price' : price,
            'quantity' : str(1),
            'duration' : str(int(math.ceil(end - start))),
            'ht' :  price + ' ' + devise,
            'ttc' : str(float(price)*1.196) + ' ' + devise,
            'tva' : str(float(price)*0.196) + ' ' + devise,
            'cours' : self.getProgramme().title_or_id(),
            'location' : self.getProgramme().getLocation().title_or_id(),
            'start' : self.toLocalizedTime(start),
            'end' : self.toLocalizedTime(end),
            #'timetable' : self.getProgramme().getTimetable(),
            'num_facture' : self.getNumFacture(),
            'cp' : subscriber.postal,
            'country' : subscriber.country,
            'city' : subscriber.city,
        }
        if subscriber.country.strip().lower() == 'france' :
            template_name = 'ModeleFactureFormationTVA.sxw'
        else :
            template_name = 'ModeleFactureFormation.sxw'

        template = str(getattr(self, template_name))
        facture = self.updateVariablesInSXW(variables, template)
        self.setFactureSXW(facture, filename="facture_%s.sxw" %
                                              self.getNumFacture())


-- 
_____________

Maric Michaud