从SharePoint/OneDrive读取Excel文件报错：File is not a zip file

问题 #

尝试用Python读取SharePoint团队OneDrive共享文件夹中的Excel文件，最初报错要求手动指定engine，设置engine='openpyxl'后，出现**"File is not a zip file"**错误。确认文件是Microsoft Excel创建的，想了解存储位置是否影响报错，以及解决方法。

代码示例 #

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File 

url_sp = 'https://company.sharepoint.com/teams/TeamE'
username_sp = 'MyUsername'
password_sp = 'MyPassword'
folder_url_sp = '/Shared%20Documents/02%20Team%20IAP/06_Da-An/Data/E/Edate.xlsx'

# 认证
ctx_auth = AuthenticationContext(url_sp)
if ctx_auth.acquire_token_for_user(username_sp, password_sp):
    ctx = ClientContext(url_sp, ctx_auth)
    web = ctx.web
    ctx.load(web)
    ctx.execute_query()
    print('Authentication sucessfull')

else:
    print(ctx_auth.get_last_error())
    
import io 

response = File.open_binary(ctx,folder_url_sp)

bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0) 

data = pd.read_excel(bytes_file_obj,sheet_name = None, engine = 'openpyxl')

完整报错信息 #

BadZipFile                                Traceback (most recent call last)
Cell In[29], line 32
     29 bytes_file_obj.write(response.content)
     30 bytes_file_obj.seek(0) 
---> 32 data = pd.read_excel(bytes_file_obj, sheet_name= None, engine = 'openpyxl')

File ~\Anaconda3\lib\site-packages\pandas\util\_decorators.py:311, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
    305 if len(args) > num_allow_args:
    306     warnings.warn(
    307         msg.format(arguments=arguments),
    308         FutureWarning,
    309         stacklevel=stacklevel,
    310     )
---> 311 return func(*args, **kwargs)

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py:457, in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
    455 if not isinstance(io, ExcelFile):
    456     should_close = True
---> 457     io = ExcelFile(io, storage_options=storage_options, engine=engine)
    458 elif engine and engine != io.engine:
    459     raise ValueError(
    460         "Engine should not be specified when passing "
    461         "an ExcelFile - ExcelFile already has the engine set"
    462     )

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py:1419, in ExcelFile.__init__(self, path_or_buffer, engine, storage_options)
   1416 self.engine = engine
   1417 self.storage_options = storage_options
-> 1419 self._reader = self._engines[engine](self._io, storage_options=storage_options)

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py:525, in OpenpyxlReader.__init__(self, filepath_or_buffer, storage_options)
    514 """
    515 Reader using openpyxl engine.
    516 
   (...)
    522     passed to fsspec for appropriate URLs (see ``_get_filepath_or_buffer``)
    523 """
    524 import_optional_dependency("openpyxl")
-> 525 super().__init__(filepath_or_buffer, storage_options=storage_options)

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py:518, in BaseExcelReader.__init__(self, filepath_or_buffer, storage_options)
    516 self.handles.handle.seek(0)
    517 try:
-> 518     self.book = self.load_workbook(self.handles.handle)
    519 except Exception:
    520     self.close()

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py:536, in OpenpyxlReader.load_workbook(self, filepath_or_buffer)
    533 def load_workbook(self, filepath_or_buffer: FilePath | ReadBuffer[bytes]):
    534     from openpyxl import load_workbook
-> 536     return load_workbook(
    537         filepath_or_buffer, read_only=True, data_only=True, keep_links=False
    538     )

File ~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py:315, in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    288 def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA,
    289                   data_only=False, keep_links=True):
    290     """Open the given filename and return the workbook
    291 
    292     :param filename: the path to open or a file-like object
   (...)
    313 
    314     """
-> 315     reader = ExcelReader(filename, read_only, keep_vba,
    316                         data_only, keep_links)
    317     reader.read()
    318     return reader.wb

File ~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py:124, in ExcelReader.__init__(self, fn, read_only, keep_vba, data_only, keep_links)
    122 def __init__(self,  fn, read_only=False, keep_vba=KEEP_VBA,
    123               data_only=False, keep_links=True):
-> 124     self.archive = _validate_archive(fn)
    125     self.valid_files = self.archive.namelist()
    126     self.read_only = read_only

File ~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py:96, in _validate_archive(filename)
     89             msg = ('openpyxl does not support %s file format, '
     90                    'please check you can open '
     91                    'it with Excel first. '
     92                    'Supported formats are: %s') % (file_format,
     93                                                    ','.join(SUPPORTED_FORMATS))
     94         raise InvalidFileException(msg)
---> 96 archive = ZipFile(filename, 'r')
     97 return archive

File ~\Anaconda3\lib\zipfile.py:1266, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps)
   1264 try:
   1265     if mode == 'r':
-> 1266         self._RealGetContents()
   1267     elif mode in ('w', 'x'):
   1268         # set the modified flag so central directory gets written
   1269         # even if no files are added to the archive
   1270         self._didModify = True

File ~\Anaconda3\lib\zipfile.py:1333, in ZipFile._RealGetContents(self)
   1331     raise BadZipFile("File is not a zip file")
   1332 if not endrec:
-> 1333     raise BadZipFile("File is not a zip file")
   1334 if self.debug > 1:
   1335     print(endrec)

BadZipFile: File is not a zip file

解答 #

存储位置是否影响报错？ #

存储位置本身不会直接导致该错误，但SharePoint的文件访问流程如果出现异常（比如权限不足、路径错误、下载内容为错误页面而非真实Excel文件），会间接引发问题。xlsx格式本质是zip压缩包，若下载的是HTML错误页面（如权限提示页），就会被openpyxl判定为非zip格式。

解决步骤 #

1. 验证下载内容有效性
   在读取前先检查下载的字节流，确认是真实的Excel文件：

   # 获取response.content后添加以下代码
   print(f"文件字节大小: {len(response.content)}")
   # 保存到本地，手动验证能否用Excel打开
   with open("test_download.xlsx", "wb") as f:
       f.write(response.content)
   

   若保存的文件无法打开，说明下载流程存在问题，需排查权限或路径。

2. 修正文件路径格式
   代码中使用了URL编码的路径（如%20），改用原始路径格式，并通过官方推荐的get_file_by_server_relative_url方法获取文件：

   # 替换原有的File.open_binary部分
   file_url = "/Shared Documents/02 Team IAP/06_Da-An/Data/E/Edate.xlsx"
   file = ctx.web.get_file_by_server_relative_url(file_url)
   ctx.load(file)
   ctx.execute_query()
   response = file.open_binary()
   

3. 检查权限认证
   确认账号拥有文件访问权限，若遇到MFA或密码过期问题，可改用应用权限认证：

   # 应用权限认证（需提前在Azure AD注册应用）
   from office365.runtime.auth.client_credential import ClientCredential
   
   client_id = "你的应用ID"
   client_secret = "你的应用密钥"
   ctx = ClientContext(url_sp).with_credentials(ClientCredential(client_id, client_secret))
   web = ctx.web
   ctx.load(web)
   ctx.execute_query()
   

4. 确保openpyxl版本兼容
   版本不兼容可能导致解析异常，建议安装稳定版本：

   pip install openpyxl==3.1.2
   

内容的提问来源于stack exchange，提问作者user20216792