VBA解析嵌套JSON [英] VBA Parse Nested JSON

问题描述

VBA Noob在这里.请原谅术语方面的空白.

VBA Noob here. Please excuse any gaps in terminology etc.

我正在尝试使用VBA-JSON v2.2.3将JSON文件解析为电子表格(c)Tim Hall-

I am trying to parse a JSON file into a spreadsheet using VBA-JSON v2.2.3 (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON.

JSON文件如下所示:

The JSON file looks like this:

{
  "site": "{5BEC7C29-FF95-4ECC-9314-064B52618EEE}",
  "from": "2017-01-16",
  "to": "2017-01-22",
  "timeSheet": [
    {
      "date": "2017-01-16",
      "person": "{E2A5FDE1-33F8-43CA-A01D-5DD4A3A5E23A}",
      "personName": "James Smith",
      "company": "{B03CF7B3-0BE9-44B4-8E55-47782FDD87C0}",
      "companyName": "Acme Company Ltd",
      "minutes": "510",
      "activities": [
        {
          "name": "Training",
          "code": "TR",
          "minutes": "240"
        },
        {
          "name": "Administration",
          "code": "AD",
          "minutes": "150"
        },
        {
          "name": "Payroll",
          "code": "PR",
          "minutes": "60"
        },
        {
          "name": "Meal break",
          "code": "",
          "minutes": "60"
        }
      ]
    }
  ]
}

每个timeSheet中可能有任意数量的"timeSheet"记录，包括零在内的任何"Activities"记录.

There may be any number of 'timeSheet' records, as well as any number of 'Activities' within each timeSheet including zero.

我想在电子表格中为每个活动设置一行，并在当日活动旁边输出名称和其他数据.本质上显示所有活动的日志，持续时间和时间.使问题复杂化，即使未记录任何活动，我仍然需要输出名称etc.然后，我将填写未分配时间"或类似内容.

I want a row in the spreadsheet for each activity, with the name and other data outputted next to that days activities. Essentially showing a log of all the activities done, for how long and by who. To complicate issues, I still need the name etc outputting even if no activities are recorded. I will then fill with 'unallocated time' or something similar.

据我所知，下面是每个循环中发生的活动的更新计数.这感觉有点棘手，无法满足我的需求，通常会添加其他行，有时会完全丢失活动.

Below is as far as I have got (abridged), with an updated count of the activities occurring every loop. This feels a little hacky and doesn't give me what I am looking for, often adding additional rows and sometimes missing activities entirely.

i = 2
j = 1
activCount = CStr(JSON("timeSheet")(1)("activities").Count)

If activCount = 0 Then activCount = 1

    ws.Cells(i, 1) = JSON("site")
    ws.Cells(i, 2) = JSON("from")
    ws.Cells(i, 3) = JSON("to")


For Each item In JSON("timeSheet")
    For j = 1 To activCount
        On Error Resume Next
        ws.Cells(i, 4) = item("date")
        ws.Cells(i, 5) = item("personName")
        ws.Cells(i, 6) = item("companyName")
        ws.Cells(i, 7) = item("minutes")
        ws.Cells(i, 9) = item("activities")(j)("name")
        ws.Cells(i, 10) = item("activities")(j)("code")
        ws.Cells(i, 11) = item("activities")(j)("minutes")

        activCount = CStr(JSON("timeSheet")(i)("activities").Count)
        If activCount = 0 Then activCount = 1
        i = i + 1

    Next
Next

有人可以帮忙吗?我已经用尽了一些想法，并且已经进行了一段时间！谢谢你. :)

Can someone help? I have run out of ideas and have been working it for some time! Thank you. :)

推荐答案

这对我来说很好:

Sub TestJson2()

    Dim ts, act
    Dim Json As Object, c As Range

    'reading json from a worksheet cell...
    Set Json = JsonConverter.ParseJson(Range("A3").Value)

    Set c = ActiveSheet.Range("C5")

    'loop over timesheets
    For Each ts In Json("timeSheet")
        'loop over timesheet activities
        For Each act In ts("activities")

            c.Resize(1, 11).Value = Array(Json("site"), Json("from"), Json("to"), _
                                       ts("date"), ts("personName"), ts("companyName"), _
                                       ts("minutes"), act("name"), act("code"), _
                                       act("minutes"))
            Set c = c.Offset(1, 0)
        Next act
    Next ts

End Sub

这篇关于VBA解析嵌套JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！