Python JSON批量导出Excel教程：多Sheet或单Sheet

把多个JSON文件或一组JSON数据批量导出成Excel，用openpyxl或pandas to_excel，支持多Sheet分文件、表头样式，附完整代码。

场景痛点

数据是JSON格式，但同事只收Excel；一份JSON里有多个数组（用户、订单、商品），想分别放到不同Sheet里发出去。手动复制粘贴到Excel，格式乱、列宽挤、中文乱码。用Python一键导出，还能顺手加表头样式。

用到的库

pip install pandas openpyxl

完整代码

# Python JSON批量导出Excel
import json
import pandas as pd
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

def export_single_sheet(json_path, excel_path):
"""单个JSON数组导出到Excel一个Sheet。"""
with open(json_path, "r", encoding="utf-8") as f:
data = json.load(f)
if isinstance(data, dict) and "data" in data:
data = data["data"]
df = pd.DataFrame(data)
df.to_excel(excel_path, index=False, sheet_name="数据", engine="openpyxl")
print(f"单Sheet导出：{excel_path}，{len(df)} 行")

def export_multi_sheet(json_dict, excel_path):
"""
多个数据集导出到同一个Excel的多个Sheet。
json_dict 形如 {"用户": list1, "订单": list2, "商品": list3}
"""
with pd.ExcelWriter(excel_path, engine="openpyxl") as writer:
for sheet_name, records in json_dict.items():
if not records:
continue
df = pd.DataFrame(records)
df.to_excel(writer, sheet_name=sheet_name, index=False)
print(f"多Sheet导出：{excel_path}，共 {len(json_dict)} 个Sheet")

def export_with_style(json_dict, excel_path):
"""
导出后加表头样式：加粗、背景色、居中、自动列宽。
"""
with pd.ExcelWriter(excel_path, engine="openpyxl") as writer:
for sheet_name, records in json_dict.items():
if not records:
continue
df = pd.DataFrame(records)
df.to_excel(writer, sheet_name=sheet_name, index=False)

# 拿到worksheet对象加样式
ws = writer.sheets[sheet_name]
header_fill = PatternFill("solid", fgColor="4472C4")
header_font = Font(bold=True, color="FFFFFF")

for cell in ws[1]:  # 第一行是表头
cell.fill = header_fill
cell.font = header_font
cell.alignment = Alignment(horizontal="center", vertical="center")

# 自动列宽（按内容长度估算）
for col_idx, col_cells in enumerate(ws.columns, 1):
max_len = max(len(str(c.value or "")) for c in col_cells)
ws.column_dimensions[get_column_letter(col_idx)].width = min(max_len * 2 + 2, 40)
print(f"带样式导出：{excel_path}")

def main():
# 示例数据
data_pack = {
"用户": [
{"id": 1, "姓名": "张三", "城市": "北京"},
{"id": 2, "姓名": "李四", "城市": "上海"},
],
"订单": [
{"订单号": "A001", "金额": 299.5, "状态": "已完成"},
{"订单号": "A002", "金额": 599.0, "状态": "待发货"},
],
}

export_single_sheet("users.json", "single_sheet.xlsx")
export_multi_sheet(data_pack, "multi_sheet.xlsx")
export_with_style(data_pack, "styled.xlsx")

if __name__ == "__main__":
main()