在VBA中返回空JSON抛出类型不匹配错误 [英] Returning Null JSON Throwing Type Mismatch Error in VBA
问题描述
我当前遇到一个问题，我返回的JSON的字段为空.
I'm currently running into a problem where the JSON that I am getting back has fields that are null.
在下面的代码中，我发现大多数字段中都有一个受让人，而下一层的受让人中则有一个displayName.我还发现有些东西没有受让人.发生这种情况时(，并且其他字段也可能会发生这种情况，我仅以此为例)，它会删除该额外的层次结构级别和实际路径(也显示在下面)将被更改.
In the code below, I've figured out that most of the fields have an assignee, and down another level assignees have a displayName. I've also found out that some things do not have an assignee. When that happens (and this would probably happen with other fields too, I'm just using this as an example) it removes that additional heirarchy level, and the actual path (also shown below) would be changed.
是否有一种简单的方法来遍历此响应，并且可能将null设置为空白?
Is there an easy way to iterate over this response, and set nulls to blanks maybe?
Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)
但这对自动化并没有真正的帮助.请注意[下面]，我在其中列出了两次组件，因为我不知道如何遍历该数据并根据需要填充该字段的次数将其拉回多次.我又知道有两个组件，但是它只带回一个组件，所以我不得不复制该代码以使其正常工作(很抱歉复制).
That doesn't really help me with automation though. Notice [below] where I list components twice, because I don't know how to loop through that data and pull back the field as many times as it needs to be populated. Aka I know there are two components, but it only brings back one component, so I had to copy that code to get it to work correctly (I'm sorry for copying).
我的代码运行良好，直到遇到空值，然后引发错误.
My code works great until it hits a null, then it throws an error.
''''''''
' Loop '
''''''''
For i = 0 To 40
' ActiveSheet.Cells(i + 1, 1) = Json("issues")(i + 1)("fields")("issuetype")("name")
' ActiveSheet.Cells(i + 1, 2) = Json("issues")(i)("key")
' ActiveSheet.Cells(i + 1, 3) = Json("issues")(i + 1)("fields")("summary")
' ActiveSheet.Cells(i + 1, 4) = Json("issues")(i + 1)("fields")("status")("name")
ActiveSheet.Cells(i + 1, 5) = Json("issues")(i + 1)("fields")("assignee")
ActiveSheet.Cells(i + 1, 5) = Json("issues")(i + 1)("fields")("assignee")("displayName")
' ActiveSheet.Cells(i + 1, 6) = Json("issues")(i + 1)("fields")("customfield_13301")
' ActiveSheet.Cells(i + 1, 7) = Json("issues")(i + 1)("fields")("components")(1)("name")
' ActiveSheet.Cells(i + 1, 8) = Json("issues")(i + 1)("fields")("components")(2)("name")
' ActiveSheet.Cells(i + 1, 9) = Json("issues")(i + 1)("fields")("customfield_13300")
' ActiveSheet.Cells(i + 1, 10) = Json("issues")(i + 1)("fields")("customfield_10002")
Next i
JSON
出于隐私原因，我不得不删除某些内容，但这将受让人显示为空.带有"displayName"的JSON只会将null转换为Array并在其下包含更多字段.
JSON
Ovbiously I had to delete some content for privacy reasons, but that shows the assignee as null. JSON with a, "displayName" just turns that null into an Array and has more fields under it.
{
"expand": "schema,names",
"startAt": 0,
"maxResults": 50,
"total": 52,
"issues": [
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{
"expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",
"id": "92110",
"self": "",
"key": "",
"fields": {
"customfield_13100": null,
"fixVersions": [],
"customfield_13500": null,
"customfield_11200": null,
"resolution": null,
"customfield_13502": null,
"customfield_13501": null,
"lastViewed": null,
"customfield_12000": null,
"customfield_12002": null,
"customfield_12001": null,
"priority": {},
"customfield_10100": null,
"customfield_10101": null,
"customfield_12003": null,
"customfield_12402": null,
"labels": [],
"customfield_11303": null,
"customfield_11305": null,
"customfield_11306": null,
"aggregatetimeoriginalestimate": null,
"timeestimate": null,
"versions": [],
"issuelinks": [],
"assignee": null,
"status": {},
"components": [],
"customfield_13200": null,
"customfield_13600": null,
"customfield_12900": null,
"aggregatetimeestimate": null,
"creator": {},
"customfield_14000": null,
"subtasks": [],
"customfield_14400": null,
"reporter": {},
"customfield_12101": null,
"customfield_12100": null,
"aggregateprogress": {},
"customfield_14401": null,
"customfield_14402": null,
"customfield_12500": null,
"customfield_13702": null,
"customfield_13704": null,
"customfield_13703": null,
"customfield_11802": null,
"progress": {},
"votes": {},
"issuetype": {},
"timespent": null,
"project": {},
"customfield_13300": null,
"aggregatetimespent": null,
"customfield_13302": null,
"customfield_13301": null,
"customfield_13700": null,
"customfield_11400": null,
"resolutiondate": null,
"workratio": -1,
"watches": {},
"created": "2017-07-21T08:04:42.000-0500",
"customfield_14102": null,
"customfield_10020": null,
"customfield_12200": null,
"customfield_14100": null,
"customfield_14101": null,
"customfield_12600": null,
"customfield_14500": null,
"customfield_10300": null,
"customfield_10016": null,
"customfield_13405": null,
"customfield_10017": null,
"customfield_13800": null,
"customfield_10018": null,
"customfield_10019": null,
"customfield_13409": null,
"updated": "2017-08-10T15:29:37.000-0500",
"timeoriginalestimate": null,
"description": null,
"customfield_10011": null,
"customfield_10012": null,
"customfield_13401": null,
"customfield_13400": null,
"customfield_10013": null,
"customfield_10014": null,
"customfield_11500": "{}",
"customfield_10015": null,
"customfield_13514": null,
"summary": "",
"customfield_14200": null,
"customfield_10000": null,
"customfield_13511": null,
"customfield_12301": null,
"customfield_10001": null,
"customfield_12300": null,
"customfield_10002": "1|i021pe:5z",
"customfield_13510": null,
"customfield_13513": null,
"customfield_10003": [],
"customfield_12302": null,
"customfield_10004": null,
"customfield_13504": null,
"customfield_13503": null,
"customfield_11600": null,
"customfield_13506": null,
"environment": null,
"customfield_13901": null,
"customfield_13505": null,
"customfield_13508": null,
"duedate": null,
"customfield_13509": null
}
},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{}
]
}
其他数据
我查看了Raw文件，只是看是否看起来有所不同(与我的Chrome JSON格式化程序插件中的不同)，并且看起来像这样:
Additional Data
I looked at the Raw file just to see if anything looked different (than it did in my JSON Formater Plugin for Chrome) and this is what it looked like:
"assignee":null,
推荐答案
如果您了解JsonConverter如何将JSON处理为复合对象，那么使用JSON文件就容易得多(IMHO).让我们看一个简单的JSON格式(取自这个有用的网站):
Working with JSON files is much easier (IMHO) if you understand how the JsonConverter processes the JSON into a compound object. Let's look at a simple JSON format (taken from this useful site):
{
"array": [
1,
2,
3
],
"boolean": true,
"null": null,
"number": 123,
"object": {
"a": "b",
"c": "d",
"e": "f"
},
"string": "Hello World"
}
JsonConverter将每个数据项映射到其VBA对应项中.
The JsonConverter maps each of these data items into their VBA counterparts.
"array" maps to Collection (anytime you see the square brackets [])
"boolean" maps to Boolean
"null" maps to Null
"number" maps to Double
"object" maps to Dictionary (anytime you see the curly braces {})
"string" maps to String
因此，现在我们可以对您的JSON示例进行一些有用的操作，例如确定"issues"数组中有多少个完整对象，
So now we can do useful things with your JSON example, such as determine how many entires are in your "issues" array by
Dim issues As Collection
Set issues = schema("issues")
Debug.Print issues.Count
"issues"数组中的每个条目实际上都是一个复合对象本身，因此它是一个Dictionary.因此，我们可以做这样的事情:
Each of the entries in your "issues" array is actually a compound object itself, so it's a Dictionary. We could, therefore, do something like this:
Dim issue As Variant
For Each issue In issues
If issue.Exists("id") Then
Debug.Print "id = " & issue("id")
End If
Next issue
当然，此单个issue的"fields"部分本身是另一个Dictionary.因此，堆叠字典引用也可以做到这一点:
Of course, the "fields" section of this single issue is itself another Dictionary. So stacking up the dictionary references we can do this too:
Debug.Print "field summary is " & issue("fields")("summary")
所有这些都是背景，希望可以更轻松地访问JSON结构的成员.您真正的问题是关于处理NULLs.如果字段的实际值设置为null(请参见上面的示例)，则应像这样检查它
All of this is background, hopefully to make it easier on accessing members of a JSON structure. Your real question is on handling NULLs. If the actual value of a field is set to null (see the above sample), then you check it like so
If IsNull(issue("fields")("customfield_13500")) Then ...
在我们将它们放在一起之前，还有一些其他注意事项:
A couple of other side notes before we put it all together:
- 始终使用
Option Explicit - 避免使用
Select和Activate - 始终定义和设置对所有工作簿和工作表的引用
- Always use
Option Explicit - Avoid
SelectandActivate - Always define and set references to all Workbooks and Sheets
在下面的示例中，您将看到我假设必须检查Null的每个字段.最好通过隔离子程序中的检查而不是使用一长串的If语句来使您的代码过度混乱来实现.下面的代码示例的优点在于，您不必对问题的数量进行硬编码，因为您的逻辑可以检测到有多少个问题.
In the example below, you'll see that I assumed you had to check each field for Null. That is best accomplished by isolating that check in a subroutine rather than over-mess your code with a long string of If statements. The advantage of the code example below is that you don't have to hard-code the number of issues because your logic can detect how many there are.
Option Explicit
Sub main()
Dim schema As Object
Set schema = GetJSON("C:\dev\junk.json")
Dim thisWB As Workbook
Dim destSH As Worksheet
Set thisWB = ThisWorkbook
Set destSH = thisWB.Sheets("Sheet1")
Dim anchor As Range
Set anchor = destSH.Range("A1")
Dim issues As Collection
Set issues = schema("issues")
Dim i As Long
Dim issue As Variant
For Each issue In issues
If issue.Exists("id") Then
SetCell anchor.Cells(1, 1), issue("fields")("issuetype")("name")
SetCell anchor.Cells(1, 2), issue("key")
SetCell anchor.Cells(1, 3), issue("fields")("summary")
'--- if you're not sure if the "name" field is there,
' then remember it's a Dictionary so check with Exists
If issue("fields")("status").Exists("name") Then
SetCell anchor.Cells(1, 4), issue("fields")("status")("name")
Else
SetCell anchor.Cells(1, 4), vbNullString
End If
SetCell anchor.Cells(1, 5), issue("fields")("assignee")
SetCell anchor.Cells(1, 6), issue("fields")("customfield_13301")
'--- possibly get the Count and iterate over the exact number of components
For i = 0 To issue("fields")("components").Count - 1
SetCell anchor.Cells(1, 7), issue("fields")("components")(i)("name")
Next i
SetCell anchor.Cells(1, 9), issue("fields")("customfield_13300")
SetCell anchor.Cells(1, 10), issue("fields")("customfield_10002")
Set anchor = anchor.Offset(1, 0)
End If
Next issue
End Sub
Function GetJSON(ByVal filename As String) As Object
'--- first ingest the JSON file and get it parsed
Dim fso As FileSystemObject
Dim jsonTS As TextStream
Dim jsonText As String
Set fso = New FileSystemObject
Set jsonTS = fso.OpenTextFile(filename, ForReading)
jsonText = jsonTS.ReadAll
Set GetJSON = JsonConverter.ParseJson(jsonText)
End Function
Private Sub SetCell(ByRef thisCell As Range, ByVal thisValue As Variant)
If IsNull(thisValue) Then
thisCell = vbNullString
Else
thisCell = thisValue
End If
End Sub
这篇关于在VBA中返回空JSON抛出类型不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
