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
  Errors if an xlsx file has blank rows in the beginning…. tester_V 1 790 Aug-28-2023, 06:22 PM
Last Post: deanhystad
  Creating a Dataframe from Zenodo zip file with multiple CSVs about Spotify man0s 0 1,326 Apr-26-2022, 01:45 PM
Last Post: man0s
  export dataframe to file.txt dramauh 5 1,884 Apr-21-2022, 01:23 AM
Last Post: sarahroxon7
  How to form a dataframe reading separate dictionaries from .txt file? Doug 1 4,197 Nov-09-2020, 09:24 AM
Last Post: PsyPy
  strange error from pandas dataframe djf123 1 4,002 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 4,242 Jul-15-2020, 06:20 PM
Last Post: pawanmtm
  Fatal Python error: initfsencoding: unable to load the file system codec gauravbhardwajee 12 28,033 Apr-30-2020, 07:45 PM
Last Post: barrpath
  error bars with dataframe and pandas Hucky 4 4,145 Apr-27-2020, 02:02 AM
Last Post: Hucky
  error : value of a DataFrame is ambiguous Sonata 1 2,201 Apr-24-2020, 05:40 PM
Last Post: anbu23
  XLSX file with multiple sheets to josn file ovidius 2 2,190 Apr-05-2020, 09:22 AM
Last Post: ovidius

Forum Jump:

User Panel Messages

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