Excel VBA - 如何从多数组JSON获取数据到列 [英] Excel VBA - How to get data from multiple-array JSON into columns

问题描述

我找到了一个解析JSON的解决方案，并且可以为所提供的示例工作正常：

I found a solution for parsing JSON, and it works fine for the presented example:

这里是代码：

    Sub Test()
    Dim jsonText As String
    Dim jsonObj As Dictionary
    Dim jsonRows As Collection
    Dim jsonRow As Collection
    Dim ws As Worksheet
    Dim currentRow As Long
    Dim startColumn As Long
    Dim i As Long

    Set ws = Worksheets("VIEW")

    'Create a real JSON object
    jsonText = ws.Range("A1").Value

    'Parse it
    Set jsonObj = JSON.parse(jsonText)

    'Get the rows collection
    Set jsonRows = jsonObj("rows")

    'Set the starting row where to put the values
    currentRow = 1

    'First column where to put the values
    startColumn = 2 'B

    'Loop through all the values received
    For Each jsonRow In jsonRows
        'Now loop through all the items in this row
        For i = 1 To jsonRow.Count
            ws.Cells(currentRow, startColumn + i - 1).Value = jsonRow(i)
        Next i

        'Increment the row to the next one
        currentRow = currentRow + 1
    Next jsonRow
End Sub

正在工作的JSON：

{"rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]}

然而，我需要解析具有这样结构的JSON：

However I need to parse JSON that has a structure like this:

 [{"Id":"2604","Price": 520.4, "State": true},{"Id":"2605","Price": 322.8, "State": false},{"Id":"2619","Price": 104.7, "State": true},{"Id":"2628","Price": 182.2, "State": true}]

这意味着在这种情况下，它应该是3列（Id，Price，Status）和4行。

That means, in this case, It should be 3 columns (Id, Price, Status) and 4 rows.

应该很简单，但我只是一个全新的手机。

It should be easy but I am just a total newbie here..

推荐答案

应该是这样的：

Dim jsonRows As Collection
Dim jsonRow As Dictionary 

'...

'Parse it
Set jsonRows = JSON.parse(jsonText)

'Set the starting row where to put the values
currentRow = 1

'First column where to put the values
startColumn = 2 'B

'Loop through all the values received
For Each jsonRow In jsonRows
    'Now set all the values in this row

    ws.Cells(currentRow, startColumn).Value = jsonRow("Id")
    ws.Cells(currentRow, startColumn + 1).Value = jsonRow("Price")
    ws.Cells(currentRow, startColumn + 2).Value = jsonRow("State")

    'Increment the row to the next one
    currentRow = currentRow + 1
Next jsonRow

这篇关于Excel VBA - 如何从多数组JSON获取数据到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！