Improve to_excel Append Documentation

[MasterAir]

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:

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.

[WillAyd]

Append mode was introduced in v0.24 off the top of my head - have you tried that?

[MasterAir]

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?

[WillAyd]

Hmm apparently not in the docs but something like this would work:

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?

[MasterAir]

Sure. I can do that. I'll have a look this afternoon.