如何将含多级表头的Excel文件转换为嵌套JSON格式

你的问题核心是Excel采用了两级表头（主分类+子分类），但原代码只读取了第一行表头，未识别嵌套关系，同时数据行起始位置错误、数值格式未按要求处理。以下是修正后的实现方案：

问题分析 #

1. 数据行起始错误：原代码从第二行读取数据，但实际数据行从第三行开始（前两行是表头），导致第一组数据读取到子表头内容，出现null和错误值。
2. 未处理嵌套表头：直接将主分类作为普通字段，无法生成Category 1、Category2这类嵌套对象结构。
3. 数值格式不符：未将数值转换为带两位小数、千分位分隔的字符串格式。

修正后的代码 #

from openpyxl import load_workbook
from json import dumps
import locale

# 设置本地化以支持千分位分隔符
locale.setlocale(locale.LC_ALL, 'zh_CN.UTF-8')

# 加载Excel文件
wb = load_workbook("sample.xlsx")
sheet = wb["Sheet1"]

# 解析两级表头：第一行主分类，第二行子分类
headers = []
for col in range(1, sheet.max_column + 1):
    main_header = sheet.cell(row=1, column=col).value
    sub_header = sheet.cell(row=2, column=col).value
    headers.append((main_header, sub_header))

# 存储最终JSON数据
result = []

# 遍历数据行（从第三行开始）
for row_num in range(3, sheet.max_row + 1):
    row_dict = {}
    current_category = {}
    current_main = None

    for col_num in range(1, sheet.max_column + 1):
        main_header, sub_header = headers[col_num - 1]
        cell_val = sheet.cell(row=row_num, column=col_num).value

        # 处理无嵌套的普通字段（如ID、Label等）
        if not sub_header:
            if main_header == "ID":
                # ID保留字符串格式，避免丢失前导零
                row_dict[main_header] = str(cell_val) if cell_val else ""
            else:
                row_dict[main_header] = cell_val
        else:
            # 处理嵌套分类字段
            if main_header != current_main:
                # 切换主分类时，将上一个分类的内容存入行字典
                if current_main:
                    row_dict[current_main] = current_category
                current_main = main_header
                current_category = {}
            
            # 格式化数值为带两位小数、千分位的字符串
            if isinstance(cell_val, (int, float)):
                formatted_val = locale.format_string("%.2f", cell_val, grouping=True)
                current_category[sub_header] = formatted_val
            else:
                current_category[sub_header] = cell_val if cell_val else "0.00"
    
    # 存入最后一个嵌套分类
    if current_main:
        row_dict[current_main] = current_category
    
    result.append(row_dict)

# 转换为格式化后的JSON
json_data = dumps(result, indent=2, ensure_ascii=False)
print(json_data)

代码关键说明 #

1. 两级表头解析：读取前两行表头，建立主分类与子分类的对应关系。
2. 嵌套结构构建：遍历列时，将同一主分类下的子分类数据存入临时字典，最终合并为嵌套对象。
3. 数值格式化：通过locale模块实现千分位分隔，同时保留两位小数，转换为字符串类型。
4. ID格式保留：将ID转换为字符串，避免数字类型丢失前导零或空格格式。

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