Repository: pandas-dev/pandas
Pull request: DOC:add append example in to_excel documentation
closes #27051



Problem statement:
Improve to_excel Append Documentation
#### Code Sample, a copy-pastable example if possible
I have recently worked on a project where I needed to insert pandas dataframes into an existing Excel workbook (.xlsx) file.

I wrote the following function:
 
```python

def append_df_to_excel(filename, df, sheet_name='Sheet1', startcol='A', startrow=1, **to_excel_kwargs):
    """
    Append a DataFrame [df] to existing Excel file [filename]
    into [sheet_name] Sheet.
    If [filename] doesn't exist, then this function will create it.

    Parameters:
      filename : File path or existing ExcelWriter
                 (Example: '/path/to/file.xlsx')
      df : dataframe to save to workbook
      sheet_name : Name of sheet which will contain DataFrame.
                   (default: 'Sheet1')
      startrow : upper left cell row to dump data frame - Excel row (starting from 1) not Python row (starting from 0)
      startcol : upper left cell column to dump data frame - Excel column (character)
      to_excel_kwargs : arguments which will be passed to `DataFrame.to_excel()`
                        [can be dictionary]

    Returns: None
    """

    # ignore [engine] parameter if it was passed
    if 'engine' in to_excel_kwargs:
        to_excel_kwargs.pop('engine')

    writer = pd.ExcelWriter(filename, engine='openpyxl')

    try:
        # try to open an existing workbook
        writer.book = load_workbook(filename)

        # copy existing sheets
        writer.sheets = {ws.title:ws for ws in writer.book.worksheets}

    except FileNotFoundError:
        # file does not exist yet, we will create it
        pass

    # Convert Excel rows into Python rows
    if startrow > 0:
        startrow = startrow - 1

    # Convert Excel columns into Python columns
    startcol = col2num(startcol) - 1

    # write out the new sheet
    if 'header' in to_excel_kwargs:
        header = to_excel_kwargs['header']
        del(to_excel_kwargs['header'])
    else:
        header = False

    df.to_excel(writer, sheet_name, startrow=startrow, startcol=startcol, header=header, index=False, **to_excel_kwargs)

    writer.save()

```

Could this be optional behaviour of df.to_excel()

#### Problem description

It is sometimes necessary to fill data into templates provided in excel without overwriting other data or formulae in the excel workbook. Being able to do so using df.to_excel (and then developing the functions to make this relatively efficient if you have to write to many places in the same file) seems a natural and useful extension.

Hints from issue discussion:
Append mode was introduced in v0.24 off the top of my head - have you tried that?
I can't find it in the docs here https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html
What incantation do you need to use? Or is it in the coming soon version?
Hmm apparently not in the docs but something like this would work:

```python
with pd.ExcelWriter('the_file.xlsx', engine='openpyxl', mode='a') as writer: 
     df.to_excel(writer) 
```

Would you be interested in adding that to the documentation?
Sure. I can do that. I'll have a look this afternoon.

Commit message: DOC:add append example in to_excel documentation

closes #27051

Patch to review:
diff --git a/pandas/core/generic.py b/pandas/core/generic.py
index 1b39f9225a0ed..413b4ac2f3609 100644
--- a/pandas/core/generic.py
+++ b/pandas/core/generic.py
@@ -2184,6 +2184,12 @@ def _repr_data_resource_(self):
     automatically chosen depending on the file extension):
 
     >>> df1.to_excel('output1.xlsx', engine='xlsxwriter')  # doctest: +SKIP
+
+    To append data to an existing excel file:
+
+    >>> with pd.ExcelWriter('the_existing_file.xlsx', engine='openpyxl',
+    ...                     mode='a') as writer:
+    ...     df.to_excel(writer)
     """
 
     @Appender(_shared_docs["to_excel"] % dict(klass="object"))


Code review comments:
diff_hunk: @@ -2184,6 +2184,12 @@ def _repr_data_resource_(self):
     automatically chosen depending on the file extension):
 
     >>> df1.to_excel('output1.xlsx', engine='xlsxwriter')  # doctest: +SKIP
+
+    To append data to an existing excel file:
+
+    >>> with pd.ExcelWriter('the_existing_file.xlsx', engine='openpyxl',
+    ...                     mode='a') as writer:
+    ...     df.to_excel(writer)
path: pandas/core/generic.py
text: @user1:
This example feels disconnected with the rest of the docstring.

I'd move it to line 2181, and say something like `ExcelWriter can also be used to append to an existing Excel file`. And then use the same file `output.xlsx` and append a sheet `Sheet_name_3`. I think that will be much clearer.

You also need the `# doctest: +SKIP` as in the other examples.

@author:
Yeah. You are right. That will be clearer. Will make the required change