PyUNO - quick minor editing of xls report from Python

Simple and fast

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 despondent. More precisely, thoughts of dismissal consistently came to my head, and then suicide. The form consisted of 14 columns. 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 a job 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-pastes - dismiss. So I had to learn a little PyUNO . Just in case - I will briefly describe the process of managing the OOo document from the Python binding, all of a sudden it will come in handy.

We will need Open Office , python and, in fact, the binding:

 $ sudo yum search pyuno ure 

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

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

... and go to write the code. First of all, let's 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 data to fill out the final document from text files and change-logs beyond the framework of this note. Let them just appear in the pseudo-field data by the wave of a magic wand. So, let's proceed to filling out our document (fill out column No. 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 on the road to Google Translate for the 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 a knee-length knee-strap, performed once. But as “well, let's quickly update the report file” - I personally saved a lot of time already.

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

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