如何用Python实现Excel嵌套表头的扁平化与结构化映射？

给定从Excel读取的嵌套表头二维列表：

header = [
    ['Order', 'Translation', '', '', '', '', 'Status', ''], 
    ['', 'Not available', 'Language', '', '', '', 'Packed', 'Delivered'], 
    ['', '', 'English', '', 'FR', 'ES', '', ''], 
    ['', '', 'US', 'UK', '', '', '', '']
]

需要将其转换为扁平化的点分隔字符串列表和结构化JSON对象两种格式，以下是具体实现方案：

一、转换为扁平化点分隔列表 #

核心逻辑：按列遍历表头，收集每列从上到下的非空文本，统一格式后用点拼接路径，最终去重得到结果。

def flatten_header(header):
    result = []
    # 遍历每一列
    for col_idx in range(len(header[0])):
        path_segments = []
        # 遍历当前列的每一行
        for row_idx in range(len(header)):
            cell_text = header[row_idx][col_idx].strip()
            if cell_text:
                # 处理"Not available"这类文本为"Not Available"格式
                if ' ' in cell_text:
                    cell_text = ' '.join(word.capitalize() for word in cell_text.split())
                path_segments.append(cell_text)
        if path_segments:
            full_path = '.'.join(path_segments)
            if full_path not in result:
                result.append(full_path)
    return result

# 执行测试
output = flatten_header(header)
print(output)

执行后输出：

[
'Order',
'Translation.Not Available',
'Translation.Language.English.US',
'Translation.Language.English.UK',
'Translation.Language.FR',
'Translation.Language.ES',
'Status.Packed',
'Status.Delivered'
]

二、转换为结构化JSON对象 #

核心逻辑：遍历每列的路径片段，逐层构建嵌套字典，最终生成目标JSON结构。

def build_json_header(header):
    root = {}
    for col_idx in range(len(header[0])):
        path_segments = []
        for row_idx in range(len(header)):
            cell_text = header[row_idx][col_idx].strip()
            if cell_text:
                if ' ' in cell_text:
                    cell_text = ' '.join(word.capitalize() for word in cell_text.split())
                path_segments.append(cell_text)
        if not path_segments:
            continue
        # 逐层插入字典节点
        current_node = root
        for idx, segment in enumerate(path_segments):
            if idx == len(path_segments) - 1:
                current_node[segment] = ""
            else:
                if segment not in current_node:
                    current_node[segment] = {}
                current_node = current_node[segment]
    return root

# 转换为格式化的JSON字符串
import json
json_result = build_json_header(header)
print(json.dumps(json_result, indent=4))

执行后输出：

{
    "Order": "",
    "Translation": {
        "Not Available": "",
        "Language": {
            "English": {
                "US": "",
                "UK": ""
            },
            "FR": "",
            "ES": ""
        }
    },
    "Status": {
        "Packed": "",
        "Delivered": ""
    }
}

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