将JSON数据解析为Excel工作表 [英] Parsing JSON data into Excel sheet

问题描述

我正在尝试通过使用以下代码将JSON数据作为表提取到Excel工作表中.

  Sub test()昏暗的httpObject作为对象设置httpObject = CreateObject("MSXML2.XMLHTTP")sURL ="https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json"sRequest = sURLhttpObject.Open"GET"，sRequest，FalsehttpObject.sendsGetResult = httpObject.responseText昏暗的oJSON作为对象设置oJSON = JsonConverter.ParseJson(sGetResult)我= 2对于oJSON中的每个sItemdItemString = oJSON(sItem)("symbol")sItemValue = oJSON(sItem)("open")vItemValue = oJSON(sItem)("high")xItemValue = oJSON(sItem)("low")单元格(i，1)= dItemString单元格(i，2)= sItemValue单元格(i，3)= vItemValue单元格(i，4)= xItemValue我=我+ 1下一个结束子 

但是，出现以下错误！

为什么我会收到此错误?请指教

解决方案

首先，您需要使用任何在线JSON查看器(例如

更进一步， data 数组中有一些对象，其中每个对象都包含一些可以在工作表上的行中填充的属性:

这是VBA示例，显示了如何检索这些值.将

顺便说一句，类似的方法在其他答案中使用了

I'm trying to extract JSON data into Excel sheet as table by using the following code.

Sub test()
    Dim httpObject As Object
    Set httpObject = CreateObject("MSXML2.XMLHTTP")

    sURL = "https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json"

    sRequest = sURL
    httpObject.Open "GET", sRequest, False
    httpObject.send
    sGetResult = httpObject.responseText

    Dim oJSON As Object
    Set oJSON = JsonConverter.ParseJson(sGetResult)

    i = 2

    For Each sItem In oJSON
        dItemString = oJSON(sItem)("symbol")
        sItemValue = oJSON(sItem)("open")
        vItemValue = oJSON(sItem)("high")
        xItemValue = oJSON(sItem)("low")
        Cells(i, 1) = dItemString
        Cells(i, 2) = sItemValue
        Cells(i, 3) = vItemValue
        Cells(i, 4) = xItemValue
        i = i + 1
    Next
End Sub

However, I'm getting the below error!

Why I'm getting this error? Kindly advise

解决方案

First of all you need to examine the structure of the JSON response, using any online JSON viewer (e. g. http://jsonviewer.stack.hu/), where you can see that your JSON object contains data array, and several properties with scalar values:

Going further there are objects within data array, each of them contains some properties that can be populated in rows on the worksheet:

Here is VBA example showing how that values could be retrieved. Import JSON.bas module into the VBA project for JSON processing.

Option Explicit

Sub Test()

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

    ' Retrieve JSON content
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json", True
        .send
        Do Until .readyState = 4: DoEvents: Loop
        sJSONString = .responseText
    End With
    ' Parse JSON sample
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then MsgBox "Invalid JSON": End
    ' Convert raw JSON to 2d array and output to worksheet #1
    JSON.ToArray vJSON("data"), aData, aHeader
    With ThisWorkbook.Sheets(1)
        .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

The output for data array for me is as follows:

BTW, the similar approach applied in other answers.

这篇关于将JSON数据解析为Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！