Write dataframe to an existing excelfile without destrying it

Question

ive got a problem.

I want to write a dataframe to an existing Excel-List which contains formulas. When i Open a workbook and use a writer with pandas, it always says there is unreadable content in it and i need to repair it when i open the Excel-List.

Do you know how to resolve this?

Here is my code to write the list:

def Writer():
    book = load_workbook(r'C:\Users\List.xlsx')
    writer = pd.ExcelWriter(r'C:\Users\List.xlsx',header=None,
     index=False, data_only=True)
    
    writer.book = book

    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    datafin=FindReqdata.datafin
    datafin.to_excel(writer, sheet_name="SheetName", startrow=2,
     startcol=5, index=None, header=None)
    writer.save()

    



Writer()

Does this answer your question? [How to write to an existing excel file without overwriting data (using pandas)?](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) — Warcupine, Mar 25 '21 at 12:41
Thats where i got the middlepart from but sadly it damages my excel-file — Thorte, Mar 25 '21 at 12:45

score 0 · Answer 1 · answered Mar 25 '21 at 12:48

0

have a look at this: https://stackoverflow.com/a/38075046/14367973

If i understood your question, you want to append more rows to a .xlsx file. The new rows are from a dataFrame that have the same number of columns than the excel file. If it is what you are trying to do the answer above should help you.

Keep the xlsx files closed while the script run sometimes it can break it.

answered Mar 25 '21 at 12:48

kaa-serpent

38
6

Yeah i tried it but once again it destroys the excel file and it just appends one row of my dataframe – Thorte Mar 25 '21 at 13:02
Ok the dataframe was empty...one second – Thorte Mar 25 '21 at 13:19
i had a similar problem where my file was getting break the python script had no problem to append to it but when i tried to open it up manually i had to repair it. is it something like that that you are experiencing ? – kaa-serpent Mar 26 '21 at 15:27
Yes that is exactly my problem. Did you solve it? – Thorte Mar 30 '21 at 06:41
i managed to solve it but i do not remember how exactly, i will have a look at the code i used. – kaa-serpent Mar 31 '21 at 11:48
have you tried adding `index=False` ? `df.to_csv(path_to_csv, sep='|', index=False)` – kaa-serpent Mar 31 '21 at 11:56

score 0 · Accepted Answer · answered Mar 30 '21 at 09:38

0

Ok so apparently openpyxl has a problem with connection in the excel spreadsheet. Because one Sheet has connections in it, the file is broken after editing it. I am still trying to fix this bug.

answered Mar 30 '21 at 09:38

Thorte

31
5

Write dataframe to an existing excelfile without destrying it

2 Answers2