After save dataframe to excel I can't open the excel file

Question

I created dataframe, and use df.to_excel('test.xlsx', index=False). I can see my python code generated excel file in a local directory, but problem is I can't open it with excel.

I also added more parameter engine='xlsxwriter' in df.to_excel('test.xlsx', index=False). Thus I tried df.to_excel('test.xlsx', index=False, engine='xlsxwriter'), but didn't work out.

import pandas as pd
import numpy as np

df = pd.read_csv('123.tsv', sep='\t')
df['M'] = df['M'].astype(str)
m = df.M.str.split(',', expand=True).values.ravel()
df = df.dropna()
df = df[~df.M.str.contains("@")]
df = df.drop_duplicates()
df.to_excel('123.xlsx', index=False, engine='xlsxwriter')

expected outcome: just wanna open 123.xlsx in excel

actual result:

Excel cannot open the file '123.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. (Mac Excel 2016)

Answer 1

I'm responding some time later, but it may be helpful to someone

You may try by using an ExcelWriter, paying attention to include the .close(), which actually saves the file. In fact, as documentation reports "The writer should be used as a context manager. Otherwise, call close() to save and close any opened file handles."

import pandas as pd

writer = pd.ExcelWriter('your_filename.xlsx'))
df.to_excel(writer, sheet_name='your_sheet_name')
writer.save()

Answer 2

It should work as expected. I tried your program with the following sample input (tab separated):

M   L
foo 123
bar 456
baz 789

And was able to open the output 123.xlsx file:

Can you try this simple input and see if it works.

Answer 3

It is excel issue after I updated it. Please close this issue stackoverflow team. Thank you.