以下为将数据从 XLSX 转换为 JSON 的可操作步骤与注意事项，面向数据挖掘场景，强调结构设计、数据清洗与可靠转换。
一、明确输出需求与JSON结构设计
- 记录粒度：明确一行代表一个实体（例如“用户”、“订单”），或需拆分/聚合。
- 主键与索引：选定可唯一标识记录的字段（如ID），是否需要作为字典键或仅保留为属性。
- 结构形式：
- 扁平对象数组（常用）：[{"colA": "...", "colB": ...}, ...]
- JSON Lines（大数据友好）：每行一个JSON对象，便于流式处理和增量加载。
- 多工作表：为每个工作表生成一个键（如{"Sheet1": [...], "Sheet2": [...]}）。
- 嵌套对象：若列表达层级关系（如user.name, user.email），需在转换时组装嵌套结构。
- 字段命名规范：统一小写、下划线或驼峰，避免空格与特殊字符，确保数据管道可读性。
二、数据准备与清洗（在转换前完成）
- 标题行识别：确认第一行是否为列名；若非，指定header位置或提供列名映射。
- 类型控制：
- ID/编码类：以字符串读取，保留前导零（如"00123"）。
- 日期时间：统一为ISO 8601（如"2023-07-21T10:15:00Z"），避免Excel序列号泄露。
- 数值与货币：确认小数精度；百分比字段明确是否保留为小数（0.05）或转换为百分数（5）。
- 布尔：将“是/否”“Y/N”等映射为 true/false。
- 缺失值规范：统一映射为空值 null，避免混用空字符串/NaN。
- 公式处理：将公式单元格转换为静态值，或确保工作簿重新计算并保存；否则可能读取到公式字符串或过期的缓存结果。
- 合并单元格：合并区域仅在左上角存值；按业务需要进行前向填充（forward-fill）以补齐空单元格。
- 空行/空列/隐藏行列：清理不用的行列；根据需求决定是否忽略隐藏内容。
- 去重与一致性：按主键或关键字段去重；统一单位与编码（如货币单位、地域代码）。
- 安全审查：剔除不必要的敏感字段，符合数据治理要求。
三、实现方法一：Python + pandas（适合中等规模文件）
- 适用场景：单表或多表、数万到百万级行、内存可容纳。
- 步骤与示例（单工作表，输出为数组JSON）：
- 安装依赖：pip install pandas openpyxl
- 读取与类型控制：
- 指定需要字符串读取的列，避免前导零丢失。
- 指定需要解析为日期的列。
- 缺失值与格式处理：
- 将NaN/空字符串统一为None，以在JSON中成为null。
- 统一日期为ISO字符串。
- 导出为JSON。
示例代码（单表 -> records）：
import pandas as pd
xlsx_path = "data.xlsx"
sheet = "Sheet1" # 或具体工作表名
需要以字符串读取的列
string_cols = ["id", "code"]
读取
df = pd.read_excel(
xlsx_path,
sheet_name=sheet,
dtype={col: "string" for col in string_cols}, # 保留前导零
engine="openpyxl"
)
可选：解析日期列（若Excel中为文本或数值）
df["date_col"] = pd.to_datetime(df["date_col"], errors="coerce")
处理缺失值为None（JSON中输出为null）
df = df.where(df.notnull(), None)
导出为UTF-8 JSON数组，日期用ISO格式
json_str = df.to_json(orient="records", force_ascii=False, date_format="iso")
with open("data.json", "w", encoding="utf-8") as f:
f.write(json_str)
- 多工作表输出为字典：
sheets = pd.read_excel(xlsx_path, sheet_name=None, engine="openpyxl")
out = {}
for name, df in sheets.items():
df = df.where(df.notnull(), None)
out[name] = df.to_dict(orient="records")
import json
with open("data_multi_sheets.json", "w", encoding="utf-8") as f:
json.dump(out, f, ensure_ascii=False)
- 嵌套结构（列名含.号生成子对象）：
def nest_record(rec):
out = {}
for k, v in rec.items():
if "." in k:
p, c = k.split(".", 1)
out.setdefault(p, {})[c] = v
else:
out[k] = v
return out
records = [nest_record(r) for r in df.to_dict(orient="records")]
with open("data_nested.json", "w", encoding="utf-8") as f:
json.dump(records, f, ensure_ascii=False)
四、实现方法二：流式转换（适合超大文件，避免内存占用）
- 适用场景：数百万行以上或内存受限；生成JSON Lines便于管道处理。
- 步骤：用openpyxl的只读模式逐行遍历，输出.ndjson/.jsonl。
示例代码（单表 -> JSON Lines）：
from openpyxl import load_workbook
import json
wb = load_workbook("data.xlsx", read_only=True, data_only=True)
ws = wb["Sheet1"]
rows = ws.iter_rows(values_only=True)
headers = [cell for cell in next(rows)]
with open("data.jsonl", "w", encoding="utf-8") as f:
for row in rows:
obj = {}
for h, v in zip(headers, row):
# 空值规范化
if v == "" or v is None:
obj[h] = None
else:
obj[h] = v
f.write(json.dumps(obj, ensure_ascii=False) + "\n")
- 注意：
- data_only=True会读取公式的缓存结果；确保工作簿已保存且计算结果最新。
- 合并单元格需自行补值（如对特定列执行前向填充）。
- 日期类型如为datetime，将直接序列化为字符串；如需ISO规范，可统一格式化。
五、质量验证与审计
- 结构与语法：
- 使用jq或Python json模块验证：jq . data.json >/dev/null 或尝试json.loads逐条加载JSON Lines。
- 完整性与一致性：
- 记录数比对：Excel行数（剔除标题与空行）与JSON对象数一致。
- 主键重复检查与缺失检查。
- 类型抽样核对：日期、数值精度、布尔、前导零保持情况。
- 特殊值与错误值：Excel中的错误（#N/A等）是否正确映射为null或标记。
- 编码与国际化：
- 变更可追溯：
- 保留转换日志：源文件名、工作表、行计数、清洗规则、时间戳。
六、常见问题与处理建议
- 前导零丢失：为对应列指定dtype为字符串；转换后抽样核对。
- 日期异常为整数：Excel日期序列需显式解析为日期；统一为ISO 8601。
- 百分比与货币：明确单位与表达方式（小数还是百分数）；必要时在转换前做派生字段。
- 合并单元格导致空值：使用forward-fill在分组列（如类别、地区）上填充。
- 公式读取为字符串或旧值：在Excel中将公式“粘贴为值”或重新计算后保存；流式读取时使用data_only=True并确保缓存刷新。
- 大文件OOM：采用JSON Lines流式输出；或分工作表/分批转换；必要时先导出为CSV再做流式JSON转换。
七、输出格式选择建议
- 小中规模：JSON数组（orient="records"）便于一次性加载与下游建模。
- 大规模或管道处理：JSON Lines（.jsonl/.ndjson），每行一个对象，易于分布式摄取与增量更新。
- 多表：字典封装或分别生成多个文件，按下游消费方式决定。
按以上步骤实施，可确保从 XLSX 到 JSON 的转换在结构、类型与质量上满足数据挖掘下游使用需求。
