JSON 转 Excel 实操指南：Python pandas 处理嵌套数据与自动化导出

JSON 转 Excel 这个需求干过几年开发的人基本都遇到过不管是前端拿到的接口数据要交付给业务还是后端处理完一批日志想快速给产品看一眼又或者是把第三方 API 返回的结果整理成报表最终落地的形式几乎都是 Excel。这活儿看着简单但实际操作里坑不少——嵌套 JSON 怎么展开、中文乱码怎么处理、一列数字导出后变成科学计数法、大文件内存溢出每一步都有讲究。这篇博文我就把自己常用的几种方案、踩过的坑和最终沉淀下来的套路一次讲清楚尤其会重点说说 Python pandas 路线适合需要批量处理、格式可控、能嵌入自动化流程的朋友参考。1. 实际需求与应用场景拆解1.1 什么时候需要 JSON 转 Excel很多人觉得 JSON 转 Excel 是个小工具的事网上随便找个在线转换器几秒钟就搞定了何必要专门研究。但实际工作里这个需求会以各种奇怪的方式冒出来。先说最常见的场景——接口数据交付。比如你对接了一个第三方开放平台对方返回的是嵌套了好几层的 JSON 数组里面既有订单详情又有用户信息还有商品快照。业务方不会关心你的 JSON 结构设计得多优雅他们只想要一份能筛选、能透视、能发给领导的 Excel。这时候手工复制粘贴显然不现实一次性转完又不甘心——因为下周数据还会更新你得想办法让流程可复用。第二种场景是日志和配置文件的分析。服务端日志经常是一行一个 JSON 对象或者整个文件就是一个大 JSON 数组。你要排查某个接口的响应时间波动想筛选出耗时超过 2 秒的记录用 Excel 处理最顺手。另外像把 JSON 配置翻译成 Excel 给非技术人员核对这种需求也挺常见翻译文件、语言包、灰度配置统统能归到这一类。第三种是数据清洗和迁移。有些同事习惯把数据存在 JSON 文件里当临时数据库用等项目做大了需要导入正式系统第一步就是转成 Excel 让业务确认。反过来Excel 导入数据库之前也经常先转成 JSON 做校验——热词里能搜到大量excel导入数据库和json python的关联搜索说明这两者互相转换本来就是高频动作。1.2 不同人群的需求差异同样是 JSON 转 Excel开发、运维、数据分析师、普通运营找到你的诉求完全不一样。开发同学的痛点是批量和自动化。他们手里的 JSON 往往很大——几个 G 的日志文件、几十万行的接口数据需要脚本稳定跑完不崩还要能定期执行。对他们来说转换只是一个前置步骤真正重要的是后面的数据处理逻辑。数据分析师更看重结构还原和类型精度。JSON 里的整数、浮点数、日期字符串、布尔值转到 Excel 后要保证类型正确不能出现true变成文本这种低级错误。他们通常还要对展开后的表格做透视、分组所以输出的一级表头必须干净。运营和非技术同事的需求最简单也最容易忽略——他们只需要结果。最好双击一下就能得到一个 xlsx 文件打开就能用。不会 Python 也没关系但作为背后支持的人你得知道怎么帮他们产出稳定可靠的文件而不是每次转换格式都差一点。2. 工具与方案选型分析2.1 主流方案横向对比我试过不下十种转法从纯手工到全自动从在线网页到命令行各有各的适用场景。这里直接给一张对比表方便你对号入座。方案上手难度灵活性处理大数据量是否可自动化典型适用人群Python pandas中高强可脚本化/定时开发、数据分析师Excel Power Query低中中可刷新运营、轻度分析Node.js SheetJS中高中可脚本化前端/Node 开发在线转换工具极低极低弱否一次性小文件jq csvkit 命令行较高中中可脚本化运维、命令行爱好者pandas 之所以是大多数人最终的选择不是因为别的方案不好而是因为它恰好覆盖了转换链路上最痛的几个环节读取 JSON、展开嵌套结构、清洗数据、类型转换、输出 Excel。这套流程你在 pandas 里做数据始终在内存的 DataFrame 里流转中间每一步都可以验证和调整不会出现转完才发现少字段这种尴尬。2.2 我为什么主力用 pandas什么时候反而不用pandas 最核心的价值是json_normalize新版叫pd.json_normalize可以把嵌套 JSON 自动拍平成表格结构再配合to_excel一条龙输出。代码就几行但能覆盖 80% 的日常需求。不过 pandas 不是银弹。有一种情况我会主动劝退——目标 Excel 对格式有非常高的要求比如指定列宽、合并单元格、条件格式、特定模板样式。pandas 虽然能通过ExcelWriter配合 openpyxl 做一定程度的样式控制但写起来麻烦远不如直接用 openpyxl 操作单元格来得自然。还有一种情况是数据量特别大千万级行pandas 一次性加载全部数据到内存容易爆这时候要么用 dask 分块处理要么改用流式写入。所以我的建议是如果你的需求只是把 JSON 结构化成表格然后手工在 Excel 里继续加工pandas 是最短路径如果你需要的是生成的 Excel 直接可用、格式完美、打开就能汇报那可能要 pandas openpyxl 组合或者干脆用 Node.js 的 SheetJS 在导出前后做精细控制。别迷信单一方案按需组合才是正路。3. 核心实操Python pandas 转换全流程3.1 环境准备与依赖安装开始之前先确认你的 Python 环境。我用的是 3.9但 3.7 以上基本都没问题。需要安装的库只有两个pandas 和 openpyxl。pandas 用来处理数据openpyxl 是读写 xlsx 文件的后端引擎to_excel默认会调用它。pip install pandas openpyxl装完验证一下import pandas as pd import openpyxl print(pd.__version__) print(openpyxl.__version__)如果你的网络环境安装慢可以用国内镜像源pip install pandas openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple注意pandas 读 xlsx 需要 openpyxl但如果你输出的是老的 xls 格式则需要 xlwt / xlrd。我强烈建议一律用 xlsx兼容性和功能都更好xlwt 那个项目基本已经不怎么维护了。3.2 最基础的单层 JSON 转换假设你现在拿到的是最简单的一层 JSON比如一个用户列表[ {name: 张三, age: 28, city: 北京}, {name: 李四, age: 34, city: 上海}, {name: 王五, age: 41, city: 广州} ]只需三行代码就能生成 Excelimport pandas as pd import json with open(users.json, r, encodingutf-8) as f: data json.load(f) df pd.DataFrame(data) df.to_excel(users.xlsx, indexFalse)这里有个很关键的细节pd.DataFrame(data)可以直接接收一个字典列表自动把每个字典的 key 变成列名value 变成单元格值。indexFalse表示不输出 pandas 默认的索引列这个一定要带上不然生成的 Excel 第一列全是对不上的序号看起来很不专业。打开users.xlsx效果如下nameagecity张三28北京李四34上海王五41广州这就是最理想的状态——字段名规范、类型正确、没有多余内容。但实际项目里能拿到这么规整 JSON 的情况太少了。3.3 处理嵌套 JSONjson_normalize 详解绝大多数真实 JSON 都是嵌套的比如一个订单接口返回的数据结构长这样{ code: 0, message: success, data: { orders: [ { order_id: A1001, total_amount: 299.5, created_at: 2026-03-15 10:30:00, customer: { name: 张三, phone: 13800001111 }, items: [ {sku: SKU001, name: 手机壳, price: 19.9}, {sku: SKU002, name: 钢化膜, price: 29.9} ] } ] } }这种结构直接丢给pd.DataFrame会得到一个很丑的结果customer 列里装的是字典对象Excel 里显示成{name: 张三, phone: 13800001111}完全失去表格的意义。这时候就该pd.json_normalize出场了。它的作用是把嵌套的字典拍平成带点号的列名比如customer.name、customer.phoneimport pandas as pd from pandas import json_normalize with open(orders.json, r, encodingutf-8) as f: raw json.load(f) orders raw[data][orders] df json_normalize(orders) print(df.columns.tolist()) # [order_id, total_amount, created_at, customer.name, customer.phone, items]你看嵌套的customer被自动展开成了customer.name和customer.phone两列。这一手真的非常省事省去了手工写递归遍历的麻烦。但items这个字段还是没展开因为它是数组列表json_normalize默认不会把列表拍平。处理数组需要用到record_path参数——专门用来展开列表字段。比如想把每个订单的items拆成独立行df_items json_normalize( orders, record_pathitems, meta[order_id, total_amount, created_at, [customer, name]] )这里的meta参数指定了从外层订单带过来的字段用列表嵌套的写法[customer, name]表示取customer.name。得到的结果是每个订单的每个商品各占一行这个结构就非常适合做进一步的数据分析了。json_normalize还有record_prefix和meta_prefix参数可以给列名加前缀避免不同层级的字段重名导致覆盖。我在处理那种订单里有商品列表、商品里又有规格列表的三层嵌套时这两个参数是必用的。3.4 通用递归展开遇到不规则的嵌套结构怎么办json_normalize也不是万能的。当你遇到那种标签数组里面是字符串商品规格里面又是字典数组的混合结构它可能不会按你想象的方式处理。比如[ { name: 商品A, tags: [新品, 热卖], attributes: [ {key: 颜色, value: 红色}, {key: 尺寸, value: M} ] } ]json_normalize对tags这种纯字符串数组的处理是直接保留成 Python 列表Excel 里会变成[新品, 热卖]这种字符串。这在某些场景下可以接受但如果你想每个 tag 单独成列或者拆成多行就得自己写逻辑。我的做法是写一个通用的递归展开函数把任意嵌套结构拍平成key 用点号分隔的扁平行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, sepsep).items()) elif isinstance(v, list): # 数组这里做特殊处理如果元素是字典就逐个编号否则直接转字符串 if v and isinstance(v[0], dict): for i, item in enumerate(v): items.extend(flatten_dict(item, f{new_key}_{i}, sepsep).items()) else: items.append((new_key, , .join(map(str, v)))) else: items.append((new_key, v)) return dict(items)这段代码是我压箱底的工具函数之一我形容它是一个暴力但不讲理的方案——不管 JSON 长什么样一律拍平到底。缺点是列名会变得很长比如attributes_0_key这种但至少信息不丢。如果你的 JSON 结构非常不可控没有固定的 schema这个递归兜底方案比json_normalize更让人安心。3.5 导出 Excel 的高阶选项当数据清洗好了to_excel也有一堆参数值得研究。除了前面说的indexFalse还有几个高频使用的df.to_excel( output.xlsx, sheet_name数据汇总, indexFalse, encodingutf-8, freeze_panes(1, 0), # 冻结首行方便滚动查看 float_format%.2f, # 浮点数保留两位小数 )sheet_name给工作表起个中文名没问题但要提醒一下某些老旧的 Excel 查看器对中文 sheet 名支持不佳如果文件要发给别人用 WPS 或企业微信在线预览建议保守一点用英文名。多 sheet 导出也是高频需求。比如一个 JSON 文件里既有订单列表又有商品列表你想同时生成一个多工作表的工作簿with pd.ExcelWriter(report.xlsx, engineopenpyxl) as writer: df_orders.to_excel(writer, sheet_nameorders, indexFalse) df_items.to_excel(writer, sheet_nameitems, indexFalse) df_customers.to_excel(writer, sheet_namecustomers, indexFalse)这个ExcelWriter上下文管理器是官方推荐的写法确保文件写入完整、资源正常释放。一个容易踩的坑是如果输出文件已存在to_excel默认会覆盖整个工作簿但多 sheet 写入时如果指定了不存在的 sheet 名openpyxl 会直接创建如果 sheet 名冲突会报错。所以要么每次生成新文件要么在写入前手动删除旧表。如果你真的很在意样式——比如标题行加粗、列宽自适应、给数据加边框——可以在to_excel之后再用 openpyxl 打开文件做后处理。原因很简单pandas 的Styler能处理一些样式但灵活性远不如直接操作单元格。我的习惯是 pandas 负责数据、openpyxl 负责颜值分工明确。4. 常见问题与排查技巧实录4.1 编码问题中文乱码和 Unicode 转义JSON 转 Excel 最常见的翻车现场就是编码。json.load默认按 UTF-8 读取但你的 JSON 文件可能是 GBK、GB2312或者带 BOM 头。这时候直接读会抛UnicodeDecodeError或者读出来一片乱码。解决方式很简单读取时显式指定编码with open(data.json, r, encodingutf-8-sig) as f: data json.load(f)utf-8-sig比utf-8更保险因为它能自动处理 BOM 头。如果你的文件是 GBK 编码with open(data.json, r, encodinggbk) as f: data json.load(f)另外一个容易被忽略的坑是 JSON 里的\uXXXX转义序列。有些接口会返回\u5f20\u4e09这样的内容json.load其实会自动把\uXXXX转成中文所以一般情况下你不用管。但如果你是用正则或者文本处理方式读取 JSON没有经过json.load那你可能看到的就是一长串\u转义记得用json.loads() 处理一下。4.2 长数字变成科学计数法这是一个非常经典的 Excel 展示问题。比如订单号12345678901234567890在 pandas 里是正常的字符串但写入 Excel 后打开一看显示成了1.23457E19精度还丢了。几乎所有新手都会在这里栽跟头。问题根源是 Excel 对超过 15 位的纯数字默认用科学计数法展示。解决办法有两个方向第一个方向在 pandas 侧先把长数字转成字符串很多场景下 ID 本来就是字符串但加载 JSON 时被自动识别成 intdf[order_id] df[order_id].astype(str)第二个方向用 openpyxl 对单元格设置文本格式for row in ws.iter_rows(min_row2, min_col1, max_col1): for cell in row: cell.number_format 我个人的经验是如果 ID 只是展示用途直接在 pandas 里astype(str)最省事如果 ID 后续还要参与 Excel 里的 VLOOKUP 或者和其他表关联最好用 openpyxl 设置文本格式因为 Excel 的文本匹配和数字匹配行为不一样很多奇怪的关联不上问题都是类型不一致引起的。4.3 嵌套数组展开后的行数爆炸有些 JSON 结构是一对多的比如一个订单下面有 10 个商品展开成明细之后行数等于所有订单商品数之和。如果订单有 10 万条每个订单平均 10 个商品展开后就是 100 万行。这时候to_excel会变得很慢内存占用飙升甚至直接卡死。针对这种情况我建议先评估一下下游是不是真的需要明细。如果做汇总可以在 pandas 里先 groupby 聚合如果确实要明细可以分块处理——比如每次处理一万个订单生成多个 xlsx 再合并。实际测试下来pandas 对百万行的to_excel写入是很吃力的但如果是分块写多个 sheet每个 sheet 控制在十几万行体验会好很多。还有一个小技巧如果你的数据源里数组嵌套得很深展开之后会有大量空列或全 NaN 列可以顺手清理一下df df.dropna(axis1, howall)这个操作把整列都是空值的列删掉Excel 文件会干净很多。4.4 非预期的文件格式报错有时候你辛辛苦苦生成的 xlsx 文件发到别人手里一打开对方用其他软件导入时报外部表不是预期的格式或者文件已损坏。这种情况通常有两种原因。第一种是文件扩展名和实际格式不匹配。比如你用 pandas 的to_excel但没安装 openpyxlpandas 可能会退回旧引擎输出 xls 格式但文件后缀写的是.xlsxExcel 打开会报警告。解决方案是确保安装了 openpyxl 并且明确指定引擎。第二种是文件被中断写入。比如代码中途报错ExcelWriter的上下文管理器没有正常退出文件只写了一半。这时候你会发现文件大小异常小用文本编辑器打开能看出来内容不完整。另外一个我踩过的坑是公司内网的某些系统只支持老版.xls文件而 pandas 的新版本已经不再支持输出 xls需要 xlwt且仅支持旧格式。遇到这种兼容性限制我的建议是输出 CSV 或者另存为 xls——说到这里to_excel不行的话可以先输出 CSV 再用 Excel 另存虽然多一步但能解决兼容问题。5. 进阶用法与工程化实践5.1 让 Excel 数据自动更新很多人的需求不是转一次就完而是每天都要同步最新数据。这时候工程化思路就派上用场了。最简单的方式是写一个 Python 脚本拉取 API 数据、转成 Excel、上传到共享盘然后用操作系统的定时任务Windows 任务计划程序或 Linux crontab每天执行。这套流程里有个细节值得注意脚本要保证幂等——重复执行不会产生副作用。我一般在脚本开头加一个日志文件记录每次运行时间、处理了多少条数据、是否有异常方便事后排查。进阶一点如果你想在 Excel 里直接触发刷新可以用 Excel 自带的 Power Query 从 JSON 接口拉数据。Power Query 的从 Web 导入 JSON功能天然能解析嵌套结构还能设置定时刷新。但它的坑是JSON 结构一旦变化Power Query 的解析步骤可能报错修起来比改 Python 脚本还麻烦。所以我的建议是——简单的固定接口用 Power Query复杂或需要清洗的流程还是走 Python。5.2 多格式输出Excel 之外还要 CSV / JSON Lines实际项目中转换往往不是终点而是一连串数据处理的中间一环。我们经常遇到早上收到一批 JSON上午要导成 Excel 给业务确认下午还要转成 CSV 丢给数据分析平台晚上又要按不同字段拆分成多个 JSON这种需求。所以我一般会把转换逻辑封装成一个函数入参是 JSON 文件路径输出是 DataFrame后续可以随意导出为各种格式import pandas as pd from pandas import json_normalize def json_to_df(filepath): with open(filepath, r, encodingutf-8-sig) as f: raw json.load(f) # 兼容两种常见结构根是数组 / 根是包了一层 data 的对象 if isinstance(raw, dict) and data in raw: raw raw[data] if isinstance(raw, list) and raw and isinstance(raw[0], dict): return json_normalize(raw) else: raise ValueError(Unsupported JSON structure)然后再写一个main函数负责根据命令行参数或配置文件决定输出格式def main(): df json_to_df(input.json) df.to_excel(output.xlsx, indexFalse) df.to_csv(output.csv, indexFalse, encodingutf-8-sig) df.to_json(output.jsonl, orientrecords, linesTrue, force_asciiFalse)to_json里orientrecords表示每行一个 JSON 对象linesTrue表示 JSON Lines 格式每行独立一个对象force_asciiFalse确保中文不被转成\uXXXX。这三个参数组合是导出 JSONL 的标准写法。5.3 样式后处理用 openpyxl 做出可直接汇报的表格最后聊一聊输出即成品这件事。很多时候数据转好了但表格素面朝天跟业务方沟通总觉得差点意思。这时候我习惯在 pandas 导出之后用 openpyxl 做一轮样式后处理。典型的操作包括设置标题行背景色、加粗字体、冻结首行、自动调整列宽虽然 openpyxl 没有真正的自动列宽但可以根据每列最大字符数算一个近似宽度、给数据区域加边框、把金额列设置成千分位格式。from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter wb load_workbook(output.xlsx) ws wb.active # 表头样式 header_font Font(boldTrue, colorFFFFFF) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) for col in range(1, ws.max_column 1): cell ws.cell(row1, columncol) cell.font header_font cell.fill header_fill cell.alignment Alignment(horizontalcenter, verticalcenter) # 冻结首行 ws.freeze_panes A2 # 粗略列宽 for col in range(1, ws.max_column 1): max_len 0 for row in range(1, min(ws.max_row, 100) 1): val ws.cell(rowrow, columncol).value if val is not None: max_len max(max_len, len(str(val))) ws.column_dimensions[get_column_letter(col)].width max_len 4 wb.save(styled_output.xlsx)这代码看着长但每次写一次就完事之后所有导出都能复用。我通常会把这段样式逻辑单独抽成一个函数参数传入文件路径和配置项用起来非常顺手。6. 写在最后的经验小结截至这里从最基础的pd.DataFrame直接转到json_normalize处理嵌套再到自定义递归函数兜底最后到多 sheet 导出和 openpyxl 样式后处理一条完整的 JSON 转 Excel 链路算是打通了。我自己做这套流程这几年最大的体会是工具本身的成本很低真正的成本全在数据结构的理解和异常情况的处理上。与其到处求一个万能脚本不如花点时间把你经常遇到的 JSON 结构摸透写一个适合自己业务的小工具函数库。每次接新需求把解析逻辑往库里一丢导出逻辑一调几分钟就能出活那种体感是很爽的。最后再分享一个压箱底的小技巧如果你不确定你手里的 JSON 结构长什么样别急着写代码先执行一句print(json.dumps(data, indent2, ensure_asciiFalse))把结构打印出来看一遍。这一步两秒钟的时间能帮你省掉后面至少半小时的 debug 时间。毕竟转换这种事数据长什么样基本决定了你会踩多少坑。