Python Forum
Export dataframe to xlsx - Error "zipfile.BadZipFile: File is not a zip file"
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Export dataframe to xlsx - Error "zipfile.BadZipFile: File is not a zip file"
#11
You can solve the problem with non-existing file by simply moving the writer line inside the try block and creating another one, with mode='w' inside the except block, e.g.:

def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
                           truncate_sheet=False, 
                           **to_excel_kwargs):
        """
        Append a DataFrame [df] to existing Excel file [filename]
        into [sheet_name] Sheet.
        If [filename] doesn't exist, then this function will create it.
     
        Parameters:
          filename : File path or existing ExcelWriter
                     (Example: '/path/to/file.xlsx')
          df : dataframe to save to workbook
          sheet_name : Name of sheet which will contain DataFrame.
                       (default: 'Sheet1')
          startrow : upper left cell row to dump data frame.
                     Per default (startrow=None) calculate the last row
                     in the existing DF and write to the next row...
          truncate_sheet : truncate (remove and recreate) [sheet_name]
                           before writing DataFrame to Excel file
          to_excel_kwargs : arguments which will be passed to `DataFrame.to_excel()`
                            [can be dictionary]
     
        Returns: None
     
        (c) [MaxU](https://stackoverflow.com/users/5741205/maxu?tab=profile)
        """
        from openpyxl import load_workbook
     
        # ignore [engine] parameter if it was passed
        if 'engine' in to_excel_kwargs:
            to_excel_kwargs.pop('engine')
     
        
     
        # Python 2.x: define [FileNotFoundError] exception if it doesn't exist 
        try:
            FileNotFoundError
        except NameError:
            FileNotFoundError = IOError
     
     
        try:
            writer = pd.ExcelWriter(filename, engine='openpyxl', mode='a')
            # try to open an existing workbook
            writer.book = load_workbook(filename)
             
            # get the last row in the existing Excel sheet
            # if it was not specified explicitly
            if startrow is None and sheet_name in writer.book.sheetnames:
                startrow = writer.book[sheet_name].max_row
     
            # truncate sheet
            if truncate_sheet and sheet_name in writer.book.sheetnames:
                # index of [sheet_name] sheet
                idx = writer.book.sheetnames.index(sheet_name)
                # remove [sheet_name]
                writer.book.remove(writer.book.worksheets[idx])
                # create an empty sheet [sheet_name] using old index
                writer.book.create_sheet(sheet_name, idx)
             
            # copy existing sheets
            writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
        except FileNotFoundError:
            # file does not exist yet, we will create it
            writer = pd.ExcelWriter(filename, engine='openpyxl')
     
        if startrow is None:
            startrow = 0
     
        # write out the new sheet
        df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
     
        # save the workbook
        writer.save()
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to form a dataframe reading separate dictionaries from .txt file? Doug 1 2,636 Nov-09-2020, 09:24 AM
Last Post: PsyPy
  strange error from pandas dataframe djf123 1 2,406 Jul-27-2020, 05:25 AM
Last Post: scidam
  Filter data based on a value from another dataframe column and create a file using lo pawanmtm 1 2,744 Jul-15-2020, 06:20 PM
Last Post: pawanmtm
  Fatal Python error: initfsencoding: unable to load the file system codec gauravbhardwajee 12 23,430 Apr-30-2020, 07:45 PM
Last Post: barrpath
  error bars with dataframe and pandas Hucky 4 2,154 Apr-27-2020, 02:02 AM
Last Post: Hucky
  error : value of a DataFrame is ambiguous Sonata 1 1,253 Apr-24-2020, 05:40 PM
Last Post: anbu23
  XLSX file with multiple sheets to josn file ovidius 2 1,152 Apr-05-2020, 09:22 AM
Last Post: ovidius
  How to copy a .csv worksheet into a .xlsx file without the number values turning into YoshikageKira 7 1,595 Mar-28-2020, 10:38 AM
Last Post: buran
  get "Birthdate" from File inside a Zipfile lastyle 0 761 Jan-05-2020, 12:34 AM
Last Post: lastyle
  Filter value from DataFrame apply a function and save to xlsx zinho 1 1,170 Dec-22-2019, 03:54 PM
Last Post: zinho

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020