CODEWITHSUNDEEP
pythonpandaspandas.excelwriter
Seamus Pitcher
Seamus Pitcher

Reputation: 53

Pandas library writing corrupted xlsx files when using an ExcelWriter

I'm trying to write some data to an excel spreadsheet. Whenever I've tried to use the DataFrame.to_excel method with a file path instead of an ExcelWriter object as the first argument (e.g. pd.DataFrame([1, 2, 3]).to_excel("test.xlsx") and that works fine except that it rewrites the whole file every time. I want to append data and I don't see an option in the documentation that lets you set it to something like append mode. So, I'm using an ExcelWriter object because that seems to have an append mode if you initialise is as follows (documentation): writer = ExcelWriter("test.xlsx", mode='a', if_sheet_exists="overlay"). Then, if I understand correctly, you should be able to pass that object into the to_excel function like this: pd.DataFrame([1, 2, 3]).to_excel(writer) and it shouldn't rewrite the whole file. But, when I use an ExcelWriter to create or modify the file, excel gives me the error:

"Excel cannot open the file 'test.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

I have tried initialising the ExcelWriter with only the first argument, writer = ExcelWriter("test.xlsx"), and that produces the same error when opening the file.

I think the writer is writing corrupted excel files, anyone know a fix?

Upvotes: 2

Views: 761

Answers (1)

Seamus Pitcher
Seamus Pitcher

Reputation: 53

Fixed it, I wasn't closing the XlsxWriter with

writer.close()

Upvotes: 2

Related Questions