ENH: Add if_sheet_exists parameter to ExcelWriter
State: closed

- [x] closes #40230
- [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


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

Comments:
Hello @mrob95! 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-04-21 19:55:25 UTC
Not sure - it might be better as it’s own library

Sent from my iPhone

> On Mar 18, 2021, at 3:02 AM, Richard Shadrach ***@***.***> wrote:
> 
> ﻿
> @rhshadrach commented on this pull request.
> 
> In pandas/io/excel/_base.py:
> 
> > @@ -667,6 +667,17 @@ class ExcelWriter(metaclass=abc.ABCMeta):
>          be parsed by ``fsspec``, e.g., starting "s3://", "gcs://".
>  
>          .. versionadded:: 1.2.0
> +    if_sheet_exists : {'new', 'replace', 'overwrite', 'fail'}, default 'new'
> +        How to behave when trying to write to a sheet that already
> +        exists (append mode only).
> +
> +        * new: Create a new sheet, with a name determined by the engine.
> +        * replace: Delete the contents of the sheet before writing to it.
> We also don't support overwrite with any other type of IO methods
> 
> I don't believe other IO methods have the use case of writing to an existing template for formatting purposes, which is what I see as being the primary case here.
> 
> and to your point above there is a lot of ambiguity as to what "overwrite" should actually overwrite
> 
> That was not my point, I merely wanted to make sure existing formatting was preserved if the DataFrame was not formatted and indeed that is the case. Not certain what you find unclear.
> 
> or if it is something we discuss more and want to include in pandas we I think would just want to spend a little bit more time going over the API
> 
> Other than what name to call this, what API choices are there?
> 
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or unsubscribe.

Hi @WillAyd @jreback @rhshadrach  apologies for the delay on this. How would you like me to proceed with this PR? Propose three options:

* Remove the overwrite option as discussed above
* Keep all of the current options
* Rethink the PR. I can see that this parameter might seem too specific, wordy or confusing. A smaller change would be to resurrect #27730, essentially changing the default behaviour of the openpyxl append mode from "new" to "overwrite". I think this is closer to what most people want to do with append mode, and would make it easier to implement the other behaviours in user code.

Happy to hear any thoughts
@mrob95 - I wonder if this should be a part of the `engine_kwargs` argument. We'd need to intercept and react to the argument there, and the docstring would need to be updated with a `Notes` section detailing this. On the one hand, it saves us from adding a new argument. But I also wonder if it might create confusion. Do users expect `engine_kwargs` to always be passed through to the engine?

cc @jreback and @WillAyd for any thoughts on this.
> @mrob95 - I wonder if this should be a part of the `engine_kwargs` argument. We'd need to intercept and react to the argument there, and the docstring would need to be updated with a `Notes` section detailing this. On the one hand, it saves us from adding a new argument. But I also wonder if it might create confusion. Do users expect `engine_kwargs` to always be passed through to the engine?
> 
> cc @jreback and @WillAyd for any thoughts on this.

the point of the `engine_kwargs` is to pass them thru *always*, so not sure what *else* one would expect here.

On the subject of this PR. The problem i have with `overwrite` is that this is a mutating method, whereas we don't have this anywhere else. Why is creating a new sheet a burden?
I'll remove `overwrite`. Writing data in raw form to a back sheet and then referencing it is probably the best way to handle the kind of "data presentation" issues discussed above, and `replace` allows that.
@jreback 

> Why is creating a new sheet a burden?

For me, a use case is to write data to an existing excel sheet (a template) that already has a format. In this way, the result is automatically formatted. I've used this several times, and see people desiring to do so on SO.

Since there is opposition to this feature, it seems like @mrob95's solution is a good compromise, at least when one has control of the template sheet (works for my use case).
looks good, can you merge master and a couple of questions (really not a big deal), but an edge case.