TableOfContents(3) Keywords: OpenOffice, Python, Uno, Templates, Document Generation, FoxPro Forms, vb Forms, Word Forms,

Install

aptitude update
aptitude install openoffice.org

aptitude install python-uno

OpenOffice and Python

Start openoffice

openoffice -accept="socket,host=localhost,port=2002;urp;"

Connect to OpenOffice

python

import uno

local = uno.getComponentContext()

resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)

context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")

Modify Content

Load Services

desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)

Load Current Document

document = desktop.getCurrentComponent()

set cursor

cursor = document.Text.createTextCursor()

Load New Document

document = desktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, ())
cursor = document.Text.createTextCursor()

Load Existing Document

document = desktop.loadComponentFromURL("file:///home/lucas/myfile.odt" ,"_blank", 0, ())
or
document = desktop.loadComponentFromURL("file:///c:"+filepath+".odt" ,"_blank", 0, ())

Modifying text

document.Text.insertString(cursor, "This text is being added to openoffice using python and uno package.", 0)

Line breaks

document.Text.insertString(cursor, "\n\nThis is a new paragraph.", 0)

Indent

document.Text.insertString(cursor, "\n\n\tAnd this is another new paragraph.", 0)

Change Font property

cursor.getPropertyValue("CharHeight")

cursor.setPropertyValue("CharHeight", 20)
cursor.setPropertyValue("CharFontName", "Arial")
cursor.setPropertyValue("CharWeight", 150)

Create Table

mytable = document.createInstance("com.sun.star.text.TextTable")

mytable.initialize(6,2)

document.Text.insertTextContent(cursor, mytable, 0)

mytable.getCellByName("A1").setString("This is first column")
mytable.getCellByName("B1").setString("This is second column")

mytable.getCellByName("A2").setString("Row 2 is here")
mytable.getCellByName("B2").setString("Here is column 2")
mytable.getCellByName("A3").setString("This manual")
mytable.getCellByName("B3").setString("You see")
mytable.getCellByName("A4").setString("was")
mytable.getCellByName("B4").setString("how easy")
mytable.getCellByName("A5").setString("Made in")
mytable.getCellByName("B5").setString("it is ")
mytable.getCellByName("A6").setString("Chicago")
mytable.getCellByName("B6").setString("to do things like this!")

Save

Save Changes

document.store()

Save file as

document.storeAsURL("file:///home/lucas/myfile2.odt",())

Exit the file

document.dispose()

Manipulate

Find and Replace

import string
#Create Search Descriptor
search = document.createSearchDescriptor()
#What to search for
search.SearchString = u"Lucas"
#Found string
found = document.findFirst( search )

while found:
    found.String = string.replace( found.String, u"Lucas", u"NewLucas" )
    found = document.findNext( found.End, search)
#Save
document.store()

search = document.createSearchDescriptor()
search.SearchString = "search for"
search.SearchCaseSensitive = True
search.SearchWords = True
or
search = document.createSearchDescriptor()
search.SearchRegularExpression = True
search.SearchString = "\\<(k|s|v|z|o|u|i|a) "

Save to PDF

from com.sun.star.beans import PropertyValue
property = (
    PropertyValue( "FilterName" , 0, "writer_pdf_Export" , 0 ),
)
document.storeToURL("file:///home/lucas/myfile2.pdf",property)

Examples

Create Document

Find and Replace

"""This program will do 'find and replace', and save an output to a different file"""
#You should have openoffice listening on specified port already.
#Load necessary items
import uno
local = uno.getComponentContext()
resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)
context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)

#Load file template
document = desktop.loadComponentFromURL("file:///home/lucas/TemplateLetter.odt" ,"_blank", 0, ())
cursor = document.Text.createTextCursor()


#----------Start doing stuff--------
import string

#Create Search Descriptor
print 'starting a search'
search = document.createSearchDescriptor()

def findandreplace(document=document,search=search,find=None,replace=None):
    """This function searches and replaces. Create search, call function findFirst, and finally replace what we found."""
    #What to search for
    search.SearchString = unicode(find)
    #search.SearchCaseSensitive = True
    #search.SearchWords = True
    found = document.findFirst( search )
    if found:
        print 'Found %s' % find
    while found:
        found.String = string.replace( found.String, unicode(find),unicode(replace))
        found = document.findNext( found.End, search)


#Create data structure of what will get replaced. I assume that "$FirstName", etc. are already in you odt file.
data={}
data['$FirstName']='Lucas'
data['$LastName']='Mylastname'
data['$Address']='123 Main St.'
data['$City']='Chicago'
data['$State']='IL'
data['$Zipcode']='60645'
data['$TodaysDate']='20080821'
data['$DOB']='My Date of Birth'

#Do a loop of the data and replace the content.
for find,replace in data.items():
    findandreplace(document,search,unicode(find),unicode(replace))
    print find,replace


#Save document
document.storeAsURL("file:///home/lucas/letter2.odt",())
#Close
document.dispose()

SqlAlchemy and OpenOffice

set paste

"""This program Gets records from sqlalchemy and replaces templates fields inside OpenOffice
Copyright Lukasz Szybalski szybalski@gmail.com 
License: GNU General Public License 2 (GPL2)"""
#Following is if we are importing a file, or setting it up in a cronjob. This will switch folder to where the .py is located at.
import os
workfolder=os.path.dirname(__file__)
if workfolder:
    print __file__
    os.chdir(workfolder)

#Start Logging error
import logging
logging.basicConfig()
#Setup config
log = logging.getLogger("MyApp")
log.setLevel(logging.DEBUG) #set verbosity to show all messages of severity >= DEBUG
#log.setLevel(logging.INFO) #set verbosity to show all messages of severity >= DEBUG
log.info("Starting my app")

import sqlalchemy
#Mysql
e = sqlalchemy.create_engine('mysql://user:pass@hostname/db_name')
#Postgres
#e = sqlalchemy.create_engine('postgres://user:pass@hostname/db_name')
#Mssql
#e = sqlalchemy.create_engine("mssql://user:pass@hostname:1433/db_name?driver=TDS&odbc_options='TDS_Version=8.0'")
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
log.info('Connection Set')

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
#Connected to a database
log.info("Connected to a Database")


#----Table Definition and Python object that maps to it-------
from sqlalchemy.orm import mapper
#Addressbook master File
addressbookrecords_table = sqlalchemy.Table('Addressbook', metadata, autoload=True)
#Python Object
class AddressbookRecords(object):
    pass
#Mapper
mapper(AddressbookRecords,addressbookrecords_table)
log.info('Connected to a database, and have all the field names')


#---------Start of OpenOffice Section------------
"""This progrma will do find and replace and save an output to a different file"""
#Load necessary items
import uno
import sys
#You need to set few variables when using OpenOffice
local = uno.getComponentContext()
resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)
#One of the variables is context which connects to openoffice. We test here if open office is listenting, if not we start it.
try:
    #Am I able to connect?
    context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
    log.debug('Connecting to OpenOffice')

except Exception ,e :
    #If I got the error
    if 'connect to socket' in str(e):
        import os
        #Start it 
        os.system('''/usr/bin/openoffice -accept="socket,host=localhost,port=2002;urp;"''')
        import time
        #Wait 3 seconds for openoffice to load.
        time.sleep(3)
        #See if we can connect again
        context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
        log.debug('OpenOffice was not opened. I have openned it and connected')
#Rest of the necessary variables.
desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)

log.debug('Done Setting up all OpenOffice Variables')

#----------Start manipulating openoffice document--------
import string

#We will use this function to find and replace things on the page.
def findandreplace(document=None,search=None,find=None,replace=None):
    """This function searches and replaces. Create search, call function findFirst, and finally replace what we found."""
    #What to search for
    search.SearchString = unicode(find)
    #search.SearchCaseSensitive = True
    #search.SearchWords = True
    found = document.findFirst( search )
    if found:
        #log.debug('Found %s' % find)
        pass
    while found:
        found.String = string.replace( found.String, unicode(find),unicode(replace))
        found = document.findNext( found.End, search)

#This will be our mini file definition. Here we can specify which fileds get replaced.
from datetime import datetime
def replace_data(record=None):
    """Key and Value for replacement. Keys are defined by you values come from sqlalchemy. Here we define what $FirstName in odt file will be replaced with."""
    data={}
    data['$FirstName']=record.FIRSTNAME
    data['$LastName']=record.LASTNAME
    data['$Address']=record.ADDRESS_1
    data['$City']=record.CITY.strip()
    data['$State']=record.STATE
    data['$Zipcode']=record.ZIP_CODE
    data['$TodaysDate']=datetime.now().date()
    data['$User']=record.USER_NO
    data['$CaseID']=str(record.CASE_NUMBER)
    return data


#Lets import page break as we will be doing a loop
from com.sun.star.style.BreakType import PAGE_BEFORE, PAGE_AFTER

#Lets create a new document (document2 and cursor2) that we will use to save our replaced pages to.
document2 = desktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, ())
cursor2 = document2.Text.createTextCursor()

#Save the document.
document2.storeAsURL("file:///home/lucas/ReminderPages.odt",())
log.info('Created New Document: ReminderPages.odt')

#Selecting a date for my letters. All records created 35 Days ago.
from datetime import timedelta
days35=timedelta(days=35)
day35=datetime.now().date()-days35
import time
day35a=time.strftime('%Y%m%d',day35.timetuple())

#Select the records from the Database.
allrecords=session.query(AddressbookRecords).filter(AddressbookRecords.CASE_OPEN_DATE==day35a).order_by(AddressbookRecords.USER_NO).all()

#Print how many records we have
log.info('We have %s Records from the database.' % len(allrecords))

#Lets start the loop
log.debug('Starting to Loop Through Records')
for record in allrecords:
    #Load file template
    document = desktop.loadComponentFromURL("file:///home/lucas/ReminderPagesTemplate.odt" ,"_blank", 0, ())
    #We don't need a cursor since we will be searching and replacing only
    #cursor = document.Text.createTextCursor()

    #Create Search Descriptor
    search = document.createSearchDescriptor()
    
    #Pass in a sqlalchemy record to our recplace_data which will fill in the values for each field we want. We will use these values to replace inside the odt.
    data=replace_data(record)
    
    #Do a loop of the data and replace the content.We pass in a dictionary data which has keys and values. We find keys and replace them with values.
    for find,replace in data.items():
        findandreplace(document,search,unicode(find),unicode(replace))
        log.debug(str(find)+','+str(replace))
    #Save replaced document as a temporary file.
    #Trying to find out how to skip this part but for now we do it.
    document.storeAsURL("file:///home/lucas/temp.odt",())
    #Close File
    document.dispose()
    
    #We now append our temp file to our document that will hold all pages. 
    cursor2.gotoEnd(False)
    cursor2.BreakType = PAGE_BEFORE
    cursor2.insertDocumentFromURL("file:///home/lucas/temp.odt", ())
    document2.store()
    #2nd copie
    #cursor2.gotoEnd(False)
    #cursor2.BreakType = PAGE_BEFORE
    #cursor2.insertDocumentFromURL("file:///home/lucas/temp.odt", ())
    #document2.store()

#Exit
document2.dispose()
log.info('Done.I am exiting')

References

  1. http://www.devshed.com/c/a/Python/Python-and-OpenOfficeorg/

  2. http://api.openoffice.org/docs/common/ref/com/sun/star/module-ix.html

  3. http://api.openoffice.org/docs/common/ref/com/sun/star/uno/module-ix.html