How do you read the cell value from the OpenOffice Calc.ods file?

Question

I was able to read the value of an Excel cell with xlrd using column and row numbers as input. Now I need to access the same cell values ​​in some spreadsheets that were saved in .ods format.

So, for example, how would I read with Python the value stored in cell E10 in the .ods file?

Answer 1

Hacking your way through XML doesn't have to be too complicated ... but there are difficulties. Just one example: OOo in her wisdom decided not to write the cell address explicitly. There is no cell attribute such as address="E10" or column="E" ; you need to count rows and columns.

Five consecutive empty cells are represented as <table:table-cell table:number-columns-repeated="5" />

The number-colums-repeated attribute defaults to "1" and also applies to non-empty cells.

Deteriorates when you merge cells; you get the covered-table-cell tag, which is 90% the same as the table-cell tag, and the attributes number-columns-spanned and number-rows-spanned should be calculated in counting the columns and rows.

Tags

A table:table-row can have the attribute number-rows-repeated . This can be used to repeat the contents of a whole non-empty line, but most often it is seen when there are more than 1 consecutive empty lines.

So, even if you are satisfied with the “working on my data” approach, this is not trivial.

You may like ODFpy . Pay attention to the second sentence: “Unlike other more convenient APIs, this is, in fact, an abstraction layer just above the XML format.” “There is an ODF-to-HTML script, which (if it is written for ODS as well as for ODT ) can be hacked to get what you want.

If you prefer "it works with almost all data and is supported and has an interface that is familiar to you," you may need to wait until the functionality is placed in xlrd ... but it doesn’t happen soon.

Answer 2

Of the libraries I tried ezodf , there was one that worked.

 from ezodf import opendoc, Sheet doc = opendoc('test.ods') for sheet in doc.sheets: print sheet.name cell = sheet['E10'] print cell.value print cell.value_type 

pyexcel-ods crashed , odfpy crashed and in addition, its documentation is either missing or horrible.

Given that the supposedly working libraries died from the first file I tested, I would prefer not to write my own processing, because sooner or later it will either crash, or worse, it will fail in some strange situation.

EDIT: Deteriorating. ezodf can silently return dummy data .