如何用Python的openpyxl库将指定格式Excel转为特定JSON结构

原始Excel数据格式 #

问题 #

我有如图所示格式的Excel数据，请问如何使用Python将其转换为指定结构的JSON？

预期输出 #

file_name = [
    {
        "A": {
            "time": 10,
            "X1": 1,
            "X2": 4
        },
        "B": {
            "time": 10,
            "X1": 1.2,
            "X2": 4.1
        },
        "C": {
            "time": 10,
            "X1": 1,
            "X2": 4
        }
    },
    {
        "A": {
            "time": 11,
            "X1": 1.1,
            "X2": 4
        },
        "B": {
            "time": 11,
            "X1": 1,
            "X2": 4.2
        },
        "C": {
            "time": 11,
            "X1": 1,
            "X2": 4
        }
    },
    {
        "A": {
            "time": 12,
            "X1": 1.3,
            "X2": 4.1
        },
        "B": {
            "time": 12,
            "X1": 1,
            "X2": 4.1
        },
        "C": {
            "time": 12,
            "X1": 1.2,
            "X2": 3.9
        }
    }
]

实现步骤及代码 #

1. 依赖库安装 #

先安装处理Excel所需的pandas库：

pip install pandas openpyxl

注：.xlsx格式用openpyxl引擎，.xls格式需额外安装xlrd

2. 核心代码实现 #

import pandas as pd

# 读取Excel文件，替换为你的文件路径
df = pd.read_excel("your_excel_file.xlsx")

# 初始化结果列表
file_name = []

# 遍历每行数据，构造目标结构
for _, row in df.iterrows():
    time_val = row["Time"]  # 替换为Excel中时间列的实际表头
    current_entry = {
        "A": {
            "time": time_val,
            "X1": row["A_X1"],  # 替换为Excel中A的X1列表头
            "X2": row["A_X2"]   # 替换为Excel中A的X2列表头
        },
        "B": {
            "time": time_val,
            "X1": row["B_X1"],  # 替换为Excel中B的X1列表头
            "X2": row["B_X2"]   # 替换为Excel中B的X2列表头
        },
        "C": {
            "time": time_val,
            "X1": row["C_X1"],  # 替换为Excel中C的X1列表头
            "X2": row["C_X2"]   # 替换为Excel中C的X2列表头
        }
    }
    file_name.append(current_entry)

# 若需输出标准JSON文件，执行以下代码
import json
with open("output.json", "w", encoding="utf-8") as f:
    json.dump(file_name, f, indent=4)

3. 生成Python类实例结构（可选） #

如果需要生成预期输出中类似Measurement类实例的结构，先定义类再构造：

class Measurement:
    def __init__(self, time, X1, X2):
        self.time = time
        self.X1 = X1
        self.X2 = X2

# 遍历行时修改构造逻辑
for _, row in df.iterrows():
    time_val = row["Time"]
    current_entry = {
        "A": Measurement(time_val, row["A_X1"], row["A_X2"]),
        "B": Measurement(time_val, row["B_X1"], row["B_X2"]),
        "C": Measurement(time_val, row["C_X1"], row["C_X2"])
    }
    file_name.append(current_entry)

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