读取Excel多表转JSON：如何用Python json.dump生成预期格式？

从Excel文件读取多个工作表并合并为单个JSON文件，示例数据如下：

df1
    Metric                      Value
0   salesamount                 9.0
1   salespercentage             80.0
2   salesdays                   56.0
3   salesconversionpercentage   0.3

df2
    Metric      Value
0   FromBudget  4K
1   ToBudget    5K

df3
    Metric                  Value
0   Objective               Customer Engagement
1   ExpectedOutcomesales    0.2
2   ExpectedOutcomeweeks    8 weeks

使用以下代码转换并生成JSON：

s = dict(zip(df1.iloc[:,0], df1.iloc[:,1]))
eb = dict(zip(df2.iloc[:,0], df2.iloc[:,1]))
eo = dict(zip(df3.iloc[:,0], df3.iloc[:,1]))

mydct = {
        'ExpectedPlanPerformance' : 
                                    {
                                     'EstimatedBudget' : eb,
                                     'Sales' : s,
                                     'ExpectedOutcome' : eo
                                    }
        }

outfile = open('file.json','w')
json.dump(mydct, outfile, indent = 4)
outfile.close()

但生成的JSON中，ExpectedPlanPerformance下的子节点被转为字符串格式的JSON，当前输出：

{
    "ExpectedPlanPerformance": {
        "EstimatedBudget": "{\"FromBudget\": \"4K\", \"ToBudget\": \"5K\"}",
        "Sales": "{\"salesamount\": \"9.0\", \"salespercentage\": \"80.0\", \"salesdays\": \"56.0\", \"salesconversionpercentage\": \"0.3\"}",
        "ExpectedOutcome": "{\"Objective\": \"Customer Engagement\", \"ExpectedOutcomesales\": \"20%\", \"ExpectedOutcomeweeks\": \"8 weeks\"}"
    }
}

预期输出格式：

"ExpectedPlanPerformance": [{
    "ExpectedOutcome": {
        "Objective": "Customer Engagement",
        "ExpectedOutcomesales": "20%",
        "ExpectedOutcomeweeks": "8 weeks"
    },
    "Sales": {
        "salesamount": "9 ",
        "salespercentage": "80",
        "salesdays": "56",
        "salesconversionpercentage": "0.3"
    },
    "EstimatedBudget": {
        "FromBudget": "4K",
        "ToBudget": "5K"
    }
}],

1. 修正字典结构 #

预期输出中ExpectedPlanPerformance是数组（列表），而非单个对象，同时要确保子节点是原生字典而非JSON字符串。修改mydct的定义：

mydct = {
    'ExpectedPlanPerformance': [
        {
            'ExpectedOutcome': eo,
            'Sales': s,
            'EstimatedBudget': eb
        }
    ]
}

2. 确保子节点为原生字典 #

如果eb、s、eo不小心被转成了JSON字符串（比如误用了json.dumps()），需要先将它们转回原生字典：

import json

# 若子节点是JSON字符串，执行转换
eb = json.loads(eb)
s = json.loads(s)
eo = json.loads(eo)

3. 完整修改后的代码 #

import pandas as pd
import json

# 读取Excel工作表（替换为你的文件路径和表名）
df1 = pd.read_excel('your_file.xlsx', sheet_name='Sheet1')
df2 = pd.read_excel('your_file.xlsx', sheet_name='Sheet2')
df3 = pd.read_excel('your_file.xlsx', sheet_name='Sheet3')

# 转换为原生Python字典
s = dict(zip(df1.iloc[:,0], df1.iloc[:,1]))
eb = dict(zip(df2.iloc[:,0], df2.iloc[:,1]))
eo = dict(zip(df3.iloc[:,0], df3.iloc[:,1]))

# 构建符合预期的结构
mydct = {
    'ExpectedPlanPerformance': [
        {
            'ExpectedOutcome': eo,
            'Sales': s,
            'EstimatedBudget': eb
        }
    ]
}

# 写入JSON文件（用with语句自动管理文件关闭）
with open('file.json', 'w') as outfile:
    json.dump(mydct, outfile, indent=4)

关键说明 #

• 将ExpectedPlanPerformance的值从单个字典改为包含一个字典的列表，匹配预期的数组格式。
• 确保eb、s、eo是原生Python字典，而非JSON字符串（若之前有多余的json.dumps操作，必须移除或用json.loads转回）。
• 使用with语句处理文件，更安全且无需手动执行close()操作。

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