xlsx file created with Pandas ExcelWriter requires repair

Question

As stated in the title, the xlsx created with the code below is corrupt. Can anyone see a problem in my code?

if "O-" in project:
    path = project_folder_order
elif "Q-" in project:
    path = project_folder_quote

book = load_workbook(path)

writer = pd.ExcelWriter(str(path), engine='openpyxl', mode='a')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
worksheet = writer.sheets["Create"]

worksheet.cell(row=int(no_target_lang_len) + 1, column=1).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')                                                                          

df1 = pd.read_csv(r"\\awsdfpudblapp.xxx.de\Planet\Krake\\" + project + "\\4_Term_TO_DO_" + project + ".csv")
df1_create = df1["Create"]
df1_search = df1["Search"]

df1_create.to_excel(writer, sheet_name="Create", index=False, startcol=0, startrow=1, merge_cells=True)
df1_search.to_excel(writer, sheet_name="Search", index=False, startcol=0, startrow=no_target_lang_len, merge_cells=True)

worksheet.merge_cells(start_row=int(no_target_lang_len) + 1, start_column=1, end_row=int(no_target_lang_len) + 1, end_column=4)

writer.save()
writer.close()

The Excel error message is rather vague:

error052160_01.xml Errors were detected in file 'D:\Users\xxx\4_Term_TODO_Q-21-001727-01.xlsx' Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

After letting Excel repair the file, the content does appear, but I need to save it as another version in order to be able to use the file in another application.

I also have the problem with this somewhat shorter code:

if "O-" in project:
    path = project_folder_order
elif "Q-" in project:
    path = project_folder_quote

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine='openpyxl', mode='a')
writer.book = book

df1 = pd.read_csv(r"\\awsdfpudblapp.xxx.de\Planet\Krake\\" + project + "\\4_Term_TO_DO_" + project + ".csv")
df1.to_excel(writer, sheet_name="Term candidates", index=False, merge_cells=True)


writer.save()
writer.close()

Answer 1

I faced this problem before.

In your last line of code. Just change writer.close() to book.close(). It works fine in my code. May cause from load_workbook and you didn't close them.