如何使用VBA将含嵌套Tags的API返回JSON数据写入Excel

实现方法 #

错误原因 #

• 最外层jsonObject是所有商品组成的数组，Tags是单个商品下的字段，不能直接从jsonObject取值
• Tags、Values均为数组结构，需要逐层遍历才能拿到嵌套属性

修改后完整代码 #

Sub test_json()
    Dim ws As Worksheet, jsonObject As Object, http As Object
    Dim i As Long, requesturl As String
    Dim Item As Object, tagItem As Object, valueItem As Object
    
    Set ws = Worksheets("test")
    ws.Range("a1:zz100000").ClearContents
    
    ' 写入表头
    ws.Cells(1, 1).Value = "Itemcode"
    ws.Cells(1, 2).Value = "DeliveryTimeInDays"
    ws.Cells(1, 3).Value = "PalletQuantity"
    ws.Cells(1, 4).Value = "TagCode"
    ws.Cells(1, 5).Value = "Description_NL"
    ws.Cells(1, 6).Value = "Description_DE"
    ws.Cells(1, 7).Value = "Description_EN"
    ws.Cells(1, 8).Value = "Description_FR"
    
    i = 3 ' 数据起始行
    requesturl = "https://customerapi_dev.nieuwkoop-europe.com/items?sysmodified=2021-02-11&itemCode=6PPNLBO80"
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", requesturl, False
    http.send
    
    Set jsonObject = JsonConverter.ParseJson(http.responseText)
    
    ' 遍历所有商品
    For Each Item In jsonObject
        ' 写入一级字段
        ws.Cells(i, 1).Value = Item("Itemcode")
        ws.Cells(i, 2).Value = Item("DeliveryTimeInDays")
        ws.Cells(i, 3).Value = Item("PalletQuantity")
        
        ' 遍历当前商品的所有Tags
        For Each tagItem In Item("Tags")
            ws.Cells(i, 4).Value = tagItem("Code") ' 写入Tag的Code属性
            ' 遍历当前Tag下的所有Values
            For Each valueItem In tagItem("Values")
                ws.Cells(i, 5).Value = valueItem("Description_NL")
                ws.Cells(i, 6).Value = valueItem("Description_DE")
                ws.Cells(i, 7).Value = valueItem("Description_EN")
                ws.Cells(i, 8).Value = valueItem("Description_FR")
                i = i + 1 ' 每个Value占一行，如需多个Value合并可调整此处逻辑
            Next valueItem
        Next tagItem
        ' 如果一个商品对应多个Tag不需要换行，可将i=i+1移到此处
    Next Item
    
End Sub

注意事项 #

• 如果单个商品存在多个Tag/单个Tag存在多个多语言值，可根据实际需求调整i的自增位置，避免出现空行
• 可添加字段判空逻辑，避免API返回字段缺失时报错

内容的提问来源于stack exchange，提问作者Phil