How to close xlsx file correctly in pd.ExcelWriter?

Question

I'm trying to write table to one xlsx file.

# file path 'E:/file.xlsx'
file = Path(file_path)
# delete file if it exists
if file.is_file():
    file.unlink()
    print("File deleted")

# dfs ontains in dictionary
for key in dfs.keys():
    # if file exists make ExcelWriter with mode 'append'
    if file.is_file():
        with pd.ExcelWriter(file_path, mode = 'a', if_sheet_exists='replace') as writer:
            new_df = transform_df(dfs[key][0], dfs[key][1])
            new_df.to_excel(writer, sheet_name=key, index=False)
            print(f"{key} - sheet appended")
    # if file not exists mode is 'write'
    else:
        with pd.ExcelWriter(file_path, mode = 'w') as writer:
            new_df = transform_df(dfs[key][0], dfs[key][1])
            new_df.to_excel(writer, sheet_name=key, index=False)
            print("New file created")

 writer.close()

Most times I can use this code to write dfs in file, but sometimes I get error then trying to delete file, cause file is still open.

writer._save() or writer.close() cause ValueError: I/O operation on closed file.

Is file closing automatically?

Pandas v. '2.2.1', openpyxl v. '3.1.2'

Answer 1

Explanation

It's closing automatically because you are using a context manager.

Here's an example from the link I added:

with open('some_file', 'w') as opened_file:
    opened_file.write('Hola!')

is equivalent to:

file = open('some_file', 'w')
try:
    file.write('Hola!')
finally:
    file.close()

Solution

So keeping that in mind, you already close your file when the with block is finished.

        with pd.ExcelWriter(file_path, mode = 'w') as writer:
            new_df = transform_df(dfs[key][0], dfs[key][1])
            new_df.to_excel(writer, sheet_name=key, index=False)
            print("New file created")

so you can remove this line

 writer.close()

and it should be ok.