Python访问SharePoint本地Excel文件遇[Errno 13]权限拒绝问题排查

• 团队流程：以Excel为前端上传数据到数据库，通过VBA调用依赖openpyxl和xlwings的Python脚本，实现Excel文件的数据提取与回写；团队成员基于同一模板另存文件，文件路径和名称不固定。
• 异常现象：代码周期性触发权限拒绝错误，文件关闭时运行正常；暂停OneDrive同步后问题依旧，通常关闭并重新打开文件可解决。
• 已做处理：VBA中已实现获取本地文件路径而非SharePoint地址的逻辑。

def data_frame_from_xlsx_range(xlsx_file, range_name, date_columns=None):
    """ Get a single rectangular region from the specified file.
    range_name can be a standard Excel reference ('Sheet1!A2:B7') or
    refer to a named region ('my_cells')."""
    wb = openpyxl.load_workbook(xlsx_file, data_only=True, read_only=True)
    if '!' in range_name:
        # passed a worksheet!cell reference
        ws_name, reg = range_name.split('!')
        if ws_name.startswith("'") and ws_name.endswith("'"):
            # optionally strip single quotes around sheet name
            ws_name = ws_name[1:-1]
        region = wb[ws_name][reg]
    else:
        # passed a named range; find the cells in the workbook
        full_range = wb.get_named_range(range_name)
        if full_range is None:
            raise ValueError(
                'Range "{}" not found in workbook "{}".'.format(range_name, xlsx_file)
            )
        # convert to list (openpyxl 2.3 returns a list but 2.4+ returns a generator)
        destinations = list(full_range.destinations)

        if len(destinations) > 1:
            raise ValueError(
                'Range "{}" in workbook "{}" contains more than one region.'
                .format(range_name, xlsx_file)
            )
        ws, reg = destinations[0]
        # convert to worksheet object (openpyxl 2.3 returns a worksheet object
        # but 2.4+ returns the name of a worksheet)
        if isinstance(ws, str):
            ws = wb[ws]
        region = ws[reg]
# catch a single-cell range (untested):
# if not isinstance(region, 'tuple'): df = pd.DataFrame(region.value)
df = pd.DataFrame([cell.value for cell in row] for row in region)
df.columns = df.iloc[0]
df = df[1:]
df.columns = [col.replace(' ','_') for col in df.columns]
# drop any empty rows at the end of the data
df.dropna(thresh=1, inplace=True)
last_row_with_data = df[df.notna().any(axis=1)].index[-1]
df.drop(index=df.index[last_row_with_data + 1:], inplace=True)

def custom_date_parser(val):
    try:
        return pd.to_datetime(val, origin='1899-12-30', unit='D')
    except:
        return val

if date_columns:
    for col in date_columns:
        if col in df.columns:
            df[col] = df[col].apply(custom_date_parser)

return df

Traceback (most recent call last):
  File "C:\Users\waly\PycharmProjects\pythonProject\db_execution.py", line 185, in <module>
    match_names_to_db(fileloc, user, host, db )
  File "C:\Users\waly\PycharmProjects\pythonProject\db_execution.py", line 50, in match_names_to_db
    tracker_names = data_frame_from_xlsx_range(fileloc, 'tracker_names_to_match')
  File "C:\Users\waly\PycharmProjects\pythonProject\sqlupdate.py", line 15, in data_frame_from_xlsx_range
    wb = openpyxl.load_workbook(xlsx_file, data_only=True, read_only=True)
  File "C:\Users\waly\PycharmProjects\pythonProject\venv\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,
  File "C:\Users\waly\PycharmProjects\pythonProject\venv\lib\site-packages\openpyxl\reader\excel.py", line 124, in __init__
    self.archive = _validate_archive(fn)
  File "C:\Users\waly\PycharmProjects\pythonProject\venv\lib\site-packages\openpyxl\reader\excel.py", line 96, in _validate_archive
    archive = ZipFile(filename, 'r')
  File "C:\Users\waly\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1248, in __init__
    self.fp = io.open(file, filemode)
PermissionError: [Errno 13] Permission denied: 'C:\\Users\\waly\\OneDrive -  Global\\Documents\\National Corporation/Tracker Template_l_Group.xlsm'

可能遗漏的原因 #

1. Excel独占锁机制：Excel打开文件时，即使处于只读状态，Office也可能因宏运行、自动保存触发等场景持有文件独占锁，导致openpyxl无法直接读取文件。
2. 临时文件干扰：Excel打开文件时会生成~$xxx.xlsm格式的隐藏临时文件，系统或同步进程可能临时占用这些文件，间接影响主文件的访问权限。
3. 路径解析异常：错误路径中混合了\和/分隔符，虽Python通常兼容，但底层文件系统解析时可能触发权限校验异常。
4. 资源未释放：若同时使用xlwings操作文件，未正确关闭工作簿会导致文件句柄泄漏，后续openpyxl访问时被拒绝。

优雅可靠的解决方案 #

方案1：用xlwings直接读取数据（替代openpyxl） #

既然已依赖xlwings，直接通过Excel COM接口读取内存中的工作簿，完全规避文件锁问题：

import xlwings as xw
import pandas as pd

def data_frame_from_xlsx_range(xlsx_file, range_name, date_columns=None):
    # 连接到已打开的工作簿，未打开则后台启动Excel打开文件
    app = xw.apps.active if xw.apps.active else xw.App(visible=False)
    target_wb = None
    for wb in app.books:
        if wb.fullname == xlsx_file:
            target_wb = wb
            break
    if not target_wb:
        target_wb = app.books.open(xlsx_file)
    
    # 读取指定范围并转为DataFrame
    try:
        rng = target_wb.range(range_name)
        df = rng.options(pd.DataFrame, header=1, index=False).value
    finally:
        # 仅关闭我们启动的Excel实例，避免关闭用户正在使用的窗口
        if not xw.apps.active:
            app.quit()
    
    # 复用原有数据清洗逻辑
    df.columns = [col.replace(' ','_') for col in df.columns]
    df.dropna(thresh=1, inplace=True)
    last_row_with_data = df[df.notna().any(axis=1)].index[-1]
    df.drop(index=df.index[last_row_with_data + 1:], inplace=True)
    
    def custom_date_parser(val):
        try:
            return pd.to_datetime(val, origin='1899-12-30', unit='D')
        except:
            return val
    
    if date_columns:
        for col in date_columns:
            if col in df.columns:
                df[col] = df[col].apply(custom_date_parser)
    
    return df

优势：直接读取Excel内存数据，无需处理文件锁；自动兼容SharePoint本地同步路径，无需额外路径转换。

方案2：添加重试机制与路径标准化（兼容现有openpyxl代码） #

如果要保留openpyxl逻辑，通过重试规避临时锁，同时标准化路径格式：

import os
import time
from openpyxl import load_workbook
import pandas as pd

def is_file_accessible(filepath):
    try:
        with open(filepath, 'rb'):
            return True
    except PermissionError:
        return False

def data_frame_from_xlsx_range(xlsx_file, range_name, date_columns=None, max_retries=3, retry_delay=2):
    # 统一路径分隔符，避免解析异常
    xlsx_file = os.path.normpath(xlsx_file)
    
    # 重试读取文件，规避临时锁
    for _ in range(max_retries):
        if is_file_accessible(xlsx_file):
            break
        time.sleep(retry_delay)
    else:
        raise PermissionError(f"文件{xlsx_file}被占用，重试{max_retries}次仍无法访问")
    
    wb = load_workbook(xlsx_file, data_only=True, read_only=True)
    # 后续原有逻辑保持不变...

方案3：VBA端优化文件状态 #

在调用Python前，确保Excel文件处于稳定状态：

Sub CallPythonScript()
    ' 保存文件，确保数据写入磁盘
    ThisWorkbook.Save
    
    ' 临时禁用自动保存，减少文件锁触发
    Dim originalAutoSave As Boolean
    originalAutoSave = Application.AutoSaveOn
    Application.AutoSaveOn = False
    
    ' 获取本地路径（复用已实现的逻辑）
    Dim localPath As String
    localPath = GetLocalFilePath(ThisWorkbook.FullName)
    
    ' 调用Python脚本
    Shell "python ""C:\path\to\your\script.py"" """ & localPath & """", vbNormalFocus
    
    ' 恢复自动保存设置
    Application.AutoSaveOn = originalAutoSave
End Sub

推广建议 #

1. 优先推广方案1：基于xlwings的实现最稳定，无需团队成员额外操作，完全兼容现有流程。
2. 统一团队Excel设置：建议关闭自动保存或延长自动保存间隔，减少临时锁触发概率。
3. 规范文件命名：要求模板另存时避免特殊字符，统一使用下划线或短横线分隔名称，降低路径解析异常风险。

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