如何用Python从含技能评分的JSON文件生成指定格式Excel表格

需求说明 #

我有两个包含不同技能评分的JSON文件，数据如下：

数据1

[
  {
     "agent_pk": 15004,
     "agent_name": "ABC",
     "skill_details": [
       {
         "skill_name": "rules.close.urgency.score",
         "skill_score": 0.0
       },
       {
         "skill_name": "rules.open.develop_rpc.score",
         "skill_score": 0.7
       }
     ]
  },
  {
    "agent_pk": 15005,
    "agent_name": "BCD",
    "skill_details": [
      {
        "skill_name": "rules.close.urgency.score",
        "skill_score": 0.2
      },
      {
        "skill_name": "rules.open.develop_rpc.score",
        "skill_score": 0.3
      }
    ]
  }
]

数据2

[
  {
    "agent_pk": 15004,
    "agent_name": "ABC",
    "skill_details": [
      {
        "skill_name": "rules.close.urgency.score",
        "skill_score": 0.6
      },
      {
        "skill_name": "rules.open.develop_rpc.score",
        "skill_score": 2.0
      }
    ]
  },
  {
    "agent_pk": 15005,
    "agent_name": "BCD",
    "skill_details": [
      {
        "skill_name": "rules.close.urgency.score",
        "skill_score": 0.2
      },
      {
        "skill_name": "rules.open.develop_rpc.score",
        "skill_score": 0.3
      }
    ]
  }
]

需要生成如下格式的Excel表格（其中代理名称和对应Score标题为加粗样式）：

解决方案 #

1. 安装依赖 #

首先需要安装处理Excel和JSON的库：

pip install pandas openpyxl

2. 实现代码 #

import json
import pandas as pd
from openpyxl.styles import Font

# 读取JSON文件
def load_json(file_path):
    with open(file_path, 'r', encoding='utf-8') as f:
        return json.load(f)

# 替换为你的实际文件路径
data1 = load_json('data1.json')
data2 = load_json('data2.json')

# 将数据转换为按agent_pk索引的字典，方便匹配技能评分
def format_data(data):
    agent_map = {}
    for agent in data:
        skill_map = {skill['skill_name']: skill['skill_score'] for skill in agent['skill_details']}
        agent_map[agent['agent_pk']] = {
            'name': agent['agent_name'],
            'skills': skill_map
        }
    return agent_map

agent_data1 = format_data(data1)
agent_data2 = format_data(data2)

# 构造输出行
output_rows = []
for agent_id in agent_data1:
    agent_info = agent_data1[agent_id]
    agent_name = agent_info['name']
    # 添加代理的加粗表头行
    output_rows.append({
        '技能/代理': f'**{agent_name}**',
        'Score 1': '**Score 1**',
        'Score 2': '**Score 2**'
    })
    # 添加每个技能的评分行
    for skill_name in agent_info['skills']:
        score1 = agent_info['skills'][skill_name]
        score2 = agent_data2[agent_id]['skills'][skill_name]
        output_rows.append({
            '技能/代理': skill_name,
            'Score 1': score1,
            'Score 2': score2
        })

# 生成DataFrame并写入Excel
df = pd.DataFrame(output_rows)
with pd.ExcelWriter('skill_scores.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False, sheet_name='技能评分')
    workbook = writer.book
    worksheet = writer.sheets['技能评分']
    
    # 设置加粗字体
    bold_font = Font(bold=True)
    # 遍历单元格，处理加粗标记并设置格式
    for row in worksheet.iter_rows(min_row=1, max_row=worksheet.max_row):
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith('**') and cell.value.endswith('**'):
                cell.value = cell.value.strip('**')
                cell.font = bold_font

print("Excel文件已生成：skill_scores.xlsx")

代码说明 #

• 先读取两个JSON文件，将数据转换成按代理ID索引的字典，方便快速匹配同一代理的两组技能评分
• 构造输出行时，每个代理先添加一行带**标记的表头行，后续再处理成Excel的加粗样式
• 使用pandas生成Excel文件，再通过openpyxl遍历单元格，去掉**标记并设置为加粗字体
• 最终生成的skill_scores.xlsx会完全符合需求格式

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