DEPS: require updated python-dateutil, openpyxl
State: closed

closes #15184 

reminder to change: https://github.com/conda-forge/pandas-feedstock on release

Labels:
name: IO Excel
description: read_excel, to_excel
name: Dependencies
description: Required and optional dependencies

Comments:
cc @neirbowj since you wrote most of the code I just ripped out :>
👍 It had a good run. I hope folks found it useful. I'm glad to see it retired.

And incidentally, no concerns for the FreeBSD port because the applicable dependencies are already at:

- `textproc/py-openpyxl` 2.4.9
- `deve/py-dateutil` 2.6.1
should we bump openpxl to even newer?
I'm probably not the right person to answer that question. I'm not an openpyxl user and haven't studied the codebase since the last time I got roped into responding to one of that project's minor updates that broke backwards compatibility. Therefore I cannot say whether a more aggressive version bump would be a good idea, bad idea, or indifferent.

From a port maintainer perspective, is there anything you'd like me to investigate?
i just want to make sure that i didn’t remove any relevant tests (that were not tied to earlier versions)
That would be non-trivial for me to verify, and I'm afraid can't justify spending time on it at the moment unless the test removal impacts the FreeBSD port.
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=h1) Report
> Merging [#18182](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/2f9d4fbc7f289a48ed8b29f573675cd2e21b2c89?src=pr&el=desc) will **increase** coverage by `0.15%`.
> The diff coverage is `87.5%`.

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

```diff
@@            Coverage Diff             @@
##           master   #18182      +/-   ##
==========================================
+ Coverage    91.4%   91.55%   +0.15%     
==========================================
  Files         163      162       -1     
  Lines       50064    49947     -117     
==========================================
- Hits        45759    45730      -29     
+ Misses       4305     4217      -88
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.36% <87.5%> (+0.16%)` | :arrow_up: |
| #single | `40.42% <62.5%> (ø)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/excel.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsLnB5) | `90.13% <100%> (+9.73%)` | :arrow_up: |
| [pandas/compat/\_\_init\_\_.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2NvbXBhdC9fX2luaXRfXy5weQ==) | `58.71% <66.66%> (+0.6%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.8% <0%> (-0.1%)` | :arrow_down: |
| [pandas/io/formats/format.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvZm9ybWF0LnB5) | `96.01% <0%> (ø)` | :arrow_up: |
| [pandas/core/config\_init.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29uZmlnX2luaXQucHk=) | `98.34% <0%> (ø)` | :arrow_up: |
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `95.72% <0%> (ø)` | :arrow_up: |
| [pandas/core/groupby.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS5weQ==) | `92.04% <0%> (+0.01%)` | :arrow_up: |
| [pandas/core/indexes/datetimes.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9kYXRldGltZXMucHk=) | `95.48% <0%> (+0.09%)` | :arrow_up: |
| [pandas/plotting/\_converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19jb252ZXJ0ZXIucHk=) | `65.2% <0%> (+1.81%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/18182?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/18182?src=pr&el=footer). Last update [2f9d4fb...e2d6b53](https://codecov.io/gh/pandas-dev/pandas/pull/18182?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/18182?src=pr&el=h1) Report
> Merging [#18182](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/bd9a3e06621e0b2bff3a7f4a6bf68c886b0db4c3?src=pr&el=desc) will **increase** coverage by `0.14%`.
> The diff coverage is `87.5%`.

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

```diff
@@            Coverage Diff             @@
##           master   #18182      +/-   ##
==========================================
+ Coverage   91.44%   91.59%   +0.14%     
==========================================
  Files         157      156       -1     
  Lines       51449    51328     -121     
==========================================
- Hits        47048    47013      -35     
+ Misses       4401     4315      -86
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.45% <87.5%> (+0.15%)` | :arrow_up: |
| #single | `40.66% <62.5%> (-0.06%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/excel.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsLnB5) | `90.13% <100%> (+9.73%)` | :arrow_up: |
| [pandas/compat/\_\_init\_\_.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182/diff?src=pr&el=tree#diff-cGFuZGFzL2NvbXBhdC9fX2luaXRfXy5weQ==) | `58.77% <66.66%> (+0.58%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.81% <0%> (-0.1%)` | :arrow_down: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `82.08% <0%> (-0.1%)` | :arrow_down: |
| [pandas/plotting/\_converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/18182/diff?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19jb252ZXJ0ZXIucHk=) | `65.25% <0%> (+1.81%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/18182?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/18182?src=pr&el=footer). Last update [bd9a3e0...f04637b](https://codecov.io/gh/pandas-dev/pandas/pull/18182?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

My suggestion would be to go with >= 2.4.0 as this includes utilities and styles for working with Pandas dataframes which should make integration code a lot easier and reliable. NB. Merged cells are **still** a problem but openpyxl 2.5 introduces something that, in time, will make these easier to work with.

2.4 also gives you the `ws.values` property which makes reading XLSX files a lot easier than the current xlrd spaghetti. xlrd is slightly faster on really big files due to a lower memory overhead but my suggestion would be to allow openpyxl in read-only mode (parallelisation of worksheets should be possible).

Would be happy to discuss this with anyone but haven't time to do it myself at the moment.

2.4 is stable and I haven't seen anything 2.3 specific issues for a good long time.
@Themanwithoutaplan thanks for the suggestion.
http://openpyxl.readthedocs.io/en/default/pandas.html explains everything (based on the work we did together in Montreal).
Also, support for a dedicated datetime celltype is coming 2.5. Removes some of the problems associated with Excel's moronic handling of dates and times.
@Themanwithoutaplan is this api supported in >= 2.4? (would you do a PR to change the pandas code)?
New datetimes are 2.5 only. Not sure when I can do a PR. We are having a [sprint here in Düsseldorf](https://www.meetup.com/de-DE/Python-Meeting-Dusseldorf/events/243737124/) next week but I was hoping to be able work on something else…

I would like to get 2.5 out the door. It's just missing the code for adding/removing rows and columns.
any comments @jorisvandenbossche @TomAugspurger 
> For dateutil, do we win (code cleanup wise) a lot by not leaving the minimum version at eg 2.4 ?

Although, by the time 0.22 will be released, dateutil 2.5 will probably be almost 2 years old, so that is probably fine.
> For openpyxl, it seems all code you removed was for versions < 2.2. Is there a reason to not just bump the minimum required to 2.2 instead of directly 2.4 in the PR?

why? that's a pretty old version, I don't see a reason to not require newer things in developing libraries (esp there is a speedup coming), see the above comments.


openpyxl 2.4 is just 1 year old, which is not that much. If it doesn't cost us anything to provide users the ability to use older version, I would do that (at the moment it is simply the check of the version that would need to be changed no?). If we would start using those newer features, then sure.
Definitely drop support for openpyxl < 2.4. There were changes in styles in all 2.x releases. 2.4 was built to provide an API for Pandas so that the spaghetti code could be cleared out. < 2.4 is not maintained.
@jorisvandenbossche @TomAugspurger 
Please also update io.rst (there are several mentions of openpyxl and its supported version)

What is the advantage of hard dropping support for older pytz versions? It does not cost us anything? (I mean in code complexity)

> Definitely drop support for openpyxl < 2.4. There were changes in styles in all 2.x releases. 2.4 was built to provide an API for Pandas so that the spaghetti code could be cleared out. < 2.4 is not maintained.

But we do not use this API at this point? As we only have special cased code for openpyxl < 2.2. If there have been many changes in style, for me that is a reason more to not drop support (as long as we don't use features of the newer releases), if people want to preserve the actual styling they have right now. 
> What is the advantage of hard dropping support for older pytz versions? It does not cost us anything? (I mean in code complexity)

this is true, yeah just wanted to set some sort of minimum, though I guess its not really needed.

> But we do not use this API at this point? As we only have special cased code for openpyxl < 2.2. If there have been many changes in style, for me that is a reason more to not drop support (as long as we don't use features of the newer releases), if people want to preserve the actual styling they have right now.

not the point, there was too much churn in the < 2.4 versions.
openpyxl used to have unreliable and incorrect style implementation so backwards-incompatible changes were essential. Not doing users any favours by keeping support for these around, especially as code coverage was never complete I've just managed to update my fork and hope to work on the > 2.4 implementation in the near future. Using the API will make it much easier in future to decide who's responsible.

`pytest pandas/tests/io/test_excel.py` is all I need to worry about, right?
I'm not having a lot of fun getting tests to run: segfault with Python 3.6 on Mac OS and unable to run test_excel.py in isolation. I'd better check the docs.
@jorisvandenbossche 
I've started work on this.
Looking at the implementation: is it going to be possible to work directly with a dataframe? openpyxl doesn't need the intermediate `ExcelCell` object.
@Themanwithoutaplan the implementation is orthogonal to us changing versions
what would hapoen if you had a version of openpyxl that can process things more efficiently 
then we could dispatch it that way, keeping existing code for older versions
if it passes all tests then that would be fine

so i assume this is not possible currently?
I'm going to look at interfacing directly from `df.to_excel()` using `openpyxl.utils.dataframe.dataframe_to_rows()` This removes the need for an `ExcelWriter` class entirely. The older code could then be binned entirely.
After some further investigation It looks to me that the best fit for the modern openpyxl API would be have a drop-in replacement for `ExcelFormatter` class.