PyUNO - quick minor editing of an xls report from Python

Quick and easy

Not so long ago, I was faced with the need to record a list of changes in our software. The customer sent me a form, which I had to fill out in accordance with their internal documentation requirements. I opened the file " Изменения 1.xls" attached to the letter and was a little depressed. More precisely, the thought of dismissal, and then of suicide, came to my mind. The form consisted of 14 columns. Quickly multiplying in my mind the number of columns with the number of atomic changes we made (about five hundred), I went to smoke.
I cannot do such work with my hands. Most of the data (numbers of new versions, descriptions of changes, etc.) I, of course, were available, but in different places and in the most bizarre formats. But seven hundred copy-paste - fire. So I had to learn a little PyUNO. Just in case - I will briefly describe the process of managing a document OOofrom a Python binding, who suddenly comes in handy.

We need it ourselves Open Office, pythonand, in fact, bindings:

$ sudo yum search pyuno ure

We start OOo(we need spreadsheets, but the method works for all applications - see example 2) with the option “listen to socket” turned on:

$ oocalc "-accept=socket,host=localhost,port=8100;urp;" &
$ soffice "-accept=socket,host=localhost,port=8100;urp;" -writer -headless &

... and set off to write code. To get started - get the document instance:

import uno
from os.path import abspath, isfile, splitext
def getDocument(inputFile) :
  localContext = uno.getComponentContext()
  resolver = localContext.ServiceManager.createInstanceWithContext( \
    "com.sun.star.bridge.UnoUrlResolver", localContext)
  try:
    context = resolver.resolve( \
    "uno:socket,host=localhost,port=%s;urp;StarOffice.ComponentContext" % 8100)
  except NoConnectException:
    raise Exception, "failed to connect to OpenOffice.org on port %s" % 8100
  desktop = context.ServiceManager.createInstanceWithContext( \
    "com.sun.star.frame.Desktop", context)
  document = desktop.loadComponentFromURL( \
    uno.systemPathToFileUrl(abspath(inputFile)), "_blank", 0, tuple([]))

I will leave the receipt of data for filling out the final document from text files and change-logs beyond the scope of this note. Let them simply appear in a pseudo data-field with a wave of a magic wand. So, let's start filling out our document (fill column number 2):

from com.sun.star.beans import PropertyValue
def fillDocument(inputFile, col, data) :
  try:
    sheet = getDocument(inputFile).getSheets().getByIndex(0)
    row = 2
    while True:
      row = row + 1
      val = sheet.getCellByPosition(col, row).getFormula()
      if val != '' :
        sheet.getCellByPosition(col, row).setFormula(val.replace(%VERSION%, data))
      else :
        break;
    '''
    All the rows are now filled, It's time to save our modified document
    '''
    props = []
    prop = PropertyValue()
    prop.Name = "FilterName"
    prop.Value = "MS Excel 97"
    props.append(prop)
    document.storeToURL(uno.systemPathToFileUrl(abspath(inputFile)) + ".out.xls", tuple(props))
  finally:
    document.close(True)

Similarly, you can do with any other column. You can even go to Google Translate on the road for a translation .

Some more useful features

Insert another document

'''
Required for Ctrl+G :-)
'''
from com.sun.star.style.BreakType import PAGE_BEFORE, PAGE_AFTER
def addAtTheEnd(inputFile) :
  cursor.gotoEnd(False)
  cursor.BreakType = PAGE_BEFORE
  cursor.insertDocumentFromURL(uno.systemPathToFileUrl(abspath(inputFile)), ())

Search and Replace

def findAndReplace(pattern, substTo, replaceAll, caseSensitive) :
  search = document.createSearchDescriptor()
  search.SearchRegularExpression = True
  search.SearchString = pattern
  search.SearchCaseSensitive = caseSensitive
  result = document.findFirst(search)
  while found:
    result.String = string.replace(result.String, pattern, substTo)
    if not replaceAll :
      break
    result = document.findNext(result.End, pattern)

Export to PDF

def exportToPDF(outputFile)
  props = []
  prop = PropertyValue()
  prop.Name = "FilterName"
  prop.Value = "writer_pdf_Export"
  props.append(prop)
  document.storeToURL(uno.systemPathToFileUrl(abspath(outputFile)), tuple(props))

Disclaimer

I want to make a reservation right away: the code claims to be the title of knee-patten govnokoda, executed once. But as “let’s quickly update the report file” - I personally saved a lot of time already.

- Here and around you can collect some more bits of information:  http://wiki.services.openoffice.org/wiki/Uno/FAQ
- Python template engine for OOo: appyframework.org
- The same thing, only for C ++: habrahabr.ru/blogs/cpp/116228

Upd: In the comments suggest another way: xlwt .
Upd2: To generate in a more "newfangled" xlsx format, there is such a useful lib: xlsx.dowski.com .
For both additions - many thanks to tanenn .