CODEWITHSUNDEEP
python
Fabiogio
Fabiogio

Reputation: 119

How to add/overwrite a sheet of an already existing xlsx file without changing/deleting the other sheets

I have a xlsx file named 'Results' with sheets 'Daily','DHW','SH'. I need to update daily results with a dataframe 'days', so that the old data in the 'Daily' sheet will be deleted and replaced by the new 'days' dataframe. Sheets 'DHW' and 'SH' must NOT be modified or deleted. Can anyone help me?

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
days.to_excel(writer, sheet_name = 'Daily')
writer.save()
writer.close()

That's what i tried but i loose other sheets

Upvotes: 0

Views: 1476

Answers (2)

Eng Osama
Eng Osama

Reputation: 1

# add new sheet to existing excel file without deleting any sheet
file_path='D:\example.xlsx' # path for exist file
workbook1 = openpyxl.load_workbook(file_path)
writer = pd.ExcelWriter(file_path, engine='openpyxl')
writer.book = workbook1
df.to_excel(writer, sheet_name='Finally', index=True, index_label="No.")
writer.save()
writer.close()

Upvotes: 0

Alderven
Alderven

Reputation: 8270

Use append_df_to_excel helper function:

import pandas as pd

def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
                       truncate_sheet=False,
                       **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.
                 Per default (startrow=None) calculate the last row
                 in the existing DF and write to the next row...
      truncate_sheet : truncate (remove and recreate) [sheet_name]
                       before writing DataFrame to Excel file
      to_excel_kwargs : arguments which will be passed to `DataFrame.to_excel()`
                        [can be dictionary]

    Returns: None
    """
    from openpyxl import load_workbook

    import pandas as pd

    # ignore [engine] parameter if it was passed
    if 'engine' in to_excel_kwargs:
        to_excel_kwargs.pop('engine')

    writer = pd.ExcelWriter(filename, engine='openpyxl')

    # Python 2.x: define [FileNotFoundError] exception if it doesn't exist
    try:
        FileNotFoundError
    except NameError:
        FileNotFoundError = IOError


    try:
        # try to open an existing workbook
        writer.book = load_workbook(filename)

        # get the last row in the existing Excel sheet
        # if it was not specified explicitly
        if startrow is None and sheet_name in writer.book.sheetnames:
            startrow = writer.book[sheet_name].max_row

        # truncate sheet
        if truncate_sheet and sheet_name in writer.book.sheetnames:
            # index of [sheet_name] sheet
            idx = writer.book.sheetnames.index(sheet_name)
            # remove [sheet_name]
            writer.book.remove(writer.book.worksheets[idx])
            # create an empty sheet [sheet_name] using old index
            writer.book.create_sheet(sheet_name, idx)

        # 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

    if startrow is None:
        startrow = 0

    # write out the new sheet
    df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)

    # save the workbook
    writer.save()


days = pd.DataFrame(data={'col1': ['Monday'], 'col2': ['Tuesday']})
append_df_to_excel('Results.xlsx', days, sheet_name='Daily', index=False)

Upvotes: 1

Related Questions