Excel 转 JSON 看起来只是换个后缀,真正动手做过的人都知道不是。Excel 的底层模型是二维表格:行、列、单元格,为人眼浏览和手工编辑而设计;JSON 的底层模型是树:对象、数组、键值对,为程序解析和机器通信而设计。转换脚本真正要处理的,是这两种结构模型之间的落差,而不是换一种序列化写法。
原文提到的一个例子很典型:订单数据在 Excel 里往往拆成主表和明细表两张 Sheet,靠订单号关联;在 JSON 里,一个订单对象直接内嵌一个 details 数组就够了。哪些列属于父对象、哪些列要汇聚成子数组、嵌套几层,必须由一套显式的映射规则交代清楚,这也是脚本存在的意义。
从组织协作看,这个需求的根源是角色差异:运营、产品、财务习惯用 Excel,改一行、加个批注就能拉群沟通;后端和测试的工作环境里全是 JSON,接口文档、消息队列、配置文件都是 JSON;数据侧夹在中间,负责把业务表格翻译成开发能消费的结构。所以这道“翻译工序”不是炫技,而是两边工具链不同导致的必然产物。
一、场景决定脚本形态
配置下发场景。运营维护推荐位表,字段包括推荐位 ID、标题、图片 URL、跳转链接、排序权重、起止时间,最终要以 JSON 下发到客户端。数据量通常几十到几百行,但更新频繁。实践中值得做的是把模板字段固定、命名规范定下来,配一个转换脚本,运营改完保存、跑一下脚本、提交 JSON 到配置仓库,十几秒完成。原文还提到一个做法:在 Excel 里加一列“环境标识”,脚本按标识过滤,一次生成测试、预发、生产三份配置,这类逻辑用 Excel 自带功能很难做,脚本几行就能搞定。
数据汇总场景。多部门各维护一份 Excel,同一字段叫法不同(客户名称、顾客姓名、公司名其实指同一个实体),必须先做字段映射;日期写法也不统一,2024/01/15、20240115、“一月十五日”混在一起,输出成什么格式要由数据侧定规则。这类场景通常还要配定时任务,用 cron 或调度平台周期性触发转换。
测试数据准备场景。JMeter、Postman 只认 JSON 或 CSV,不认 Excel。测试团队常用一张表一行一条用例、一列一个请求字段,脚本逐行生成 JSON 请求体。原文举的例子是支付接口自动化:1000 条用例覆盖正常支付、余额不足、重复支付、超时等场景,脚本按场景分组生成 1000 个 JSON 文件,并在生成时追加随机订单号和时间戳,把原来手工复制粘贴的数小时压缩到几十秒。
低代码/业务系统导入场景。审批流、页面表单、数据字典这类配置层级很深,外层是流程名和版本号,内部嵌节点列表,节点里还有审批人、条件规则、跳转设置。这类需求最大的风险不是脚本写不出来,而是模板设计不严谨导致业务方填错值,所以动手写代码前先把字段定义、枚举值、必填逻辑确认清楚。
二、三种方案的取舍
Excel 公式拼接。Excel 365 里可以用 TEXTJOIN 把多行拼成数组字符串,配合 CHAR(34) 拼出双引号。只适合结构扁平、字段不超过五六个、一次性应急的场合,一旦出现嵌套或条件判断,公式会膨胀到难以维护,一个引号写错都很难定位。
Power Query。可视化、不需要代码基础,对懂 Excel 但少写代码的分析人员友好。但原文明确指出它没有直接的“导出 JSON 文件”按钮,需要写一段 M 语言构造记录和列表再输出,处理嵌套结构时步骤绕。适合一个月手动转几次、结构固定的低频操作。
Python 脚本。pandas 或 openpyxl 读表,标准库 json 序列化,可重复执行、可批量、可自定义字段映射与清洗逻辑、可挂定时任务,适配绝大多数真实场景。三个方案里,Python 的可扩展性最强,只要团队里有人会一点 Python,就优先选它;没有条件再退到 Power Query,最后才用公式应急。
三、可复用的转换脚本骨架
设计上把可变部分抽成参数,固定部分沉到方法里:默认第一行是表头且表头名即 JSON 字段名,每行数据对应一个对象,整表输出为数组。生产环境建议在脚本顶部集中放输入路径、输出路径、Sheet 名、字段类型映射、字段重命名映射,改参数不改逻辑。
- import pandas as pd
- import json
- from datetime import datetime, date
- class ExcelToJsonConverter:
- def __init__(self, excel_path, json_path, sheet_name=0):
- self.excel_path = excel_path
- self.json_path = json_path
- self.sheet_name = sheet_name
- def read_excel(self, dtype_map=None):
- # dtype_map 用于强制指定某些列为字符串,避免长数字丢精度
- df = pd.read_excel(
- self.excel_path,
- sheet_name=self.sheet_name,
- dtype=dtype_map,
- )
- return df
- def clean_dataframe(self, df):
- # 删除全空行
- df = df.dropna(how="all")
- # 将 NaN 统一转为 None,空字符串也可以自行调整
- df = df.where(pd.notnull(df), None)
- return df
- def format_dates(self, df, date_columns):
- for col in date_columns:
- if col in df.columns:
- df[col] = pd.to_datetime(df[col], errors="coerce")
- df[col] = df[col].apply(
- lambda x: x.strftime("%Y-%m-%d") if pd.notnull(x) else None
- )
- return df
- def rename_columns(self, df, column_map):
- if column_map:
- df = df.rename(columns=column_map)
- return df
- def convert(self, date_columns=None, dtype_map=None, column_map=None):
- date_columns = date_columns or []
- df = self.read_excel(dtype_map)
- df = self.clean_dataframe(df)
- df = self.format_dates(df, date_columns)
- df = self.rename_columns(df, column_map)
- records = df.to_dict(orient="records")
- with open(self.json_path, "w", encoding="utf-8") as f:
- json.dump(records, f, ensure_ascii=False, indent=2)
- print(f"转换完成,共 {len(records)} 条记录,输出至 {self.json_path}")
复制代码
调用方式:
- converter = ExcelToJsonConverter(
- excel_path="商品配置.xlsx",
- json_path="product_config.json",
- sheet_name="Sheet1",
- )
- converter.convert(
- date_columns=["上下架时间"],
- dtype_map={"商品ID": str, "条形码": str},
- column_map={"商品名称": "productName", "上下架时间": "publishDate"},
- )
复制代码
三个参数各司其职:date_columns 指定需要统一格式化的日期列;dtype_map 把商品 ID、条形码这类长数字列强制按字符串读取;column_map 把中文表头改成接口要求的字段名。ensure_ascii=False 让中文原样输出,而不是变成 \u 转义;indent=2 只是方便人查看,对接方对体积敏感时可以去掉。
四、主从 Sheet 合成嵌套 JSON
单表只能输出扁平数组。要还原订单主表加明细的结构,一般约定父表每行有唯一键(如订单号),子表每行有指向父表的外键,转换时以父表行作为对象,把子表中外键相同的记录挂到指定字段名下。
- def convert_nested(parent_df, child_df, parent_key, child_fk, child_field_name):
- # 将子表按外键分组
- child_groups = {}
- for _, row in child_df.iterrows():
- row_dict = row.to_dict()
- fk_value = str(row_dict.get(child_fk, ""))
- if fk_value not in child_groups:
- child_groups[fk_value] = []
- child_groups[fk_value].append(row_dict)
- result = []
- for _, row in parent_df.iterrows():
- parent_dict = row.to_dict()
- pk_value = str(parent_dict.get(parent_key, ""))
- parent_dict[child_field_name] = child_groups.get(pk_value, [])
- result.append(parent_dict)
- return result
复制代码
注意这里把主键和外键都做了 str() 转换再比较,因为 Excel 读进来的键可能是数字也可能是字符串,直接比对容易出现明明能对上却匹配为空的情况。这段逻辑能覆盖票据主从表、审批流节点与审批人、订单与支付流水等一大类结构。
五、数据量大时的处理方向
pandas 默认会把整个文件读进内存,几万到几十万行叠加多 Sheet,很容易吃满内存。可选的优化有四条:一是用 openpyxl 的 read_only 模式逐行读取,降低内存占用,代价是 pandas 的便捷操作基本不可用,需要手写循环;二是清洗阶段尽量用向量化操作,不要 for 循环逐行改值;三是分批处理,每次读一批(原文给的参考是五万行)转成 JSON 片段,最后合并;四是如果只是给程序消费,先把 Excel 另存为 CSV,再读 CSV,速度与内存表现通常更好。
更根本的判断是:Excel 本身不适合超过 100 万行的数据,几十万行以上的导出,应该建议业务方拆分文件或改用数据库查询导出,而不是硬扛。这个结论最好在项目早期就与业务方对齐,否则只能等脚本跑几十分钟或内存溢出后再补锅。
六、五个高频问题与排查路径
1)中文变成 \u7528\u6237 这类转义。json.dump 默认把非 ASCII 字符转义,这是合法 JSON,程序能解析,但人读起来痛苦,加 ensure_ascii=False 即可。另一个更隐蔽的情况是文件被 Windows 记事本或老系统打开显示乱码,即使内容本身是正确 UTF-8,这时可以用 encoding="utf-8-sig" 写入 BOM 让 Windows 正确识别;但如果对接方是 Linux,不建议加 BOM,因为部分解析器会把 BOM 当成内容的一部分。
2)长数字精度丢失。18 位身份证号、20 位订单号在常规格式单元格里会被显示成 1.23E+17 这类科学计数法。根因是 Excel 最多只精确存储 15 位有效数字,超出部分尾数会被舍入成 0,pandas 默认也按浮点读取。两道防线:源头把这类列的单元格格式设为“文本”;脚本里用 dtype 强制按字符串读。
- dtype_map = {
- "身份证号": str,
- "订单号": str,
- }
- df = pd.read_excel("input.xlsx", dtype=dtype_map)
复制代码
需要特别提醒:如果单元格里已经是 1.23E+17 这种形式,用 str 读出来的也只是字符串 "1.23E+17",原始精度在 Excel 文件里就已损坏。遇到这种情况只能回源头重新导出,转换阶段救不回来。
3)日期格式对不上。Excel 里的日期可能是真正的日期类型、文本日期字符串,或者 45205 这样的序列数。处理方式是显式指定日期列,用 pd.to_datetime 统一解析,errors="coerce" 把无法解析的值转为 NaT,再统一替换成 None,避免整个任务崩掉;解析成功后再按接口要求 strftime 成 "%Y-%m-%d" 或追加时分秒。如果接口要求带时区偏移,可以在格式化后手动追加,但务必跟业务方确认,不要猜。
4)空行、空值与空格导致数据错乱。分三步:dropna(how="all") 删除整行为空的尾部数据;NaN 统一转 None 输出为 null,若对接系统对 null 不友好可改为空字符串,但两种情况不要混用,否则对方无法判断规则;字符串字段批量 strip() 去首尾空格。另外,表头合并单元格或多行表头会让解析非常不可靠,最好在模板阶段就禁止合并单元格,否则脚本只能堆一堆特例兼容。
5)几十万行内存爆炸。优先按第五节的思路处理:分批读、改 CSV、read_only 模式。原文的结论是,这类问题大多不是脚本写得差,而是用错了工具——Excel 更适合小批量、模板化的数据入口。
七、写在最后
这套脚本的技术难度并不高,真正耗时的是搞清楚 Excel 里的隐含约定:哪些列不允许为空,同一字段在各部门有几种叫法,日期到底是哪种格式,接口对 null 和空字符串有没有偏好。这些规则在模板设计阶段定清楚,脚本就是一个干净的翻译器;放任不管,脚本就会变成一堆特例补丁的叠加。所以动手写代码之前,先花点时间跟维护表格的同事把数据规则聊明白,这份投入通常比多写两百行兼容代码更值。 |