在 Excel VBA 中解析 JSON [英] Parsing JSON in Excel VBA
问题描述
我遇到了与 Excel VBA:解析的 JSON 对象循环 但找不到任何解决方案.我的 JSON 有嵌套对象,因此建议的解决方案如 VBJSON 和 vba-json 对我不起作用.我还修复了其中一个以正常工作,但结果是由于 doProcess 函数的多次递归导致调用堆栈溢出.
I have the same issue as in Excel VBA: Parsed JSON Object Loop but cannot find any solution. My JSON has nested objects so suggested solution like VBJSON and vba-json do not work for me. I also fixed one of them to work properly but the result was a call stack overflow because of to many recursion of the doProcess function.
最好的解决方案似乎是在原始帖子中看到的 jsonDecode 函数.它非常快速且高效;我的对象结构都在一个 JScriptTypeInfo 类型的通用 VBA 对象中.
The best solution appears to be the jsonDecode function seen in the original post. It is very fast and highly effective; my object structure is all there in a generic VBA Object of type JScriptTypeInfo.
此时的问题是我无法确定对象的结构是什么,因此,我事先不知道将驻留在每个通用对象中的键.我需要遍历通用 VBA 对象以获取键/属性.
The issue at this point is that I cannot determine what will be the structure of the objects, therefore, I do not know beforehand the keys that will reside in each generic objects. I need to loop through the generic VBA Object to acquire the keys/properties.
如果我的解析 javascript 函数可以触发 VBA 函数或子函数,那就太好了.
If my parsing javascript function could trigger a VBA function or sub, that would be excellent.
推荐答案
如果你想建立在 ScriptControl
之上,你可以添加一些辅助方法来获取所需的信息.JScriptTypeInfo
对象有点不幸:它包含所有相关信息(正如您在 Watch 窗口中看到的那样),但使用 VBA 似乎无法获得它.但是,Javascript 引擎可以帮助我们:
If you want to build on top of ScriptControl
, you can add a few helper method to get at the required information. The JScriptTypeInfo
object is a bit unfortunate: it contains all the relevant information (as you can see in the Watch window) but it seems impossible to get at it with VBA. However, the Javascript engine can help us:
Option Explicit
Private ScriptEngine As ScriptControl
Public Sub InitScriptEngine()
Set ScriptEngine = New ScriptControl
ScriptEngine.Language = "JScript"
ScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "
ScriptEngine.AddCode "function getKeys(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } "
End Sub
Public Function DecodeJsonString(ByVal JsonString As String)
Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")")
End Function
Public Function GetProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Variant
GetProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function
Public Function GetObjectProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Object
Set GetObjectProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function
Public Function GetKeys(ByVal JsonObject As Object) As String()
Dim Length As Integer
Dim KeysArray() As String
Dim KeysObject As Object
Dim Index As Integer
Dim Key As Variant
Set KeysObject = ScriptEngine.Run("getKeys", JsonObject)
Length = GetProperty(KeysObject, "length")
ReDim KeysArray(Length - 1)
Index = 0
For Each Key In KeysObject
KeysArray(Index) = Key
Index = Index + 1
Next
GetKeys = KeysArray
End Function
Public Sub TestJsonAccess()
Dim JsonString As String
Dim JsonObject As Object
Dim Keys() As String
Dim Value As Variant
Dim j As Variant
InitScriptEngine
JsonString = "{""key1"": ""val1"", ""key2"": { ""key3"": ""val3"" } }"
Set JsonObject = DecodeJsonString(CStr(JsonString))
Keys = GetKeys(JsonObject)
Value = GetProperty(JsonObject, "key1")
Set Value = GetObjectProperty(JsonObject, "key2")
End Sub
一些注意事项:
- 如果
JScriptTypeInfo
实例引用一个 Javascript 对象,For Each ... Next
将不起作用.但是,如果它引用 Javascript 数组,它确实可以工作(请参阅GetKeys
函数). - 名称仅在运行时已知的访问属性,使用函数
GetProperty
和GetObjectProperty
. - Javascript 数组提供属性
length
、0
、Item 0
、1
、Item1
等.使用 VBA 点符号(jsonObject.property
),只有长度属性是可访问的,并且只有当你声明一个名为length
的变量时,所有的小写字母.否则案例不匹配,它不会找到它.其他属性在 VBA 中无效.所以最好使用GetProperty
函数. - 代码使用早期绑定.因此,您必须添加对Microsoft Script Control 1.0"的引用.
- 在使用其他函数进行一些基本初始化之前,您必须调用一次
InitScriptEngine
.
- If the
JScriptTypeInfo
instance refers to a Javascript object,For Each ... Next
won't work. However, it does work if it refers to a Javascript array (seeGetKeys
function). - The access properties whose name is only known at run-time, use the functions
GetProperty
andGetObjectProperty
. - The Javascript array provides the properties
length
,0
,Item 0
,1
,Item 1
etc. With the VBA dot notation (jsonObject.property
), only the length property is accessible and only if you declare a variable calledlength
with all lowercase letters. Otherwise the case doesn't match and it won't find it. The other properties are not valid in VBA. So better use theGetProperty
function. - The code uses early binding. So you have to add a reference to "Microsoft Script Control 1.0".
- You have to call
InitScriptEngine
once before using the other functions to do some basic initialization.
这篇关于在 Excel VBA 中解析 JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!