1. 为什么需要将表格数据转为JSON在日常开发和多语言翻译场景中我们经常遇到这样的需求产品经理给了一个Excel表格里面是几百条多语言翻译后端工程师发来一个数据模板要求按照特定格式整理成JSON文件。这时候手动一条条复制粘贴显然不现实。JSON作为轻量级的数据交换格式已经成为前后端交互的标准。它比XML更简洁比CSV更结构化特别适合存储层级化的数据。比如一个多语言翻译的JSON可能是这样的结构{ login: { title: 用户登录, username: 请输入用户名, password: 请输入密码 } }而表格数据通常是平铺的二维结构如何高效地将行和列转换为这种嵌套的JSON对象下面分享三种我实践过的高效方法从最基础的手动拼接到全自动的脚本处理总有一款适合你。2. 公式拼接法零代码基础也能搞定2.1 基础版手动拼接JSON字符串这是最原始但最可控的方法适合数据量小50行以内的情况。我去年处理一个简单的多语言文件时就用了这个方法具体步骤在原始数据右侧插入三列辅助列第一列输入A2给键加双引号第二列输入: 键值对分隔符第三列输入B2给值加双引号新建第四列用CONCATENATE合并前三列CONCATENATE(C2,D2,E2)最终效果username: 请输入用户名提示WPS和Excel的公式略有差异WPS需要用TEXTJOIN替代CONCATENATE2.2 进阶版自动生成完整JSON结构当需要处理嵌套JSON时可以这样优化IF(ROW()2, {, ) IF(A2A1, IF(ROW()2, },, ) A2 : {, ) B2 : C2 这个公式会自动处理对象开闭括号适合这样的结构转换{ login: { title: 用户登录, username: 请输入用户名 } }3. VBA脚本法一键转换大批量数据3.1 基础转换脚本当数据超过100行时公式会变得卡顿。这是我常用的一个VBA宏保存为.xlsm文件即可使用Sub ExportToJSON() Dim ws As Worksheet Set ws ActiveSheet Dim lastRow As Long lastRow ws.Cells(ws.Rows.Count, A).End(xlUp).Row Dim jsonStr As String jsonStr { For i 2 To lastRow jsonStr jsonStr ws.Cells(i, 1).Value : _ ws.Cells(i, 2).Value If i lastRow Then jsonStr jsonStr , Next i jsonStr jsonStr } Dim fso As Object Set fso CreateObject(Scripting.FileSystemObject) Dim filePath As String filePath ThisWorkbook.Path \output.json Dim fileOut As Object Set fileOut fso.CreateTextFile(filePath, True) fileOut.Write jsonStr fileOut.Close End Sub3.2 处理复杂嵌套结构对于多级JSON需要递归处理。这个脚本可以自动识别标题层级Function ParseJSON(rng As Range) As String Dim dict As Object Set dict CreateObject(Scripting.Dictionary) For Each cell In rng.Columns(1).Cells Dim keys() As String keys Split(cell.Value, .) Dim currentDict As Object Set currentDict dict For i LBound(keys) To UBound(keys) - 1 If Not currentDict.Exists(keys(i)) Then currentDict.Add keys(i), CreateObject(Scripting.Dictionary) End If Set currentDict currentDict(keys(i)) Next i currentDict.Add keys(UBound(keys)), cell.Offset(0, 1).Value Next cell ParseJSON DictToJSON(dict) End Function4. 第三方工具法无痛转换方案4.1 在线转换工具推荐TableConvert支持直接粘贴Excel数据可视化调整字段映射CSVJSON简单粗暴的CSV转JSON适合规整的表格数据Airtable云表格工具自带JSON API导出功能注意敏感数据不要使用在线工具建议用本地软件4.2 本地软件方案VS Code插件安装Excel Viewer插件预览数据使用Excel to JSON插件直接转换配合Prettier格式化输出Python脚本import pandas as pd import json df pd.read_excel(data.xlsx) result df.set_index(key).to_dict(orientindex) with open(output.json, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2)4.3 专业ETL工具对于企业级数据转换Talend Open Studio可视化数据管道Apache NiFi自动化数据流处理Microsoft Power QueryExcel内置的ETL工具5. 避坑指南我踩过的那些雷编码问题中文乱码时保存为UTF-8格式JSON文件头加\uFEFF特殊字符处理引号、换行符需要转义可以用SUBSTITUTE(A1,,\)日期格式Excel日期是数字需要先用TEXT函数转换大数据量超过1万行建议分批次处理或者用Python等脚本语言数据校验转换后用JSONLint验证格式是否正确有次我处理一个3000行的多语言文件因为没注意德语中的引号导致整个API报错。后来养成了用这个校验公式的习惯IF(ISERROR(JSONVALUE(A1)), Invalid, Valid)6. 性能优化技巧当数据量达到10万行级别时禁用Excel自动计算Application.Calculation xlCalculationManual使用数组操作替代循环分批写入文件避免内存溢出考虑使用Power Query的增量刷新一个实际案例去年我们需要转换一个包含8种语言、每个语言5万条记录的翻译文件。最终采用的方案是用Power Query清洗数据通过Python多进程处理输出按语言分片的JSON文件用jq工具合并最终结果处理时间从最初的6小时优化到15分钟内存占用从32GB降到4GB。关键点在于流式处理替代全量加载并行化处理独立语种使用更高效的解析库orjson替代标准json库
高效转换WPS/Excel表格数据为JSON的3种实用技巧
发布时间：2026/5/15 22:25:37
1. 为什么需要将表格数据转为JSON在日常开发和多语言翻译场景中我们经常遇到这样的需求产品经理给了一个Excel表格里面是几百条多语言翻译后端工程师发来一个数据模板要求按照特定格式整理成JSON文件。这时候手动一条条复制粘贴显然不现实。JSON作为轻量级的数据交换格式已经成为前后端交互的标准。它比XML更简洁比CSV更结构化特别适合存储层级化的数据。比如一个多语言翻译的JSON可能是这样的结构{ login: { title: 用户登录, username: 请输入用户名, password: 请输入密码 } }而表格数据通常是平铺的二维结构如何高效地将行和列转换为这种嵌套的JSON对象下面分享三种我实践过的高效方法从最基础的手动拼接到全自动的脚本处理总有一款适合你。2. 公式拼接法零代码基础也能搞定2.1 基础版手动拼接JSON字符串这是最原始但最可控的方法适合数据量小50行以内的情况。我去年处理一个简单的多语言文件时就用了这个方法具体步骤在原始数据右侧插入三列辅助列第一列输入A2给键加双引号第二列输入: 键值对分隔符第三列输入B2给值加双引号新建第四列用CONCATENATE合并前三列CONCATENATE(C2,D2,E2)最终效果username: 请输入用户名提示WPS和Excel的公式略有差异WPS需要用TEXTJOIN替代CONCATENATE2.2 进阶版自动生成完整JSON结构当需要处理嵌套JSON时可以这样优化IF(ROW()2, {, ) IF(A2A1, IF(ROW()2, },, ) A2 : {, ) B2 : C2 这个公式会自动处理对象开闭括号适合这样的结构转换{ login: { title: 用户登录, username: 请输入用户名 } }3. VBA脚本法一键转换大批量数据3.1 基础转换脚本当数据超过100行时公式会变得卡顿。这是我常用的一个VBA宏保存为.xlsm文件即可使用Sub ExportToJSON() Dim ws As Worksheet Set ws ActiveSheet Dim lastRow As Long lastRow ws.Cells(ws.Rows.Count, A).End(xlUp).Row Dim jsonStr As String jsonStr { For i 2 To lastRow jsonStr jsonStr ws.Cells(i, 1).Value : _ ws.Cells(i, 2).Value If i lastRow Then jsonStr jsonStr , Next i jsonStr jsonStr } Dim fso As Object Set fso CreateObject(Scripting.FileSystemObject) Dim filePath As String filePath ThisWorkbook.Path \output.json Dim fileOut As Object Set fileOut fso.CreateTextFile(filePath, True) fileOut.Write jsonStr fileOut.Close End Sub3.2 处理复杂嵌套结构对于多级JSON需要递归处理。这个脚本可以自动识别标题层级Function ParseJSON(rng As Range) As String Dim dict As Object Set dict CreateObject(Scripting.Dictionary) For Each cell In rng.Columns(1).Cells Dim keys() As String keys Split(cell.Value, .) Dim currentDict As Object Set currentDict dict For i LBound(keys) To UBound(keys) - 1 If Not currentDict.Exists(keys(i)) Then currentDict.Add keys(i), CreateObject(Scripting.Dictionary) End If Set currentDict currentDict(keys(i)) Next i currentDict.Add keys(UBound(keys)), cell.Offset(0, 1).Value Next cell ParseJSON DictToJSON(dict) End Function4. 第三方工具法无痛转换方案4.1 在线转换工具推荐TableConvert支持直接粘贴Excel数据可视化调整字段映射CSVJSON简单粗暴的CSV转JSON适合规整的表格数据Airtable云表格工具自带JSON API导出功能注意敏感数据不要使用在线工具建议用本地软件4.2 本地软件方案VS Code插件安装Excel Viewer插件预览数据使用Excel to JSON插件直接转换配合Prettier格式化输出Python脚本import pandas as pd import json df pd.read_excel(data.xlsx) result df.set_index(key).to_dict(orientindex) with open(output.json, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2)4.3 专业ETL工具对于企业级数据转换Talend Open Studio可视化数据管道Apache NiFi自动化数据流处理Microsoft Power QueryExcel内置的ETL工具5. 避坑指南我踩过的那些雷编码问题中文乱码时保存为UTF-8格式JSON文件头加\uFEFF特殊字符处理引号、换行符需要转义可以用SUBSTITUTE(A1,,\)日期格式Excel日期是数字需要先用TEXT函数转换大数据量超过1万行建议分批次处理或者用Python等脚本语言数据校验转换后用JSONLint验证格式是否正确有次我处理一个3000行的多语言文件因为没注意德语中的引号导致整个API报错。后来养成了用这个校验公式的习惯IF(ISERROR(JSONVALUE(A1)), Invalid, Valid)6. 性能优化技巧当数据量达到10万行级别时禁用Excel自动计算Application.Calculation xlCalculationManual使用数组操作替代循环分批写入文件避免内存溢出考虑使用Power Query的增量刷新一个实际案例去年我们需要转换一个包含8种语言、每个语言5万条记录的翻译文件。最终采用的方案是用Power Query清洗数据通过Python多进程处理输出按语言分片的JSON文件用jq工具合并最终结果处理时间从最初的6小时优化到15分钟内存占用从32GB降到4GB。关键点在于流式处理替代全量加载并行化处理独立语种使用更高效的解析库orjson替代标准json库
