Reading an OpenOffice spreadsheet into Python
Sometimes the best way to manage simulation output data is in a spreadsheet. Or the data you want to use is already in a spreadsheet.
Before reading it into Python for numerical analysis or plotting, or reading into xmgrace to plot, etc, it is normal to export the spreadsheet to a CSV or tab-separated file. But then you have two files. And if you do something in the spreadsheet you have to export it again and this is inefficient.
Here is a way to read an .ods file into a Python array:
- Install ODFPY. If you are running Ubuntu this can be found in the official software repository under the package name “python-odf”.
- Get the ODSReader script by Marco Conti . But wait! There is a patched version here that has some additional benefits, such as the ability to include empty cells.
To use it you will need to import the ODSReader class from ODSReader.py. Here is an example by myself where I cut off some unnecessary empty lines at the end:
The numpy array now contains an array of strings so I modified the OSDReader.py script to eval() each cell item before returning the array. Eval() is safe if you are using your own data but can in some cases be exploited for malicious purposes; there is a lot of discussion about this online.