Возвращение нулевого JSON-типа с ошибкой несоответствия типов в VBA

проблема

Описание

В настоящее время я сталкиваюсь с проблемой, когда JSON, который я получаю обратно, имеет пустые поля.

В приведенном ниже коде я выяснил, что у большинства полей есть правопреемник, а у праведников другого уровня есть displayName. Я также узнал, что некоторые вещи не имеют правопреемника. Когда это происходит (и это, вероятно, будет происходить и с другими полями, я просто использую это в качестве примера), он удаляет этот дополнительный уровень иерархии, и фактический путь (также показанный ниже) будет изменен.

Вопрос

Есть ли простой способ перебрать этот ответ и установить пустые значения в пробелы?

Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)

Это не очень помогает мне с автоматизацией. Обратите внимание на [ниже], где я перечисляю компоненты дважды, потому что я не знаю, как перебрать эти данные и вытащить поле назад столько раз, сколько необходимо для заполнения. Ака, я знаю, что есть два компонента, но он возвращает только один компонент, поэтому мне пришлось скопировать этот код, чтобы заставить его работать правильно (извините за копирование).

Код Снип

Мой код работает отлично, пока он не достигнет нуля, а затем выдает ошибку.

''''''''
' 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

Очевидно, мне пришлось удалить некоторый контент по соображениям конфиденциальности, но это показывает, что цессионарий является нулевым. JSON с "displayName" просто превращает этот нуль в массив и имеет больше полей под ним.

{
"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, чтобы посмотреть, не выглядит ли что-то по-другому (чем это было в моем плагине JSON Formater для Chrome), и вот как это выглядело:

"assignee":null,
Источник

3 ответа

Работать с файлами JSON гораздо проще (IMHO), если вы понимаете, как JsonConverter обрабатывает JSON в составной объект. Давайте посмотрим на простой формат JSON (взят с этого полезного сайта):

{
  "array": [
    1,
    2,
    3
  ],
  "boolean": true,
  "null": null,
  "number": 123,
  "object": {
    "a": "b",
    "c": "d",
    "e": "f"
  },
  "string": "Hello World"
}

JsonConverter отображает каждый из этих элементов данных в их аналоги VBA.

"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" массив по

Dim issues As Collection
Set issues = schema("issues")
Debug.Print issues.Count

Каждая из записей в вашем "issues" массив на самом деле сам составной объект, так что это Dictionary, Поэтому мы могли бы сделать что-то вроде этого:

Dim issue As Variant
For Each issue In issues
    If issue.Exists("id") Then
        Debug.Print "id = " & issue("id")
    End If
Next issue

Конечно, "fields" раздел этого сингла issue само по себе другое Dictionary, Таким образом, составляя ссылки на словарь, мы можем сделать это тоже:

Debug.Print "field summary is " & issue("fields")("summary")

Все это фоновое, надеюсь, упростит доступ к членам структуры JSON. Ваш реальный вопрос по обработке NULLs, Если фактическое значение поля установлено в null (см. пример выше), затем вы проверяете это так

If IsNull(issue("fields")("customfield_13500")) Then ...

Несколько других примечаний стороны прежде, чем мы соединим все это:

  1. Всегда используйте Option Explicit
  2. избежать Select а также Activate
  3. Всегда определяйте и устанавливайте ссылки на все рабочие книги и листы

В приведенном ниже примере вы увидите, что я предполагал, что вам нужно проверить каждое поле на Null, Это лучше всего сделать, изолировав эту проверку в подпрограмме, а не перегружая ваш код длинной строкой If заявления. Преимущество приведенного ниже примера кода состоит в том, что вам не нужно жестко кодировать количество проблем, потому что ваша логика может определить их количество.

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
Источник

Вы можете получить данные JSON в массивы, как показано в примере кода ниже. Импортируйте модуль JSON.bas в проект VBA для обработки JSON.

Sub Test()

    ' Put sourse JSON string to "\source.json" file, and save as ANSI or Unicode

    Dim sJSONString As String
    Dim vJSON As Variant
    Dim sState As String
    Dim aData()
    Dim aHeader()

    sJSONString = ReadTextFile(ThisWorkbook.Path & "\source.json", -2)
    JSON.Parse sJSONString, vJSON, sState
    vJSON = vJSON("issues")
    JSON.ToArray vJSON, aData, aHeader
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Function ReadTextFile(sPath As String, lFormat As Long) As String
    ' lFormat -2 - System default, -1 - Unicode, 0 - ASCII
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 1, False, lFormat)
        ReadTextFile = ""
        If Not .AtEndOfStream Then ReadTextFile = .ReadAll
        .Close
    End With
End Function
Источник

Исправлять

Вот что я сделал, чтобы заставить это работать:

If IsNull(Json("issues")(i + 1)("fields")("components")) Then
    ActiveSheet.Cells(i + 1, 5).Value = ""
Else
    ActiveSheet.Cells(i + 1, 7) = Json("issues")(i + 1)("fields")("components")(1)("name")
End If
Источник
Другие вопросы по тегам