实现功能:
- 1:解析json文件内容，加载成json字符串和对象
- 2:解析模版excel文件中的列表header内容
- 3:循环对比json中二级key，二级key+三级key与excel中的header内容对比
- 4:检查两者数据相同，或者包含相同字符串的内容数据
- 5:把数据写入的新的excel文件中，包括header已经data数据
`import pandas as pd
import os
import json
def process_json(data, parent_key=''):
result = {}
if isinstance(data, dict):
for key, value in data.items():
current_key = f"{parent_key}{key}" if parent_key else key
if isinstance(value, dict):
result.update(process_json(value, current_key))
elif isinstance(value, list):
result[current_key] = ','.join(map(str, value))
else:
result[current_key] = value
elif isinstance(data, list):
for idx, item in enumerate(data):
result[parent_key] = ','.join(map(str, data)) if idx == 0 else result[parent_key]
return result
def write_json_to_excel(name, data, excel_template, output_excel):
读取Excel模板
df_template = pd.read_excel(excel_template)
创建一个空的DataFrame用于存储匹配的数据
df_data = pd.DataFrame()
处理JSON数据
processed_data = process_json(data)
for col in df_template.columns:
print(col)
打印处理后的结果
for key, value in processed_data.items():
print(f"{key}: {value}")
遍历模板的列名，查找与JSON中匹配的键，并将值写入DataFrame
for col in df_template.columns:
print(col)
if col in key:
print("ssx :" + col)
df_data[col] = [value]
elif col == "二级实体":
print(col)
df_data[col] = [name]
将匹配的数据追加到模板DataFrame的后面
df_combined = pd.concat([df_template, df_data], ignore_index=True)
保存到新的Excel文件
df_combined.to_excel(output_excel, index=False)
if name == 'main':
获取当前目录下所有.json文件
json_files = [f for f in os.listdir('.') if os.path.isfile(f) and f.endswith('.json')]
print(len(json_files))
excel_template = 'template.xlsx' # Excel模板路径
output_excel = 'output.xlsx' # 输出Excel文件路径
读取Excel模板
df_template = pd.read_excel(excel_template)
初始化一个空的DataFrame来存储所有JSON数据
all_data = []
data_frames_to_add = []
data_frames_to_add.append(df_template)
for json_file in json_files:
print(json_file[:-5])
name = json_file[:-5]
with open(json_file, 'r') as f:
data = json.load(f)
创建一个空的DataFrame用于存储匹配的数据
df_data = pd.DataFrame()
处理JSON数据
processed_data = process_json(data)
for col in df_template.columns:
print(col)
打印处理后的结果
for key, value in processed_data.items():
print(f"{key}: {value}")
遍历模板的列名，查找与JSON中匹配的键，并将值写入DataFrame
for col in df_template.columns:
print(col)
if col in key:
print("ssx :" + col)
df_data[col] = [value]
elif col == "二级实体":
df_data[col] = [name]
elif "灵动岛" in col and "灵动岛" in key:
df_data[col] = [value]
elif "售价" in col and "价格" in key:
df_data[col] = [value]
elif "刷新率" in col and "刷新率" in key:
df_data[col] = ["TRUE"]
elif "中央处理器" in col and "芯片构成" in key:
df_data[col] = [value]
elif "图形处理器" in col and "芯片构成" in key:
df_data[col] = [value]
elif "神经网络引擎" in col and "芯片构成" in key:
df_data[col] = [value]
elif "后置摄像头像素" in col and "后置摄像头组成" in key:
df_data[col] = [value]
elif "光学变焦倍率" in col and "后置摄像头变焦" in key:
df_data[col] = [value]
elif "Apple Pay" in col and "Apple Pay" in key:
df_data[col] = [value]
elif "视频通话" in col and "视频通话" in key:
df_data[col] = [value]
elif "NFC" in col and "NFC" in key:
df_data[col] = [value]
elif "光追功能" in col and "光追功能" in key:
df_data[col] = [value]
elif "续航" in col and "续航" in key:
df_data[col] = [value]
elif "充电" in col and "电池充电" in key:
df_data[col] = [value]
data_frames_to_add.append(df_data)
将匹配的数据追加到模板DataFrame的后面
df_combined = pd.concat(data_frames_to_add, ignore_index=True)
保存到新的Excel文件
df_combined.to_excel("allIphone" + ".xlsx", index=False)
