从包含JSON的API链接中导入数据到EXCEL中[英] Import data from an API link that contains JSON to EXCEL

问题描述

I want to import the data from this link https://api.cartolafc.globo.com/time/slug/umo/16 (the last two references, "umo" and "16" are dynamic) to a spreedsheet, linking the references to the cells. I have no knowledgment about api or JSON. Is there a "simple" way to do this?

推荐答案

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 atletas array, clubes, posicoes, status, time objects, and several properties with scalar values:

Going further there are objects within atletas array, each of them contains some properties that can be populated 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
    Dim sName

    ' Retrieve JSON content
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://api.cartolafc.globo.com/time/slug/umo/16", 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
    End If
    ' Example of processing single property
    ' Get 'atletas' array of objects, there is no Set keyword for arrays
    vResult = vJSON("atletas")
    '' Optional get 'clubes' object of objects, Set keyword used for objects represented by dictionaries
    '' Set vResult = vJSON("clubes")
    ' Convert to 2d array
    JSON.ToArray vResult, aData, aHeader
    ' Output 2d array to first worksheet
    With ThisWorkbook.Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    ' Remove 'atletas' array from root object
    vJSON.Remove "atletas"
    ' Remove all worksheets but the first
    Application.DisplayAlerts = False
    With ThisWorkbook.Sheets
        Do Until .Count = 1
            .Item(.Count).Delete
        Loop
    End With
    Application.DisplayAlerts = True
    ' Example of processing multiply properties on separate worksheets
    ' Processing all the rest of objects and arrays
    For Each sName In vJSON
        ' Check if the property is array or object
        If IsArray(vJSON(sName)) Or IsObject(vJSON(sName)) Then
            ' Convert to 2d array
            JSON.ToArray vJSON(sName), aData, aHeader
            ' Output 2d array to worksheet
            With ThisWorkbook.Sheets.Add ' Create new worksheet for output
                OutputArray .Cells(1, 1), aHeader
                Output2DArray .Cells(2, 1), aData
                .Columns.AutoFit
            End With
            ' Remove output object from root object
            vJSON.Remove sName
        End If
    Next
    ' Processing all the rest of properties with scalar values which remain in root object
    ' Convert root object to 2d array
    JSON.ToArray vJSON, aData, aHeader
    ' Output 2d array to worksheet
    With ThisWorkbook.Sheets.Add ' Create new worksheet for output
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    ' Or the whole JSON structure could be flattened and output to worksheet
    ' Parse JSON sample
    JSON.Parse sJSONString, vJSON, sState
    ' Flatten JSON
    JSON.Flatten vJSON, vResult
    ' Convert flattened JSON to 2d array
    JSON.ToArray vResult, aData, aHeader
    ' Output 2d array to worksheet
    With ThisWorkbook.Sheets.Add ' Create new worksheet for output
        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 atletas array for me is as follows:

BTW, the similar approach applied in other answers.