JSON 导入到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8044423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JSON import to Excel
提问by user1034706
Is it possible to script JSON calls in a macro?
是否可以在宏中编写 JSON 调用脚本？
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?
我想通过 API 连接获取 JSON 字符串。看起来问题是 Excel 期望在 HTML 字符串中传递参数，但 JSON 在 HTML 正文中传递参数。有任何想法吗？
回答by almog.ori
Since this is VBA, I'd use COM to call xmlhttprequestbut use it in synchronous manner as not to upset VBA's single threaded execution environment, A sample class that illustrates a postand getrequest in this manner follows:
由于这是 VBA，我将使用 COM 调用xmlhttprequest但以同步方式使用它，以免扰乱 VBA 的单线程执行环境，以这种方式说明 apost和get请求的示例类如下：
'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
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 Collectionto handle JSON arrays and Dictionaryto handle members and their declarations, with a parser factory method Parsethat basically makes creating these collections of dictionaries much simpler.
这里的问题是我们希望能够以某种方式读取从服务器返回的数据，而不是直接操作 JSON 字符串。对我有用的是使用VBA-JSON（此处为google 代码导出）COM 类型Collection来处理 JSON 数组并Dictionary处理成员及其声明，使用解析器工厂方法Parse基本上使创建这些字典集合变得更加简单。
So now we can parse the JSON:
所以现在我们可以解析 JSON：
[{"Name":"test name","Surname":"test surname","Address":{"Street":"test street","Suburb":"test suburb","City":"test city"}}]
into something like the following:
类似于以下内容：
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
That's nice but what about stuff like being able to edit and post back the data? Well there's also a method toStringto create a JSON string from the above [Collection/Dictionary] JSON data, assuming the server accepts JSON back.
这很好，但是诸如能够编辑和回发数据之类的东西呢？那么还有一种方法toString可以从上面的 [Collection/Dictionary] JSON 数据创建一个 JSON 字符串，假设服务器接受 JSON。
回答by Bjoern Stiel
I wrote a .NET Excel-Addin for this. It's a generic Excel JSON client that streams any JSON object straight into Excel via http.
我为此编写了一个 .NET Excel-Addin。它是一个通用的 Excel JSON 客户端，可通过 http 将任何 JSON 对象直接流式传输到 Excel 中。
Docs and installation instructions can be found here: http://excel-requests.pathio.com/en/master/
文档和安装说明可以在这里找到：http: //excel-requests.pathio.com/en/master/
And here's the GitHub link: https://github.com/ZoomerAnalytics/excel-requests
这是 GitHub 链接：https: //github.com/ZoomerAnalytics/excel-requests
