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调用 xmlhtt prequest
但用它同步以不打乱VBA的单线程的执行环境,这说明了一个后
和 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 (谷歌$ C $ ç出口这里)COM类型收藏
处理JSON数组和词典
来处理成员和他们的声明,提供一个解析器工厂方法解析
,基本上使创建这些字典集合简单多了。
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"}}]
成类似以下内容:
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
这是好的,但有关的东西,如能够编辑和上传回数据是什么?那么还有一个方法的toString
以创建从上面的[收藏/词典] 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屋!