JSON 导入到 Excel [英] JSON import to Excel

问题描述

是否可以在宏中编写 JSON 调用脚本?

Is it possible to script JSON calls in a macro?

我想通过 API 连接获取 JSON 字符串.看起来问题是 Excel 期望在 HTML 字符串中传递参数，但 JSON 在 HTML 正文中传递参数.有什么想法吗?

I want to get a JSON string through an API connection. It looks like the problem is Excel expects the parameters to be passed in the HTML-string, but JSON passes parameters in the HTML body. Any ideas?

推荐答案

由于这是 VBA，我会使用 COM 调用 xmlhttprequest 但以同步方式使用它，以免扰乱 VBA 的单线程执行环境，以这种方式说明 post 和 get 请求的示例类如下:

Since this is VBA, I'd use COM to call xmlhttprequest but use it in synchronous manner as not to upset VBA’s single threaded execution environment, A sample class that illustrates a post and get request in this manner follows:

'BEGIN CLASS syncWebRequest

Private Const REQUEST_COMPLETE = 4

Private m_xmlhttp As Object
Private m_response As String

Private Sub Class_Initialize()
    Set m_xmlhttp = CreateObject("Microsoft.XMLHTTP")
End Sub

Private Sub Class_Terminate()
    Set m_xmlhttp = Nothing
End Sub


Property Get Response() As String
    Response = m_response
End Property

Property Get Status() As Long
    Status = m_xmlhttp.Status
End Property

Public Sub AjaxPost(Url As String, Optional postData As String = "")
    m_xmlhttp.Open "POST", Url, False
    m_xmlhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    m_xmlhttp.setRequestHeader "Content-length", Len(postData)
    m_xmlhttp.setRequestHeader "Connection", "close"
    m_xmlhttp.send (postData)
    If m_xmlhttp.readyState = REQUEST_COMPLETE Then
        m_response = m_xmlhttp.responseText
    End If
End Sub

Public Sub AjaxGet(Url As String)
    m_xmlhttp.Open "GET", Url, False
    m_xmlhttp.setRequestHeader "Connection", "close"
    m_xmlhttp.send
    If m_xmlhttp.readyState = REQUEST_COMPLETE Then
        m_response = m_xmlhttp.responseText
    End If
End Sub

'END CLASS syncWebRequest   

所以现在你可以调用上面的方法来返回服务器的响应:

So now you can call the above to return you the server's response:

Dim request As New syncWebRequest
request.ajaxGet "http://localhost/ClientDB/AllClients?format=json" 
Dim json as string 
json = request.Response

这里的问题是我们希望能够以某种方式读取从服务器返回的数据，而不是直接操作 JSON 字符串.对我有用的是使用 VBA-JSON(谷歌代码导出 此处) COM 类型 Collection 处理 JSON 数组和 Dictionary 处理成员及其声明，带有解析器工厂方法 Parse 基本上使创建这些字典集合变得更加简单.

The problem here is we want to be able to read the data returned from the server in some way, more so than manipulating the JSON string directly. What's worked for me is using the VBA-JSON (google code export here) COM type Collection to handle JSON arrays and Dictionary to handle members and their declarations, with a parser factory method Parse that basically makes creating these collections of dictionaries much simpler.

所以现在我们可以解析 JSON:

So now we can parse the JSON:

[{"Name":"test name","Surname":"test surname","Address":{"Street":"test street","Suburb":"test suburb","City":"test city"}}]

类似于以下内容:

Set clients = parser.parse(request.Response)
For Each client In clients
    name = client("Name")
    surname = client("Surname")
    street = client("Address")("Street")
    suburb = client("Address")("Suburb")
    city = client("Address")("City")
Next

这很好，但是诸如能够编辑和回发数据之类的东西呢?好吧，还有一个方法 toString 可以从上面的 [Collection/Dictionary] JSON 数据创建一个 JSON 字符串，假设服务器接受 JSON.

That's nice but what about stuff like being able to edit and post back the data? Well there's also a method toString to create a JSON string from the above [Collection/Dictionary] JSON data, assuming the server accepts JSON back.

这篇关于JSON 导入到 Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！