Excel语法-解析JSON字符串并根据key：values设置单元格值

eaf3rand 于 5个月前发布在其他

关注(0)|答案(2)|浏览(74)

我有一个Excel电子表格列：

|      A     |    B   |      C     |      D     |
    | Job number | Status | Actual Hrs | % Complete |
1___|____________|________|____________|____________|
2___|____________|________|____________|____________|

字符串
我有一个宏从外部Web API获取以下字符串：

[{
  "jobnumber":"123",
  "status":"Active",
  "actualhrs":"133",
  "completion":"94"},
{
  "jobnumber":"124",
  "status":"Active",
  "actualhrs":"13",
  "completion":"5"},
  ...
]

型
字符串将包含数百个作业。我试图将响应解析为Excel中的对象，然后填充电子表格中的行，这让我很为难。
有谁能给我一些提示吗？我知道我没有显示任何代码，但说实话，我在兜圈子。
我已经尝试过将数据作为对象。

JSON

来源：https://stackoverflow.com/questions/77695547/excel-vba-parse-json-string-and-set-cell-values-based-on-keyvalues

2条答案

按热度按时间

pbwdgjma1#

从https://github.com/VBA-tools/VBA-JSON下载JsonConverter.bas
将JsonConverter.bas导入工作簿
添加Scripting引用（VBE工具>引用，检查Microsoft Scripting Runtime）
将所有JSON数据存储在数组中，并将输出一次性写入工作表，以提高效率

Option Explicit

Sub JsonData()
    Dim jsonStr As String, oJson, vKey
    Dim JsonParse As Object
    Dim iR As Long, iC As Long, arrRes()
    jsonStr = [a1] ' JSON string, modify as needed
    Set JsonParse = JsonConverter.ParseJson(jsonStr)
    If Not JsonParse Is Nothing Then
        ReDim arrRes(JsonParse.Count, 1 To JsonParse(1).Count)
        iR = 0
        iC = 0
        For Each vKey In Split("Job number|Status|Actual Hrs|% Complete", "|")
            iC = iC + 1
            arrRes(iR, iC) = vKey
        Next
        For Each oJson In JsonParse
            iC = 0
            iR = iR + 1
            For Each vKey In oJson
                iC = iC + 1
                arrRes(iR, iC) = oJson(vKey)
            Next
        Next
        Sheets.Add
        Range("A1").Resize(iR + 1, iC).Value = arrRes
    End If
End Sub

字符串

的数据

解析JSON数据的一种纯粹的方法
- 注意：* 如果由于缺少JSON验证而导致JSON数据无效，则代码可能会导致运行时错误。

Option Explicit

Sub JsonData2()
    Dim jsonStr As String
    Dim iR As Long, iC As Long, arrRes()
    Dim aData, vItem, vKey, vJson
    ' Remove space, newline, header and trailer chars
    jsonStr = Replace(Replace([a1], Chr(10), ""), Chr(32), "")
    jsonStr = Replace(Mid(jsonStr, 3, Len(jsonStr) - 4), Chr(34), "")
    ' Split json data
    aData = Split(jsonStr, "},{")
    ReDim arrRes(UBound(aData) + 1, 1 To 4)
    iR = 0
    iC = 0
    ' Populate header
    For Each vKey In Split("Job number|Status|Actual Hrs|% Complete", "|")
        iC = iC + 1
        arrRes(iR, iC) = vKey
    Next
    ' Extract json data
    For Each vJson In aData
        iR = iR + 1
        iC = 0
        For Each vItem In Split(vJson, ",")
            iC = iC + 1
            arrRes(iR, iC) = Split(vItem, ":")(1)
        Next
    Next
    ' Write output to sheet
    Sheets.Add
    Range("A1").Resize(iR + 1, iC).Value = arrRes
End Sub

型

赞(0）回复(0）举报 5个月前

wsxa1bj12#

你可以使用VBA-JSON。下面是示例代码：

Sub ParseJSONAndPopulateSheet()
    ' Declare variables
    Dim json As String
    Dim jsonObj As Object
    Dim i As Integer
    Dim jobData As Object
    Dim rowData As Variant
    
    ' Replace this with your actual JSON string received from the API
    json = "[{""jobnumber"":""123"",""status"":""Active"",""actualhrs"":""133"",""completion"":""94""},{""jobnumber"":""124"",""status"":""Active"",""actualhrs"":""13"",""completion"":""5""}]"
    
    ' Parse JSON string to object
    Set jsonObj = JsonConverter.ParseJson(json)
    
    ' Loop through each object in the JSON array
    For i = 1 To jsonObj.Count
        Set jobData = jsonObj(i)
        
        ' Get job data
        rowData = Array(jobData("jobnumber"), jobData("status"), jobData("actualhrs"), jobData("completion"))
        
        ' Populate spreadsheet with job data
        With ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
            .Cells(i + 1, 1).Resize(1, UBound(rowData) + 1).Value = rowData
        End With
    Next i
End Sub

字符串
下面是上面代码的示例输出：

的数据
注意：不要忘记查看安装指南