第334讲:用Python和VBA两种工具实现JSON/XML数据解析与Excel输出——从SaaS数据到业务报表的实战路径
在企业日常数据处理中，我们经常会遇到这样的场景：从第三方SaaS平台（比如电商ERP、CRM系统、物流管理系统）导出订单数据，格式往往是JSON或XML。
这些数据虽然结构清晰，但直接阅读和分析非常吃力——运营需要看订单金额趋势，财务需要统计税费明细，仓库需要核对发货信息，最终都得把这些数据转成Excel表格。
今天我们就围绕这个高频需求，分别用Python和VBA两种工具实现JSON/XML数据解析并输出到Excel，重点对比两者的技术差异和适用场景。文章后半段还准备了5道针对性练习题，帮你巩固核心知识点。
一、为什么JSON/XML成了SaaS数据导出的"标配"？
在动手写代码前，我们先搞懂这两个格式的特性——这决定了我们怎么解析它们。
JSON：轻量级的"数据快递员"
JSON（JavaScript Object Notation）的本质是键值对结构，语法类似Python字典。比如一个订单数据可能是这样的：
{ "order_id": "ORD20240520001", "create_time": "2024-05-20 14:30:00", "customer": { "name": "张三", "phone": "13800138000" }, "items": [ {"product_id": "P1001", "price": 299.0, "quantity": 2}, {"product_id": "P1002", "price": 199.0, "quantity": 1} ], "total_amount": 797.0}
它的优势很明显：体积小（比XML少30%-50%冗余字符）、易读性强（人类和机器都能快速理解）、跨语言兼容（几乎所有编程语言都内置支持）。这也是为什么90%以上的现代SaaS API选择JSON作为数据交换格式。
XML：传统系统的"老伙计"
XML（eXtensible Markup Language）则是标签化结构，通过嵌套标签描述数据。同样的订单用XML表示：
<?xml version="1.0" encoding="UTF-8"?><order> <order_id>ORD20240520001</order_id> <create_time>2024-05-20 14:30:00</create_time> <customer> <name>张三</name> <phone>13800138000</phone> </customer> <items> <item> <product_id>P1001</product_id> <price>299.0</price> <quantity>2</quantity> </item> <item> <product_id>P1002</product_id> <price>199.0</price> <quantity>1</quantity> </item> </items> <total_amount>797.0</total_amount></order>
XML的优势在于自描述性（标签名直接说明数据含义）和严格的层级结构，但缺点也很明显：标签冗余导致文件体积大，解析逻辑相对复杂。目前它更多存在于传统企业系统（如SAP、Oracle旧版本）的数据接口中。
二、Python实现：原生支持+高效处理
Python之所以成为数据处理的首选，一个重要原因就是对JSON/XML的原生支持——不需要安装额外组件，标准库就能搞定解析。我们分JSON和XML两种情况演示。
场景准备：模拟SaaS导出的订单数据
假设我们从SaaS后台下载了两个文件：
orders.json：包含100条订单的JSON数组
orders.xml：包含同样数据的XML文件
我们先以JSON为例，最后再对比XML的处理差异。
1. Python解析JSON并输出Excel（核心代码）
步骤1：读取JSON文件
Python的json模块是标准库的一部分，无需额外安装。我们用json.load()直接读取文件内容：
import json# 读取JSON文件with open('orders.json', 'r', encoding='utf-8') as f: orders = json.load(f) # orders是列表，每个元素是一个订单
字典
这里有个细节：encoding='utf-8'必须指定，否则中文可能会出现乱码（很多SaaS导出的文件默认是UTF-8编码）。
步骤2：数据清洗与结构化
SaaS导出的JSON往往包含嵌套结构（比如上面的customer对象和items数组），而Excel是二维表格，需要把嵌套数据"拍平"。比如我们要提取：
订单基础信息（order_id, create_time, total_amount）
客户信息（customer.name, customer.phone）
商品明细（这里需要注意：一个订单可能有多个商品，需要拆分成多行）
处理嵌套数组的典型方法是"展开"：
import pandas as pd# 展平订单数据flat_orders = []for order in orders: # 提取基础信息 base_info = { '订单ID': order['order_id'], '创建时间': order['create_time'], '总金额': order['total_amount'], '客户姓名': order['customer']['name'], '客户电话': order['customer']['phone'] } # 处理商品明细（每个商品一行） for item in order['items']: row = base_info.copy() row.update({ '商品ID': item['product_id'], '单价': item['price'], '数量': item['quantity'], '商品小计': item['price'] * item['quantity'] }) flat_orders.append(row)# 转换为DataFrame（pandas的核心数据结构）df = pd.DataFrame(flat_orders)
步骤3：输出到Excel
pandas的to_excel()方法可以直接生成Excel文件，还能设置格式（如日期格式、金额保留两位小数）：
# 输出到Excel（指定引擎为openpyxl，支持.xlsx格式）with pd.ExcelWriter('订单报表.xlsx', engine='openpyxl') as writer: df.to_excel(writer, index=False, sheet_name='订单明细') # 调整列宽（让表格更易读） worksheet = writer.sheets['订单明细'] for col in worksheet.columns: max_length = max(len(str(cell.value)) for cell in col) worksheet.column_dimensions[col[0].column_letter].width = max_length + 2print("Excel生成完成！")
关键优势总结（Python侧）
原生支持：json模块是Python标准库，无需安装任何第三方包即可解析JSON；
数据处理能力强：pandas能轻松处理百万级数据，支持复杂的数据清洗（如去重、填充空值、合并单元格）；
生态完善：如果需要进一步分析（如用matplotlib画趋势图、用sqlalchemy存入数据库），Python可以无缝衔接；
容错性好：json.loads()会自动处理转义字符（如\"），而VBA需要手动处理这些细节。
2. Python解析XML的对比实现
如果SaaS导出的是XML格式，Python的xml.etree.ElementTree模块（标准库）可以应对。还是用上面的订单XML示例：
import xml.etree.ElementTree as ET# 解析XML文件tree = ET.parse('orders.xml')root = tree.getroot() # root是<order>元素的父节点flat_orders = []for order in root.findall('order'): # 遍历所有<order>标签 # 提取基础信息（.find()找子标签，.text取文本内容） base_info = { '订单ID': order.find('order_id').text, '创建时间': order.find('create_time').text, '总金额': float(order.find('total_amount').text), '客户姓名': order.find('customer/name').text, # XPath语法：customer下的name '客户电话': order.find('customer/phone').text } # 处理商品明细（遍历<items>下的所有<item>） items = order.find('items') for item in items.findall('item'): row = base_info.copy() row.update({ '商品ID': item.find('product_id').text, '单价': float(item.find('price').text), '数量': int(item.find('quantity').text), '商品小计': float(item.find('price').text) * int(item.find('quantity').text) }) flat_orders.append(row)# 后续用pandas输出Excel的步骤和JSON完全一致df = pd.DataFrame(flat_orders)df.to_excel('订单报表_XML.xlsx', index=False)
可以看到，XML的解析逻辑和JSON类似，但代码更冗长——因为需要通过.find()逐层定位标签，而JSON可以直接用键名访问（如order['customer']['name']）。
三、VBA实现：依赖组件+兼容旧环境
VBA（Visual Basic for Applications）是Excel内置的脚本语言，适合在不安装额外软件的环境中处理数据（比如公司电脑禁止安装Python的场景）。但它的JSON/XML解析能力较弱，必须依赖外部组件。
1. VBA解析JSON的两种方案
VBA本身没有内置JSON解析器，常用两种方案：
方案A：ScriptControl（Windows特有，32位Excel推荐）
调用Windows的ScriptControl组件，执行JavaScript代码来解析JSON（因为JSON本质是JavaScript的子集）。
方案B：第三方JSON解析库（如VBA-JSON）
导入开源的VBA模块（如https://github.com/VBA-tools/VBA-JSON），通过纯VBA代码解析JSON。
这里重点讲方案A（最常用，但有限制），因为它不需要额外下载文件，适合紧急场景。
步骤1：启用ScriptControl组件
打开VBA编辑器（Alt+F11）→ 工具 → 引用 → 勾选「Microsoft Script Control 1.0」（如果找不到，可能需要注册scrrun.dll，但现代Windows通常已预装）。
步骤2：编写JSON解析代码
Sub ParseJSON_ToExcel() Dim fso As Object, jsonFile As Object Dim jsonText As String, sc As Object Dim orders As Object, order As Object Dim ws As Worksheet, rowNum As Integer ' 1. 读取JSON文件 Set fso = CreateObject("Scripting.FileSystemObject") Set jsonFile = fso.OpenTextFile("C:\orders.json", 1, False, -1) ' -1表示UTF-8编码 jsonText = jsonFile.ReadAll jsonFile.Close ' 2. 用ScriptControl解析JSON（执行JavaScript代码） Set sc = CreateObject("ScriptControl") sc.Language = "JScript" ' 设置为JavaScript引擎 ' 执行JS代码：将JSON字符串转为对象（注意：JSON字符串中的双引号需要转义） sc.Eval "var data = (" & jsonText & ");" ' 3. 初始化Excel表格 Set ws = ThisWorkbook.Sheets("Sheet1") ws.Cells.Clear rowNum = 1 ' 表头 ws.Cells(rowNum, 1) = "订单ID" ws.Cells(rowNum, 2) = "创建时间" ws.Cells(rowNum, 3) = "总金额" ws.Cells(rowNum, 4) = "客户姓名" ws.Cells(rowNum, 5) = "客户电话" ws.Cells(rowNum, 6) = "商品ID" ws.Cells(rowNum, 7) = "单价" ws.Cells(rowNum, 8) = "数量" ws.Cells(rowNum, 9) = "商品小计" rowNum = rowNum + 1 ' 4. 遍历JSON对象（通过JS的for循环） Dim i As Integer, j As Integer Dim items As Object, item As Object ' 获取订单数组的长度（data是JS数组） Dim orderCount As Integer orderCount = sc.Eval("data.length") For i = 0 To orderCount - 1 ' 获取单个订单对象 Set order = sc.Eval("data[" & i & "]") ' 获取商品数组 Set items = sc.Eval("data[" & i & "].items") Dim itemCount As Integer itemCount = sc.Eval("data[" & i & "].items.length") For j = 0 To itemCount - 1 Set item = sc.Eval("data[" & i & "].items[" & j & "]") ' 写入Excel行 ws.Cells(rowNum, 1) = sc.Eval("data[" & i & "].order_id") ws.Cells(rowNum, 2) = sc.Eval("data[" & i & "].create_time") ws.Cells(rowNum, 3) = sc.Eval("data[" & i & "].total_amount") ws.Cells(rowNum, 4) = sc.Eval("data[" & i & "].customer.name") ws.Cells(rowNum, 5) = sc.Eval("data[" & i & "].customer.phone") ws.Cells(rowNum, 6) = sc.Eval("data[" & i & "].items[" & j & "].product_id") ws.Cells(rowNum, 7) = sc.Eval("data[" & i & "].items[" & j & "].price") ws.Cells(rowNum, 8) = sc.Eval("data[" & i & "].items[" & j & "].quantity") ws.Cells(rowNum, 9) = sc.Eval("data[" & i & "].items[" & j & "].price * data[" & i & "].items[" & j & "].quantity") rowNum = rowNum + 1 Next j Next i ' 调整列宽 ws.Columns.AutoFit MsgBox "JSON解析完成！"End Sub
VBA解析JSON的痛点
环境限制：ScriptControl仅支持32位Windows版Excel（64位Excel无法引用该组件）；
性能差：每调用一次sc.Eval()都要执行JS代码，遍历1000条订单时速度会明显下降；
调试困难：JS代码的错误（如JSON格式错误）不会直接反馈到VBA，需要手动排查；
编码问题：读取UTF-8文件需要显式指定-1参数（OpenTextFile的第四个参数），否则中文会乱码。
2. VBA解析XML的实现（MSXML2.DOMDocument）
相比JSON，VBA解析XML更成熟——因为微软早期大力推广XML，Office内置了对MSXML2.DOMDocument的支持。
代码示例：
Sub ParseXML_ToExcel() Dim xmlDoc As Object, orders As Object, order As Object Dim ws As Worksheet, rowNum As Integer ' 1. 创建XML文档对象 Set xmlDoc = CreateObject("MSXML2.DOMDocument") xmlDoc.async = False ' 同步加载（避免文件未加载完就解析） xmlDoc.Load ("C:\orders.xml") ' 加载XML文件 ' 2. 检查XML是否加载成功 If xmlDoc.parseError.ErrorCode <> 0 Then MsgBox "XML加载失败：" & xmlDoc.parseError.reason Exit Sub End If ' 3. 初始化Excel表格 Set ws = ThisWorkbook.Sheets("Sheet1") ws.Cells.Clear rowNum = 1 ' 表头（和JSON示例一致） ws.Cells(rowNum, 1) = "订单ID" ws.Cells(rowNum, 2) = "创建时间" ws.Cells(rowNum, 3) = "总金额" ws.Cells(rowNum, 4) = "客户姓名" ws.Cells(rowNum, 5) = "客户电话" ws.Cells(rowNum, 6) = "商品ID" ws.Cells(rowNum, 7) = "单价" ws.Cells(rowNum, 8) = "数量" ws.Cells(rowNum, 9) = "商品小计" rowNum = rowNum + 1 ' 4. 遍历XML节点（XPath语法） Set orders = xmlDoc.SelectNodes("//order") ' 选取所有<order>节点 Dim items As Object, item As Object For Each order In orders ' 提取基础信息（SelectSingleNode找单个节点） Dim orderId As String: orderId = order.SelectSingleNode("order_id").Text Dim createTime As String: createTime = order.SelectSingleNode("create_time").Text Dim totalAmount As Double: totalAmount = CDbl(order.SelectSingleNode("total_amount").Text) Dim custName As String: custName = order.SelectSingleNode("customer/name").Text Dim custPhone As String: custPhone = order.SelectSingleNode("customer/phone").Text ' 处理商品明细 Set items = order.SelectNodes("items/item") For Each item In items Dim productId As String: productId = item.SelectSingleNode("product_id").Text Dim price As Double: price = CDbl(item.SelectSingleNode("price").Text) Dim quantity As Integer: quantity = CInt(item.SelectSingleNode("quantity").Text) ' 写入Excel ws.Cells(rowNum, 1) = orderId ws.Cells(rowNum, 2) = createTime ws.Cells(rowNum, 3) = totalAmount ws.Cells(rowNum, 4) = custName ws.Cells(rowNum, 5) = custPhone ws.Cells(rowNum, 6) = productId ws.Cells(rowNum, 7) = price ws.Cells(rowNum, 8) = quantity ws.Cells(rowNum, 9) = price * quantity rowNum = rowNum + 1 Next item Next order ws.Columns.AutoFit MsgBox "XML解析完成！"End Sub
VBA解析XML的优势是兼容性稳定（几乎所有Windows版Excel都支持MSXML2.DOMDocument），但缺点是代码冗长，且XPath语法的学习成本高于Python的字典访问方式。
四、Python vs VBA：核心技术差异对比
我们通过一个表格总结两者的关键区别，帮你在实际工作中做选择：
对比维度 | Python | VBA |
|---|
JSON解析依赖 | 原生json模块（标准库） | 需ScriptControl组件或第三方库 |
XML解析依赖 | 原生xml.etree（标准库） | 原生MSXML2.DOMDocument（Windows特有） |
代码简洁度 | 高（字典直接访问，如order['customer']['name']） | 低（需逐层调用SelectSingleNode） |
大数据处理能力 | 强（pandas可处理百万级数据） | 弱（万级以上数据会卡顿） |
环境要求 | 需安装Python（约100MB） | Excel内置（无需额外安装） |
调试体验 | 优秀（IDE如PyCharm支持断点调试） | 一般（VBA编辑器功能较简单） |
扩展性 | 强（可对接数据库、可视化工具） | 弱（主要局限于Office生态） |
一句话总结：
如果你需要处理大量数据、复杂清洗，或希望代码可复用、可扩展，选Python；
如果你只能在公司电脑（无法安装Python）、数据量小（千行以内），且只需要一次性转换，选VBA。
五、避坑指南：实际工作中的常见问题
1. JSON解析时的编码问题
无论是Python还是VBA，读取JSON文件时一定要确认编码。SaaS导出的JSON通常是UTF-8，但有些系统会用GBK。Python中可以通过chardet库自动检测编码：
import chardetwith open('orders.json', 'rb') as f: result = chardet.detect(f.read())print(result['encoding']) # 输出检测到的编码（如'utf-8'或'GB2312'）
VBA中如果读取GBK文件，需要将OpenTextFile的第四个参数改为936（GBK的代码页）：
Set jsonFile = fso.OpenTextFile("C:\orders_gbk.json", 1, False, 936)
2. 嵌套结构的"拍平"技巧
SaaS导出的JSON常有深层嵌套（如order.customer.address.city），Python可以用pandas.json_normalize()一键拍平：
from pandas import json_normalize# 直接拍平嵌套JSON（sep参数指定分隔符）df = json_normalize(orders, record_path='items', # 展开items数组 meta=['order_id', 'create_time', 'total_amount', ['customer', 'name'], ['customer', 'phone']], meta_prefix='order_', sep='_')df.rename(columns={'order_customer_name':'客户姓名', ...}, inplace=True)
这比手动写循环高效得多，是Python处理JSON的"隐藏利器"。
3. VBA中ScriptControl的64位替代方案
如果你的Excel是64位，无法使用ScriptControl，可以改用VBA-JSON库（纯VBA实现，无组件依赖）。步骤：
下载JsonConverter.bas（https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas）；
在VBA编辑器中导入该模块；
使用时调用ParseJson(jsonText)函数，返回的集合可以像字典一样访问：
Dim parsed As ObjectSet parsed = ParseJson(jsonText)Debug.Print parsed("order_id") ' 类似Python的字典访问
六、练习题（答案见文末）
以下关于Python解析JSON的说法，正确的是？
A. 必须使用第三方库simplejson
B. json.load()用于读取字符串，json.loads()用于读取文件
C. JSON中的null会被解析为Python的None
D. JSON对象的键必须是双引号，单引号会导致解析错误
VBA中使用ScriptControl解析JSON时，以下哪项是其局限性？
A. 仅支持32位Windows版Excel
B. 无法处理嵌套的JSON数组
C. 不支持UTF-8编码的文件
D. 需要手动安装第三方DLL
Python中xml.etree.ElementTree解析XML时，获取<customer>标签下<name>文本的正确方式是？
A. root.find('customer/name').text
B. root.find('customer').find('name').value
C. root.select_one('customer name').string
D. root.xpath('//customer/name/text()')[0]
以下关于JSON和XML的描述，错误的是？
A. JSON的体积通常比XML小
B. XML支持命名空间，JSON不支持
C. Python的json模块可以解析XML格式数据
D. SaaS系统更倾向于使用JSON作为API返回格式
VBA解析XML时，MSXML2.DOMDocument的SelectNodes("//item")的作用是？
A. 选取当前节点下的所有<item>子节点
B. 选取XML文档中所有<item>节点（无论层级）
C. 选取根节点下的<item>节点
D. 选取第一个<item>节点的文本内容
答案
CD（注：C正确，JSON的null对应Python的None；D正确，JSON标准要求键用双引号，单引号是非标准扩展）
A
A
C（Python的json模块仅处理JSON，解析XML需用xml.etree或lxml）
B
