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

问题描述

我想从此链接 https://api中导入数据. cartolafc.globo.com/time/slug/umo/16 (最后两个参考，"umo"和"16"是动态的)链接到spreedsheet，将参考链接到单元格. 我对api或JSON不了解.有没有一种简单"的方式来做到这一点?

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?

推荐答案

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

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:

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

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

这是VBA示例，显示了如何检索该值. 将 JSON.bas 模块导入VBA项目中以进行JSON处理.

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

对我来说，atletas数组的输出如下:

The output for atletas array for me is as follows:

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

BTW, the similar approach applied in other answers.

这篇关于从包含JSON的API链接导入数据到EXCEL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！