如何用openpyxl正确读取跨工作簿的单元格引用公式？

当你用openpyxl读取包含跨工作簿引用的Excel文件时，出现=B2 + '[1]Sheet1'!$B$1这类占位符式引用，是因为openpyxl在加载主工作簿时，若对应的外部工作簿未被加载或无法访问，会自动将外部文件路径替换为[索引]格式的占位符。以下是两种可行的解决方法：

方法一：同时加载关联的外部工作簿 #

如果外部工作簿文件可正常访问，可在加载主工作簿前先加载外部文件，让openpyxl自动解析完整的引用路径：

import pandas as pd
from openpyxl import load_workbook

# 先加载外部工作簿（路径需替换为实际文件路径）
external_wb = load_workbook(r'C:\path\to\file\example2.xlsx')
# 加载主工作簿
main_wb = load_workbook('example1.xlsx', data_only=False)
sheet = main_wb[sheet_name]

# 直接读取单元格公式，此时会保留原始跨工作簿路径
data = []
for row in sheet.iter_rows(values_only=False):
    row_data = [cell.formula if cell.data_type == 'f' else cell.value for cell in row]
    data.append(row_data)

df = pd.DataFrame(data)

方法二：手动映射外部链接替换占位符 #

若无法加载外部工作簿，可通过主工作簿的external_links属性获取外部文件的实际路径，再替换公式中的占位符：

import pandas as pd
from openpyxl import load_workbook

main_wb = load_workbook('example1.xlsx', data_only=False)
sheet = main_wb[sheet_name]

# 构建占位符与实际路径的映射表
external_map = {}
for idx, link in enumerate(main_wb.external_links, start=1):
    # 提取外部工作簿路径，不同Excel版本可能需调整属性获取方式
    if hasattr(link, 'target'):
        # 还原Excel公式中路径的格式（带单引号和方括号）
        full_path = f"'{link.target}'"
        external_map[f'[{idx}]'] = full_path

# 遍历单元格处理公式
data = []
for row in sheet.iter_rows(values_only=False):
    row_data = []
    for cell in row:
        if cell.data_type == 'f':
            formula = cell.formula
            # 替换所有占位符
            for placeholder, actual_path in external_map.items():
                formula = formula.replace(placeholder, actual_path)
            row_data.append(formula)
        else:
            row_data.append(cell.value)
    data.append(row_data)

df = pd.DataFrame(data)

注意：部分Excel文件的外部链接路径可能存储在link.file_link.target等其他属性下，若上述代码无法正确获取路径，可打印main_wb.external_links的结构来调整属性访问逻辑。

内容的提问来源于stack exchange，提问作者Brayan Muñoz