Append Mode for ExcelWriter with openpyxl
State: closed

- [X] closes #3441
- [X] tests added / passed
- [X] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [X] whatsnew entry


Labels:
name: IO Excel
description: read_excel, to_excel

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21251?src=pr&el=h1) Report
> Merging [#21251](https://codecov.io/gh/pandas-dev/pandas/pull/21251?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/c85ab083919b59ce84c220d5baf7d34ff4a0bcf2?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `n/a`.

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

```diff
@@            Coverage Diff             @@
##           master   #21251      +/-   ##
==========================================
+ Coverage   91.84%   91.85%   +<.01%     
==========================================
  Files         153      153              
  Lines       49538    49546       +8     
==========================================
+ Hits        45499    45509      +10     
+ Misses       4039     4037       -2
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.25% <ø> (ø)` | :arrow_up: |
| #single | `41.87% <ø> (ø)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/21251?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/series.py](https://codecov.io/gh/pandas-dev/pandas/pull/21251/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc2VyaWVzLnB5) | `94.12% <0%> (ø)` | :arrow_up: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/21251/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.22% <0%> (ø)` | :arrow_up: |
| [pandas/util/\_decorators.py](https://codecov.io/gh/pandas-dev/pandas/pull/21251/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvX2RlY29yYXRvcnMucHk=) | `82.25% <0%> (ø)` | :arrow_up: |
| [pandas/tseries/offsets.py](https://codecov.io/gh/pandas-dev/pandas/pull/21251/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvb2Zmc2V0cy5weQ==) | `97% <0%> (ø)` | :arrow_up: |
| [pandas/core/arrays/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/21251/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvYXJyYXlzL2NhdGVnb3JpY2FsLnB5) | `95.69% <0%> (+0.01%)` | :arrow_up: |
| [pandas/core/sparse/array.py](https://codecov.io/gh/pandas-dev/pandas/pull/21251/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc3BhcnNlL2FycmF5LnB5) | `91.38% <0%> (+0.06%)` | :arrow_up: |
| [pandas/core/algorithms.py](https://codecov.io/gh/pandas-dev/pandas/pull/21251/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvYWxnb3JpdGhtcy5weQ==) | `94.81% <0%> (+0.31%)` | :arrow_up: |

------

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

thanks @WillAyd 
I am not sure if I am supposed to be posting issues on this board so please let me know if I should be posting elsewhere.  I have tried utilizing `mode='a'` but it is not working (more than likely, it is a user issue!).  I have a StackOverflow thread going so I am not sure if I should describe my issue here or reference the link here: [link_to_post](https://stackoverflow.com/questions/54186519/appending-pandas-dataframe-to-existing-excel-document#54186803)
@enterdisplaynamehere this feature is being released as part of 0.24 which we just issued a release candidate for over the past few days. You can try on the RC or here on master and if neither works open an issue per the contributing guide, but this wouldn't be expected to work on versions older than that
@WillAyd Ok, got it, thanks.  It's the first time I've ever heard of 'RC'.  I just downloaded it and gave it a shot.  It seems to work for an existing workbook but not for a workbook that doesn't exist.  I am assuming a workbook should be created per `self.book = Workbook()` in the changes section.  I'll submit a ticket via the contributing guide.  Thanks for the quick response!  Update:  Actually, I read the code wrong.. It just creates a new workbook if `mode!='a'`.
Hi,

How come when looking at ExcelWriter documentation on
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html 
``>>> with ExcelWriter('path_to_file.xlsx', mode='a') as writer:"
...     df.to_excel(writer, sheet_name='Sheet3')"

But when I try using the same syntax I receive this error 
"ValueError: Append mode is not supported with xlsxwriter!"

I tried using engine='openpyxl' but it is very slow and sometimes causes the file to become corrupted and not being able to open. Any feedback is appreciated.


Thanks.

@yehia123 this was only implemented for openpyxl and I'm not sure it's even possible to append with xlsxwriter. If you research and find otherwise certainly can open as an enhancement request on the issue tracker
@WillAyd Thank you for the quick response. I ended up making my file csv instead of xlsx and used to_csv(); the append mode is much faster than 'openpyxl'.