读取Excel工作表多表生成DataFrame：表ID不连续问题求助

我有一个包含不同行列数表格的Excel工作表，编写了如下代码读取表格并保存为独立DataFrame，代码可运行但返回的表编号/ID不连续（并非从Table1开始）。

原代码如下：

from openpyxl import load_workbook
import pandas as pd

# read file
df = load_workbook(r'C:\Users\zz\Desktop\Analyticaldata.xlsx') # Set the filepath + filename

dfa=df.active()
# select the sheet where tables are located
dff = df["Sheet1"]

# check what tables that exist in the worksheet
print({key : value for key, value in dff.tables.items()})

mapping = {}

# loop through all the tables and add to a dictionary
for entry, data_boundary in dff.tables.items():
    # parse the data within the ref boundary
    data = dff[data_boundary]
    
    ### extract the data ###
    # the inner list comprehension gets the values for each cell in the table
    content = [[cell.value for cell in ent] 
               for ent in data]
    
    header = content[0]
    
    #the contents ... excluding the header
    rest = content[1:]
    
    #create dataframe with the column names
    #and pair table name with dataframe
    df1 = pd.DataFrame(rest, columns = header)
    mapping[entry] = df1

#  print(mapping)
# Extract all the tables to individually dataframes from the dictionary
Table4, Table5, Table6, Table7, Table3, Table8, Table9, Table10 = mapping.values()

# Print each dataframe
print(Table4)
print(Table5)
# etc

问题原因 #

Excel的表格编号是自动生成的，如果之前删除过Table1、Table2这类早期表格，新建表格时会延续之前的编号序列，不会重新从1开始计数。原代码直接使用Excel内部存储的表格名称作为字典的键，自然会得到不连续的编号。

解决方案 #

方案1：强制生成连续编号的DataFrame #

遍历表格时，手动从1开始给表格重新编号，替换原有的不连续名称：

from openpyxl import load_workbook
import pandas as pd

# 读取Excel文件
wb = load_workbook(r'C:\Users\zz\Desktop\Analyticaldata.xlsx') 
# 选择目标工作表
ws = wb["Sheet1"]

# 查看原表格的名称和范围
print({key : value for key, value in ws.tables.items()})

mapping = {}

# 遍历表格，按顺序生成连续编号的键
for idx, (entry, data_boundary) in enumerate(ws.tables.items(), start=1):
    # 读取表格范围内的单元格数据
    data = ws[data_boundary]
    
    # 提取单元格值，整理成二维列表
    content = [[cell.value for cell in row] for row in data]
    # 表头取第一行
    header = content[0]
    # 数据行取剩余部分
    data_rows = content[1:]
    
    # 创建DataFrame并添加到字典
    df = pd.DataFrame(data_rows, columns=header)
    mapping[f"Table{idx}"] = df

# 提取连续编号的DataFrame
Table1, Table2, Table3, Table4, Table5, Table6, Table7, Table8 = mapping.values()

# 打印验证
print(Table1)
print(Table2)
# 后续表格依此类推

方案2：按表格在工作表中的位置排序后编号 #

如果希望按表格在Excel中的从上到下顺序生成连续编号，可以先解析表格的起始行位置，排序后再处理：

from openpyxl import load_workbook
import pandas as pd

wb = load_workbook(r'C:\Users\zz\Desktop\Analyticaldata.xlsx') 
ws = wb["Sheet1"]

# 按表格在工作表中的起始行排序
sorted_tables = sorted(ws.tables.items(), key=lambda x: int(x[1].ref.split(':')[0][1:]))

mapping = {}
# 遍历排序后的表格，生成连续编号
for idx, (entry, data_boundary) in enumerate(sorted_tables, start=1):
    data = ws[data_boundary]
    content = [[cell.value for cell in row] for row in data]
    header = content[0]
    data_rows = content[1:]
    
    df = pd.DataFrame(data_rows, columns=header)
    mapping[f"Table{idx}"] = df

# 提取连续编号的DataFrame
Table1, Table2, Table3, Table4, Table5, Table6, Table7, Table8 = mapping.values()

print(Table1)

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