pip install Spire.XLS.Free

[
    {
        "id": 1001,
        "name": "張三",
        "age": 28,
        "email": "zhangsan@example.com",
        "is_active": true
    },
    {
        "id": 1002,
        "name": "李四",
        "age": 32,
        "email": "lisi@example.com",
        "is_active": false
    },
    {
        "id": 1003,
        "name": "王五",
        "age": 25,
        "email": "wangwu@example.com",
        "is_active": true
    }
]

import json
from spire.xls import *
from spire.xls.common import *

def json_to_excel_basic(json_data, output_path):
    # 1. 初始化Excel工作簿
    workbook = Workbook()
    # 移除默認(rèn)工作表，創(chuàng)建新工作表
    workbook.Worksheets.Clear()
    worksheet = workbook.Worksheets.Add("用戶數(shù)據(jù)")

    # 2. 解析JSON數(shù)據(jù)（若為文件讀取，可替換為json.load(open("data.json", "r", encoding="utf-8"))）
    data = json.loads(json_data)
    if not data:
        raise ValueError("JSON數(shù)據(jù)為空，無法轉(zhuǎn)換")

    # 3. 寫入表頭（取第一條數(shù)據(jù)的鍵作為表頭）
    headers = list(data[0].keys())
    for col_idx, header in enumerate(headers):
        worksheet.Range[1, col_idx + 1].Text = header

    # 4. 寫入數(shù)據(jù)行
    for row_idx, item in enumerate(data, start=2):  # 從第2行開始寫入數(shù)據(jù)
        for col_idx, key in enumerate(headers):
            value = item.get(key, "")
            worksheet.Range[row_idx, col_idx + 1].Text = str(value)

    # 5. 自動(dòng)調(diào)整列寬
    worksheet.AllocatedRange.AutoFitColumns()

    # 6. 保存Excel文件
    workbook.SaveToFile(output_path, ExcelVersion.Version2016)
    workbook.Dispose()  # 釋放資源

# 測試調(diào)用
if __name__ == "__main__":
    # 示例JSON字符串（實(shí)際場景可從文件/接口讀?。?
    json_str = '''
    [
        {"id": 1001, "name": "張三", "age": 28, "email": "zhangsan@example.com", "is_active": true},
        {"id": 1002, "name": "李四", "age": 32, "email": "lisi@example.com", "is_active": false},
        {"id": 1003, "name": "王五", "age": 25, "email": "wangwu@example.com", "is_active": true}
    ]
    '''
    try:
        json_to_excel_basic(json_str, "基礎(chǔ)版JSON轉(zhuǎn)Excel.xlsx")
        print("轉(zhuǎn)換完成！")
    except Exception as e:
        print(f"轉(zhuǎn)換失敗：{e}")

import json
from spire.xls import *
from spire.xls.common import *

def flatten_dict(d, parent_key='', sep='_'):
    """遞歸扁平化嵌套字典"""
    items = []
    for k, v in d.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.extend(flatten_dict(v, new_key, sep=sep).items())
        else:
            items.append((new_key, v))
    return dict(items)

def json_to_excel_nested(json_data, output_path):
    workbook = Workbook()
    workbook.Worksheets.Clear()
    worksheet = workbook.Worksheets.Add("嵌套JSON數(shù)據(jù)")

    data = json.loads(json_data)
    if not data:
        raise ValueError("JSON數(shù)據(jù)為空")

    # 扁平化所有數(shù)據(jù)，提取完整表頭
    flattened_data = [flatten_dict(item) for item in data]
    headers = list(flattened_data[0].keys())

    # 寫入表頭
    for col_idx, header in enumerate(headers):
        worksheet.Range[1, col_idx + 1].Text = header

    # 寫入扁平化后的數(shù)據(jù)
    for row_idx, item in enumerate(flattened_data, start=2):
        for col_idx, key in enumerate(headers):
            worksheet.Range[row_idx, col_idx + 1].Text = str(item.get(key, ""))

    # 自動(dòng)列寬
    worksheet.AllocatedRange.AutoFitColumns()
    # 保存文件
    workbook.SaveToFile(output_path, ExcelVersion.Version2016)
    workbook.Dispose()

# 測試嵌套JSON轉(zhuǎn)換
if __name__ == "__main__":
    nested_json = '''
    [
        {
            "id": 1001,
            "name": "張三",
            "age": 28,
            "contact": {
                "email": "zhangsan@example.com",
                "phone": "13800138000"
            },
            "address": {
                "province": "北京",
                "city": "北京市"
            }
        },
        {
            "id": 1002,
            "name": "李四",
            "age": 32,
            "contact": {
                "email": "lisi@example.com",
                "phone": "13900139000"
            },
            "address": {
                "province": "上海",
                "city": "上海市"
            }
        }
    ]
    '''
    try:
        json_to_excel_nested(nested_json, "嵌套JSON轉(zhuǎn)Excel.xlsx")
        print("嵌套JSON轉(zhuǎn)換完成！")
    except Exception as e:
        print(f"轉(zhuǎn)換失?。簕e}")

# 在寫入表頭后添加樣式設(shè)置代碼
header_range = worksheet.Range[1, 1, 1, len(headers)]
# 設(shè)置表頭背景色
header_range.Style.Color = Color.get_SkyBlue()
# 設(shè)置表頭字體加粗
header_range.Style.Font.IsBold = True
# 設(shè)置表頭文字居中
header_range.Style.HorizontalAlignment = HorizontalAlignType.Center