在 VBA 中使用 ScriptControl 解析 JSON：将结果转换为字典和集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10691644/
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
Use ScriptControl to parse JSON in VBA: transform result to dictionaries and collections
提问by Abiel
I would like to use Microsoft ScriptControl to parse a JSON string in VBA, and then transform the resulting Object into Dictionary and Collection objects. I already know how to do the parsing with ScriptControl, but cannot figure out how to map the result into the Dictionary and Collection classes. I'm guessing that if I could figure out how to loop through the properties of an Object this would become clear...
我想使用 Microsoft ScriptControl 来解析 VBA 中的 JSON 字符串，然后将生成的 Object 转换为 Dictionary 和 Collection 对象。我已经知道如何使用 ScriptControl 进行解析，但无法弄清楚如何将结果映射到 Dictionary 和 Collection 类中。我猜如果我能弄清楚如何遍历对象的属性，这将变得清晰......
Dim sc As ScriptControl
Dim obj As Variant
Set sc = CreateObject("ScriptControl")
sc.Language = "JScript"
Set obj = sc.Eval("("+json+")") ' json is a string containing raw JSON
' Now what?
By the way, I've used the vba-jsonlibrary to get the output in terms of Dictionaries and Collections, but I find this library somewhat slow. It does not use ScriptControl.
顺便说一下，我已经使用vba-json库来获取字典和集合方面的输出，但我发现这个库有点慢。它不使用 ScriptControl。
EDIT: I found a discussion of getting object properties in thispost.
编辑：我在这篇文章中找到了有关获取对象属性的讨论。
回答by cyboashu
Using JavaScript features of parsing JSON, on top of ScriptControl, we can create a parser in VBA which will list each and every data point inside the JSON. No matter how nested or complex the data structure is, as long as we provide a valid JSON, this parser will return a complete tree structure.
使用解析 JSON 的 JavaScript 功能，在 ScriptControl 之上，我们可以在 VBA 中创建一个解析器，它将列出 JSON 中的每个数据点。无论数据结构如何嵌套或复杂，只要我们提供一个有效的 JSON，这个解析器就会返回一个完整的树结构。
JavaScript's Eval, getKeys and getProperty methods provide building blocks for validating and reading JSON.
JavaScript 的 Eval、getKeys 和 getProperty 方法提供用于验证和读取 JSON 的构建块。
Coupled with a recursive function in VBA we can iterate through all the keys (up to nth level) in a JSON string. Then using a Tree control (used in this article) or a dictionary or even on a simple worksheet, we can arrange the JSON data as required.
结合 VBA 中的递归函数，我们可以遍历 JSON 字符串中的所有键（最多 n 级）。然后使用 Tree 控件（本文中使用的）或字典，甚至在一个简单的工作表上，我们可以根据需要排列 JSON 数据。
VBA Code:http://ashuvba.blogspot.in/2014/09/json-parser-in-vba-browsing-through-net.html
VBA 代码：http: //ashuvba.blogspot.in/2014/09/json-parser-in-vba-browsing-through-net.html
