ENH: ExcelWriter append or create mode

[flori-ko]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

I work with scripts, which write a lot of dataframes to different sheets in excel files. For my use case, the excel files I want to write to may or may not exist yet.
Since dataframe.to_excel("path/file.xlsx") always creates a new excel file I am using the ExcelWriter with mode="a" and if_sheet_exists="replace" for updating the data on the specific sheet. However, when the excel file doesn't exist, I need to use mode="w" and also use None for if_sheet_exists.

I wish it would be easier to append data to excel files, regardless of they exist or not.

What I want is a behaviour that is actually similar to the if_sheet_exists parameter. There, I don't need to know if the sheet already exist or not, I simply specify what I want to happen if it exists.

Feature Description

Add a new Option to the mode parameter of the "ExcelWriter".

mode : {{'w', 'a', 'append_or_create'}}, default 'w'
        File mode to use (write, append, append to the file and create the file if it doesn't exist yet). Append does not work with fsspec URLs.

The value for this option can be any other value that fits.

The mode "append_or_create" would check if the excel file exists, if it does it appends the sheet to it, using the value of if_sheet_exists. If the excel file doesn't exist it is created.

Alternative Solutions

import os
from typing import Literal

import pandas as pd


def write_excel_append_or_create(
    dataframe: pd.DataFrame,
    path: str,
    sheet_name: str = "Sheet1",
    if_sheet_exists: Literal["error", "new", "replace", "overlay"] = "replace",
    **kwargs,
) -> None:
    excel_file_exists = os.path.exists(path)
    mode = "a" if excel_file_exists else "w"
    replace = if_sheet_exists if mode == "a" else None
    with pd.ExcelWriter(path=path, mode=mode, engine="openpyxl", if_sheet_exists=replace) as writer:
        dataframe.to_excel(excel_writer=writer, sheet_name=sheet_name, **kwargs)

Additional Context

No response