Append PandasDataframe to existing ExcelFile

Question

I want to add the Data which is saved inside of a Pandas Dataframe to an existing ExcelSheet. I have seen a solution which adds the data to a new sheet in the excel file, but i need all the Data in one ExcelSheet so it just needs to add a new row and saves the data in the new row.

This Error accurs when I try the Solution with the ExcelWriter: KeyError: "There is no item named 'xl/sharedStrings.xml' in the archive"' when trying to open Excel

Code:

def render(app: Dash) -> html.Div:
    @app.callback(
        Output(ids.ANSWER_OUTPUT, "children"),
        Input(ids.BUTTON, "n_clicks"),
        State(ids.QUESTION_1, "value"),
        ...
        State(ids.DATE_PICKER_SINGLE, "date"),
    )
    def collect(
        n_clicks,
        answer_1_value,
        ...
        date_picker_date,
    ):
        dataframe = [
            answer_1_value,
            ...
            date_picker_date,
        ]
        df = pd.DataFrame(dataframe).T
        df.to_excel("output.xlsx", header=False)
        if n_clicks is not None:
            return df

What do I need to add as Code in mine so it adds to the existing file? Thx in advance

Check - https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas — Divyank, Aug 23 '22 at 13:59
Does this answer your question? [Append existing excel sheet with new dataframe using python pandas](https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas) — rtoijala, Aug 24 '22 at 11:35

score 0 · Answer 1 · answered Aug 23 '22 at 14:05

0

I usually use this workbook code to append to a xlsx in the last line. Hope it helps

from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import load_workbook

#Openning Excel via workbook
wb = load_workbook('file_path/file.xlsx')
sheet_to_update= wb["Sheet1"]

#Saving
for line in dataframe_to_rows(df, index=False, header=False):  
    sheet_to_update.append(line)
wb.save('file_path/file.xlsx')

answered Aug 23 '22 at 14:05

Suetam016

101
7

sadly doesnt work:( – SebastianHeeg Aug 24 '22 at 11:59

score 0 · Answer 2 · answered Aug 24 '22 at 17:54

0

It would be helpful if pandas had this ability baked in, however, it relies upon third-party libraries for the excel writer engines.

The simplest solution here would be:

read the existing excel file into a dataframe
append the new data to that dataframe
write the combined dataframe to the same file

df.to_excel(path="output.xlsx", if_sheet_exists="replace", sheet_name="Sheet1")

answered Aug 24 '22 at 17:54

JerodG

1,248
1
16
34

this would also be a great solution. I have done it with a csv. I append to a .csv and then query the data in excel – SebastianHeeg Aug 25 '22 at 08:20

Append PandasDataframe to existing ExcelFile

2 Answers2