如何将Excel中的内嵌电子表格加载到Pandas中？

我的Excel文件包含内嵌电子表格，用以下Pandas代码读取时，仅返回单元格中的名称，未获取到预期的内嵌表格数据：

import pandas as pd
df = pd.read_excel('t1-7.xlsx')

print(df)

print("\n\n\n\n")

print(df.iloc[:,2][8])

执行后输出结果：

Contents  ...                                         Unnamed: 3
0               NaN  ...                                                NaN
1  Type of Dwelling  ...                                                NaN
2               NaN  ...                                                NaN
3               NaN  ...  Resident Households by Type of Dwelling, House...
4               NaN  ...  Resident Households by Type of Dwelling, Ethni...
5               NaN  ...  Resident Households by Type of Dwelling, Marit...
6               NaN  ...  Resident Households by Type of Dwelling, Ethni...
7               NaN  ...  Resident Households by Type of Dwelling and Ag...
8               NaN  ...  Resident Households by Type of Dwelling and Hi...
9               NaN  ...  Resident Households by Type of Dwelling and Oc...
[10 rows x 4 columns]





6.0

问题原因 #

Pandas默认的read_excel仅读取当前工作表的单元格内容，而内嵌电子表格属于OLE嵌入对象，无法通过常规读取方式直接获取。

解决方法 #

以下两种方案可提取内嵌表格数据：

方案1：Windows环境用win32com.client提取

借助Windows系统的Excel COM接口直接操作内嵌对象：

import win32com.client as win32
import pandas as pd

# 启动Excel后台进程
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False

# 打开目标文件
wb = excel.Workbooks.Open(r'你的文件路径\t1-7.xlsx')
ws = wb.Worksheets(1)  # 假设内嵌表格在第一个工作表

# 遍历所有OLE对象
for ole_obj in ws.OLEObjects():
    # 筛选Excel类型的内嵌对象
    if ole_obj.ProgID == 'Excel.Sheet.12':
        ole_obj.Activate()
        embedded_wb = excel.ActiveWorkbook
        embedded_ws = embedded_wb.Worksheets(1)
        
        # 读取内嵌表格数据到DataFrame
        data_range = embedded_ws.UsedRange.Value
        df_embedded = pd.DataFrame(data_range[1:], columns=data_range[0])
        
        print("内嵌表格数据：")
        print(df_embedded)
        
        embedded_wb.Close(SaveChanges=False)

# 清理资源
wb.Close(SaveChanges=False)
excel.Quit()

需先安装依赖：pip install pywin32

方案2：跨平台用oletools提取

通过oletools提取内嵌的Excel文件，再用Pandas读取：

from oletools.oleobj import OleObjectParser
import pandas as pd
import io

# 解析文件中的OLE对象
with open('t1-7.xlsx', 'rb') as f:
    parser = OleObjectParser(f)
    for obj in parser.parse():
        # 筛选内嵌的Excel文件
        if obj.is_package and obj.filename.endswith('.xlsx'):
            embedded_data = obj.data
            # 读取内存中的Excel数据
            df_embedded = pd.read_excel(io.BytesIO(embedded_data))
            print("内嵌表格数据：")
            print(df_embedded)

需先安装依赖：pip install oletools

注意事项 #

• 若内嵌对象是CSV等其他格式，需调整读取逻辑匹配对应格式
• 两种方案均需确保内嵌对象未被加密或损坏

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