ENH: add if_sheet_exists='overlay' to ExcelWriter
State: closed

- [x] closes #42221, related to #40231
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [x] whatsnew entry - not applicable

I mainly wanted this functionality myself, and am very open to input :)

Labels:
name: Enhancement
name: IO Excel
description: read_excel, to_excel

Comments:
This pull request is stale because it has been open for thirty days with no activity. Please update or respond to this comment if you're still interested in working on this.
This is still under discussion
(Most of the) Original discussion: https://github.com/pandas-dev/pandas/pull/40231#discussion_r594359677
Summary of why this is a regression: https://github.com/pandas-dev/pandas/issues/42221#issuecomment-868825950

As back in #40231, I'm +1 on having this as a feature, and even more so since it was identified that PR actually removed the feature in question.
The original opposition to the 'overwrite'/'write_to': https://github.com/pandas-dev/pandas/pull/40231#issuecomment-817881615
Basically that it is a mutating method that is not provided somewhere else.
But append mode is also only provided here (AFAIK, could be wrong). So you're already mutating the workbook, why would mutating sheets then be a problem.
On top of that, it was possible previously #42221
can you rebase
Hello @Joeperdefloep! 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 2021-11-15 15:26:58 UTC
I messed up a bit while rebasing and had to force-push. apologies for that :/
@github-actions pre-commit
I really do not understand why the test fails. It says it fails doctests in `_base.py`, without mentioning a reason...
@Joeperdefloep - still some docstring issues on the CI. ping if you're not sure how to fix

```
/home/runner/work/pandas/pandas/pandas/io/excel/_base.py:docstring of pandas.io.excel._base.ExcelWriter:52: WARNING: Explicit markup ends without a blank line; unexpected unindent.
looking for now-outdated files... none found
/home/runner/work/pandas/pandas/pandas/io/excel/_base.py:docstring of pandas.io.excel._base.ExcelWriter:53: WARNING: Bullet list ends without a blank line; unexpected unindent.
```
@rhshadrach shuold be good. Small question:
How did you get that output? I tried `$ python scripts/validate_docstrings.py pandas.ExcelWriter`
but got `NameError` due to `ExcelWriter`. Also in  #43445.

these errors you see are definitely because of [these lines](https://github.com/pandas-dev/pandas/pull/42222/files#diff-63200ddb7f5656b8ee868a28d9cb7720ffe50689b0e3fb0b4e15cc5c0ae80dd7R690-R695). I wanted to have only the *overlay* option as a `versionadded 1.4.0` But was not sure how to do that. I just made a `versionchanged 1.4.0`

If you click on Details unde the "Some checks were not successful", you can dig into the CI output.
@Joeperdefloep can you merge master and address test failures
Sorry it took so long.... Done now :)
@Joeperdefloep - Can you resolve the conflicts.
@jreback - friendly ping.
thanks @Joeperdefloep very nice!