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

CodePudding user response：

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')

CodePudding user response：

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:

1. read the existing excel file into a dataframe

2. append the new data to that dataframe

3. write the combined dataframe to the same file

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