我从2019年开始就反复被同一个问题找上门爬虫跑完了、接口对接完了、数据库导出了对方开口就是一句“能给我个Excel吗”。JSON数据本身结构清晰、信息密度高但业务同事、客户、甚至部分开发就是不看JSON文件只要.xlsx。去网站在线转换确实快但有一次我要把一批含客户手机号的订单数据处理成Excel刚把文件拖进网页就反应过来——这等于把敏感数据交到了第三方服务器上吓得我立刻关掉页面。从那以后我坚持用本地Python脚本做JSON转Excel这个习惯一直用到现在。这篇内容会从最小可运行的转换代码讲起逐步扩展到能应对日常实际场景的通用脚本再聊嵌套JSON怎么拆解、大数据量怎么处理以及我踩过的具体坑。适合刚接触Python、需要处理数据交付的人也适合已经会基础语法但被各种边界情况折磨过的朋友。你可以直接把我给的脚本拿去改也可以只读思路按自己的数据重新组织都会有收获。1. 现成工具不少我为什么坚持用本地Python脚本转先说结论不是所有场景都必须写代码。你只有一份几十条数据的JSON转完就一次性交付那打开在线转换工具、贴上内容、点击导出三分钟搞定完全没问题。但只要你把“转一次Excel”当作一个长期存在的需求或者数据本身有敏感属性、格式有特殊要求本地脚本几乎是唯一靠谱的选择。在线转换工具的硬伤主要集中在这几个方面数据安全不可控。上传的JSON内容会被解析到对方的服务器哪怕对方声明“不留存”也无法核实。业务数据、用户信息、企业内部报表一旦外传出问题就是事故。哪怕数据不敏感很多人也容易忽略一个细节某些网站会利用你上传的数据做模型训练或内容抓取这种风险不可见、不可追。格式不可控。在线工具大多数做的是“JSON数组→规则二维表”的傻瓜式转换遇到嵌套对象、数组套数组、字段缺失、数据规范不一致时它要么直接报错要么给你生成一屏乱七八糟的列你还无从干预。批处理和自动化几乎为零。你有一百个JSON文件在线工具你得手动上传一百次如果数据每天更新一次你每天都要重复这个流程。用脚本处理一个循环全部解决还能直接接入定时任务。Python脚本的核心优势在于JSON解析、数据结构处理和Excel生成三件事都有非常成熟的库组合起来非常自由。JSON本质上是字典、列表、字符串、数字、布尔值和空值的组合嵌套Python用json模块可以无损解析成原生数据类型Excel本质上是一张二维表pandas的DataFrame恰好就是干这个的。中间再加一层json_normalize做嵌套拍平就能覆盖绝大多数实际需求。当然本地脚本也有自己的边界比如你需要一个能跑Python的环境比如转换逻辑写复杂后需要维护。但对绝大多数“JSON转Excel”的使用场景来说这个成本完全可以接受而且是一次投入、长期复用。2. 先用最小代码跑通流程安装环境与三行核心转换我见过不少朋友一上来就搜“JSON转Excel代码”复制了一大段几百行的脚本跑出一堆报错最后放弃。其实最核心的就三行代码。2.1 环境准备Python版本与依赖安装先确认机器上有Python环境。建议直接用Python 3.8以上的版本太旧的版本在某些第三方库的兼容性上容易出问题。官方安装包去python.org下载安装时记得勾选“Add Python to PATH”这一步漏掉的后果就是命令行里输入python毫无反应很多人卡在这一步很久其实只是没加到环境变量里。依赖两个库pip install pandas openpyxlpandas负责数据解析与二维表结构组织openpyxl是pandas写.xlsx文件时实际调用的引擎。如果你只是转一个简单的二维表这两个就够用了。pandas默认依赖里还带着numpy所以装一个pandas相当于把基础科学计算环境一起装上了这在后续处理数据时反而是件好事。装完后可以验证一下import pandas as pd print(pd.__version__)能打印出版本号说明环境就绪。用什么IDE无所谓命令行、VSCode、Jupyter都可以重点是能执行python脚本。2.2 最简版本read_json加to_excel假设你手上有一个data.json内容长这样[ {姓名: 张三, 城市: 上海, 年龄: 28}, {姓名: 李四, 城市: 北京, 年龄: 32}, {姓名: 王五, 城市: 广州, 年龄: 25} ]顶层是一个JSON数组数组里每个元素都是结构一致的JSON对象。这是最理想、最适合直接转换的结构。用下面这三行代码就能生成Excelimport pandas as pd df pd.read_json(data.json) df.to_excel(output.xlsx, indexFalse)重点解释一下这两行的行为。pd.read_json()会自动把JSON数组解析成DataFrame数组里的每个对象变成一行对象的每个key变成一列。如果JSON对象的key顺序是姓名、城市、年龄那么生成的Excel列顺序也遵循这个顺序不会乱排。df.to_excel(output.xlsx, indexFalse)是关键中的关键。indexFalse的意思是不要把DataFrame的行号写进Excel。很多人第一次运行不写这个参数生成的Excel最左侧多了一列0、1、2、3的序号虽然不影响数据本身但交付给别人的时候看起来非常不专业还得手动删。养成习惯to_excel默认带上indexFalse。2.3 一个很容易踩的细节JSON顶层不是数组时怎么办现实中的数据源很少给你一个干干净净的顶层数组。最常见的是包了一层对象{ code: 0, message: success, data: [ {姓名: 张三, 城市: 上海, 年龄: 28}, {姓名: 李四, 城市: 北京, 年龄: 32} ] }这种情况下直接pd.read_json(data.json)得到的DataFrame会长得很怪只有一行三列code、message、data各占一列其中data列里装的是一个嵌套的列表对象。这时候有两种处理方式第一种先读成Python对象再手动取出数组import json import pandas as pd with open(data.json, r, encodingutf-8) as f: raw json.load(f) df pd.DataFrame(raw[data]) df.to_excel(output.xlsx, indexFalse)第二种用pd.json_normalize直接按路径抽数据df pd.json_normalize(raw, record_pathdata)我建议你从一开始就养成“分两步”的习惯先用json.load读文件再动手处理数据而不是一把梭用pd.read_json。因为后面遇到嵌套结构、编码问题时拆开写调试起来方便得多。pd.read_json适合处理已经非常规范的数组JSON而真实世界里不规范的占大多数。3. 一个能处理日常80%需求的通用转换脚本既然要长期自己转换就别每次打开Python文件改路径。我把平时最常用的逻辑封装成了一个通用脚本输入任意JSON文件路径自动识别顶层结构输出Excel。这个脚本没有用到任何高深技巧但足够稳。3.1 脚本要解决的四个现实问题在设计脚本之前我先梳理了现实中反复遇到的问题文件编码不确定。有的JSON是UTF-8有的是GBK直接open经常抛UnicodeDecodeError。脚本需要自动尝试多种编码读不到就跳过。顶层结构不确定。可能是数组可能是带状态码的包装对象可能是字典也可能是JSON Lines每行一个独立JSON对象。脚本需要自动识别。嵌套字段的处理。规范化解析应该用pd.json_normalize而不是手动循环因为它能自动把嵌套对象展开成字段_子字段的列名减少手工逻辑。字段顺序和空值。Excel要保证列的顺序和可读性空值不能直接留NaN至少要让读者看着不困惑。基于这四个问题我写出的脚本如下。3.2 完整代码json_to_excel_tool.pyimport json import sys from pathlib import Path import pandas as pd def load_json(file_path): 读取JSON文件自动兼容UTF-8与GBK编码。 raw_bytes Path(file_path).read_bytes() for encoding in (utf-8, gbk): try: return json.loads(raw_bytes.decode(encoding)) except UnicodeDecodeError: continue except json.JSONDecodeError: break # 最后兜底忽略非法字符尽量解析 return json.loads(raw_bytes.decode(utf-8, errorsignore)) def extract_list(data): 自动从JSON数据中提取最有可能作为表格的数据列表。 优先级数组本身 对象中第一个数组类型的值 嵌套在data字段里的数组。 if isinstance(data, list): return data if isinstance(data, dict): if data in data and isinstance(data[data], list): return data[data] for value in data.values(): if isinstance(value, list) and value and isinstance(value[0], (dict, list)): return value raise ValueError(未找到可转换的数组数据请检查JSON结构) def json_to_excel(file_path, output_pathNone, sheet_nameSheet1): 主转换函数JSON文件 - Excel文件。 file_path Path(file_path) if output_path is None: output_path file_path.with_suffix(.xlsx) data load_json(file_path) table_data extract_list(data) if table_data and isinstance(table_data[0], dict): df pd.json_normalize(table_data, sep_) else: df pd.DataFrame(table_data) # 空值填充为空字符串避免Excel里出现NaN字样 df df.fillna() df.to_excel(output_path, indexFalse, sheet_namesheet_name, engineopenpyxl) print(f转换完成{file_path} - {output_path}共 {len(df)} 行{len(df.columns)} 列) if __name__ __main__: if len(sys.argv) 2: print(用法python json_to_excel_tool.py json文件路径 [输出的Excel路径] [sheet名称]) sys.exit(1) input_file sys.argv[1] output_file sys.argv[2] if len(sys.argv) 2 else None sheet sys.argv[3] if len(sys.argv) 3 else Sheet1 json_to_excel(input_file, output_file, sheet)这段脚本的关键点在于extract_list函数。它按优先级做三件事如果输入本身是数组直接用如果是带data字段的对象且data是数组用data的值否则遍历对象的所有值找到第一个由字典组成的数组。这个逻辑覆盖了我见过的绝大多数JSON返回结构。3.3 使用方式与运行效果写完后在命令行运行python json_to_excel_tool.py data.json它会自动生成data.xlsx并打印出“转换完成data.json - data.xlsx共500行12列”。如果你想指定输出路径和Sheet名称python json_to_excel_tool.py data.json report.xlsx 订单数据这样生成的Excel文件里Sheet名称就是“订单数据”而不是默认的Sheet1。很多朋友拿脚本的时候只顾着复制粘贴跑通一次就不管了。其实这个通用脚本最好的用法是把它放在一个固定目录下以后所有JSON转Excel的需求都通过命令行调用。如果有多个文件要转还能配合系统自带的任务计划程序做成定时转换完全不用打开编辑器。4. 嵌套JSON不再怕从拍平到主从表拆分前面脚本里用了pd.json_normalize它对付“对象套对象”的结构非常有效但遇到“对象套数组”的情况还是需要人为干预。这是JSON转Excel过程中最考验经验的部分。4.1 单层嵌套对象用json_normalize做自动拍平假设订单数据长这样[ { order_id: A001, customer: {name: 张三, level: VIP}, amount: 2999 }, { order_id: A002, customer: {name: 李四, level: 普通}, amount: 139 } ]直接用pd.DataFrame(data)得到的是两行三列其中customer列里每个单元格都是一个字典对象非常难看也不能直接交付。用pd.json_normalize(data, sep_)之后列会自动展开成order_idcustomer_namecustomer_levelamountA001张三VIP2999这是json_normalize最核心的用法自动递归展开一层嵌套的字典把customer.name变成customer_name列。sep参数控制连接符默认是.但Excel列名里带点有时会引起其他工具兼容问题我习惯用下划线_。4.2 一对多结构主从表拆分是正道真正让人头疼的是“对象套数组”。继续上面的订单例子一个订单往往包含多个商品{ order_id: A001, customer: 张三, items: [ {name: 手机, price: 2999, qty: 1}, {name: 钢化膜, price: 39, qty: 2} ] }这种情况下不管用什么normalize方式都没法把items数组里的多条子记录塞进同一行Excel。强行塞要么变成字符串列表要么只能取第一条数据是丢的。我的处理方式是把数据拆成两张表订单主表order_id、customer订单明细表order_id、name、price、qty通过order_id字段关联回主表代码如下import pandas as pd data { order_id: A001, customer: 张三, items: [ {name: 手机, price: 2999, qty: 1}, {name: 钢化膜, price: 39, qty: 2} ] } # 主表去掉items字段就是订单维度 master pd.DataFrame([{k: v for k, v in data.items() if k ! items}]) # 明细表用record_path指定子数组路径用meta把外层字段作为外键带进来 detail pd.json_normalize( data, record_pathitems, meta[order_id] )record_path指定要展开的子数组meta指定需要从外层带进明细表的外键字段。运行后detail是这样的namepriceqtyorder_id手机29991A001钢化膜392A001如果原始数据是一整个数组每个元素都包含订单主键和items子数组同样可以用pd.json_normalize(orders, record_pathitems, meta[order_id, customer])批量拆分所有订单一次处理完数据会自动按顺序展开。这是处理“一对多”JSON时最省心的方法不用手写循环。4.3 列顺序、空值兜底与类型转换拆分完之后还有三个细节直接影响交付质量。第一个是列顺序。json_normalize展开出来的列顺序取决于JSON里字段出现的先后顺序这个顺序不一定是业务方想要的。比如客户要求“客户姓名”放在第一列你可以用列名重排来收尾df df.reindex(columns[customer_name, order_id, amount])如果reindex之后发现有些列名不在DataFrame里说明你的JSON里某些对象缺了这个字段这时候需要回头检查数据是否完整而不是硬着头皮往下走。第二个是空值处理。json_normalize在遇到所有记录都缺失某个字段时会生成一列全NaN。直接to_excelExcel单元格里就会显示nan或NAN很不美观。我的习惯是统一填充为空字符串df df.fillna()第三个是类型转换。JSON里没有Excel那么严格的列类型概念数字5可能以字符串5出现日期可能是2024-01-15 10:30:00。写Excel之前建议对关键列做一次显式转换df[price] pd.to_numeric(df[price], errorscoerce) df[order_date] pd.to_datetime(df[order_date], errorscoerce)这里errorscoerce的意思是遇到无法转换的值就置为NaN而不是让整个脚本报错。转换完成后你可以print(df.dtypes)检查每一列的类型是否符合预期再把结果写出Excel。5. 批量转换与大数据量导入性能表现的真实感受单文件转换跑通后很多人很快就遇到第二个问题文件多了怎么办数据大了怎么办5.1 多文件JSON的批量转换假设一个目录下有几十个JSON文件每个文件对应一天的报表数据或者每个文件是一个独立接口返回你需要全部生成Excel。手写循环是效率最高的import glob from pathlib import Path from json_to_excel_tool import json_to_excel for json_file in glob.glob(data/*.json): json_to_excel(json_file, output_pathfexcel/{Path(json_file).stem}.xlsx)这段代码会自动读取data目录下所有.json文件在excel目录下生成同名.xlsx文件。Path(json_file).stem拿到的是不带后缀的文件名比如order_20240101.json会生成order_20240101.xlsx。一个容易忽略的点如果excel目录不存在pandas的to_excel会报错因为pandas不会自动创建目录。所以在批量处理之前先加一行Path(excel).mkdir(exist_okTrue)让目录存在。5.2 大数据量JSON分块读取是唯一出路当JSON文件达到几百MB甚至几个GB时json.load会把整个文件一次性读入内存内存占用轻松超过2GB电脑直接卡死这是新手最容易踩的坑。对这个量级的数据我的建议是先用工具评估数据规模再决定处理方式。如果是JSON Lines格式每一行一个独立的JSON对象用pandas.read_json的linesTrue加chunksize参数分块读取import pandas as pd writer pd.ExcelWriter(large_output.xlsx, engineopenpyxl) for chunk in pd.read_json(large_data.jsonl, linesTrue, chunksize50000): chunk.to_excel(writer, indexFalse, sheet_nameSheet1) writer.close()这里每5万行写一次ExcelWriter会把数据持续追加到同一个工作表里。整个过程中内存里只保留5万行数据不会爆内存。如果文件不是JSON Lines而是嵌套很深的树状结构我通常先用ijson这类流式解析库做逐条读取边读边整理成扁平结构再分块写入Excel。这样做的编码复杂度会上升但内存占用能控制在稳定水平。5.3 一个务实选择什么时候放弃xlsx改用CSVExcel文件本身有行数上限一个Sheet最多1048576行超过这个数to_excel直接写不进去。如果你的数据量级在百万行以上我的建议是不要死磕Excel直接用CSV交付更现实。对比维度xlsxcsv最大行数约104万行/Sheet无硬性限制单个字段格式可设置数字、日期、文本等纯文本无格式概念打开方式Excel等表格软件记事本、Excel等生成速度较慢需要拼XML很快纯文本写入中文兼容性天然支持需用utf-8-sig编码防止乱码CSV导出时最容易踩坑的就是中文乱码。用默认参数写CSVExcel打开后中文会变成乱码因为Excel默认按ANSI编码解析CSV文件。解决办法是写文件时指定encodingutf-8-sigdf.to_csv(output.csv, indexFalse, encodingutf-8-sig)utf-8-sig会在文件开头加一个BOM标记Excel识别到BOM后就明白这是UTF-8编码中文就能正常显示。这个细节我在实际项目中至少见过三次同事踩坑每次都以为是数据问题最后发现只是编码没指定。6. 想起就想拍大腿的坑Excel导出经常翻车的地方最后一章聊几个具体报错和异常表现。这些坑看似不起眼但每一个都让我在真实项目里折腾过几个小时。6.1 长数字被自动变成科学计数法身份证号、订单号这类长数字写入Excel后会自动显示成1.23457E17双击单元格后面几位数字还变成了0。原因在于Excel的数字精度只有15位超过15位的部分会被舍入。解决方式有两种本质思路其实就一句话让Excel把这一列当成文本而不是数字。最直接的方法是转成字符串再写df[订单号] df[订单号].astype(str)但这里有个细节如果转换后的字符串恰好是类似“123456789012345”这样的纯数字某些版本的Excel还是会自作聪明地转回数字。更稳妥的方案是在Excel单元格里加一个文本标记前缀我一般用单引号开头df[订单号] df[订单号].astype(str)Excel看到单引号前缀会强制把内容当文本处理并且单引号本身不会显示在单元格里。代价是生成的Excel里单元格左上角会出现一个绿色三角“文本格式”标记不影响阅读和使用。6.2 文件被占用时报PermissionError脚本运行结束你想复查一下Excel数据用Excel打开了刚才生成的output.xlsx。然后又跑到脚本里去重新运行转换这时大概率会抛PermissionError: [Errno 13] Permission denied。原因很简单Excel已经锁定了这个文件pandas没法覆盖写入。我在本地开发时经常遇到也是花了一段时间才习惯。解决办法有两个一是生成文件名时加上时间戳比如output_20240115_1030.xlsx避免覆盖正在打开的文件二是写文件前先判断目标文件是否存在如果存在且在占用状态就提示用户关闭。最简单的方案用时间戳文件名from datetime import datetime timestamp datetime.now().strftime(%Y%m%d_%H%M%S) df.to_excel(foutput_{timestamp}.xlsx, indexFalse)6.3 超过Excel上限导致写入失败前面提到过单Sheet最多1048576行实际操作中还有一个相近的坑列数也不能超过16384列。用json_normalize处理深度嵌套的JSON时字段数量特别容易失控。比如一个人物对象里包含几十个子对象每个子对象又有十几个属性展开出来可能直接几百列。遇到这种情况先停下来想一下业务上是否需要这么多列。如果只是临时分析可以只保留关键字段如果是正式交付尽量拆分主从表不要让Excel变成一个宽得离谱的平面表。这个经验不是技术问题是数据结构设计问题但处理不好会导致整个转换失败。6.4 空值导入变成“nan”字符串还有一个特别误导人的表现JSON里明明没有某个字段转完Excel后单元格里显示的不是空而是nan。原因前面提过json_normalize会把缺失值补成NaNto_excel默认把NaN写成空单元格但如果你在中间环节执行了df.astype(str)NaN就会变成字符串nan然后作为普通文本写入Excel。如果你需要在转换过程中对某些列做类型转换顺序应该是先fillna()把所有空值替换成空字符串再执行astype(str)等操作这样能避免把NaN污染成可见文本。这个顺序问题不太起眼但一旦出现排查起来非常费时间。最后再分享一个个人经验。我一开始也习惯在Jupyter里反复调试代码每次手动改文件路径。后来发现最有效率的方式是把这个转换脚本作为一个长期维护的小工具所有参数都通过命令行传进来数据文件放固定目录批量转换直接执行一条命令。这套工具从第一版用到今天唯一的重大改动就是从纯json.load升级到了支持JSON Lines分块读取。如果你经常需要处理JSON转Excel建议你也走这个路径先跑通一个极简版本再逐步加上容错和扩展最后固定成自己的标准工具。这个积累过程本身才是比任何单一脚本更值钱的东西。
