简介：这是一款面向开发人员、数据工程师及办公自动化需求者的轻量级Excel与JSON互转工具，解决日常数据格式转换中嵌套结构处理难、多工作表映射不清晰、类型转换易出错等痛点，适用于API调试、配置导出导入、测试数据生成等典型场景。资源包共2个文件，含1个Java源码（JsonTOExcel.java）用于二次开发与逻辑学习，1个可直接运行的exe程序（ExcelToJsonTool.exe），便于零环境依赖快速使用；压缩包大小仅1.92MB，开箱即用。目前已有456人学习下载，体现了其在小工具类资源中的实用认可度。用户可直接获得完整可执行工具、核心转换逻辑源码及清晰的工程结构，便于理解JSON对象/数组到Excel行列与多Sheet的映射规则，掌握嵌套数据展开策略、日期/布尔值类型适配方法，以及Excel列名到JSON键的规范化处理技巧。
1. Excel 和 JSON 互转不是格式搬运，而是数据契约的落地：当业务方甩来一个带合并单元格的销售报表 Excel，而后端 API 只认标准 JSON Schema，你得在 15 分钟内交出可校验、可嵌套、无歧义的结构化数据——这才是真实场景里“Excel Json 互转工具”的存在意义
这不是一个玩具脚本，而是连接业务侧原始数据和工程侧结构化消费的关键链路。我见过太多团队卡在这一步：用 Excel 手动填表 → 导出 CSV → 再写 Python 脚本硬解析 → 发现日期列被 pandas 自动转成 datetime64 → 前端报错“不能序列化 datetime 对象”；或者把 JSON 拖进 Excel 时，嵌套对象直接变成[object Object]，再导出就只剩字符串。问题不在工具多不多，而在转换过程是否保留语义、是否可逆、是否支持真实业务约束。比如销售数据里“区域→大区→省份→城市”是四层树形结构，Excel 用合并单元格表达层级，JSON 必须转成嵌套数组+对象；又比如“产品SKU”列含空格和斜杠，JSON key 不能直接用，得做安全转义。这个工具要解决的，是“人眼能看懂的表格”和“机器能验证的结构”之间的可信映射。适合需要高频对接业务部门、常处理非标 Excel 表单、又要求前后端数据契约强一致的工程师、数据产品经理、测试开发——尤其当你发现每次改接口字段都要重导三次 Excel、校对五遍 JSON，你就该把它放进本地 bin 目录了。
2. 为什么不用 pandas + json.dumps 硬凑？选型逻辑与最小可行命令
2.1 三类常见误用路径及其血泪代价
很多开发者第一反应是pd.read_excel()+df.to_dict(orient='records')+json.dumps()，看似一行搞定，但实际踩坑极深：
- 合并单元格彻底失联：pandas 默认把合并单元格拆成 NaN + 重复值，原层级关系完全丢失。某跨平台系统曾因此把“华东大区”下所有省份全归到“华北”，因为合并单元格被识别为 NaN 后，
ffill()填充逻辑错位。 - 类型坍塌不可逆：Excel 里“2024/3/15”是日期，“00123”是文本（带前导零），但 pandas 读取后统一转成
datetime64或int64，导出 JSON 时前导零消失、时区信息丢失，下游校验直接失败。 - 空行/注释行无感知：业务 Excel 常在顶部加说明、底部加统计行，pandas 默认全读，
to_dict后变成无效记录，还得手动dropna(how='all')+df.iloc[:-2]，一改模板就翻车。
这些不是 bug，是设计边界——pandas 是为数值计算设计的，不是为语义保真转换设计的。
2.2 为什么最终锁定 openpyxl + custom parser 组合？
我们对比过 7 种方案（包括 xlrd、pyxlsb、xlsxwriter、json-table-schema、以及几个 npm 包），最终选择openpyxl（读） + 手写结构化解析器（转） + jsonschema（验）的组合，理由很务实：
| 方案 | 合并单元格支持 | 类型保真度 | 可逆性 | 学习成本 | 生产就绪度 |
|---|---|---|---|---|---|
pandas + openpyxl | ❌（需额外 fill logic） | ⚠️（需 dtype 强制） | ❌（无反向写入控制） | 低 | 中（依赖 pandas 行为） |
xlrd2（新版） | ✅ | ✅（原生 text/datetime） | ❌（只读） | 中 | 低（社区维护弱） |
openpyxl原生 | ✅（merged_cells属性直取） | ✅（.value+.data_type双源） | ✅（可ws.cell().value = {...}写回） | 中高 | 高（活跃维护，文档全） |
| 在线转换网站 | ✅ | ❌（自动清洗） | ❌ | 极低 | ❌（无审计、无 schema） |
关键突破点在于：openpyxl 不仅能读单元格值，还能读.merge_cells区域、.font.bold、.number_format、甚至.hyperlink。这意味着我们可以把“合并单元格”当作显式语法节点，而不是靠启发式填充去猜。例如，A1:A3 合并且值为“华东”，B1 值为“上海”，B2 值为“南京”，那结构就该是：
{ "region": "华东", "cities": [ {"name": "上海", "sales": 120000}, {"name": "南京", "sales": 98000} ] }而不是靠ffill()把“华东”复制三遍再分组。
2.3 用 openpyxl 在本地跑通互转的最小命令
先装依赖（注意：不要用xlrd，它已停止维护且不支持 .xlsx 新格式）：
pip install openpyxl jsonschema最简正向转换（Excel → JSON）脚本，仅 32 行，可直接保存为excel2json.py：
#!/usr/bin/env python3 import json from openpyxl import load_workbook def excel_to_json(excel_path: str, sheet_name: str = None) -> dict: wb = load_workbook(excel_path, data_only=True) # data_only=True 读公式结果而非公式本身 ws = wb.active if sheet_name is None else wb[sheet_name] # 提取合并单元格信息：{(min_row, max_row, min_col, max_col): value} merged_map = {} for merge in ws.merged_cells.ranges: merged_map[(merge.min_row, merge.max_row, merge.min_col, merge.max_col)] = \ ws.cell(merge.min_row, merge.min_col).value # 按行扫描，构建结构化数据 result = [] headers = None for row_idx, row in enumerate(ws.iter_rows(values_only=False), 1): # values_only=False 才能取 cell 对象 # 跳过空行 if all(cell.value is None for cell in row): continue # 第一行作为 header（简单策略，实际项目需支持自定义 header 行号） if row_idx == 1: headers = [cell.value for cell in row] continue # 构建当前行 dict，处理合并单元格覆盖 row_dict = {} for col_idx, cell in enumerate(row, 1): # 检查该单元格是否属于某个合并区域 cell_value = cell.value for (min_r, max_r, min_c, max_c), merge_val in merged_map.items(): if min_r <= row_idx <= max_r and min_c <= col_idx <= max_c: cell_value = merge_val break key = headers[col_idx-1] if col_idx-1 < len(headers) else f"col_{col_idx}" row_dict[key] = cell_value result.append(row_dict) return {"data": result} if __name__ == "__main__": import sys if len(sys.argv) < 2: print("Usage: python excel2json.py <input.xlsx> [sheet_name]") sys.exit(1) output = excel_to_json(sys.argv[1], sys.argv[2] if len(sys.argv) > 2 else None) print(json.dumps(output, ensure_ascii=False, indent=2))逻辑说明：
data_only=True是关键开关，否则读到的是=SUM(A1:A10)字符串而非计算结果；values_only=False才能拿到cell对象，进而访问.merged_cells和.value；- 合并单元格映射用元组
(min_row, max_row, min_col, max_col)作 key，避免字符串拼接歧义；- 每次遍历单元格时，主动检查其坐标是否落入任一合并区域，是则覆盖为合并单元格的左上角值——这是保真核心。
运行示例：
python excel2json.py sales_q1.xlsx "2024销售汇总"输出即为标准 JSON，可直接喂给jsonschema.validate()或前端组件。
3. JSON → Excel 反向生成：不只是写入，而是重建语义布局
3.1 为什么不能用pandas.DataFrame.to_excel()一招鲜？
to_excel()会把嵌套字典展平成字符串（如{"a": {"b": 1}}→"{'a': {'b': 1}}"），且完全无法生成合并单元格、设置列宽、冻结首行。某导师曾用它导出组织架构 JSON，结果“技术中心”合并了 A1:A5，“前端组”在 B1，“后端组”在 B2……导出后全是独立单元格，业务方拒收。
真正可用的反向流程必须支持：
- 层级映射：JSON 中
{"region": "华东", "cities": [{"name": "上海"}, {"name": "南京"}]}→ Excel 中 A1:A3 合并写“华东”，B1 写“上海”，B2 写“南京”； - 样式还原：标题行加粗、数字列右对齐、金额列加千分位；
- 动态列宽：根据内容最长字符串自动设列宽，避免“#####”。
3.2 用 openpyxl 手动构建带合并的 Excel：从 JSON 结构推导布局规则
核心思路：把 JSON 当作 DOM 树，Excel 单元格当作渲染目标，写一个轻量级 layout engine。我们定义三条映射规则：
| JSON 结构 | Excel 布局行为 | openpyxl 实现要点 |
|---|---|---|
顶层 key 为"region"，值为字符串 | 单元格 A1 合并 A1:A3，写入值 | ws.merge_cells('A1:A3'); ws['A1'] = data['region'] |
"cities"是 list，每个 item 是 object | 从第 5 行开始，每行一条 city，B 列写 name，C 列写 sales | for i, city in enumerate(data['cities']): ws.cell(row=5+i, column=2).value = city['name'] |
数值字段"sales" | 设置 number_format 为#,##0.00，右对齐 | ws.cell(...).number_format = '#,##0.00'; ws.cell(...).alignment = Alignment(horizontal='right') |
以下是完整json2excel.py脚本（含自动列宽、样式、合并）：
#!/usr/bin/env python3 import json from openpyxl import Workbook from openpyxl.styles import Font, Alignment, PatternFill, Border, Side from openpyxl.utils import get_column_letter def json_to_excel(json_data: dict, output_path: str): wb = Workbook() ws = wb.active ws.title = "Data" # 定义样式 header_font = Font(bold=True, size=11) header_fill = PatternFill(start_color="DDEBF7", end_color="DDEBF7", fill_type="solid") center_align = Alignment(horizontal="center", vertical="center") right_align = Alignment(horizontal="right", vertical="center") thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) # 写入 header（假设 data 是 list of dict） if "data" not in json_data: raise ValueError("JSON must have 'data' key as list") data = json_data["data"] if not data: ws["A1"] = "No data" wb.save(output_path) return # 写 header 行 headers = list(data[0].keys()) for col_idx, h in enumerate(headers, 1): cell = ws.cell(row=1, column=col_idx, value=h) cell.font = header_font cell.fill = header_fill cell.alignment = center_align cell.border = thin_border # 写数据行 for row_idx, record in enumerate(data, 2): for col_idx, key in enumerate(headers, 1): val = record.get(key) cell = ws.cell(row=row_idx, column=col_idx, value=val) cell.border = thin_border # 数值列右对齐 + 格式化 if isinstance(val, (int, float)): cell.alignment = right_align if key.lower() in ["sales", "amount", "price", "revenue"]: cell.number_format = '#,##0.00' else: cell.alignment = Alignment(horizontal="left", vertical="center") # 自动列宽（取 header 和前 100 行最大宽度） for col_idx, header in enumerate(headers, 1): max_length = len(header) for row_idx in range(2, min(102, len(data)+2)): # 前 100 条数据 cell_val = ws.cell(row=row_idx, column=col_idx).value if cell_val is not None: length = len(str(cell_val)) if length > max_length: max_length = length adjusted_width = min(max_length + 2, 50) # 限制最大宽度 ws.column_dimensions[get_column_letter(col_idx)].width = adjusted_width # 冻结首行 ws.freeze_panes = "A2" wb.save(output_path) if __name__ == "__main__": import sys if len(sys.argv) < 3: print("Usage: python json2excel.py <input.json> <output.xlsx>") sys.exit(1) with open(sys.argv[1], "r", encoding="utf-8") as f: data = json.load(f) json_to_excel(data, sys.argv[2])参数说明：
get_column_letter()将数字列号转为 Excel 列名（1→A, 2→B）；freeze_panes = "A2"让滚动时首行始终可见；- 列宽计算取 header 长度和前 100 行内容最大长度，兼顾性能与可读性；
number_format仅对明确命名的金额字段生效，避免误格式化 ID 字段。
运行：
python json2excel.py sales_output.json sales_recovered.xlsx生成的 Excel 具备：合并单元格（按需）、列宽自适应、首行冻结、金额千分位、字体加粗——业务方可直接打印或二次编辑。
4. 避坑：生产环境里 5 个真实翻车现场与解法
4.1 现象：Excel 里日期显示为44205，导出 JSON 变成整数而非字符串
原因：Excel 内部用“自 1900-01-01 起的天数”存日期，openpyxl 默认返回datetime对象，但若data_only=True且单元格是公式（如=TODAY()），可能返回float。
解决：在excel_to_json()中增加类型标准化：
from datetime import datetime, date def safe_date_convert(val): if isinstance(val, (datetime, date)): return val.strftime("%Y-%m-%d") elif isinstance(val, (int, float)) and 36500 < val < 100000: # 粗略判断为 Excel 日期序号 try: return datetime(1900, 1, 1) + timedelta(days=int(val)-2).strftime("%Y-%m-%d") except: return str(val) return val # 然后在 row_dict[key] = safe_date_convert(cell_value) 处调用4.2 现象：中文列名导出 JSON 后，前端解析报SyntaxError: Unexpected token u in JSON at position 0
原因：Excel 列名含不可见 Unicode 字符（如零宽空格\u200b），json.dumps()生成非法 JSON。
解决：清洗 header：
import re def clean_header(s): if not isinstance(s, str): return s # 移除零宽字符、BOM、控制字符 s = re.sub(r'[\u200b-\u200f\u202a-\u202e\ufeff]', '', s) s = s.strip() return re.sub(r'\s+', '_', s) # 多空格转下划线 # headers = [clean_header(cell.value) for cell in row]4.3 现象：合并单元格跨多行，但ws.merged_cells.ranges返回空
原因：Excel 文件由某些国产办公软件（非 Microsoft Excel）生成，未正确写入mergeCellsXML 节点。
解决：fallback 启用“视觉合并”检测——扫描连续行同列值相同且字体/背景一致，则视为逻辑合并：
# 在 excel_to_json() 中添加 fallback def detect_visual_merge(ws, start_row, end_row, col_idx): first_val = ws.cell(start_row, col_idx).value if first_val is None: return None for r in range(start_row+1, end_row+1): if ws.cell(r, col_idx).value != first_val or \ ws.cell(r, col_idx).font != ws.cell(start_row, col_idx).font: return None return (start_row, end_row, col_idx, col_idx)4.4 现象：JSON → Excel 后，长文本（如备注）在单元格里显示不全，且不自动换行
原因：openpyxl 默认关闭自动换行，且行高固定。
解决：启用 wrap_text 并动态设行高：
# 在 json2excel.py 的写数据循环中 cell = ws.cell(row=row_idx, column=col_idx, value=val) cell.alignment = Alignment(wrap_text=True, vertical="top") # 动态设行高（按行内最长字符串估算） if isinstance(val, str) and len(val) > 50: ws.row_dimensions[row_idx].height = min(len(val) // 20 * 15, 120)4.5 现象：Excel 有多个 sheet，但工具只处理第一个
原因：脚本硬编码wb.active，未提供 sheet 选择机制。
解决：增强 CLI 参数，支持-s "Sheet1,Sheet2"或-a（全部）：
python excel2json.py data.xlsx -s "Q1汇总,Q2汇总"对应代码中解析sys.argv，循环处理指定 sheet，输出为{ "Q1汇总": [...], "Q2汇总": [...] }。
5. 进阶技巧：用 JSON Schema 约束 Excel 输入，让业务方一次填对
5.1 为什么 Schema 是 Excel 转换的后悔药？
没有约束的 Excel 就是黑匣子。业务方随手在“销售额”列填了个“N/A”，或把“2024-03”写成“2024/3”，下游解析全崩。而 JSON Schema 能在转换前就拦截：
"type": "number"→ 拒绝非数字；"format": "date"→ 拒绝2024/3；"enum": ["华东","华南"]→ 拒绝“东区”。
我们把 Schema 当作 Excel 的“填写说明书”，而非事后校验器。
5.2 三步实现 Schema 驱动的 Excel 校验与转换
第一步：定义 schema.json（符合 Draft 07）
{ "$schema": "https://json-schema.org/draft-07/schema#", "type": "object", "properties": { "region": { "type": "string", "enum": ["华东", "华南", "华北", "西南"] }, "city": { "type": "string", "minLength": 2 }, "sales": { "type": "number", "minimum": 0 }, "date": { "type": "string", "format": "date" } }, "required": ["region", "city", "sales"] }第二步：用jsonschema+openpyxl做预校验
from jsonschema import validate, ValidationError import json def validate_excel_against_schema(excel_path: str, schema_path: str, sheet_name: str = None): with open(schema_path, "r", encoding="utf-8") as f: schema = json.load(f) # 先转成中间 JSON（不走完整逻辑，只取数据） data = excel_to_json(excel_path, sheet_name)["data"] # 逐条校验 errors = [] for i, record in enumerate(data): try: validate(instance=record, schema=schema) except ValidationError as e: errors.append(f"Row {i+2}: {e.message} (at {e.json_path})") if errors: raise ValueError("Excel validation failed:\n" + "\n".join(errors)) return True第三步：CLI 集成，失败时精准定位
# 先校验 python validator.py sales.xlsx schema.json --sheet "2024销售汇总" # 输出：Row 15: 'N/A' is not of type 'number' (at $.sales) # 校验通过后再转 python excel2json.py sales.xlsx --schema schema.json提示：校验失败时，我们返回 Excel 行号（
i+2因 header 占一行），业务方可直接跳转修改，无需对照 JSON 错误堆栈。
5.3 把 Schema 反向生成 Excel 模板（带数据验证）
更进一步，用 openpyxl 的data_validation模块，把 enum 生成下拉菜单，把 date format 生成输入提示：
from openpyxl.worksheet.datavalidation import DataValidation # 为 C 列（sales）加数值范围验证 dv = DataValidation(type="decimal", operator="between", formula1=0, formula2=10000000) dv.error = "销售额必须是 0~1000 万之间的数字" dv.errorTitle = "输入错误" ws.add_data_validation(dv) dv.add(f"C2:C{len(data)+1}") # 为 A 列（region）加下拉 dv_region = DataValidation(type="list", formula1='"华东,华南,华北,西南"') dv_region.error = "请选择有效大区" ws.add_data_validation(dv_region) dv_region.add("A2:A1000")运行后生成的模板 Excel，业务方打开即看到下拉菜单和错误提示，填错实时弹窗——这才是真正的“防呆设计”。
我坚持把 Schema 校验做成默认开关，哪怕多耗 200ms。因为修复一个填错的 Excel，平均要花 17 分钟沟通；而加一行--schema，能省下整个团队每天两小时。希望帮到你。
本文还有配套的精品资源，点击获取
