JSON VBA Parse в Excel

Question

Я получил несколько разборов JSON. Я использую VBA для анализа кода JSON с моего веб-сервера и записываю его в ячейку A1 на своем рабочем листе Excel. Но я не могу превратить это в другие клетки.

Вот мой пример JSON:

{
    "@type":["IN.areaList.1","OII.list.1"],
    "@self":"/bereiche",
    "list":[
          {"@type":["IN.bereich.1"],
           "@self":"/1.1.Bereich.2.7",
           "scha":false,
           "trlState":"",
           "oiischa":false,
           "readyTo1":false,
           "readyTo2":false,
           "numberOfBypassedDevices":0,
           "test":"",
           "TestActive":false,
           "chModeActive":false,
           "incs":[]}
            ]
}

Это мой саб, он работает для другого образца:

Sub JsonToExcelExample()

    Dim jsonText As String
    Dim jsonObject As Object
    Dim item As Object
    Dim i As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Remote")
    jsonText = ws.Cells(1, 1)
    Set jsonObject = JsonConverter.ParseJson(jsonText)
    i = 3
    ws.Cells(2, 1) = "Color"
    ws.Cells(2, 2) = "Hex Code"
    For Each item In jsonObject("0")
        ws.Cells(i, 1) = item("color")
        ws.Cells(i, 2) = item("value")
        i = i + 1
    Next

End Sub

Как изменить этот код VBA, чтобы приведенный выше пример JSON был размещен на листе как таблица?

Answer 1

Посмотрите на приведенный ниже пример. Импортируйте модуль JSON.bas в проект VBA для обработки JSON.

Option Explicit

Sub Test()

    Dim sJSONString As String
    Dim vJSON
    Dim sState As String
    Dim aData()
    Dim aHeader()
    Dim vResult

    ' Retrieve question #50068973 HTML content
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://stackru.com/questions/50068973", False
        .send
        sJSONString = .responseText
    End With
    ' Extract JSON sample from the question
    sJSONString = "{" & Split(sJSONString, "<code>{", 2)(1)
    sJSONString = Split(sJSONString, "</code>", 2)(0)
    ' Parse JSON sample
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then
        MsgBox "Invalid JSON"
        End
    End If
    ' Convert raw JSON to array and output to worksheet #1
    JSON.ToArray vJSON, aData, aHeader
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    ' Flatten JSON
    JSON.Flatten vJSON, vResult
    ' Convert flattened JSON to array and output to worksheet #2
    JSON.ToArray vResult, aData, aHeader
    With Sheets(2)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    MsgBox "Completed"

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Вывод на листе № 1 для предоставленного вами необработанного образца выглядит следующим образом:

И есть сглаженный пример вывода на листе № 2:

Кстати, аналогичный подход применяется в следующих ответах: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16 и 17.