Python对比两个JSON文件差异并导出至Excel的解决方案咨询

原有代码问题说明 #

• jsondiff返回的差异结果为特殊嵌套结构，默认携带$delete/$add/修改标记，你的一维遍历逻辑无法匹配嵌套内容，导致写入失败
• 未对差异做分类处理，无法直接对应你需要的三类差异输出
• 原始JSON本身是无序列表/键值对结构，若需要行号，需要在加载JSON时额外记录行位置映射

可控性更高的自定义对比实现（无需依赖jsondiff） #

自己实现递归遍历逻辑，可以完全自定义差异分类和输出格式，代码如下：

完整代码 #

import json
import xlsxwriter

# 递归对比函数
def compare_json(json1, json2, path="", only_in1=None, only_in2=None, modified=None):
    if only_in1 is None:
        only_in1 = []
    if only_in2 is None:
        only_in2 = []
    if modified is None:
        modified = []
    
    # 对比两个字典
    if isinstance(json1, dict) and isinstance(json2, dict):
        # 仅json1存在的键
        for key in json1.keys() - json2.keys():
            current_path = f"{path}.{key}" if path else key
            only_in1.append((current_path, json1[key]))
        # 仅json2存在的键
        for key in json2.keys() - json1.keys():
            current_path = f"{path}.{key}" if path else key
            only_in2.append((current_path, json2[key]))
        # 共同存在的键递归对比
        for key in json1.keys() & json2.keys():
            current_path = f"{path}.{key}" if path else key
            compare_json(json1[key], json2[key], current_path, only_in1, only_in2, modified)
    # 对比两个列表
    elif isinstance(json1, list) and isinstance(json2, list):
        max_len = max(len(json1), len(json2))
        for i in range(max_len):
            current_path = f"{path}[{i}]"
            if i >= len(json1):
                only_in2.append((current_path, json2[i]))
            elif i >= len(json2):
                only_in1.append((current_path, json1[i]))
            else:
                compare_json(json1[i], json2[i], current_path, only_in1, only_in2, modified)
    # 基本类型值对比
    else:
        if json1 != json2:
            modified.append((path, json1, json2))
    return only_in1, only_in2, modified

# 加载JSON文件
with open('C/abc/file1.json', 'r', encoding='utf-8') as f:
    json1 = json.load(f)
with open('C/abc/file2.json', 'r', encoding='utf-8') as f:
    json2 = json.load(f)

# 执行对比
only_in1, only_in2, modified = compare_json(json1, json2)

# 写入Excel
workbook = xlsxwriter.Workbook('json_diff.xlsx')
worksheet = workbook.add_worksheet('差异对比')

# 表头格式
header_format = workbook.add_format({'bold': True, 'bg_color': '#D3D3D3'})
headers = ['差异类型', 'JSON路径', 'file1值', 'file2值']
for col, header in enumerate(headers):
    worksheet.write(0, col, header, header_format)

row = 1
# 写入仅file1存在的内容
for path, val in only_in1:
    worksheet.write(row, 0, '仅file1存在')
    worksheet.write(row, 1, path)
    worksheet.write(row, 2, str(val))
    row += 1
# 写入仅file2存在的内容
for path, val in only_in2:
    worksheet.write(row, 0, '仅file2存在')
    worksheet.write(row, 1, path)
    worksheet.write(row, 3, str(val))
    row += 1
# 写入修改的内容
for path, val1, val2 in modified:
    worksheet.write(row, 0, '值修改')
    worksheet.write(row, 1, path)
    worksheet.write(row, 2, str(val1))
    worksheet.write(row, 3, str(val2))
    row += 1

# 自适应列宽（可选）
for col in range(4):
    worksheet.set_column(col, col, 30)

workbook.close()

行号获取方案 #

如果需要输出原始JSON文件中对应字段的行号，可以使用json5库加载时记录行位置，或者使用demjson库的解析结果携带行号属性，替换原有json.load逻辑即可。

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