如何在VBA Excel中以良好格式显示JSON对象 [英] how to display my JSON object in good format in VBA Excel
问题描述
我知道当Json是简单的"(当它只是里面的字符串时)在单元格Excel中显示我的Parse Json，但是现在我有了String，对象和数组，我有点迷失了.我的json如下:
[{名称":null，类型":null，动作":[]，屏幕":null，容器":null，"sysid":5"uftitem":null}，{名称":null，类型":null，动作":[]，屏幕":null，容器":null，"sysid":6"uftitem":null}，{"name":"UTProject5"，"type":"type"，动作":[{"name":"UTProject"，"description":"UTProject"，"pattern":"UTProject"，"isCheck":是的，"sysid":1，"uftaction":{"sysid_uftAction":2"code":"code uft"，"maxTime":10，"nbCycle":20}}，{"name":"UTProject2"，"description":"UTProject"，"pattern":"UTProject"，"isCheck":是的，"sysid":3，"uftaction":{"sysid_uftAction":4"code":"code uft"，"maxTime":10，"nbCycle":20}}]，屏幕": {名称":null，类型":null，动作":[]，屏幕":null，容器":null，"sysid":5"uftitem":null}，容器": {名称":null，类型":null，动作":[]，屏幕":null，容器":null，"sysid":6"uftitem":null}，"sysid":7"uftitem":{"code":代码"，"parentCode":"tooooz"，"sysid":8}}，{"name":"UTProject6"，"type":"type"，动作":[{"name":"UTProject"，"description":"UTProject"，"pattern":"UTProject"，"isCheck":是的，"sysid":1，"uftaction":{"sysid_uftAction":2"code":"code uft"，"maxTime":10，"nbCycle":20}}，{"name":"UTProject2"，"description":"UTProject"，"pattern":"UTProject"，"isCheck":是的，"sysid":3，"uftaction":{"sysid_uftAction":4"code":"code uft"，"maxTime":10，"nbCycle":20}}]，屏幕": {名称":null，类型":null，动作":[]，屏幕":null，容器":null，"sysid":5"uftitem":null}，容器": {名称":null，类型":null，动作":[]，屏幕":null，容器":null，"sysid":6"uftitem":null}，"sysid":9"uftitem":{代码":null，"parentCode":null，"sysid":10}}] 我想访问所需的内容并将其显示在单元格中，但我不知道对数组和对象的访问.
谢谢大家！
常规:
您可以使用以下代码清空整个内容，该代码利用
示例代码输出到立即窗口:
您可以通过将 Debug.Print 语句替换为工作表范围来选择写入单元格的方式.
VBA:
选项显式公共子GetInfoFromSheet()Dim jsonStr作为字符串jsonStr = [A1]'< ==从工作表中读取昏暗的json作为对象设置json = JsonConverter.ParseJson(jsonStr)昏暗i长，j长，关键变体对于我= 1到json.Count对于json(i).keys中的每个键选择案例键案例名称"，类型"Debug.Print键&"&json(i)(键)其他情况选择大小写TypeName(json(i] [key))案例字典"Dim key2 As Variant对于json(i)(key)中的每个key2选择大小写TypeName(json(i)(key)(key2))案例收藏"昏暗k长对于k = 1到json(i] [key] [key2).CountDebug.Print键&"&密钥2和"&json [i] [key] [key2] [k)下一个k其他情况Debug.Print键&"&密钥2和"&json(i)(密钥)(密钥2)结束选择下一个键2案例收藏"对于j = 1到json(i)(key).Count'< ==动作"Dim key3 As Variant对于json(i)(key)(j).keys中的每个key3选择大小写TypeName(json(i] [key] [j] [key3))大小写字符串"，布尔"，双精度"Debug.Print键&"&密钥3和"&json(i] [密钥] [j] [密钥3)其他情况Dim key4 As Variant对于json(i)(key)(j)(key3)中的每个key4.keysDebug.Print键&"&密钥3和"&密钥4和"&json(i] [key] [j] [key3] [key4)下一个键4结束选择下一个键3下一个j其他情况Debug.Print键&"&json(i)(键)结束选择结束选择下一个键接下来我结束子 tl; dr;教程点:
因此，上面的内容可能已经很完整了，因为它不需要任何解释就可以获取所有内容.下面，我们将更详细地介绍如何通过关联的VBA定位某些JSON和 对话" .
为此，您可以使用在线JSON解析器来更清晰地查看 JSON 的结构.我将您的 JSON 字符串发布到
这表示 Collection 对象，它是通过 JsonConverter 转换后的 JSON 字符串.其他所有内容都嵌套在此"[" 开头的括号和末尾与其结尾的结尾之间.
接下来要注意的是，这是词典的集合，因此在其中形成组"的所有内容都是字典.
看到表示字典开头的"{" 吗?
该词典的键为名称"，类型"，动作" 等.
初步观察到，此信息很多都是空的. null .我们可以使用 IsNull 测试忽略这些设置(我选择根据"name" 字段执行此操作):
If Not IsNull(json(i)("name")) 我们还可以看到，在"name" 不是 null 的词典中，"actions" 包含另一个词典集合.您会看到我们前面有"[" ，后面是"{" .
我们可以看到每个内部字典都有键，分别是"name"，"description" 等.我们还可以看到它们的值具有不同的数据类型.
在 JSON 结构中观察"actions" ，您可以看到以下内容(使用示例字典):
- 字符串
"name":"UTProject" - 字符串
"description":"UTProject" - 字符串"
pattern":"UTProject" - 布尔值
"isCheck":true - 双
"sysid":1 - 字典
"uftaction"'==>的键；"sysid_uftAction":2，"code":"code uft"，"maxTime":10，"nbCycle":20
因此，我们可以使用 Select Case 通过对 TypeName
对于原始布尔，字符串和双精度数据类型，我们可以简单地通过使用键将它们打印出来，例如
json(i)("actions")(j)("isCheck") 这将是 True 或 False的布尔结果. i 和 j 是当前位置的索引在内部和外部集合的循环中.
对于字典"uftaction" ，我们可以遍历其键:
对于json(i)("actions")(j)(key)中的每个key2.keysDebug.Print操作"&关键和"&密钥2和"&json(i)("actions")(j)(key)(key2)下一个键2 您当然可以使用密钥名称进行访问，而无需在末尾循环遍历密钥，例如:
json(i)("actions")(j)(key)("maxTime") 在整个过程中，您可以通过索引而不是循环访问特定位置，从而可以将 i 和 j 直接替换为数字值.对于任何给定的 key ， key，key2 等都可以替换为实际的文字字符串.
希望这能给您更多的见识.
VBA:
选项显式公共子GetInfoFromJSON()Dim jsonStr作为字符串jsonStr = [A1]'< ==从工作表中读取Dim json作为对象，我只要设置json = JsonConverter.ParseJson(jsonStr)'< ==这是由Debug.Print TypeName(JSON)验证的集合对于我= 1到json.Count如果不是IsNull(json(i)("name"))然后'忽略仅填充系统ID的空名称Debug.Print名称"&"&json(i)(名称")Debug.Print类型"&"&json(i)(类型")Dim j As Long对于j = 1到json(i)("actions").Count'actions是字典的集合变暗键作为变体对于json(i)("actions")(j).keys'dictionary中的每个键'观察JSON结构中的动作，您可以看到:'字符串名称":"UTProject"'字符串"description":"UTProject"，'字符串模式":"UTProject"，'布尔值"isCheck":true，'双"sysid":1，'字典"uftaction"'==>"sysid_uftAction":2，"code":"code uft"，"maxTime":10，"nbCycle":20'因此，我们可以使用Select Case通过对TypeName进行测试来处理数据类型选择大小写TypeName(json(i)("actions")(j)(key))大小写"String"，"Boolean"，"Double"'< ==好，不需要额外做任何事情Debug.Print操作"&关键和"&json(i)(动作")(j)(键)案例其他"，我们正在处理Uftaction，我们知道它是一本字典Dim key2 As Variant对于json(i)("actions")(j)(key).keys中的每个key2'< ==我们现在都知道我们正在循环包含关键字"sysid_uftAction"，"code"，"maxTime"的uftaction字典，"nbCycle"Debug.Print操作"&关键和"&密钥2和"&json(i)("actions")(j)(key)(key2)下一个键2结束选择下一个键下一个j万一接下来我结束子 I know display my Parse Json in cell Excel when the Json is "simple" (when it's just string inside) but now i have String, object and array and i'm a little lost.. my json is following:
[
{
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
{
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
{
"name": "UTProject5",
"type": "type",
"actions": [
{
"name": "UTProject",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 1,
"uftaction": {
"sysid_uftAction": 2,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
},
{
"name": "UTProject2",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 3,
"uftaction": {
"sysid_uftAction": 4,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
}
],
"screen": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
"container": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
"sysid": 7,
"uftitem": {
"code": "code",
"parentCode": "tooooz",
"sysid": 8
}
},
{
"name": "UTProject6",
"type": "type",
"actions": [
{
"name": "UTProject",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 1,
"uftaction": {
"sysid_uftAction": 2,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
},
{
"name": "UTProject2",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 3,
"uftaction": {
"sysid_uftAction": 4,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
}
],
"screen": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
"container": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
"sysid": 9,
"uftitem": {
"code": null,
"parentCode": null,
"sysid": 10
}
}
]
I would like to access to what I want and display it in cell, but I don't know access in array and object.
Thanks every one!
General:
You can empty the whole thing with the following code which makes use of JSON converter:
Note:
I am reading JSON string in from sheet and storing in object via JSONConverter. The initial object is a collection. I loop that collection and every nested level within using TypeName function* to determine what object(s) are stored at each level. I then use Select Case to handle these objects appropriately.
More efficient would be to design a re-usuable class to handle this. I have seen some other questions on SO where this is done.
* VarType is actually more robust
Example JSON
Example code output to immediate window:
You can choose how you write to the cell by replacing the Debug.Print statements with assignments to sheet ranges.
VBA:
Option Explicit
Public Sub GetInfoFromSheet()
Dim jsonStr As String
jsonStr = [A1] '<== read in from sheet
Dim json As Object
Set json = JsonConverter.ParseJson(jsonStr)
Dim i As Long, j As Long, key As Variant
For i = 1 To json.Count
For Each key In json(i).keys
Select Case key
Case "name", "type"
Debug.Print key & " " & json(i)(key)
Case Else
Select Case TypeName(json(i)(key))
Case "Dictionary"
Dim key2 As Variant
For Each key2 In json(i)(key)
Select Case TypeName(json(i)(key)(key2))
Case "Collection"
Dim k As Long
For k = 1 To json(i)(key)(key2).Count
Debug.Print key & " " & key2 & " " & json(i)(key)(key2)(k)
Next k
Case Else
Debug.Print key & " " & key2 & " " & json(i)(key)(key2)
End Select
Next key2
Case "Collection"
For j = 1 To json(i)(key).Count '<== "actions"
Dim key3 As Variant
For Each key3 In json(i)(key)(j).keys
Select Case TypeName(json(i)(key)(j)(key3))
Case "String", "Boolean", "Double"
Debug.Print key & " " & key3 & " " & json(i)(key)(j)(key3)
Case Else
Dim key4 As Variant
For Each key4 In json(i)(key)(j)(key3).keys
Debug.Print key & " " & key3 & " " & key4 & " " & json(i)(key)(j)(key3)(key4)
Next key4
End Select
Next key3
Next j
Case Else
Debug.Print key & " " & json(i)(key)
End Select
End Select
Next key
Next i
End Sub
tl;dr; Tutorial spot:
So the above might have been a bit full on as it gets everything without lots of explanation. Below, we take a more detailed look at how to target some of that JSON and "talk" through the associated VBA.
For this you can use an online JSON parser to view the structure of your JSON more clearly. I posted your JSON string into Json Parser Online, and then examined the structure in the String/parseJS eval; left hand side section. There are other tools available.
The initial thing to note is the beginning "[". The very first one you can see below.
This denotes the Collection object, which is your JSON string when converted with JsonConverter. Everything else is nested between this opening "[" bracket and its closing counterpart at the very end.
The next thing to note is that this is a collection of dictionaries, so everything that forms a "group" within, is a dictionary.
See the "{" denoting the start of the dictionary?
The dictionary has keys of "name","type","actions" etc.
An initial observation is that lots of this info is empty ie. null.
We can ignore these with an IsNull test (I choose to do this based on "name" field):
If Not IsNull(json(i)("name"))
We can also see that "actions", in dictionaries where the "name" is not null, contains another collection of dictionaries. You see we have the "[" followed by the "{" as described before.
We can see that each inner dictionary has keys of "name", "description" etc. We can also see that their values are of different datatypes.
Observing "actions" in the JSON structure, you can see these are (using an example dictionary):
- String
"name":"UTProject" - String
"description":"UTProject" - String "
pattern":"UTProject" - Boolean
"isCheck":true - Double
"sysid":1 - Dictionary
"uftaction" 'keys of ==> "sysid_uftAction":2,"code":"code uft","maxTime":10,"nbCycle":20
So, we can use Select Case to handle the data type by testing with TypeName
For the primitive boolean, string and double data types we can simply print them by using the key e.g.
json(i)("actions")(j)("isCheck")
That will be a boolean result of True or False. i and j being indices of current position in loops of both outer and inner collections.
For the dictionary "uftaction", we can loop over its keys:
For Each key2 In json(i)("actions")(j)(key).keys
Debug.Print "actions " & key & " " & key2 & " " & json(i)("actions")(j)(key)(key2)
Next key2
You could of course access with the name of the key without the loop over the keys at the end, e.g.:
json(i)("actions")(j)(key)("maxTime")
And throughout you could access specific positions via index rather than looping such that i and j would be replaced directly with a numeric value. And key, key2 etc could be replaced by the actual literal string for any given key.
Hopefully that has given you some more insight.
VBA:
Option Explicit
Public Sub GetInfoFromJSON()
Dim jsonStr As String
jsonStr = [A1] '<== read in from sheet
Dim json As Object, i As Long
Set json = JsonConverter.ParseJson(jsonStr) '<==This is a collection verified by Debug.Print TypeName(JSON)
For i = 1 To json.Count
If Not IsNull(json(i)("name")) Then
'ignore the null names which have sys id only populated
Debug.Print "name" & " " & json(i)("name")
Debug.Print "type" & " " & json(i)("type")
Dim j As Long
For j = 1 To json(i)("actions").Count 'actions are a collection of dictionaries
Dim key As Variant
For Each key In json(i)("actions")(j).keys 'dictionary
'observing actions in the JSON structure you can see there are:
' String "name":"UTProject"
'String "description":"UTProject",
'String "pattern":"UTProject",
'Boolean "isCheck":true,
'Double "sysid":1,
'Dictionary "uftaction" '==> "sysid_uftAction":2,"code":"code uft","maxTime":10,"nbCycle":20
'So we can use Select Case to handle the data type by testing with TypeName
Select Case TypeName(json(i)("actions")(j)(key))
Case "String", "Boolean", "Double" '<==good to go nothing extra needed
Debug.Print "actions " & key & " " & json(i)("actions")(j)(key)
Case Else ' we are dealing with uftaction which we know is a dictionary
Dim key2 As Variant
For Each key2 In json(i)("actions")(j)(key).keys '<==We know now we are looping the uftaction dictionary which has keys "sysid_uftAction","code","maxTime","nbCycle"
Debug.Print "actions " & key & " " & key2 & " " & json(i)("actions")(j)(key)(key2)
Next key2
End Select
Next key
Next j
End If
Next i
End Sub
这篇关于如何在VBA Excel中以良好格式显示JSON对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
