Reading OpenOffice spreadsheet in Python?

[Skip Montanaro]

I don't have Windows and since upgrading my Mac to Mavericks I no
longer have Excel of any flavor. I have a few Excel spreadsheets in
which I store parameters from which I generate other config files. I
read those spreadsheets using xlrd.

I am so fed up with LibreOffice's inability to properly support really
basic Excel capabilities, I'm about ready to throw my computer out the
window. So, I'm looking for alternatives. Before someone suggests
config parser/Windows INI files... A spreadsheet format is kinda handy
in this case because I do use a few formulas to define some of the
parameters. Adding a new row (new config file) or column (new
parameter) is a breeze. The simplest solution would seem to be to
submit to LibreOffice's terror and just start saving my spreadsheets
in OpenDocument format. That then puts me in the market for an xlrd
replacement. Is there something akin to xlrd for OpenDocument
spreadsheets? I see a couple possibilities in PyPI (exodf, odfpy), but
none which have a really high weight (suggesting they are "category
killers").

I'm open to other options as well. I see a number of Google
spreadsheet modules, and there is pyspread. The former has the
permission issue (besides, where I work everything goes into Git), and
I'm not sure how full-featured or stable the latter is (but, will
investigate).

Thx,

Skip

 

[Martin Manns]

I am so fed up with LibreOffice's inability to properly support really
basic Excel capabilities, I'm about ready to throw my computer out the

Could you please give some examples, what basic Excel capabilities you
are missing?

I'm open to other options as well. I see a number of Google
spreadsheet modules, and there is pyspread. The former has the
permission issue (besides, where I work everything goes into Git), and
I'm not sure how full-featured or stable the latter is (but, will
investigate).

Maybe gnumeric may be an option, too.

Pyspread should run fine on the Mac. However, Macs are not officially
supported because there are no testers for OSX, yet.

Please note that pyspread does not translate Excel "xls" files into
Python but only imports cell contents and formatting via xlrd. If you
use Excel cell functions or VBA then you would have to migrate your
code to Python.


Martin

 

[Skip Montanaro]

Could you please give some examples, what basic Excel capabilities you
are missing?

That's the problem. It's not that it's obviously missing some
features. It's that the files it writes sometimes are misinterpreted
by xlrd. (Maybe the problem is xlrd, but it never has a problem with
actual Excel-generated XLS files.)

For example, I use a formula to generate a sequence of strings in one column:

"F0", "G0", "H0", ..., "Z0", "F1", "G1", ...

continuing for as long as I have data in an adjoining column. If I add
or delete rows, this sequence grows and shrinks. It looks fine in the
spreadsheet. I rarely, if ever, change the number of rows, so the
values in this column rarely, if ever, change. Still, sometimes when
xlrd reads the values out of that column it finds all cells in that
column contain the number 0. If I mess around with the spreadsheet in
ways which are apparently unrelated to this column, I can sometimes
get it to read right, sort of like hitting a jukebox to stop a record
from skipping.

I haven't tried changing the output format to XLSX format (isn't that
a compressed XML document?), but maybe I should give that a whirl. I
don't know if xlrd will read such files (at first blush, it appears
not).

Also, Mac isn't my primary platform. This problem occurs using the
Linux version of LibreOffice as well. Sometimes I edit this
spreadsheet from home though. Before I upgraded to OS X Mavericks, I
still had an ancient version of Excel for Mac which worked fine
(despite all the disparaging remarks I've seen over the years about
that product). Once I upgraded though, that was no longer an option.

Thanks for the gnumeric and pyspread suggestions. I thought gnumeric
was a long dead project, but see that it's available for my Mac, so
I'll try that right off. I'll also play around with pyspread and see
how that does. I don't mind rewriting my couple of formulas, though I
will no longer be able to rely on the Excel experts at work.

Skip

 

[Rustom Mody]

I don't have Windows and since upgrading my Mac to Mavericks I no
longer have Excel of any flavor. I have a few Excel spreadsheets in
which I store parameters from which I generate other config files. I
read those spreadsheets using xlrd.

I am so fed up with LibreOffice's inability to properly support really
basic Excel capabilities, I'm about ready to throw my computer out the
window. So, I'm looking for alternatives. Before someone suggests
config parser/Windows INI files... A spreadsheet format is kinda handy
in this case because I do use a few formulas to define some of the
parameters. Adding a new row (new config file) or column (new
parameter) is a breeze.

Are you familiar with emacs' org mode tables?

http://orgmode.org/org.html#Tables

 

[Skip Montanaro]

Are you familiar with emacs' org mode tables?

http://orgmode.org/org.html#Tables

No. Thanks for the pointer.

S

 

[Rustom Mody]

I don't have Windows and since upgrading my Mac to Mavericks I no
longer have Excel of any flavor. I have a few Excel spreadsheets in
which I store parameters from which I generate other config files. I
read those spreadsheets using xlrd.

I am so fed up with LibreOffice's inability to properly support really
basic Excel capabilities, I'm about ready to throw my computer out the
window.

Another possibility: Use google drive/docs spreadsheet capability.
Makes much less mess than libreoffice and will export to standard formats

 

[Skip Montanaro]

Another possibility: Use google drive/docs spreadsheet capability.
Makes much less mess than libreoffice and will export to standard formats

Correct, though it separates my spreadsheet from the Git repository,
and means anyone else at work who might work on this stuff would need
to be granted access. Not insurmountable problems, but not great
either.

I tried Gnumeric today. The first edits caused no problems. Knock on wood...

Skip