简介Excel与JSON互转小工具面向需要频繁交换表格和结构化数据的开发者、测试人员及数据分析师可快速完成双向格式转换减少手工整理成本。压缩包内含1个Java源文件与1个Windows可执行程序共2个文件体积仅1.92MB轻量易用。功能覆盖两个方向JSON转Excel时键名映射为列、值填入对应行嵌套对象或数组可展开为多级表头或拆分为多个工作表Excel转JSON时列名转为键多工作表生成对应JSON数组同时处理数值、日期、布尔值差异并进行基础数据清洗整个流程可配备配置选项以适配不同结构。目前已有454人浏览学习适合初学数据交换或需要快速处理格式转换的用户。通过提供的可执行程序可直接体验转换效果参考Java源码还能理解转换逻辑为二次开发或嵌入自动化流程提供便利。1. 需求背景为什么大家都在找 Excel 和 JSON 的互转工具先说个真实场景。我上周帮一个做运营的同事处理数据她手里是一份三百多行的 Excel 表格里面是各个渠道的投放配置要转成 JSON 格式发给开发同学做接口联调。她先是手动复制粘贴改格式搞了快一个小时结果 JSON 校验报错——某个字段值带了多余的逗号。后来我用脚本帮她转三十秒搞定她整个人都愣住了。这个例子特别典型。Excel 和 JSON 之间的转换需求几乎横跨所有跟数据打交道的岗位运营要导配置、测试要造接口数据、开发要从 Excel 批量生成 JSON 文件、数据分析师要把接口返回的 JSON 数据摊平到表格里做透视。但真正好用的互转工具反而没那么好找。Excel 自带的 JSON 相关功能几乎没有在线转换工具又有数据泄露风险自己写脚本又不知道怎么处理各种边界情况。所以这篇内容我把实际项目里趟过坑的经验整理出来从需求拆解、工具选型、实操步骤到问题排查一套走完。不管你是写代码的、管数据的还是日常跟表格打交道的业务同学都能找到直接能用的方案。2. 互转方案选型先想清楚你要解决的是哪一类问题2.1 Excel 转 JSON 的三种常见形态很多人上来就问“有没有一键转换的工具”但实际业务里 Excel 转 JSON 至少有三种完全不同的形态选错方案会非常痛苦。第一种是单表转单对象。比如一行数据对应一个配置项表头是字段名每一行是一个对象的实例。这种最简单类似把表格“竖着读”成对象数组。第二种是嵌套结构。比如 Excel 里有“用户信息”和“订单列表”两张表要通过用户 ID 关联起来生成一个包含嵌套数组的 JSON。这种就不能简单按行读取了得先理解表间关系。第三种是单元格内容本身就是 JSON 片段。比如某一列放的是接口返回的原始 JSON 字符串你需要把它解析出来再和其他列合并。听起来简单但字符串解析和转义处理会让新手劝退。2.2 工具对比在线工具、Excel 插件、脚本方案怎么选方案优点缺点适合场景在线转换网站上手快零安装数据隐私风险批量处理弱格式控制差一次性、非敏感、小数据量Excel 插件如 Power Query 配合 JSON 扩展在 Excel 内完成可视化嵌套结构麻烦插件安装和版本兼容问题多简单互转、不想装 PythonPython 脚本pandas / openpyxl灵活、可批量、可处理复杂嵌套、可入库需要一点编程基础日常高频、复杂结构、自动化流程Node.js / 前端方案前后端通用JSON 原生友好Excel 解析需要额外库如 xlsxWeb 工具集成、全栈项目我个人的建议很直接如果你只是偶尔转一次数据又不敏感在线工具能用但凡涉及重复操作、敏感数据、嵌套结构直接上 Python。学习成本一次性的后面全是效率收益。3. Python 实现 Excel 转 JSON核心实操全流程3.1 环境准备与依赖选型当前这个方案我基于 Python 3.9 以上版本实测主要依赖两个库pandas负责数据处理openpyxl作为 Excel 解析引擎。Power Query 方案后面我再细说Python 这版覆盖绝大多数场景。安装非常简单pip install pandas openpyxlpandas读 Excel 时默认依赖openpyxl或xlrd.xlsx格式推荐openpyxl如果你还在用老的.xls格式需要xlrd。这里注意新版xlrd已经不支持.xlsx所以不要混着装。提示如果公司网络限制没法直接 pip 安装可以用国内镜像源比如pip install pandas openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple。3.2 最简单的单表转对象数组先看最基础的需求一张表表头是字段名每一行一条记录转成 JSON 数组。import pandas as pd # 读取 Excel 表格 df pd.read_excel(config.xlsx, sheet_nameSheet1, dtypestr) # 处理缺失值把 NaN 转为 None这样 JSON 里输出 null 而不是 NaN df df.where(df.notna(), None) # 转成列表[字典]结构 data df.to_dict(orientrecords) # 输出 JSON 文件ensure_asciiFalse 保证中文可读 import json with open(output.json, w, encodingutf-8) as f: json.dump(data, f, ensure_asciiFalse, indent2)这段代码里有个很关键的参数dtypestr。如果不指定pandas 会自动推断列类型身份证号、订单号这种长数字会被读成科学计数法到时候转出来的 JSON 就是错的。加了这个参数所有列先按字符串读入后续再按需做类型转换。to_dict(orientrecords)是核心方法它会把 DataFrame 的每一行变成一个字典所有行组成一个列表正好是 JSON 数组的结构。orient参数还有其他选项比如index会变成“行号: 该行数据”的嵌套结构要根据需要选。3.3 处理 Excel 中的日期和时间字段日期字段是转 JSON 时最容易出问题的地方。Excel 里日期本质是一个序列号比如45292表示 2024 年 1 月 1 日。如果不处理直接转成 JSON 会变成数字非常坑。df[日期] pd.to_datetime(df[日期], errorscoerce) df[日期] df[日期].dt.strftime(%Y-%m-%d %H:%M:%S)errorscoerce的意思是解析失败的值变成NaT不会被错误中断。strftime格式化后日期就变成标准的 JSON 友好字符串。如果不需要时分秒就改成%Y-%m-%d。另外一个隐藏的坑有些 Excel 文件里的日期根本不是日期类型而是“2024/01/01”这样的文本。这种情况pd.to_datetime也能识别但如果有多种格式混在一起最好先把列统一成字符串再用errorscoerce兜底。3.4 多表关联生成嵌套 JSON这是 Excel 转 JSON 的高级需求。举个实际项目例子一张用户表user_id、name、age一张订单表order_id、user_id、amount。要生成每个用户及其订单列表的嵌套 JSON。users_df pd.read_excel(data.xlsx, sheet_name用户) orders_df pd.read_excel(data.xlsx, sheet_name订单) orders_grouped orders_df.groupby(user_id, as_indexFalse).apply( lambda x: x.to_dict(orientrecords) ).to_dict() result [] for _, user in users_df.iterrows(): uid user[user_id] item { user_id: uid, name: user[name], age: user[age], orders: orders_grouped.get(uid, []) } result.append(item) with open(nested_output.json, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2)这里用groupby按用户 ID 把订单分组再转成字典。注意as_indexFalse的应用如果不设置user_id会变成索引而不是列后面get就会找不到键。这个细节我调过不少时间。iterrows()在数据量小的时候没问题几十万行时性能会下降。大数据量场景可以改用apply或向量化写法这里为了可读性先用简单方案。3.5 单元格内含 JSON 字符串的解析与合并还有一种很常见的情况Excel 某一列存的是 JSON 字符串而你要把它和其他列数据组合成完整 JSON。import json def parse_json_field(value): if value is None: return None if isinstance(value, str): try: return json.loads(value) except json.JSONDecodeError: return {error: invalid json, raw: value} return value df[解析结果] df[原始JSON列].apply(parse_json_field)这段代码里我特别做了异常处理如果单元格内容不是合法 JSON就返回一个带原始值的错误标记而不是让整个转换中断。这在真实数据处理中非常实用——你永远不知道上游数据里有什么妖魔鬼怪。3.6 大数据量 Excel 的性能优化当 Excel 文件特别大比如超过 10 万行时read_excel会变得很慢甚至内存溢出。这时候换用openpyxl的只读模式更合适from openpyxl import load_workbook wb load_workbook(big_file.xlsx, read_onlyTrue, data_onlyTrue) ws wb[Sheet1] rows [] for i, row in enumerate(ws.iter_rows(values_onlyTrue)): if i 0: headers row else: rows.append(dict(zip(headers, row)))read_onlyTrue是性能核心它不会一次性加载整个工作簿到内存而是逐行读取。data_onlyTrue是为了拿公式的计算结果而不是公式本身。注意这个模式下ws.iter_rows的返回是流式的不要反复遍历。实际测下来15 万行 20 列的文件用 pandas 约 8 秒用 openpyxl 只读模式约 5 秒内存占用从 1.2GB 降到 300MB 左右。4. JSON 转 Excel反向流程同样有讲究4.1 扁平 JSON 转表格JSON 转 Excel 的核心是“展平”。最常见的场景是接口返回了一堆嵌套 JSON你需要在表格里继续分析。最直接的方案是pandas.json_normalizeimport pandas as pd import json with open(data.json, r, encodingutf-8) as f: data json.load(f) df pd.json_normalize(data, sep_) df.to_excel(output.xlsx, indexFalse)这个json_normalize会把嵌套的字典自动展开成多列层级之间用_连接。比如{user: {name: 张三}}会变成user_name这一列。sep参数控制层级分隔符默认是.但 Excel 列名里点号有时会带来麻烦所以改成下划线更稳妥。如果 JSON 里还有数组json_normalize默认会把数组值存成一个列表对象写进 Excel 时会变成 Python 字符串表示阅读体验很差。这种情况需要手动对数组列做展开或聚合。4.2 含数组的嵌套 JSON 拆分为多行假设 JSON 结构是这样的每个用户有多条订单记录你想在 Excel 里做成“一条订单一行”的明细表。records [] for user in data: for order in user.get(orders, []): records.append({ user_id: user[user_id], user_name: user[name], order_id: order[order_id], amount: order[amount] }) df pd.DataFrame(records) df.to_excel(orders_flat.xlsx, indexFalse)这个展开操作的思路是用双重循环把嵌套数组“炸开”每个数组元素生成一行同时保留父级的公共字段。数据量大时可以用explode()函数优化但那需要先把列表拆列逻辑稍绕。4.3 JSON 转 Excel 时保留数据类型的几个坑数字精度JSON 里的长整数比如雪花 ID在 Excel 中会被转成科学计数法看起来面目全非。处理方式是手动改成文本格式。df[order_id] df[order_id].astype(str)空值处理JSON 里的null在 DataFrame 里是NaN写入 Excel 会变成空单元格。如果想保留“null”这几个字符先fillna(null)。特殊字符JSON 字符串里的换行符\n写在 Excel 单元格里没问题但如果你后续把这个 Excel 再转回 JSON换行符附近容易多出空格处理时注意 strip 一下。4.4 大批量 JSON 文件合并成一个 Excel实际工作中经常遇到接口分页返回了很多个 JSON 文件要合并成一张总表。用 Python 分分钟搞定import glob import pandas as pd files glob.glob(data/*.json) all_data [] for f in files: with open(f, r, encodingutf-8) as fp: all_data.extend(json.load(fp)) df pd.json_normalize(all_data, sep_) df.to_excel(merged.xlsx, indexFalse)glob.glob支持通配符匹配文件路径注意如果文件里有 JSON 数组而不是单个对象extend的用法刚合适。如果每个文件是单个对象用append而不是extend。5. 可视化与非脚本方案Power Query 也能干这活很多读者可能不写 Python那 Excel 里的 Power Query 也能做互转只是嵌套处理相对麻烦。在 Excel 的“数据”选项卡选择“从文件/从其他源获取数据”进入 Power Query 编辑器后可以用“从 JSON 获取数据”直接加载 JSON 文件在查询编辑器中展开嵌套的列点列名右边的扩展箭头处理完字段类型后“关闭并上载”回 Excel。Power Query 的优势是完全可视化不需要代码缺点是层级特别深的 JSON 展开非常繁琐如果 JSON 结构经常变化每次都要重新配大数据量下 Power Query 的刷新速度不如 Python 脚本直接。所以团队的取舍一般是一次性分析用 Power Query反复执行的流程用 Python 脚本。6. 常见问题与排查技巧实录6.1 中文乱码现象生成的 JSON 文件里中文变成\uXXXX或者 Excel 里中文显示正常但读出来乱码。原因json.dump时没设置ensure_asciiFalse读取 Excel 时编码不对新版 pandas 一般不会老版本曾经有过文件保存格式不是 UTF-8。解决所有写 JSON 的操作都带上ensure_asciiFalse和encodingutf-8读 Excel 时不用特别指定编码用记事本打开 JSON 时选 UTF-8。6.2 读取 Excel 时数字变成科学计数法现象订单号、身份证号这类长数字读到 DataFrame 后变成1.23457E11。原因pandas 自动推断为数值类型。解决pd.read_excel(file.xlsx, dtypestr)如果已经读进来了用.astype(str)转回去也没用因为精度已经丢失。所以务必在读入时指定dtypestr。6.3 JSON 里多了 NaN 而不是 null现象生成的 JSON 里有NaN而不是合法的 JSON 值null。原因pandas 的缺失值是以NaN表示的但 Python 标准库的json不认这个值会原样输出NaN。这样生成的 JSON 不是严格合法的。解决df df.where(df.notna(), None)6.4 JSON 转 Excel 后数字变成字符串现象金额列在 JSON 里明明是数字Excel 里却变成了文本。原因json_normalize把混合类型列推断成了 object写 Excel 时按文本写入。解决手动指定列类型df[amount] pd.to_numeric(df[amount], errorscoerce)6.5 嵌套 JSON 写入 Excel 变成一大串字符现象写入 Excel 的单元格里是一行类似[{k: v}]的字符串。原因把 Python 列表直接放进了 DataFrame 单元格。解决要么对数组列做展开拆分要么用json.dumps把列表转成 JSON 字符串再写入前用json.dumps转换成 JSON 字符串时注意ensure_asciiFalse这样在 Excel 里看到的是可读的 JSON 文本而不是 Python 的字典表示。6.6 转换后表头变了列名带点号或特殊字符原因嵌套字段拼接时用了默认的.分隔符Excel 对特殊字符支持良好但后续处理麻烦。解决json_normalize指定sep_手动清洗列名去掉空格、括号等。7. 扩展思路互转工具还能怎么玩Excel 与 JSON 的互转绝不是孤立的动作它往往是数据处理链路上的一环。这里分享几个我实际项目里用到的扩展玩法。第一个是“Excel 配置表 脚本 自动化配置生成器”。很多系统需要大量配置文件比如流程引擎的节点配置、报表看板的图表配置、规则引擎的规则配置。把配置项维护在 Excel 里方便业务人员阅读理解用脚本转成 JSON 发布给系统使用研发不用写死配置业务改表格就能生效。这个模式落地后业务同学非常喜欢因为不需要提需求单排队等开发改配置了。第二个是“接口返回 JSON → Excel → 业务分析报表”。接口拿到的原始数据往往结构复杂直接看不方便。转成 Excel 后用数据透视表和图表做分析就轻车熟路了。这里建议json_normalize只做第一层展平深层的再按需展开避免列数爆炸。第三个是“Excel 批量生成测试数据”。构造测试数据的时候用 Excel 写好边界值和异常值脚本转成 JSON 文件按场景命名直接丢给接口测试工具批量执行。这个流程能显著提升测试前期的数据准备效率。8. 把工具做成内网服务团队一起用如果你在团队里发现很多人都在做 Excel/JSON 互转那值得花点时间把它封装成一个简单的小工具放到内网给大家用。技术栈很轻Flaskpandasopenpyxl就够界面就是上传文件和下载结果。from flask import Flask, request, send_file import pandas as pd import json app Flask(__name__) app.route(/excel2json, methods[POST]) def excel_to_json(): f request.files[file] df pd.read_excel(f, dtypestr) df df.where(df.notna(), None) data df.to_dict(orientrecords) tmp /tmp/output.json with open(tmp, w, encodingutf-8) as fp: json.dump(data, fp, ensure_asciiFalse, indent2) return send_file(tmp, as_attachmentTrue) app.run(host0.0.0.0, port5001)前端加一个支持拖拽上传的页面就完整了。这个服务的内网使用体验比让每个人自己装 Python、写脚本要好太多。9. 个人实操体会与最后的建议做了这么多轮 Excel 和 JSON 的互转我最深的体会是这个需求看似简单真正做熟练了会发现到处都是细节。表头命名规范、列类型保持、空值处理、嵌套层级设计每一样都影响最终结果的正确性。我给新手的建议是分三步走。第一步先把最简单的单表互转脚本跑通理解to_dict(orientrecords)和json_normalize这两个核心方法。第二步拿一个真实项目的数据来做处理日期、金额、长数字这几个经典坑。第三步遇到嵌套结构时别急着写长代码先在纸上画出 JSON 的层级关系再顺着结构写解析逻辑。最后再分享一个小技巧写转换脚本之前先用几行数据的小样本把逻辑跑通确认 JSON 结构完全正确之后再跑全量数据。小样本调试的反馈速度快发现结构问题也容易定位。等脚本成熟了套用到全量上就是几秒钟的事。这套思路不管你是用 Python、Node.js 还是纯粹的 Excel 函数都适用。本文还有配套的精品资源点击获取