查看: 67|回复: 3

Python脚本实现Excel转JSON嵌套结构与精度处理

[复制链接]
发表于 半小时前 | 显示全部楼层 |阅读模式
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 名、字段类型映射、字段重命名映射,改参数不改逻辑。
  1. import pandas as pd
  2. import json
  3. from datetime import datetime, date
  4. class ExcelToJsonConverter:
  5.     def __init__(self, excel_path, json_path, sheet_name=0):
  6.         self.excel_path = excel_path
  7.         self.json_path = json_path
  8.         self.sheet_name = sheet_name
  9.     def read_excel(self, dtype_map=None):
  10.         # dtype_map 用于强制指定某些列为字符串,避免长数字丢精度
  11.         df = pd.read_excel(
  12.             self.excel_path,
  13.             sheet_name=self.sheet_name,
  14.             dtype=dtype_map,
  15.         )
  16.         return df
  17.     def clean_dataframe(self, df):
  18.         # 删除全空行
  19.         df = df.dropna(how="all")
  20.         # 将 NaN 统一转为 None,空字符串也可以自行调整
  21.         df = df.where(pd.notnull(df), None)
  22.         return df
  23.     def format_dates(self, df, date_columns):
  24.         for col in date_columns:
  25.             if col in df.columns:
  26.                 df[col] = pd.to_datetime(df[col], errors="coerce")
  27.                 df[col] = df[col].apply(
  28.                     lambda x: x.strftime("%Y-%m-%d") if pd.notnull(x) else None
  29.                 )
  30.         return df
  31.     def rename_columns(self, df, column_map):
  32.         if column_map:
  33.             df = df.rename(columns=column_map)
  34.         return df
  35.     def convert(self, date_columns=None, dtype_map=None, column_map=None):
  36.         date_columns = date_columns or []
  37.         df = self.read_excel(dtype_map)
  38.         df = self.clean_dataframe(df)
  39.         df = self.format_dates(df, date_columns)
  40.         df = self.rename_columns(df, column_map)
  41.         records = df.to_dict(orient="records")
  42.         with open(self.json_path, "w", encoding="utf-8") as f:
  43.             json.dump(records, f, ensure_ascii=False, indent=2)
  44.         print(f"转换完成,共 {len(records)} 条记录,输出至 {self.json_path}")
复制代码

调用方式:
  1. converter = ExcelToJsonConverter(
  2.     excel_path="商品配置.xlsx",
  3.     json_path="product_config.json",
  4.     sheet_name="Sheet1",
  5. )
  6. converter.convert(
  7.     date_columns=["上下架时间"],
  8.     dtype_map={"商品ID": str, "条形码": str},
  9.     column_map={"商品名称": "productName", "上下架时间": "publishDate"},
  10. )
复制代码

三个参数各司其职:date_columns 指定需要统一格式化的日期列;dtype_map 把商品 ID、条形码这类长数字列强制按字符串读取;column_map 把中文表头改成接口要求的字段名。ensure_ascii=False 让中文原样输出,而不是变成 \u 转义;indent=2 只是方便人查看,对接方对体积敏感时可以去掉。

四、主从 Sheet 合成嵌套 JSON

单表只能输出扁平数组。要还原订单主表加明细的结构,一般约定父表每行有唯一键(如订单号),子表每行有指向父表的外键,转换时以父表行作为对象,把子表中外键相同的记录挂到指定字段名下。
  1. def convert_nested(parent_df, child_df, parent_key, child_fk, child_field_name):
  2.     # 将子表按外键分组
  3.     child_groups = {}
  4.     for _, row in child_df.iterrows():
  5.         row_dict = row.to_dict()
  6.         fk_value = str(row_dict.get(child_fk, ""))
  7.         if fk_value not in child_groups:
  8.             child_groups[fk_value] = []
  9.         child_groups[fk_value].append(row_dict)
  10.     result = []
  11.     for _, row in parent_df.iterrows():
  12.         parent_dict = row.to_dict()
  13.         pk_value = str(parent_dict.get(parent_key, ""))
  14.         parent_dict[child_field_name] = child_groups.get(pk_value, [])
  15.         result.append(parent_dict)
  16.     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 强制按字符串读。
  1. dtype_map = {
  2.     "身份证号": str,
  3.     "订单号": str,
  4. }
  5. 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 和空字符串有没有偏好。这些规则在模板设计阶段定清楚,脚本就是一个干净的翻译器;放任不管,脚本就会变成一堆特例补丁的叠加。所以动手写代码之前,先花点时间跟维护表格的同事把数据规则聊明白,这份投入通常比多写两百行兼容代码更值。
回复

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python脚本实现Excel转JSON嵌套结构与精度处理

楼主的这个分析挺到位的,特别是把 Excel 的二维表模型和 JSON 的树模型之间的“结构落差”点出来,这确实是这类脚本最容易翻车的地方——很多人以为只是换个序列化方式,结果写到主表带明细的时候就卡住了。 几个地方想补充一下自己的体会: 第一,关于“精度处理”这一点,楼主提到 dtype_map 强制某些列为字符串,这个非常关键。实际踩过的坑是订单号、身份证号、银行卡号这类长数字,pandas 默认会读成 int64 或 float,超过 15 位有效数字直接丢精度,而且一旦丢了你后面再转字符串也救不回来。建议除了显式指定 dtype,最好在读之前先用 openpyxl 把单元格原始值看一眼,确认 Excel 里存的是文本还是数字,两者行为不一样。 第二,嵌套结构这块,我觉得可以在骨架里加一个“父子映射规则”的配置,比如指定某列是分组键、哪些列进子数组、子数组的键名叫什么。这样同一个订单号的多行明细可以聚合成一个 details 数组,而不是每行都输出一个扁平对象。实现上一般是先按分组键 groupby,再对每组构造父对象和子列表,难点在于父级字段要取第一行还是校验一致性,这个规则最好显式写出来,不要隐式假设。 第三,日期处理那个 format_dates 方法里,`df = pd.to_datetime(df)` 会对整个 DataFrame 做转换,如果表里还有别的文本列,可
回复 支持 反对

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python脚本实现Excel转JSON嵌套结构与精度处理

这篇内容很实在,尤其把配置下发、数据汇总、测试数据准备、低代码导入四类场景分开讲,比单纯贴一段转换代码更有参考价值。三种方案的取舍也讲得清楚,Python 适合可重复执行、批量处理和挂定时任务,这个判断很贴合实际项目。 脚本骨架把输入输出路径、Sheet 名、字段类型映射、重命名映射抽成参数,这个思路很好,改配置不改逻辑,后续维护会轻松很多。有两个细节值得注意:format_dates 里 pd.to_datetime(df, errors="coerce") 是对整表转,而且后面 apply 可能把 DataFrame 变成 Series,建议按列处理,比如 df = pd.to_datetime(df, errors="coerce").dt.strftime("%Y-%m-%d");另外订单号、手机号、身份证号这类列一定要用 dtype_map 指定成字符串,否则 Excel 读进来容易丢前导零或变成科学计数。 主表明细表关联、内嵌 details 数组这部分正文提到了映射规则的重要性,但骨架里还没完全展开,如果后续能补上按订单号分组、明细聚合、字段重命名和空值处理的完整示例,这个脚本骨架的可复用性会更强。整体是一份能直接落到项目里的经验总结。
回复 支持 反对

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python脚本实现Excel转JSON嵌套结构与精度处理

楼主的分析很到位,尤其“二维表格和树结构之间的落差才是脚本要处理的核心”这句总结得准确。很多教程把 Excel 转 JSON 写成两行代码,真到业务里卡住的地方,往往都在结构映射和类型精度,而不在代码本身。场景分四类、方案分三档也很实用,最后落在 Python 骨架上是合理选择。 代码这块提几个实际使用中容易踩的细节,供参考: 一是精度问题只靠 dtype_map 还不够稳妥。pandas 读表时,如果某列整列都是长数字(订单号、身份证号、运单号),即使没传 dtype,也会被推断成 int 或 float,再经过 json 序列化就变成科学计数法或末尾丢位。所以能显式给字符串的列尽量给,读表时顺便带上 keep_default_na=False 和 na_values 控制空值判定,比在 clean_dataframe 里靠 dropna 兜底更可控。 二是 format_dates 那一段,pd.to_datetime(df, errors="coerce") 传的是整个 DataFrame,会对所有列做转换,一旦有空列或非日期列就会整列变成 NaT。应该只针对 df 做转换,写回 df,否则日期列和其他列容易一起被破坏。 三是主从表嵌套,骨架里还缺一段关联逻辑。常见做法是先按订单号把主表读成字典,再把明细表按订单号 groupby 聚成 list,最后逐条塞进父对象的 det
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

指导单位

江苏省公安厅

江苏省通信管理局

浙江省台州刑侦支队

DEFCON GROUP 86025

Hacking Group 021A

旗下站点

态势感知中心

应急响应中心

红盟安全

联系我们

官方QQ群:112851260

官方邮箱:security#ihonker.org(#改成@)

官方核心成员

关注微信公众号

Archiver|手机版|小黑屋| ( 沪ICP备2021026908号 )

GMT+8, 2026-11-21 04:40 , Processed in 0.028949 second(s), 17 queries , Gzip On, Redis On.

Powered by ihonker.com

Copyright © 2015-现在.

  • 返回顶部