Download this page: OpenOffice.pdf

Keywords: OpenOffice, Python, Uno, Templates, Document Generation, FoxPro Forms, vb Forms, Word Forms, Microsoft Windows Templates Replacement,

Install

aptitude update
aptitude install openoffice.org
aptitude install openoffice.org-headless

aptitude install python-uno

OpenOffice and Python

Start openoffice

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

You also have an option to start OpenOffice like this:

/usr/bin/openoffice -accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" -norestore -nofirstwizard -nologo -headless

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

http://wiki.alfresco.com/wiki/Running_OpenOffice_From_Terminal#Launching_OpenOffice.org_in_a_headless_.28non-graphical.29_display_environment

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')

Introducing and Implementing OpenOffice in Corporate Environment

Do you know OpenOffice? OpenOffice is just like Microsoft Office, but its made by a different company. Its like Pepsi and Coke, same but different!

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

  4. http://stuvel.eu/ooo-python Additonal Code that can be used. Althouth they talk about different library, the code will be the same as the library only wraps the setup stuff we do to connect.

  5. http://www.linuxjournal.com/content/starting-stopping-and-connecting-openoffice-python Start stop open office from python.

ootools

MyWiki: OpenOffice (last edited 2009-10-01 00:40:58 by LukaszSzybalski)