You need to enable JavaScript to run this app. 如何用VBA解析多层嵌套JSON并将订单数据填充到Excel
优惠活动
大模型
产品
解决方案
定价
更多

如何用VBA解析多层嵌套JSON并将订单数据填充到Excel

VBA解析UPSales嵌套JSON并写入Excel的实现方案

核心前置规则

JsonConverter是VBA环境下通用的JSON解析库,解析结果和VBA数据类型的映射关系如下,掌握这个映射是处理嵌套结构的核心依据:

  • 带键值对的JSON对象(如user/client/stage) = VBA Dictionary类型,可通过键名直接取值
  • 带有序列表的JSON数组(如data/orderRow/client.users) = VBA Collection类型,通过For Each遍历
  • JSON中的null值 = VBA Empty类型,取值时需做容错判断,避免类型不匹配导致代码中断

不要采用递归遍历全JSON节点的方式取数,只需针对需要导出的业务字段,明确其在JSON结构中的层级路径,直接取值即可,从根源上避免逻辑混乱。


原有代码的基础修正

现有代码的HTTP请求头存在参数错位问题,会导致API返回异常,需调整:

' 错误写法:参数名和值位置错位
httpObject.setRequestHeader "Accept: ", "application/json"
' 正确写法
httpObject.setRequestHeader "Accept", "application/json"

嵌套结构的取值逻辑

针对示例中的UPSales订单JSON,按业务输出需求定位字段路径:

  1. 顶层入口:data为订单数组,每个元素对应一个完整订单对象
  2. 订单头字段(每个订单全局唯一):
    • 基础属性:id/description/closeDate/probability等直接挂载在订单对象上
    • 归属用户:属于Dictionary类型,路径为user.id/user.name/user.email
    • 客户信息:属于Dictionary类型,路径为client.name/client.id;client.users为数组,按业务需求取对应联系人即可
    • 阶段信息:属于Dictionary类型,路径为stage.name/stage.id
  3. 订单明细字段(每个订单下可存在多条):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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.02 09:27:00