1. 为什么Excel转Json这件事没有想象中那么简单先交代一下背景。我之前在做一个数据迁移项目业务方维护的是一份产品配置表列名有中文有英文表头还分了上下两行合并单元格数据量大概两万行。项目需要把这些配置灌进一个新的服务里服务只认Json格式。最开始我打算手动把Excel里的数据复制出来然后照着改格式弄了两百行就放弃了眼睛花不谈还特别容易把字符串和数字搞混。后来我干脆花了一个下午写了个工具把Excel直接转成Json省下来的时间足够再写十个工具。之所以说这事没想象中简单是因为Excel和Json的根本思维模式完全不同。Excel是二维表格思维行、列、单元格一切信息都在一个二维坐标里表达。Json是树形结构思维对象套数组、数组套对象信息通过层级的嵌套来表达。中间这层转换不是简单的把单元格拼成字符串而是要完成一次数据模型的翻译。举一个最常见的例子Excel里一行数据是一笔完整记录表头是字段名这很好理解转成Json就是一组对象。可一旦出现合并单元格、多级表头、或者一列里既有数字又有百分比还有日期事情就麻烦了。你没法用一套遍历单元格然后拼JSON字符串的粗暴办法走到底否则输出的Json大概率是乱的或者类型全是字符串到了下游又得二次清洗。而且Excel转Json这个需求往往不是独立存在的。很多人的实际场景是Excel是上游的维护入口Json是下游系统的输入格式。中间还夹着清洗、校验、字段映射这些环节。只做一次性的转换没意义真正有价值的是把转换工具变成可靠的转换管线——这才是从零到一和从一到能用之间的距离。2. 动手前先想清楚数据模型与边界情况2.1 表头识别策略从第一行是字段名开始大部分转换工具默认第一行是字段名后续行是数据。这个假设对简单表格成立但真实世界的Excel往往不按常理出牌。有的表前面带两行标题说明有的表头在中间有的表有合并单元格把几列归到一大类下面。所以工具的第一步不是读数据而是确认字段名从哪一行开始。我最初的做法是打印前几行让用户确认后来发现人工确认太啰嗦改成自动识别遍历前10行若某一行满足所有列都有值且非空单元格占比超过80%就默认它是表头。自动识别的误判率不算低但配合一个可配置的skiprows参数就够用了。工具不是万能的给使用者留出覆盖默认行为的口子比把所有情况都做进判断里更务实。字段名本身也要处理。Excel里允许同一列有两个相同的表头也允许表头里有空格、括号、换行符。这些字符进入Json后会成为隐患尤其是空格和处理下游逻辑时的key引用问题。我的习惯是在读取时统一做一次清洗去除首尾空格把中间的空格替换为下划线去掉影响Json路径表达的特殊符号。这个操作不改变业务含义但能把后续的对接成本降到最低。2.2 数据类型推断字符串、数字与看起来像数字的字符串Json对类型是敏感的数字不加引号就是数值类型加了引号就是字符串。100和100在下游系统里可能走完全不同的分支逻辑。Excel里并没有严格的类型约定你看着是数字的单元格底层可能是文本格式存储的你看着是日期的单元格底层可能是序列号。转换工具最重要的工作之一就是把Excel单元格的显示值翻译成Json里的类型值。我开始写的时候只做了简单判断能转成数字就转成数字其余一律当字符串。结果很快就出问题了——某列是产品编号里面确实全是数字比如00123。Excel显示的时候是00123但底层已经被当成数字123了。我转出来的Json里变成了123下游系统匹配编号匹配不上排查了很久才找到根因。数字前导零本身就是一种格式信息Excel转数字的时候直接丢掉了。所以我现在做类型推断时有两条铁律凡是看起来像编号、代码、电话、卡号的列一律按字符串处理不做数字转换。需要数字转换的列必须读取Excel的单元格格式来判断而不是只看内容是否像数字。这两条靠纯自动识别很难百分之百做对所以我给工具加了一个人工校验的环节转换后输出一份字段类型摘要让使用者检查哪些列被识别成了数字、哪些被保留为字符串。看起来多了一步操作实际上省掉了大量下游返工时间。2.3 合并单元格、多Sheet与嵌套表头怎么处理合并单元格算是Excel转Json里最容易让新手崩溃的情况。常见场景是分组表头第一行是基本信息,第二行才是姓名、年龄、性别。转换时如果直接把第一行当字段名得到的Json就是一堆没意义的基本信息下面的数据全乱套。处理思路分两种。如果合并单元格只是用来做视觉分组实际的数据模型其实在第二行那就需要用跳过多行表头以最后一行字段名为准的策略。如果合并单元格代表的是层级关系比如基本信息下面有姓名职业信息下面有公司那这棵树的层级就需要完整表达出来我一般是把两层表头用.拼起来转成name这样的二叉key在Json里展开成嵌套结构。后一种情况更少见但属于一旦遇到就不会处理就要卡很久的典型。多Sheet的处理相对简单原则是一个Sheet转一个Json对象或一个Json数组。但Sheet名称本身可能含有空格和中文直接用作Json的key需要处理。我的默认做法是Sheet名为keySheet内容为value生成一个顶层为对象、中间层为数组的结构。如果只有一个Sheet则可以直接输出数组省去一层无意义的包裹。2.4 确定Json的输出结构数组、对象还是嵌套很多人写转换工具的时候忽略了这一步心想Excel反正就是个二维表输出成数组套对象准没错。大部分时候确实如此但有些场景需要的是对象套数组甚至对象套对象。关键在于确定你的查找主键。下游系统是通过某个字段来取一整行数据那就应该以该字段为key转成一个对象而不是数组。我用过一个上游配置表主键是产品的sku如果转成数组下游每次查询都要遍历扫描转成对象后按sku取值效率高一个量级。这个区别在工作量上只是几行代码但在下游的使用体验上差异巨大。还有一个容易被忽略的点空值怎么表达。Excel空白单元格转成Json时有人输出null有人输出空字符串有人直接删掉这个字段。三种写法对下游的影响完全不一样。null表示显式的空值空字符串表示有一个空字符串缺字段表示这个记录里没有这个属性。我最后选择的是全局可配置默认把空白单元格输出为null但允许针对特定列覆盖。简单有效不会让使用者在面对大量空值时崩溃。3. Python实现一个最小可用转换工具的核心代码3.1 依赖选型为什么用openpyxl而不是pandas这个标题下主要的实现语言我推荐Python生态最成熟而且Excel转Json这种IO密集、逻辑简单的任务用Python写最快。库的选择上新手可能第一反应是用pandas因为pd.read_excel太出名了。但我个人更推荐openpyxl原因有三第一openpyxl是专门操作Excel文件的库能读取单元格的格式信息比如数字格式、对齐方式这一点在类型推断阶段非常重要第二pandas的read_excel底层调用的是xlrd或openpyxl等于多包了一层遇到格式怪异的Excel反而不好排查第三openpyxl对合并单元格的处理更直接你能明确知道哪些单元格被合并了、合并范围是什么。当然如果你手里的Excel来自某个固定模板格式非常干净用pandas确实快一行就能读完DataFrame。但从零到一的内涵不是走捷径而是建立一个遇到任何Excel都不慌的工具所以我选openpyxl。安装依赖很简单一个命令就搞定pip install openpyxl3.2 两步走先读单元格再组装Json整个工具的核心逻辑可以拆成两段第一段是从Excel里读出行数据列表第二段是把行数据列表组装成Json。分开写的好处是便于测试也便于在中间插入清洗和类型转换逻辑。下面这段代码是从Excel读取数据并转为字典列表的最小实现import json from openpyxl import load_workbook def excel_to_json(file_path, sheet_nameNone): wb load_workbook(file_path, data_onlyTrue) ws wb[sheet_name] if sheet_name else wb.active rows list(ws.iter_rows(values_onlyTrue)) if not rows: return [] headers rows[0] # 默认第一行为表头 data [] for row in rows[1:]: record {} for idx, value in enumerate(row): if idx len(headers): record[headers[idx]] value # 过滤全空行 if any(v is not None for v in record.values()): data.append(record) return data这里要注意data_onlyTrue这个参数。它决定了你读取的是单元格的公式结果还是公式本身。如果一个单元格里写的是A1B1没有data_only的时候拿到的是公式字符串转成Json就毫无意义开了data_only才能拿到计算后的实际值。但data_only也有一个副作用如果Excel文件从未被打开并被Excel软件重新计算过公式结果可能不存在读出来是None。这种文件多半是程序生成的遇到后建议先问来源。组装Json输出也很直接def write_json(data, output_path): with open(output_path, w, encodingutf-8) as f: json.dump(data, f, ensure_asciiFalse, indent2)ensure_asciiFalse必须写否则中文会被转成\uXXXX那串转义字符文件打开以后全是乱码没法看。indent2是为了可读性如果要压缩体积去掉indent改成紧凑模式就行。3.3 类型推断与空值处理的细节单纯把单元格的值复制到Json里是不够的因为openpyxl读取出来的是Python对象直接json.dump时整型、浮点、字符串、bool、None这些Python原生类型会正确转换但日期类型会被json模块拒绝。所以类型转换这块需要专门的逻辑。我的做法是写一个convert_value函数在组装record的时候调用from datetime import datetime, date def convert_value(value): if value is None: return None if isinstance(value, (datetime, date)): return value.strftime(%Y-%m-%d %H:%M:%S) if isinstance(value, float): # 把x.0还原成整数避免1.0这种尴尬的数字 if value.is_integer(): return int(value) return value这段代码解决的问题很实际。Excel里一个单元格如果内容是1但格式是常规openpyxl读出来是整数1但如果是1.0或者列宽格式是数值读出来可能就是浮点1.0。json.dump输出1.0完全合法但下游系统看到的是1.0而不是1做精确匹配的时候就会出问题。我在这里做了浮点转整数的处理遇到1.0自动变成1从根源上减少这类问题。日期处理的取舍更典型。Json标准里没有日期类型所以你必须决定一种表达方式。输出成2026-03-15 10:30:00这种字符串是最通用、最不容易出问题的方案。有些场景会要求时间戳其实后端系统更方便但如果Excel里的日期是给业务人员看的转成时间戳反而又丢失了可读性。我建议工具的默认方案输出为字符串同时预留一个参数来切换时间戳格式。空值处理在convert_value里返回None后json.dump会输出null这是最标准的Json空值表达。如果你需要让空字符串和null区分开可以在convert_value里根据单元格类型判断openpyxl里字符串类型的空单元格读出来是None但如果你保存的是空字符串读出来就是空字符串两者在Convert时天然保持区分。3.4 批量Sheet与指定列范围的支持工具做到能转一个Sheet只是一个起点。真实Excel文件往往有多个Sheet有的Sheet里还有多余的说明列、统计行。我给工具加了三个参数分别是sheet_name、skip_rows、use_cols用起来大概这样data excel_to_json( 配置.xlsx, sheet_name商品信息, skip_rows2, use_colsA:F )skip_rows用于跳过文件顶部的说明行use_cols用于限定需要导出的列范围。实现其实不复杂skip_rows就是在iter_rows后做切片use_cols需要利用openpyxl的列索引转换把Excel的A、B、C这种列号转换为0、1、2。这两个参数加起来能覆盖掉大部分Excel里混入杂物的场景。4. 实测避坑Excel的数据怪癖与Json的严格性4.1 编号列被误转数字一次真实的排查过程前面提到了产品编号前导零的问题这里把当时的完整排查过程写出来。现象是这样的工具生成了Json下游系统校验时发现有一部分编号匹配不上。对比Excel源文件和Json文件后发现凡是纯数字开头的编号全部丢掉了前导零比如源文件里的00123到了Json里变成了123但AB123这种带字母的编号一切正常。根因不难推断Excel底层存储的是数字123前导零只是显示格式层面的东西。openpyxl读取时拿到的是存储值123显示格式的信息在读取单元格时没有自动带上。所以data_onlyTrue拿到的数据本身就缺了前导零。这是Excel的老毛病不是转换工具特有的。我最后的解决方案分两步。第一步在读取源文件时检查每个单元格的number_format如果发现列格式是文本类型就强制按字符串处理不做数字转换。第二步增加人工校验工具转完之后打印每列的样本值和推断类型使用者过一眼就知道哪列被识别错了。这两个措施叠加之后这种问题基本做到了事前避免而不是事后返工。4.2 明明看起来是日期Json里却变成一串数字我刚用openpyxl的时候犯过一个错误读取一个包含日期的ExcelJson输出里出现了一堆类似45126.0的数字。原因是Excel内部表示日期的机制是从1900年1月1日到该日期的天数虽然显示的是2023-07-18但存储值就是序列号。openpyxl在data_onlyTrue模式下拿到的是存储值不会自动做日期转换。如果不显式读取单元格的日期格式就会输出这一堆天知道什么意思的数字。解决办法就是在读取后用isinstance(value, datetime)判断然后格式化成字符串。但这里有个坑openpyxl不会把纯数字变成datetime对象它把Excel里的日期存储值保持为float。所以你不能只靠类型判断还要检查单元格的number_format是否包含yyyy、mm这类日期格式字符再做一次转换。我在convert_value里补了一段逻辑只有同时满足数字格式且number_format看起来像日期时才转成日期字符串避免误伤普通数字列。4.3 空行、隐藏行与底部汇总行Excel表格里经常有一些看似存在但实际无效的行。比如数据区中间夹了一两个空行工具遍历时如果不过滤得到的Json数组里就会有一堆全null的对象下游可能会把这些null对象当成有效记录处理。我的工具里已经加了过滤逻辑整行所有字段都是空值就跳过。还有两种行容易被忽略隐藏行和底部的汇总行。隐藏行可能是业务侧为了方便折叠而隐藏的要不要导出取决于需求但工具至少应该能识别出来否则有些数据在你不知情的情况下被导出了。openpyxl获取行的隐藏状态不算麻烦通过ws.row_dimensions[row_num].hidden判断即可。底部汇总行更恶心常见于Excel里用SUM公式在数据末尾加了一行合计。转换时这行会被当成普通数据行导出去到了下游就多出一条不该存在的记录。我的处理方式是加一个exclude_rows参数允许手动指定末尾要跳过的行数同时在默认输出里打印已跳过N行非数据内容的提示让使用者知道自己有没有漏掉东西。4.4 编码问题你写的Json为什么到别人电脑上就是乱码Json文件本身是文本文件编码必须明确。Python的json.dump默认输出编码是UTF-8这本身没错。但如果你在Windows上打开一个UTF-8编码的Json文件用系统自带的记事本或Excel查看很可能显示成乱码因为Windows的默认文本编码是GBK记事本打开文件时会先猜编码猜错了就乱。解决办法是在写入文件时手动写入BOM头即utf-8-sig编码。带BOM的UTF-8文件能被记事本正确识别也不会影响后端代码读取标准UTF-8文件。我在write_json里把encoding参数改成utf-8-sig之后这类乱码问题就再也没出现过。4.5 公式单元格与缓存值Excel里大量使用公式比如VLOOKUP(...)、IF(...)之类。转换工具读取公式单元格时有两个选择读公式本身还是读公式计算后的结果。对下游来说关心的是结果所以工具要用data_onlyTrue。但前面也提到了如果Excel文件是某个程序自动生成的从来没被Excel软件打开计算过公式部分的结果缓存可能不存在读出来就是None。这时候data_only反而成了一个麻烦。我建议的兜底策略是读取时对每个单元格判断如果值是None但单元格确实有公式就额外调用一下ws.formula拿到公式原文至少把这里有个公式但结果没缓存提示出来不要让数据无声消失。这个需求在现实中不常见但一旦出现就是那种你查了半天找不到原因的疑难问题。5. 从单文件到批量管线一个工具的工程化升级5.1 命令行封装让非程序员也能用工具停留在一个Python脚本阶段只要你自己能用就够了。但现实是Excel转Json的需求往往来自业务协作方他们大概率不会跑Python脚本。所以从自己用的脚本走向别人能用的工具第一步就是做一个简单的命令行包装。我用argparse封装了一个入口用法大概是python excel2json.py -i 输入.xlsx -o 输出.json --sheet 商品信息 --skip-rows 2再配合一个简单的交互模式不传参数时工具进入问答模式依次问Excel路径、Sheet名、输出路径。交互模式上线之后我们团队里完全不写代码的同事也能自己完成转换不用每次来找我跑脚本沟通成本降了很多。5.2 批量文件夹处理一次性搞定几十个Excel实际业务里还有一个高频需求一个文件夹里几十个Excel结构相同只是数据不同。循环处理就行但在设计时要额外注意两个细节。第一输出文件命名不能覆盖我默认使用原文件名.json作为输出名放在独立的输出目录里。第二每个文件转换完后要输出一条统计信息比如转换成功生成100条记录这样批量跑的时候能一眼看出哪个文件出问题了。批量场景对出错的宽容度也更低因为几十个文件里只要有一个编码异常或Sheet名对不上整批就会中断。我在批量模式里加了try-except单个文件失败只记录日志不阻断整批处理。这个改动看着不大但在日常使用里的价值极高。批量处理部分的伪代码大概这样def batch_convert(input_dir, output_dir): files list(Path(input_dir).glob(*.xlsx)) for file in files: try: data excel_to_json(str(file)) write_json(data, Path(output_dir) / f{file.stem}.json) print(f[OK] {file.name}: {len(data)}条记录) except Exception as e: print(f[FAIL] {file.name}: {e})5.3 对接下游从生成Json到导入数据库或推送消息转换工具的价值最终体现在下游的使用上。根据最近的实践常见下游有三类第一类是导入数据库第二类是作为API请求体第三类是推送消息到聊天工具。导入数据库的场景常见做法是先把Excel转成Json再通过程序读取Json批量插入数据库。这里就有一个之前提到的问题Json里是null数据库字段是NOT NULL插入时就会失败。所以在这个场景下空值处理策略要反过来——把null统一替换成空字符串或者跳过空字段具体取决于表结构定义。这个适配最好做成工具的可选项而不是硬编码在转换逻辑里。API请求体场景更麻烦一些因为接口通常定义了固定的Json模板比如外层必须有{data: [...]}每条记录必须有id、name、status这几个字段多一个不行少一个也不行。这时候工具的输出几乎无法直接使用除非在转换时加一层模板映射。我做过一个小功能用一个单独的映射配置文件把Excel列名对应到接口字段名转换时按映射关系重命名。这个设计留下的经验是转换工具不应该只做Excel到Json还要能做Excel到特定结构的Json。至于推送消息其实跟Json转换本身关系不大核心还是生成一个符合聊天机器人格式要求的Json然后把Json作为请求体POST过去。Excel只是数据的来源转换工具关心到生成合法Json这一步就足够了。5.4 测试与回归数据转换工具也需要单元测试很多人写数据处理工具不写测试觉得反正是一次性任务跑完就完了。但实际上Excel转Json工具迭代几次之后各种边界情况越来越多改一个地方很容易把另一个场景搞坏。我后来把工具接入了pytest建了一个test目录里面放几个不同结构的Excel样例文件每个样例对应一个预期Json输出。测试思路很简单读入Excel执行转换把输出Json和预期的Json做比对。比对时不比较全量字符串而是比较解析后的对象结构这样字段顺序变化不会导致测试失败。这块测试帮我抓住过一次回归有次我优化了类型推断逻辑导致产品编号列全部从字符串变成了数字如果没写测试这种改动就会带着bug上线。6. 工具完成后我对流程设计的新认识写这个Excel转Json工具的过程让我重新想明白了一件事数据处理工具的核心难点往往不是数据处理本身而是你对输入数据的认识到底有多深。Excel看起来格式简单但它在实际使用中被人类赋予了各种奇怪的含义——合并单元格表达分组、前导零表达编号语义、颜色标记表达状态、批注表达补充说明。转换到Json时这些excel隐藏的语义全部丢失了。工具能做的是尽量识别这些语义并用显式的规则转达给下游而不是假装它们不存在。还有一个感受就是转换工具要尽量让中间结果可见。我每次跑完转换都会输出一个摘要包含每个字段的样本值和推断类型。看似是给自己看的调试信息但实际使用中业务协作方反而最依赖这段摘要来确认转换规则是否符合预期。工具如果是个黑盒输出直接就是Json用户面对一个几十兆的文件完全没法判断对不对。有了摘要双方基于同一份数据做确认流程协作效率高了很多。另一个比较重要的心得是不要追求一次写一个万能工具。最开始我试图把所有Excel格式的边界情况全部处理掉结果越写越复杂维护成本直线上升。后来我改成识别到不支持的情况就明确报错并说明原因反而更实用。比如遇到横向合并单元格时工具能报暂不支持三级表头嵌套比默默生成错误数据要好得多——错误数据造成的返工成本远比一个报错提示大得多。最后一点在写这类工具时永远要把最可能发生在真实世界的脏数据想清楚。Excel里几乎没有干净的数据要么有合并单元格要么有空白行要么有格式混乱的日期。工具的价值恰恰在于能稳定地处理这些不干净而不是只对完美表格有效。站在这份经验上以后再遇到其它格式转换需求比如CSV转Json、数据库导出转Excel我都不会太慌张核心思路是相通的清楚输入数据的特性明确输出数据的结构然后让中间的每一步都透明可见。
