python 3.9 and opepyxl : Error "zipfile.BadZipFile: File is not a zip file"

[Marco Baggio]

Hi current configuration: python: 3.9 |openpyxl: 3.0.6 | pandas: 1.2.3 |xlrd 2.0.1

Running the function below I get the error "zipfile.BadZipFile: File is not a zip file". How should I fix the code in order to get it working? Thank you

[Marco Baggio]

(PS I was looking for a way to share formatted code, but I could not find a way, sorry for that)

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')

    writer = pd.ExcelWriter(filename, engine='openpyxl')

    # Python 2.x: define [FileNotFoundError] exception if it doesn't exist

    try:

        FileNotFoundError

    except NameError:

        FileNotFoundError = IOError

    try:

        # 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

        pass

    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()

pathExport = r"F:\PYTHON\NB-Suite_python39\MNE\outputData\df.xlsx"

df1 = pd.DataFrame({'numbers': [1, 2, 3],

                    'colors': ['red', 'white', 'blue'],

                    'colorsTwo': ['yellow', 'white', 'blue']

                    })

append_df_to_excel(pathExport, df1, sheet_name="DF1", index=False, startcol=0, startrow=0)

[Marco Baggio]

Here the full trackback:

Traceback (most recent call last):

  File "<input>", line 83, in <module>

  File "<input>", line 46, in append_df_to_excel

  File "C:\Users\20180043\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook

    reader = ExcelReader(filename, read_only, keep_vba,

  File "C:\Users\20180043\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\reader\excel.py", line 124, in __init__

    self.archive = _validate_archive(fn)

  File "C:\Users\20180043\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\reader\excel.py", line 96, in _validate_archive

    archive = ZipFile(filename, 'r')

  File "C:\Users\20180043\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1257, in __init__

    self._RealGetContents()

  File "C:\Users\20180043\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1324, in _RealGetContents

    raise BadZipFile("File is not a zip file")

zipfile.BadZipFile: File is not a zip file

[Dustin Hall]

I got this error when I was attempting to read an excel file that had a "Sensitivity Tag" or any other security feature that would required a user to authenticate again before viewing the excel file. After removing the tag for the document, this issue resolved itself. 

[Marco Baggio]

thanks Dustin, in the meanwhile I found a fix, I reported the solution here : https://stackoverflow.com/questions/66531396/export-pandas-dataframe-to-xlsx-dealing-with-the-openpyxl-issue-on-python-3-9

[Sanju Kapoor]

Gzip, renaming, tracing, and 7zip expressions are the most basic ways to overcome the problem (Zipfile.badzipfile: file is not a zip) in Python. In fact, these two options necessitate the installation of new modules.

[Yogesh Mane]

@ Sanju Kapoor with due respect sir, plz read the issue then post solution. All your solutions are irrelevant to the topic.

[Patricia Watts]

I ran into this error using openpyxl with python 3.9 under MacOS Ventura 13.1 on an Apple Silicon (M1) MBP. (note - not using pandas for this.)

It turned out to be a bug in Dropbox. The xlsx file I was attempting to open in the script was listed in Finder, but it was just a stub and Dropbox was not pulling it down physically onto my machine when openpyxl was trying to open it. The issued was solved by double-clicking the file in Finder to force download (opens in Excel). Thereafter, my script was able to open the target file via openpyxl.

[Ovin Gamage]

I had the same issue. Running a code to compare 2 files using openpyxl. 

Using Visual Studio Code on MacBook. My file was saved with "Sensitive Tag". After changing that everything worked fine. 

[Charlie Clark]

On 1 Feb 2023, at 4:24, Ovin Gamage wrote:

> I had the same issue. Running a code to compare 2 files using openpyxl.
>
> Using Visual Studio Code on MacBook. My file was saved with "Sensitive
>
> Tag". After changing that everything worked fine.

Anything to do with file encryption or online functions are not covered by the specification and are likely to make the files unreadable by anything other than the application that made the changes.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226