python-3.xfileiopandas.excelwriter

why pd.ExcelWriter can't create new file if not exist when setting mode='a'


I've learned from Python - Files I/O mode='a':

Opens a file for appending. The file pointer is at the end of the file if the file exists. That is, the file is in the append mode. If the file does not exist, it creates a new file for writing.

So where i can find an explaination about why pd.ExcelWriter(mode='a) can not creat file if file doesn't exists?

If pd.ExcelWriter('path+filename', mode='a') does not creat a new file. Is there any other better way to do so? The only way i can find is

if os.path.exists(filename):
    with pd.ExcelWriter(filename, mode='a') as writer:
        "blabla"
else:
    with pd.ExcelWriter(filename, mode='w') as writer:
        "blabla"

Solution

  • An .xlsx file is a zipfile with a particular predetermined structure. It makes no sense to append anything to an existing .xslx file. Either Excel will ignore what you have appended, or will refuse to open the file at all.

    If you want to add a new worksheet to an existing .xlsx file you will have to read the file using something like openpyxl and amend its internal structure yourself.

    Calling open() with mode "a" is for textfiles and straightforward record-oriented binary files, where it makes sense to simply tack bytes onto the end. More complex file structures are the province of the application, not the filesystem.