Class to read OpenDocument Tables
State: closed

closes #2311 

This is primarily intended for LibreOffice calc spreadsheets but will
also work with LO Writer and probably with LO Impress documents.

This is an alternate solution to https://github.com/pandas-dev/pandas/pull/9070
There are test cases with several different problematic LibreOffice spread sheets.
git diff upstream/master -u | flake8 appeared to pass.

... I didn't do the whats new entry. Though I expect there's some more work to do before submitting this. I just wanted to get the core code in for comments.

The open issues is, the workaround for https://github.com/pandas-dev/pandas/issues/25422 is embedded in the current code (so all my tests pass right now) but that, or a better solution should move closer to the iso 8601 parser.

Also I don't have the parser class hooked up to a read_excel or read_ods function.

Using read_excel bothers me some... because its not an excel file. I think it would be more clear if there was either a separate read_ods function or a generic read_spreadsheet function than just pressing read_excel into being a generic spreadsheet reader.

Also this just a reader. I had never gotten around to implementing a writer.


Labels:
name: Enhancement
name: IO Data
description: IO issues that don't fit into a more specific label

Comments:
Hello @detrout! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:

















There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-07-02 12:42:15 UTC
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=h1) Report
> Merging [#25427](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/85572de5e7bb188cfecc575ee56786406e79dc79?src=pr&el=desc) will **decrease** coverage by `50.06%`.
> The diff coverage is `14.91%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/25427/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=tree)

```diff
@@             Coverage Diff             @@
##           master   #25427       +/-   ##
===========================================
- Coverage   91.74%   41.68%   -50.07%     
===========================================
  Files         173      175        +2     
  Lines       52923    53037      +114     
===========================================
- Hits        48554    22107    -26447     
- Misses       4369    30930    +26561
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `?` | |
| #single | `41.68% <14.91%> (-0.06%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/opendocument/\_\_init\_\_.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL29wZW5kb2N1bWVudC9fX2luaXRfXy5weQ==) | `100% <100%> (ø)` | |
| [pandas/io/opendocument/odfreader.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL29wZW5kb2N1bWVudC9vZGZyZWFkZXIucHk=) | `13.39% <13.39%> (ø)` | |
| [pandas/io/formats/latex.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvbGF0ZXgucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/core/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY2F0ZWdvcmljYWwucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/sas/sas\_constants.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXNfY29uc3RhbnRzLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/plotting.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvcGxvdHRpbmcucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvY29udmVydGVyLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/formats/html.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvaHRtbC5weQ==) | `0% <0%> (-99.35%)` | :arrow_down: |
| [pandas/core/groupby/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS9jYXRlZ29yaWNhbC5weQ==) | `0% <0%> (-95.46%)` | :arrow_down: |
| [pandas/io/sas/sas7bdat.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXM3YmRhdC5weQ==) | `0% <0%> (-91.17%)` | :arrow_down: |
| ... and [134 more](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=footer). Last update [85572de...f5f42e5](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=h1) Report
> Merging [#25427](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/a272b60aabc2a3b4128a7365e9b2b0080bcd5121?src=pr&el=desc) will **decrease** coverage by `50.74%`.
> The diff coverage is `14.81%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/25427/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=tree)

```diff
@@             Coverage Diff             @@
##           master   #25427       +/-   ##
===========================================
- Coverage   91.86%   41.12%   -50.75%     
===========================================
  Files         180      175        -5     
  Lines       50794    50851       +57     
===========================================
- Hits        46660    20910    -25750     
- Misses       4134    29941    +25807
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `?` | |
| #single | `41.12% <14.81%> (-0.86%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/excel/\_base.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsL19iYXNlLnB5) | `28.26% <100%> (-63.67%)` | :arrow_down: |
| [pandas/io/excel/\_odfreader.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsL19vZGZyZWFkZXIucHk=) | `14.01% <14.01%> (ø)` | |
| [pandas/io/formats/latex.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvbGF0ZXgucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/gcs.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2djcy5weQ==) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/sas/sas\_constants.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXNfY29uc3RhbnRzLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/core/groupby/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS9jYXRlZ29yaWNhbC5weQ==) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/plotting.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvcGxvdHRpbmcucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvY29udmVydGVyLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/s3.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3MzLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/formats/html.py](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvaHRtbC5weQ==) | `0% <0%> (-99.37%)` | :arrow_down: |
| ... and [157 more](https://codecov.io/gh/pandas-dev/pandas/pull/25427/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=footer). Last update [a272b60...8302fd7](https://codecov.io/gh/pandas-dev/pandas/pull/25427?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

Ah the coverage went down but that's because the tests couldn't run because odfpy wasn't available.

Also I fixed the whitespace problem.
Wait a second... I just realized that read_excel already supports using two totally different libraries for reading .xls and .xlsx files. Now it makes more sense why you want it hooked into io.excel.

OK it'll take me a bit to understand how the hook works.

My current reader class is ODFReader, but ODFFile or OpenDocumentFile might be more consistent with ExcelFile. Do you have any preferences?

Lastly is there any place I can add then odfpy dependency so the ci framework will be able to run my tests?

Thanks you for your comments.
Diane




> My current reader class is ODFReader, but ODFFile or OpenDocumentFile might be more consistent with ExcelFile. Do you have any preferences?

you don't need to expose any of this directly. These can all be internal. invoking ``pd.read_excel(...., engine='ods')`` should just work.
deps you would add in the ci/deps/* files; don't add to *every* one as you also have to function w/o being included (the framework handles this for you though)
Ok everything is moved into pandas.io.excel and tests/io/test_excel.py

Currently its triggered by passing engine='odf' to read_excel. It looks like pandas is currently depending on xlrd to figure out what to do with the various excel style file extensions.

Should there be something to activate odf reading in ExcelFile.__init__ based on file extension or trying xlrd parsing and catching the error?
Drat. I forgot the CI dependency change.

Ok I added odfpy to the travis-36.yaml file since that seemed to be a thorough test configuration.
references the issue #2311 in your whatsnew
Ok I resolved many of the issues and pushed them as separate commits for the moment so its easier to see what I did.

There's a few issues I'd like to discuss further, and I left those issues open.
can you merge master
@detrout can you merge master and update; let's see if we can get this in
Sorry I got distracted. I'll try to do it tomorrow.
Ok I rebased to master. Convered all my important test docstrings to comments and added a whatsnew for v0.25 about the OpenDocument support.

I hope I remembered to fix everything (and that rebasing was the right merge strategy)

Also since you use python as a macro language for LibreOffice, at some point I'd like to extend my ODFReader class to support reading tables while running in LO. (I only tried it once, and had problems with importing pandas in the LO environment, so it's going to be a while before that works. For now those experiments are going to stay in my [detrout/pandasodf](https://github.com/detrout/pandasodf) repository)
@detrout looks like there are still some conflicts. Workflow to resolve locally on your branch should be as follows:

```sh
git fetch upstream
git merge upstream/master
# Resolve conflicts here...
git merge --continue
git push origin YOUR_BRANCH_NAME
```
I'm not sure what to do about this travis failure?

```
self = <pandas.io.excel._odfreader._ODFReader object at 0x7fbefb3f1c18>
name = 0
    def _get_sheet(self, name):
        """Given a sheet name or index, return the root ODF Table node
        """
>       if isinstance(name, compat.string_types):
E       AttributeError: module 'pandas.compat' has no attribute 'string_types'
pandas/io/excel/_odfreader.py:42: AttributeError
```

You asked me to use pandas.compat.string_types, and its present in 0.23.3, but it looks gone in 0.25. There's just a couple of python calls to isinstance(source, bastring) or (isinstance(string, str)
@detrout just use
``isinstance(name, str)`` as the string_types has been removed now that we only support PY3
@detrout can you merge master? We've had quite a few changes between dropping Py2 support and refactoring some of the Excel classes that come into play here.

This feature is certainly welcome so hoping we can get this pushed through!
Ok ... I'll try to get to it in the next day or two. I might be back with some questions.

Or is there a recommended place to look for guidance on the desired changes?
Feel free to ask here or on Gitter if you need help
Ok thanks. 
As far as I could tell the test failures in continuous-integration/travis-ci/pr look like they're from deprecation warnings in xlrd?
@detrout seems to be the case; not sure on first glance why they would start appearing here
> @detrout seems to be the case; not sure on first glance why they would start appearing here

I don't know. I did find this though:
https://github.com/pandas-dev/pandas/issues/22682

Also in the travis log there's a variable PANDAS_TESTING_MODE="deprecation". Does that enable deprecation testing? Do the other test configurations disable checking deprecations?
Hmm so I'm not able to reproduce locally with xlrd 1.2 and python 3.7.2; must be a combination of some factors at play here

> Also in the travis log there's a variable PANDAS_TESTING_MODE="deprecation". Does that enable deprecation testing? Do the other test configurations disable checking deprecations?

That's for a different build so not applicable in this case. Something weird going on we'll just have to figure out
~~Can confirm locally that these warnings appear in Python 3.6.8:~~

EDIT: this post was incorrect. See next post for clarification

<details>

INSTALLED VERSIONS
------------------
commit: 223f7caad35412afbedaa76b1ca40fdb10dbb0a2
python: 3.6.8.final.0
python-bits: 64
OS: Darwin
OS-release: 18.5.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.25.0.dev0+589.g223f7caad
pytest: 4.5.0
pip: 19.1
setuptools: 41.0.1
Cython: 0.29.7
numpy: 1.16.3
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: 1.2.0
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

</details>

Interestingly enough downgrading to xlrd 1.1 fixes the issue:

<details>

INSTALLED VERSIONS
------------------
commit: 223f7caad35412afbedaa76b1ca40fdb10dbb0a2
python: 3.6.8.final.0
python-bits: 64
OS: Darwin
OS-release: 18.5.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.25.0.dev0+589.g223f7caad
pytest: 4.5.0
pip: 19.1
setuptools: 41.0.1
Cython: 0.29.7
numpy: 1.16.3
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: 1.1.0
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

</details>
Ah so I've figured it out. Ignore my previous comment as my environments were not properly configured.

The problem here is that `odfpy` installs defusedxml as a dependency. There is some code on the xlrd side which prioritizes imports and sets global state as to whether or not iteration is supported. defusedxml is prioritized above all else but fails the check as to whether or not iteration is supported, hence the subsequent usage of the method that throws all of these warnings

Here's the import machinery:

https://github.com/python-excel/xlrd/blob/c5a95aedce43a5997e76e468e286d85ef035b173/xlrd/xlsx.py#L39

Here's the global state setting, which fails with defusedXML and throws all the warnings subsequently:

https://github.com/python-excel/xlrd/blob/c5a95aedce43a5997e76e468e286d85ef035b173/xlrd/xlsx.py#L59

Not sure what the best course of action is just yet. This is pretty nuanced and would throw a lot of warnings on the user side of things if they install xlrd and defusedxml together
So the issue on the xlrd was closed in #315 and per #26487 xlrd is unmaintained so there may not be a way around this. I think will need to filter those warnings in the tests and maybe add a whatsnew note about how installing odfpy (specifically defusedxml) alongside xlrd may generate those warnings