如何使用openpyxl获取Excel二级多级表头的子表头并优化手动处理逻辑

核心优化逻辑 #

原有实现最大的问题是直接过滤表头中的None值，丢失了一级表头和子表头的列对应关系，我们优先保留列位置信息来构建映射关系：

• 保留前两行表头的完整列顺序（不提前过滤None）
• 遍历所有列，将连续列的子表头归属到同一个非空的一级表头下
• 构建{一级表头: [子表头列表, 子表头对应列索引列表]}的结构化映射，方便后续取值

优化后实现代码 #

1. 表头映射构建与数据读取 #

from openpyxl import load_workbook

path = r'你的文件路径'
# 加data_only=True直接读取单元格计算结果，不需要公式
wb = load_workbook(path, data_only=True)
ws = wb.active

# 读取前两行完整表头，保留空值占位
row1 = [cell.value for cell in ws[1]]  # 一级表头行
row2 = [cell.value for cell in ws[2]]  # 子表头行
max_col = ws.max_column

# 构建一级表头到子表头的映射
header_map = {}
current_main_header = None
for col_idx in range(max_col):
    main_val = row1[col_idx]
    sub_val = row2[col_idx]
    # 遇到非空的一级表头就更新当前主表头
    if main_val is not None:
        current_main_header = main_val
        header_map[current_main_header] = {
            'sub_headers': [],
            'col_indexes': []
        }
    # 非空的子表头加入当前主表头的映射中
    if sub_val is not None and current_main_header is not None:
        header_map[current_main_header]['sub_headers'].append(sub_val)
        header_map[current_main_header]['col_indexes'].append(col_idx)

# 读取所有数据行（从第3行开始）
row_list = []
for row in ws.iter_rows(min_row=3, values_only=True):
    # 保留原始行的所有值，按列索引对应取值
    row_list.append(list(row))

2. 自动化生成XML代码 #

原有XML生成逻辑是硬编码每个一级表头的索引，现在可以直接遍历header_map自动生成，不需要重复写多段相似代码：

from yattag import Doc, indent

doc, tag, text = Doc().tagtext()
# 此处dic为你原有逻辑中定义的行首值映射字典，保留原有实现即可
dic = {} 

with tag("Data"):
    for row in row_list:
        # 自动跳过空行
        if not any(row):
            continue
        row_key = row[0]
        with tag("Row"):
            with tag("Input"):
                # 遍历所有一级表头自动生成标签
                for main_header, info in header_map.items():
                    sub_headers = info['sub_headers']
                    col_indexes = info['col_indexes']
                    # 取出当前一级表头对应的所有子表头的值
                    sub_values = [row[idx] for idx in col_indexes]
                    # 生成合规的标签名
                    tag_name = main_header.replace(' ', '_').replace('\n', '_')
                    with tag(tag_name):
                        # 动态拼接文本内容，自适应不同数量的子表头
                        text_parts = [f"In {dic[row_key]} the precentage of Students regarding the {main_header}"]
                        for i in range(len(sub_headers)):
                            if i == 0:
                                text_parts.append(f" the Precentage of Students with {sub_headers[i]} is {sub_values[i]}")
                            else:
                                text_parts.append(f" whereas the {sub_headers[i]} are {sub_values[i]}")
                        text(''.join(text_parts))
                        # 生成Row_Data内容
                        row_data_parts = [dic[row_key], main_header]
                        for i in range(len(sub_headers)):
                            row_data_parts.append(sub_headers[i])
                            row_data_parts.append(str(sub_values[i]))
                        with tag("Row_Data"):
                            text(' | '.join(row_data_parts))

# 格式化输出XML
result = indent(
    doc.getvalue(),
    indentation='   ',
    indent_text=True
)

with open("output.xml", "w", encoding="utf-8") as f:
    f.write(result)

方案优势 #

• 不需要手动维护表头索引，自动适配一级表头跨列数量变化
• 完全避免重复的XML生成代码，表头增减不需要修改业务逻辑
• 保留了原始列的对应关系，不会出现表头和数据错位的问题

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