Python. Working with xls using xlwt/xlrd and python-uno · English

I needed to modify xls documents without losing any data or functionality. In other words, we have several sheets in a document, and one of the sheets has filters, lists, and other components of modern Excel.

The first thing that comes to mind is good old Python Excel (xlwt/xlrd). Let’s try modifying a cell with this tool, and then move on to PyUNO.

Python Excel: xlwt, xlrd, xlutils

You can read more about these packages on the Python Excel page.
First, let’s install them:


sudo pip install xlwt xlrd xlutils

And now, a code example:


import xlwt, xlrd
from xlutils.copy import copy as xlcopy

source_filename = "/tmp/example.xls"
destination_filename = "/tmp/example_new.xls"

read_book = xlrd.open_workbook(source_filename, on_demand=True)  # Открываем исходный документ
read_sheet = read_book.get_sheet(0)  # Читаем из первого листа
write_book = xlcopy(read_book)  # Копируем таблицу в память, в неё мы ниже будем записывать
write_sheet = write_book.get_sheet(0)  # Будем записывать в первый лист
write_sheet.write(0, 0, read_sheet.cell_value(0, 0) + 42)  # Прибавим к значению из ячейки "A1" число 42
write_book.save(destination_filename)  # Сохраняем таблицу

This very simple usage example will damage our document if it contains formulas, filters, and other spreadsheet components, so this solution won’t work for us.

PyUNO

Now let’s try python-uno. We’ll install it too, and we’ll also need to install libreoffice, since that’s where we’ll actually be making the changes:


sudo apt-get install python-uno libreoffice

The python-uno and libreoffice packages can be installed on different hosts, since the former will act as a client for the latter.

Now let’s start libreoffice on the local host:


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

Here’s a code example that modifies a single cell through PyUNO:


import uno

source_filename = "/tmp/example.xls"
destination_filename = "/tmp/example_new.xls"

def get_document(filename):
    """Небольшая обертка для корретного открытия документа"""
    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)
    document = desktop.loadComponentFromURL(uno.systemPathToFileUrl(filename), "_blank", 0, tuple())
    return document

document = get_document(source_filename)  # Окроем исходный документ
sheet = document.getSheets().getByIndex(0)  # Получем первый лист
cell = sheet.getCellByPosition(0, 0)  # Получим необходимую нам ячейку
cell.setString(int(cell.getString()) + 42)  # И запишем в неё новый результат

# Сохраняем документ
document.storeToURL(uno.systemPathToFileUrl(destination_filename), ())

The code is far from perfect, but it’s just right for demonstrating the capabilities.

I got the result I wanted: all the components in the spreadsheet were left untouched, and only the cell we were modifying changed.
All in all, python-uno is ideal for automating work with office documents without any loss!

Python Excel: xlwt, xlrd, xlutils

PyUNO

Recommended reading