Other Articles‎ > ‎

Control OpenOffice Through a Program

In the May issue, we discussed programming using Open Office components. This time, we will take a database application using the simplest of databases and storing somewhat complex data in it. We will then extract the desired data and use the spreadsheet to offer a very nice user interface, which we have not had to write.

Many of us in the IT field have one stock which we wish to monitor. So, this is a good example for us to consider. We will normally buy a number of shares at a particular price. Occasionally, we will notice the price of the share and add it to the list of prices for this stock. However, for our viewing, we are interested in the purchase price, the range of prices and the current price.

We will use gdbm or equivalent simple database. These databases store a key and a value, both of which must be strings. Hence, the first part will demonstrate how we store objects easily in such a database. By the way, zodb is an excellent option if a person is interested in using an object database which is very easy to use as well.

So, let us start with our database part of the application. In order to focus on the essentials, the program is poorly written. It leaves out error handling and recovery completely and does not offer the user any flexibility.

import dbm # on 1.1 import gdbm as dbm

db = dbm.open(“shares.db”, “c”)

while 1:

stockCode = raw_input(“Enter the Stock Code(Just Enter to quit) : “)

if not stockCode :


if not stockCode in db.keys(): # A new stock

nStock = input(“The number of stocks purchased : “)

purchasePrice = input(“The Purchase Price : “)

# our first item in stock data is a tuple of number of shares and the purchase price

stockData = [(nStock, purchasePrice)]

else: # we wish to add the current price to the stock data

currentPrice = input(“The current price : “)

# we take the stockdata stored as a string and convert it into an object

stockData = eval(db[stockCode])


# we convert the object into a string and store it

db[stockCode] = stockData.__repr__()


The key concept to notice is that our stockdata is an array consisting of a variable number of items and not all of the same type. Hence, we can store fairly complex structures even in as simple a database as gdbm or Berkeley DB and manipulate it easily. Also, the syntax for using this database is identical to using a dictionary.

Now, our data is safe. We can give the desired data to the user which he can manipulate and process the way he needs. The test below was done with OpenOffice 2 Beta version but works with the stable 1.1 version as well. We will need to use the python interpreter, “programs/python”, in the open office installation directory. For OO-1.1 version, we need to use “import gdbm as dbm” instead of “import dbm” because the Python version is 2.2.

The first thing we need to do is start OpenOffice so that it is listening on a socket.

$ soffice “-accept=socket,host=localhost,port=2002;urp;”

We then connect to it and get the desktop object.

import uno

localContext = uno.getComponentContext()

resolver = localContext.ServiceManager.createInstanceWithContext(

"com.sun.star.bridge.UnoUrlResolver", localContext)

ctx = resolver.resolve(


smgr = ctx.ServiceManager

desktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)

It is worth highlighting at this point that Python code is much easier than the Java examples because Python does not require calls to queryInterface before calling a method in an interface supported by the object.

We need to create a new spreadsheet document and open a new spreadsheet.

xSpreadsheetDocument = desktop.loadComponentFromURL(

"private:factory/scalc", "_blank", 0, ())

xSpreadsheets = xSpreadsheetDocument.getSheets()

xSpreadsheets.insertNewByName("MySheet", 0)

sheet = xSpreadsheets.getByName("MySheet")

We will now select the data for each stock from our database and load it in the spreadsheet.

import dbm # on 1.1 – import gdbm as dbm

db = dbm.open(“shares.db”, “r”)

# insert Column headers

headers = ['StockCode”, “No shares”, “Purchase Price”, “Minimum”, “Maximum”,

“Current Price”, “Profit/Loss”]

row = 1

for col in range(len(headers)):

setCellValue(sheet,col,row, headers[col]) # A utility function see below

# Read from the database and populate the spreadsheet

for stockCode in db.keys():

stockData = eval(db[stockCode])

row += 1

setCellValue(sheet, 0, row, stockCode)

setCellValue(sheet, 1, row, stockData[0][0]) # number of shares

setCellValue(sheet, 2, row, stockData[0][1]) # purchasePrice

setCellValue(sheet, 3, row, min(stockData[1:]) # min excluding 1st col

setCellValue(sheet, 4, row, min(stockData[1:]) # max excluding 1st col

setCellValue(sheet, 5, row, stockData[-1]) # the most recent price

# formula = Bn * (Fn – Cn)

n = str(row + 1)

xCell = sheet.getCellByPosition(6, row)

xCell.setFormula(“= B” + n +”*(F” + n + “ - C” + n + “)”)

We can add a little colour to our spreadsheet and colour-code the stocks based on current performance.

theRow = sheet.getCellRangeByPosition(0, row, 6, row)

if stockData[-1] > 1.5*stockData[0][1]: # Stock Doing well

color = 256*255 # green

elif stockData[-1] < stockData[0][1]: # losing money

color = 256*256*255 # red


color = 225*(1 + 256 + 256*256) # gray

theRow.setPropertyValue(“CellBackColor”, color)

Now, we come to our utility function, setCellValue. It seems un-Pythonic to call a different method depending upon the type of data. Hence, we hide that need in a utility function as follows:

def setCellValue(sheet, col, row, value):

xCell = sheet.getCellByPosition(col,row)

if type(value) == type('x'): # String type?

cellCursor = xCell.createTextCursor()

xCell.insertString(cellCursor, value, False)

else: # assume a numeric value


Needless to add, the utility function should appear in the source file before it is used.

Hopefully, with the previous article and this one, the pattern of programming OpenOffice becomes clearer. Formatting of the document is controlled by setting property values. The interfaces are inherited and used across similar objects. So, method names are common. After we get over the initial speed breakers, OpenOffice no longer appears so difficult to control through a program. It is fun.