如何用VBA解析多层嵌套JSON并将订单数据填充到Excel
VBA解析UPSales嵌套JSON并写入Excel的实现方案
核心前置规则
JsonConverter是VBA环境下通用的JSON解析库,解析结果和VBA数据类型的映射关系如下,掌握这个映射是处理嵌套结构的核心依据:
- 带键值对的JSON对象(如
user/client/stage) = VBADictionary类型,可通过键名直接取值 - 带有序列表的JSON数组(如
data/orderRow/client.users) = VBACollection类型,通过For Each遍历 - JSON中的
null值 = VBAEmpty类型,取值时需做容错判断,避免类型不匹配导致代码中断
不要采用递归遍历全JSON节点的方式取数,只需针对需要导出的业务字段,明确其在JSON结构中的层级路径,直接取值即可,从根源上避免逻辑混乱。
原有代码的基础修正
现有代码的HTTP请求头存在参数错位问题,会导致API返回异常,需调整:
' 错误写法:参数名和值位置错位 httpObject.setRequestHeader "Accept: ", "application/json" ' 正确写法 httpObject.setRequestHeader "Accept", "application/json"
嵌套结构的取值逻辑
针对示例中的UPSales订单JSON,按业务输出需求定位字段路径:
- 顶层入口:
data为订单数组,每个元素对应一个完整订单对象 - 订单头字段(每个订单全局唯一):
- 基础属性:
id/description/closeDate/probability等直接挂载在订单对象上 - 归属用户:属于Dictionary类型,路径为
user.id/user.name/user.email - 客户信息:属于Dictionary类型,路径为
client.name/client.id;client.users为数组,按业务需求取对应联系人即可 - 阶段信息:属于Dictionary类型,路径为
stage.name/stage.id
- 基础属性:
- 订单明细字段(每个订单下可存在多条):
orderRow为Collection数组,每个元素对应单个商品行,遍历每一行时,将订单头公共字段和明细字段拼接为同一行输出,避免出现字段错位
完整可运行代码
Sub GetOrders() Dim sGetResult As String Dim ws As Worksheet Dim d_lr As Long Dim httpObject As Object Dim dict_json As Object Dim objData As Collection Dim objOrder As Dictionary Dim objOrderRowList As Collection Dim objOrderRow As Dictionary Dim outRow As Long ' 绑定目标工作表,定位起始写入行 Set ws = ActiveSheet d_lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row outRow = d_lr + 1 ' 初始化HTTP请求 Set httpObject = CreateObject("MSXML2.XMLHTTP") sURL = "https://integration.upsales.com/api/v2/orders?token=" & wAdmin.Range("C4") & "&probability=100" httpObject.Open "GET", sURL, False httpObject.setRequestHeader "Accept", "application/json" httpObject.Send sGetResult = httpObject.responseText ' 解析JSON内容 Set dict_json = JsonConverter.ParseJson(sGetResult) Set objData = dict_json("data") ' 遍历所有订单 For Each objOrder In objData ' 遍历当前订单下的所有商品行(一对多结构) Set objOrderRowList = objOrder("orderRow") For Each objOrderRow In objOrderRowList ' 逐列写入对应字段,嵌套值通过统一函数做容错处理 ws.Cells(outRow, 1) = objOrder("id") ws.Cells(outRow, 2) = objOrder("description") ws.Cells(outRow, 3) = objOrder("closeDate") ws.Cells(outRow, 4) = getSafeValue(objOrder("user")) ws.Cells(outRow, 5) = getSafeValue(objOrder("client")) ws.Cells(outRow, 6) = getSafeValue(objOrder("stage")) ws.Cells(outRow, 7) = objOrder("currency") ws.Cells(outRow, 8) = objOrder("value") ws.Cells(outRow, 9) = objOrderRow("quantity") ws.Cells(outRow, 10) = objOrderRow("price") ws.Cells(outRow, 11) = getSafeValue(objOrderRow("product")) outRow = outRow + 1 Next objOrderRow Next objOrder MsgBox "数据同步完成,共写入" & outRow - d_lr - 1 & "行" End Sub ' 通用容错取值函数:处理null值、嵌套对象取指定字段的场景 Function getSafeValue(inputVal As Variant, Optional fieldName As String = "name") As String If IsEmpty(inputVal) Then getSafeValue = "" ElseIf TypeOf inputVal Is Dictionary Then On Error Resume Next getSafeValue = inputVal(fieldName) On Error GoTo 0 Else getSafeValue = CStr(inputVal) End If End Function
注意事项
- 对于
client.users/custom这类数组型嵌套字段,无需做全量循环,按业务需求提取指定字段值即可(如取第一个关联联系人、按fieldId匹配自定义字段值),避免产生冗余行 - 对于所有Dictionary类型的嵌套对象,无需编写循环遍历其内部键值,直接通过
对象("目标键名")的方式取值即可 - 如需要扩展导出字段,只需在代码中增加对应路径的取值语句即可,不需要修改核心遍历逻辑
内容的提问来源于stack exchange,提问作者Mr K
相关产品推荐
相关产品推荐

