是否有适用于 VB6/VBA 的 JSON 解析器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2782076/
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
Is There a JSON Parser for VB6 / VBA?
提问by Ben McCormack
I am trying to consume a web service in VB6. The service - which I control - currently can return a SOAP/XML message or JSON. I am having a really difficult time figuring out if VB6's SOAP type (version 1) can handle a returned object- as opposed to simple types like string, int, etc. So far I cannot figure out what I need to do to get VB6 to play with returned objects.
我正在尝试在 VB6 中使用 Web 服务。我控制的服务当前可以返回 SOAP/XML 消息或 JSON。我有一个非常困难的时候搞清楚，如果VB6的SOAP类型（第1版）可以处理返回object-而不是简单的类型，如string，int等。到目前为止，我想不出什么，我需要做的就是VB6与返回玩对象。
So I thought I might serialize the response in the web service as a JSON string. Does a JSON parser exist for VB6?
所以我想我可以将 Web 服务中的响应序列化为 JSON 字符串。VB6 是否存在 JSON 解析器？
回答by Ben McCormack
Check out JSON.orgfor an up-to-date list (see bottom of main page) of JSON parsers in many different languages. As of the time of this writing, you'll see a link to several different JSON parsers there, but only one is for VB6/VBA (the others are .NET):
查看JSON.org以获取许多不同语言的 JSON 解析器的最新列表（参见主页底部）。在撰写本文时，您将在那里看到几个不同的 JSON 解析器的链接，但只有一个用于 VB6/VBA（其他的是 .NET）：
- When I tried to download the zip file, Windows said the data was corrupt. However, I was able to use 7-zipto pull the files out. It turns out that the main "folder" in the zip file isn't recognized as a folder by Windows, by 7-zip can see the contents of that main "folder," so you can open that up and then extract the files accordingly.
The actual syntax for this VB JSON library is really simple:
Dim p As Object Set p = JSON.parse(strFormattedJSON) 'Print the text of a nested property ' Debug.Print p.Item("AddressClassification").Item("Description") 'Print the text of a property within an array ' Debug.Print p.Item("Candidates")(4).Item("ZipCode")- Note: I had to add the "Microsoft Scripting Runtime" and "Microsoft ActiveX Data Objects 2.8" library as references via Tools > References in the VBA editor.
- Note: VBJSON code is actually based on a google code project vba-json. However, VBJSON promises several bug fixes from the original version.
- 当我尝试下载 zip 文件时，Windows 说数据已损坏。但是，我能够使用7-zip将文件拉出。事实证明，zip 文件中的主“文件夹”未被 Windows 识别为文件夹，通过 7-zip 可以看到该主“文件夹”的内容，因此您可以打开它，然后相应地提取文件.
这个 VB JSON 库的实际语法非常简单：
Dim p As Object Set p = JSON.parse(strFormattedJSON) 'Print the text of a nested property ' Debug.Print p.Item("AddressClassification").Item("Description") 'Print the text of a property within an array ' Debug.Print p.Item("Candidates")(4).Item("ZipCode")- 注意：我必须通过 VBA 编辑器中的“工具”>“引用”添加“Microsoft Scripting Runtime”和“Microsoft ActiveX Data Objects 2.8”库作为引用。
- 注意：VBJSON 代码实际上是基于谷歌代码项目vba-json。但是，VBJSON 承诺对原始版本的几个错误进行修复。
回答by Wolfgang Kuehn
Building on ozmike solution, which did not work for me (Excel 2013 and IE10). The reason is that I could not call the methods on the exposed JSON object. So its methods are now exposed through functions attached to a DOMElement. Didn't know this is possible (must be that IDispatch-thing), thank you ozmike.
基于 ozmike 解决方案，这对我不起作用（Excel 2013 和 IE10）。原因是我无法调用公开的 JSON 对象上的方法。所以它的方法现在通过附加到 DOMElement 的函数公开。不知道这是可能的（必须是 IDispatch 的东西），谢谢 ozmike。
As ozmike stated, no 3rd-party libs, just 30 lines of code.
正如 ozmike 所说，没有 3rd-party libs，只有 30 行代码。
Option Explicit
Public JSON As Object
Private ie As Object
Public Sub initJson()
Dim html As String
html = "<!DOCTYPE html><head><script>" & _
"Object.prototype.getItem=function( key ) { return this[key] }; " & _
"Object.prototype.setItem=function( key, value ) { this[key]=value }; " & _
"Object.prototype.getKeys=function( dummy ) { keys=[]; for (var key in this) if (typeof(this[key]) !== 'function') keys.push(key); return keys; }; " & _
"window.onload = function() { " & _
"document.body.parse = function(json) { return JSON.parse(json); }; " & _
"document.body.stringify = function(obj, space) { return JSON.stringify(obj, null, space); }" & _
"}" & _
"</script></head><html><body id='JSONElem'></body></html>"
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "about:blank"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
.Visible = False
.document.Write html
.document.Close
End With
' This is the body element, we call it JSON:)
Set JSON = ie.document.getElementById("JSONElem")
End Sub
Public Function closeJSON()
ie.Quit
End Function
The following test constructs a JavaScript Object from scratch, then stringifies it. Then it parses the object back and iterates over its keys.
以下测试从头开始构造一个 JavaScript 对象，然后对其进行字符串化。然后它解析对象并迭代它的键。
Sub testJson()
Call initJson
Dim jsObj As Object
Dim jsArray As Object
Debug.Print "Construction JS object ..."
Set jsObj = JSON.Parse("{}")
Call jsObj.setItem("a", 1)
Set jsArray = JSON.Parse("[]")
Call jsArray.setItem(0, 13)
Call jsArray.setItem(1, Math.Sqr(2))
Call jsArray.setItem(2, 15)
Call jsObj.setItem("b", jsArray)
Debug.Print "Object: " & JSON.stringify(jsObj, 4)
Debug.Print "Parsing JS object ..."
Set jsObj = JSON.Parse("{""a"":1,""b"":[13,1.4142135623730951,15]}")
Debug.Print "a: " & jsObj.getItem("a")
Set jsArray = jsObj.getItem("b")
Debug.Print "Length of b: " & jsArray.getItem("length")
Debug.Print "Second element of b: "; jsArray.getItem(1)
Debug.Print "Iterate over all keys ..."
Dim keys As Object
Set keys = jsObj.getKeys("all")
Dim i As Integer
For i = 0 To keys.getItem("length") - 1
Debug.Print keys.getItem(i) & ": " & jsObj.getItem(keys.getItem(i))
Next i
Call closeJSON
End Sub
outputs
产出
Construction JS object ...
Object: {
"a": 1,
"b": [
13,
1.4142135623730951,
15
]
}
Parsing JS object ...
a: 1
Length of b: 3
Second element of b: 1,4142135623731
Iterate over all keys ...
a: 1
b: 13,1.4142135623730951,15
回答by dashmug
Hopefully this will be a big help to others who keep on coming to this page after searching for "vba json".
希望这对在搜索“vba json”后继续访问此页面的其他人有很大帮助。
I found this pageto be very helpful. It provides several Excel-compatible VBA classes that deal with processing data in JSON format.
我发现这个页面非常有帮助。它提供了几个与 Excel 兼容的 VBA 类，用于处理 JSON 格式的数据。
回答by Patrick B?ker
回答by S Meaden
UPDATE: Found a safer way of parsing JSON than using Eval, this blog post shows the dangers of Eval ... http://exceldevelopmentplatform.blogspot.com/2018/01/vba-parse-json-safer-with-jsonparse-and.html
更新：找到了比使用 Eval 更安全的解析 JSON 的方法，这篇博文展示了 Eval 的危险...... http://exceldevelopmentplatform.blogspot.com/2018/01/vba-parse-json-safer-with-jsonparse-和.html
Late to this party but sorry guys but by far the easiest way is to use Microsoft Script Control. Some sample code which uses VBA.CallByName to drill in
迟到了，但对不起，到目前为止，最简单的方法是使用 Microsoft Script Control。一些使用 VBA.CallByName 进行钻取的示例代码
'Tools->References->
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx
Private Sub TestJSONParsingWithCallByName()
Dim oScriptEngine As ScriptControl
Set oScriptEngine = New ScriptControl
oScriptEngine.Language = "JScript"
Dim sJsonString As String
sJsonString = "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }"
Dim objJSON As Object
Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
Debug.Assert VBA.CallByName(objJSON, "key1", VbGet) = "value1"
Debug.Assert VBA.CallByName(VBA.CallByName(objJSON, "key2", VbGet), "key3", VbGet) = "value3"
End Sub
I have actually done a series of Q&As which explore JSON/VBA related topics.
我实际上做了一系列的问答来探索 JSON/VBA 相关主题。
Q1在 Windows 上的 Excel VBA 中，如何缓解被 IDE 的大写行为破坏的解析 JSON 的点语法遍历问题？
Q2 In Excel VBA on Windows, how to loop through a JSON array parsed?
Q2在 Windows 上的 Excel VBA 中，如何遍历解析的 JSON 数组？
Q3在 Windows 上的 Excel VBA 中，如何为解析的 JSON 变量获取字符串化的 JSON 表示而不是“[object Object]”？
Q4在 Windows Excel VBA 中，如何获取 JSON 键以抢占“运行时错误 '438'：对象不支持此属性或方法”？
Q5 In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?
Q5在 Windows 上的 Excel VBA 中，对于解析的 JSON 变量，这个 JScriptTypeInfo 到底是什么？
回答by ozmike
Here is a "Native" VB JSON library.
这是一个“本机”VB JSON 库。
It is possible to use JSON that is already in IE8+. This way your not dependent on a third party library that gets out of date and is untested.
可以使用 IE8+ 中已有的 JSON。这样您就不会依赖于过时且未经测试的第三方库。
see amedeus' alternative version here
在此处查看 Amedeus 的替代版本
Sub myJSONtest()
Dim oJson As Object
Set oJson = oIE_JSON() ' See below gets IE.JSON object
' using json objects
Debug.Print oJson.parse("{ ""hello"": ""world"" }").hello ' world
Debug.Print oJson.stringify(oJson.parse("{ ""hello"": ""world"" }")) ' {"hello":"world"}
' getting items
Debug.Print oJson.parse("{ ""key1"": ""value1"" }").key1 ' value1
Debug.Print oJson.parse("{ ""key1"": ""value1"" }").itemGet("key1") ' value1
Debug.Print oJson.parse("[ 1234, 4567]").itemGet(1) ' 4567
' change properties
Dim o As Object
Set o = oJson.parse("{ ""key1"": ""value1"" }")
o.propSetStr "key1", "value\""2"
Debug.Print o.itemGet("key1") ' value\"2
Debug.Print oJson.stringify(o) ' {"key1":"value\\"2"}
o.propSetNum "key1", 123
Debug.Print o.itemGet("key1") ' 123
Debug.Print oJson.stringify(o) ' {"key1":123}
' add properties
o.propSetNum "newkey", 123 ' addkey! JS MAGIC
Debug.Print o.itemGet("newkey") ' 123
Debug.Print oJson.stringify(o) ' {"key1":123,"newkey":123}
' assign JSON 'objects' to properties
Dim o2 As Object
Set o2 = oJson.parse("{ ""object2"": ""object2value"" }")
o.propSetJSON "newkey", oJson.stringify(o2) ' set object
Debug.Print oJson.stringify(o) ' {"key1":123,"newkey":{"object2":"object2value"}}
Debug.Print o.itemGet("newkey").itemGet("object2") ' object2value
' change array items
Set o = oJson.parse("[ 1234, 4567]") '
Debug.Print oJson.stringify(o) ' [1234,4567]
Debug.Print o.itemGet(1)
o.itemSetStr 1, "234"
Debug.Print o.itemGet(1)
Debug.Print oJson.stringify(o) ' [1234,"234"]
o.itemSetNum 1, 234
Debug.Print o.itemGet(1)
Debug.Print oJson.stringify(o) ' [1234,234]
' add array items
o.itemSetNum 5, 234 ' add items! JS Magic
Debug.Print o.itemGet(5) ' 234
Debug.Print oJson.stringify(o) ' [1234,234,null,null,null,234]
' assign JSON object to array item
o.itemSetJSON 3, oJson.stringify(o2) ' assign object
Debug.Print o.itemGet(3) '[object Object]
Debug.Print oJson.stringify(o.itemGet(3)) ' {"object2":"object2value"}
Debug.Print oJson.stringify(o) ' [1234,234,null,{"object2":"object2value"},null,234]
oIE_JSON_Quit ' quit IE, must shut down or the IE sessions remain.
Debug.Print oJson.stringify(o) ' can use after but but IE server will shutdown... soon
End Sub
You can bridge to IE.JSON from VB.
Create a function oIE_JSON
您可以从 VB 桥接到 IE.JSON。
创建函数 oIE_JSON
Public g_IE As Object ' global
Public Function oIE_JSON() As Object
' for array access o.itemGet(0) o.itemGet("key1")
JSON_COM_extentions = "" & _
" Object.prototype.itemGet =function( i ) { return this[i] } ; " & _
" Object.prototype.propSetStr =function( prop , val ) { eval('this.' + prop + ' = ""' + protectDoubleQuotes (val) + '""' ) } ; " & _
" Object.prototype.propSetNum =function( prop , val ) { eval('this.' + prop + ' = ' + val + '') } ; " & _
" Object.prototype.propSetJSON =function( prop , val ) { eval('this.' + prop + ' = ' + val + '') } ; " & _
" Object.prototype.itemSetStr =function( prop , val ) { eval('this[' + prop + '] = ""' + protectDoubleQuotes (val) + '""' ) } ; " & _
" Object.prototype.itemSetNum =function( prop , val ) { eval('this[' + prop + '] = ' + val ) } ; " & _
" Object.prototype.itemSetJSON =function( prop , val ) { eval('this[' + prop + '] = ' + val ) } ; " & _
" function protectDoubleQuotes (str) { return str.replace(/\/g, '\\').replace(/""/g,'\""'); }"
' document.parentwindow.eval dosen't work some versions of ie eg ie10?
IEEvalworkaroundjs = "" & _
" function IEEvalWorkAroundInit () { " & _
" var x=document.getElementById(""myIEEvalWorkAround"");" & _
" x.IEEval= function( s ) { return eval(s) } ; } ;"
g_JS_framework = "" & _
JSON_COM_extentions & _
IEEvalworkaroundjs
' need IE8 and DOC type
g_JS_HTML = "<!DOCTYPE html> " & _
" <script>" & g_JS_framework & _
"</script>" & _
" <body>" & _
"<script id=""myIEEvalWorkAround"" onclick=""IEEvalWorkAroundInit()"" ></script> " & _
" HEllo</body>"
On Error GoTo error_handler
' Create InternetExplorer Object
Set g_IE = CreateObject("InternetExplorer.Application")
With g_IE
.navigate "about:blank"
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
.Visible = False ' control IE interface window
.Document.Write g_JS_HTML
End With
Set objID = g_IE.Document.getElementById("myIEEvalWorkAround")
objID.Click ' create eval
Dim oJson As Object
'Set oJson = g_IE.Document.parentWindow.Eval("JSON") ' dosen't work some versions of IE
Set oJson = objID.IEEval("JSON")
Set objID = Nothing
Set oIE_JSON = oJson
Exit Function
error_handler:
MsgBox ("Unexpected Error, I'm quitting. " & Err.Description & ". " & Err.Number)
g_IE.Quit
Set g_IE = Nothing
End Function
Public Function oIE_JSON_Quit()
g_IE.Quit
Exit Function
End Function
Up vote if you find useful
如果您觉得有用，请投票
回答by Bob77
VB6 - JsonBag, Another JSON Parser/Generatorshould also be importable into VBA with little trouble.
VB6 - JsonBag，另一个 JSON 解析器/生成器也应该可以轻松导入到 VBA 中。
回答by SIM
As Json is nothing but strings so it can easily be handled if we can manipulate it the right way, no matter how complex the structure is. I don't think it is necessary to use any external library or converter to do the trick. Here is an example where I've parsed json data using string manipulation.
因为 Json 只不过是字符串，所以如果我们能以正确的方式操作它，无论结构多么复杂，它都可以很容易地处理。我认为没有必要使用任何外部库或转换器来实现这一目标。这是我使用字符串操作解析 json 数据的示例。
Sub GetJsonContent()
Dim http As New XMLHTTP60, itm As Variant
With http
.Open "GET", "http://jsonplaceholder.typicode.com/users", False
.send
itm = Split(.responseText, "id"":")
End With
x = UBound(itm)
For y = 1 To x
Cells(y, 1) = Split(Split(itm(y), "name"": """)(1), """")(0)
Cells(y, 2) = Split(Split(itm(y), "username"": """)(1), """")(0)
Cells(y, 3) = Split(Split(itm(y), "email"": """)(1), """")(0)
Cells(y, 4) = Split(Split(itm(y), "street"": """)(1), """")(0)
Next y
End Sub
回答by MarkJ
I would suggest using a .Net component. You can use .Net components from VB6 via Interop- here's a tutorial. My guess is that .Net components will be more reliable and better supported than anything produced for VB6.
我建议使用 .Net 组件。您可以通过Interop使用来自 VB6 的 .Net 组件- 这是一个教程。我的猜测是 .Net 组件将比为 VB6 生产的任何组件更可靠和更好的支持。
There are components in the Microsoft .Net framework like DataContractJsonSerializeror JavaScriptSerializer. You could also use third party libraries like JSON.NET.
Microsoft .Net 框架中有一些组件，如DataContractJsonSerializer或JavaScriptSerializer。您还可以使用第三方库，如JSON.NET。
回答by Bjoern Stiel
You could write an Excel-DNA Add-in in VB.NET. Excel-DNA is a thin library that lets you write XLLs in .NET. This way you get access to the entire .NET universe and can use stuff like http://james.newtonking.com/json- a JSON framework that deserializes JSON in any custom class.
您可以在 VB.NET 中编写 Excel-DNA 插件。Excel-DNA 是一个瘦库，可让您在 .NET 中编写 XLL。通过这种方式，您可以访问整个 .NET 世界，并且可以使用诸如http://james.newtonking.com/json 之类的东西- 一个 JSON 框架，可以在任何自定义类中反序列化 JSON。
If you are interested, here's a write up of how to build a generic Excel JSON client for Excel using VB.NET:
如果您有兴趣，这里有一篇关于如何使用 VB.NET 为 Excel 构建通用 Excel JSON 客户端的文章：
http://optionexplicitvba.com/2014/05/09/developing-a-json-excel-add-in-with-vb-net/
http://optionexplicitvba.com/2014/05/09/developing-a-json-excel-add-in-with-vb-net/
And here's the link to the code: https://github.com/spreadgit/excel-json-client/blob/master/excel-json-client.dna
这是代码的链接：https: //github.com/spreadgit/excel-json-client/blob/master/excel-json-client.dna
