ENH: #40231 does not allow writing multiple dataframes to a single sheet

[feefladder]

The problem

The following code works well, both in 1.4.0 and 1.2.5:

df = pd.DataFrame(["=1+7"])
with pd.ExcelWriter("test.xlsx",engine="openpyxl") as writer:
    df.to_excel(writer,sheet_name="Sheet2")
    pd.DataFrame(["=1+1"]).to_excel(writer,sheet_name="Sheet2",header=None,index=False,startcol=4,startrow=3)

however, the following code raises an error in 1.4.0, but works in 1.2.5 (it creates a new sheet and writes both dataframes to it):

pd.DataFrame(["=1+1"]).to_excel("test.xlsx")
df = pd.DataFrame(["=1+7"])
with pd.ExcelWriter("test.xlsx",engine="openpyxl",mode="a") as writer:
    df.to_excel(writer,sheet_name="Sheet2")
    pd.DataFrame(["=1+1"]).to_excel(writer,sheet_name="Sheet2",header=None,index=False,startcol=4,startrow=3)

Also other options cannot replicate the old behaviour.

Describe the solution you'd like

An added option to if_sheet_exists="write_to" that justs selects the sheet and writes into it, without first deleting its contents. This also allows to append a dataframe within a sheet.

EDIT: in #40230 this was also suggested behaviour, but it was not implemented:

Originally I considered adding a flag like overwrite=True to pd.ExcelWriter but I don't think this would be sufficient to cover all the potential behaviours, so would suggest an enum along the lines of the if_exists option in df.to_sql, with the following options:

* new_sheet: Create a new sheet with a different name.

* overwrite_sheet: Delete the contents of the sheet, then write to it.

* overwrite_cells: Write directly to the named sheet without deleting the previous contents.

[rhshadrach]

Tagging for 1.3 pending further discussion.

[rhshadrach]

Summary: #40230 did not include the overwrite option to modify an already existing excel sheet, I've confirmed this was the default behavior of mode="a" on 1.2.x when using ExcelWriter and making multiple .to_excel calls within a single context manager. As such, the default behavior of 1.2.x is no longer possible to achieve.

cc @WillAyd @jreback

[feefladder]

I've made a #42222 that solves this issue.

Also, naming is inconsistent between to_sql(if_exists={"fail","replace","append"}) and to_excel(if_sheet_exists={"error","replace","new",<"write_to">}) in #40230
should this be solved?