使用Excel从API提取数据

问题描述

我是编码方面的新手，非常感谢您在项目中的帮助.

I am a complete novice when it comes to coding and would really appreciate your help in a project.

我想从网站提供的API中以Excel的形式提取数据(资源URL: http://api.opensignal.com/v2/networkrank.json ).

I want to pull data in Excel from an API offered by a website (resource URL: http://api.opensignal.com/v2/networkrank.json).

请问我应该怎么做.还是可以请您提供示例代码.

Can you please advice how should I go about it. Or could you please help with a sample code.

非常感谢

推荐答案

我制作了 VBA-Web(Excel-REST)，用于使用Excel访问网络服务和API.虽然我鼓励您研究有关如何使用Excel执行Web请求的教程(查找XMLHTTPRequest)，但我发现入门有些棘手，特别是如果您不熟悉编程，那么这里有一些示例基于 OpenSignal示例的代码:

I made VBA-Web (Excel-REST) for accessing webservices and APIs with Excel. While I encourage you to look into tutorials on how to perform web requests with Excel (look for XMLHTTPRequest), I've found it to be a little tricky to get started, especially if you're new to programming, so here is some sample code based on OpenSignal's example:

Sub GetNetworkRank(Latitude As Double, Longitude As Double)
    ' Create client for executing requests
    Dim Client As New WebClient
    Client.BaseUrl = "http://api.opensignal.com/v1/"

    ' Create specific request
    Dim Request As New WebRequest
    Request.Resource = "networkrank.json"
    ' Request.Method = WebMethod.HttpGet is default
    ' Request.Format = WebFormat.Json is default

    Request.AddQuerystringParam "lat", Latitude
    Request.AddQuerystringParam "lng", Longitude

    ' distance=20 -> 20 km around lat-lng -> 40km x 40km bounding box
    Request.AddQuerystringParam "distance", 20

    ' network_id=3 -> 3G networks
    Request.AddQuerystringParam "network_id", 3

    Request.AddQuerystringParam "apikey", "YOUR_API_KEY"

    ' Get response from request
    Set Response = Client.Execute(Request)
    ' -> GET http://api.opensignal.com/v1/networkrank.json?lat=...&lng=...&...

    If Response.StatusCode = 200 Then
        ' Get network rank
        ' (json response is automatically parsed)
        Response.Data("networkRank")("...")
    Else
        Debug.Print "Error: " & Response.StatusCode & " " & Response.Content
    End If
End Sub

这篇关于使用Excel从API提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！