Search code examples
pythonpandaspandas.excelwriter

Why does calling pd.ExcelWriter in this way create an invalid file format or extension?

I am trying to export two Pandas dataframes to the same Excel file, in different sheets.

The code below runs ok, but the file it creates has a size of 0kb. When I try to open it in Excel, I get the message "Excel cannot open the file myfile.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":

V_writer = pd.ExcelWriter("myfile.xlsx", mode = 'w')
V_df_1.to_excel(V_writer, sheet_name = "df_1", index = False)
V_df_2.to_excel(V_writer, sheet_name = "df_2", index = False)

However, the following code works perfectly:

with pd.ExcelWriter("myfile.xlsx", mode = 'w') as V_writer:
    V_df_1.to_excel(V_writer, sheet_name = "df_1", index = False)
    V_df_2.to_excel(V_writer, sheet_name = "df_1", index = False)

Could anyone explain why this is please? The file name and file extension are the same in each bit of code and the same parameters are being used, so I don't understand why one creates an invalid file and the other does not.

Solution

  • You need to close your file to properly write the changes and for it to be valid:

    V_writer = pd.ExcelWriter("myfile.xlsx", mode = 'w')
V_df_1.to_excel(V_writer, sheet_name = "df_1", index = False)
V_df_2.to_excel(V_writer, sheet_name = "df_2", index = False)
V_writer.close()

    The with statement does that automatically for you (by calling the __exit__ method, which does nothing more than running close):

    V_writer.__exit__??

Source:
    def __exit__(
        self,
        exc_type: type[BaseException] | None,
        exc_value: BaseException | None,
        traceback: TracebackType | None,
    ) -> None:
        self.close()