- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在创建的 Excel 程序中有问题。简而言之,我必须从网站中提取 JSON 数据,对其进行解析,然后将响应扔到工作表中以供以后使用。每当代码到达要输出响应文本的位置时,输出就会从响应文本中传递我需要的第一组数据。以下所有数据和示例。
创建和发送 HTTP 请求的代码:
For i = 1 To 100
URL = "REDACTED"
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
httpRequest.Open "GET", URL, False
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpRequest.send ""
Set Output = parse(httpRequest.responseText)
Pallet_Inv.Cells(1 + i, d) = Output.Item("result").Item("contains").Item(i).Item("resourceLabel")
Next
Public Function parse(ByRef str As String) As Object
Dim Index As Long
Index = 1
psErrors = ""
On Error Resume Next
Call skipChar(str, Index)
Select Case Mid(str, Index, 1)
Case "{"
Set parse = parseObject(str, Index)
Case "["
Set parse = parseArray(str, Index)
Case Else
psErrors = "Invalid JSON"
End Select
End Function
' skip special character
'
Private Sub skipChar(ByRef str As String, ByRef Index As Long)
Dim bComment As Boolean
Dim bStartComment As Boolean
Dim bLongComment As Boolean
Do While Index > 0 And Index <= Len(str)
Select Case Mid(str, Index, 1)
Case vbCr, vbLf
If Not bLongComment Then
bStartComment = False
bComment = False
End If
Case vbTab, " ", "(", ")"
Case "/"
If Not bLongComment Then
If bStartComment Then
bStartComment = False
bComment = True
Else
bStartComment = True
bComment = False
bLongComment = False
End If
Else
If bStartComment Then
bLongComment = False
bStartComment = False
bComment = False
End If
End If
Case "*"
If bStartComment Then
bStartComment = False
bComment = True
bLongComment = True
Else
bStartComment = True
End If
Case Else
If Not bComment Then
Exit Do
End If
End Select
Index = Index + 1
Loop
End Sub
'
' parse collection of key/value
'
Private Function parseObject(ByRef str As String, ByRef Index As Long) As Dictionary
Set parseObject = New Dictionary
Dim sKey As String
' "{"
Call skipChar(str, Index)
If Mid(str, Index, 1) <> "{" Then
psErrors = psErrors & "Invalid Object at position " & Index & " : " & Mid(str, Index) & vbCrLf
Exit Function
End If
Index = Index + 1
Do
Call skipChar(str, Index)
If "}" = Mid(str, Index, 1) Then
Index = Index + 1
Exit Do
ElseIf "," = Mid(str, Index, 1) Then
Index = Index + 1
Call skipChar(str, Index)
ElseIf Index > Len(str) Then
psErrors = psErrors & "Missing '}': " & Right(str, 20) & vbCrLf
Exit Do
End If
' add key/value pair
sKey = parseKey(str, Index)
On Error Resume Next
parseObject.Add sKey, parseValue(str, Index)
If Err.Number <> 0 Then
psErrors = psErrors & Err.Description & ": " & sKey & vbCrLf
Exit Do
End If
Loop
eh:
End Function
Private Function parseKey(ByRef str As String, ByRef Index As Long) As String
Dim dquote As Boolean
Dim squote As Boolean
Dim Char As String
Call skipChar(str, Index)
Do While Index > 0 And Index <= Len(str)
Char = Mid(str, Index, 1)
Select Case (Char)
Case """"
dquote = Not dquote
Index = Index + 1
If Not dquote Then
Call skipChar(str, Index)
If Mid(str, Index, 1) <> ":" Then
psErrors = psErrors & "Invalid Key at position " & Index & " : " & parseKey & vbCrLf
Exit Do
End If
End If
Case "'"
squote = Not squote
Index = Index + 1
If Not squote Then
Call skipChar(str, Index)
If Mid(str, Index, 1) <> ":" Then
psErrors = psErrors & "Invalid Key at position " & Index & " : " & parseKey & vbCrLf
Exit Do
End If
End If
Case ":"
Index = Index + 1
If Not dquote And Not squote Then
Exit Do
Else
parseKey = parseKey & Char
End If
Case Else
If InStr(vbCrLf & vbCr & vbLf & vbTab & " ", Char) Then
Else
parseKey = parseKey & Char
End If
Index = Index + 1
End Select
Loop
End Function
'
' parse string / number / object / array / true / false / null
'
Private Function parseValue(ByRef str As String, ByRef Index As Long)
Call skipChar(str, Index)
Select Case Mid(str, Index, 1)
Case "{"
Set parseValue = parseObject(str, Index)
Case "["
Set parseValue = parseArray(str, Index)
Case """", "'"
parseValue = parseString(str, Index)
Case "t", "f"
parseValue = parseBoolean(str, Index)
Case "n"
parseValue = parseNull(str, Index)
Case Else
parseValue = parseNumber(str, Index)
End Select
End Function
'
' parse list
'
Private Function parseArray(ByRef str As String, ByRef Index As Long) As Collection
Set parseArray = New Collection
' "["
Call skipChar(str, Index)
If Mid(str, Index, 1) <> "[" Then
psErrors = psErrors & "Invalid Array at position " & Index & " : " + Mid(str, Index, 20) & vbCrLf
Exit Function
End If
Index = Index + 1
Do
Call skipChar(str, Index)
If "]" = Mid(str, Index, 1) Then
Index = Index + 1
Exit Do
ElseIf "," = Mid(str, Index, 1) Then
Index = Index + 1
Call skipChar(str, Index)
ElseIf Index > Len(str) Then
psErrors = psErrors & "Missing ']': " & Right(str, 20) & vbCrLf
Exit Do
End If
' add value
On Error Resume Next
parseArray.Add parseValue(str, Index)
If Err.Number <> 0 Then
psErrors = psErrors & Err.Description & ": " & Mid(str, Index, 20) & vbCrLf
Exit Do
End If
Loop
End Function
'
' parse number
'
Private Function parseNumber(ByRef str As String, ByRef Index As Long)
Dim Value As String
Dim Char As String
Call skipChar(str, Index)
Do While Index > 0 And Index <= Len(str)
Char = Mid(str, Index, 1)
If InStr("+-0123456789.eE", Char) Then
Value = Value & Char
Index = Index + 1
Else
parseNumber = CDec(Value)
Exit Function
End If
Loop
End Function
'
' parse string
'
Private Function parseString(ByRef str As String, ByRef Index As Long) As String
Dim quote As String
Dim Char As String
Dim Code As String
Dim SB As New cStringBuilder
Call skipChar(str, Index)
quote = Mid(str, Index, 1)
Index = Index + 1
Do While Index > 0 And Index <= Len(str)
Char = Mid(str, Index, 1)
Select Case (Char)
Case "\"
Index = Index + 1
Char = Mid(str, Index, 1)
Select Case (Char)
Case """", "\", "/", "'"
SB.Append Char
Index = Index + 1
Case "b"
SB.Append vbBack
Index = Index + 1
Case "f"
SB.Append vbFormFeed
Index = Index + 1
Case "n"
SB.Append vbLf
Index = Index + 1
Case "r"
SB.Append vbCr
Index = Index + 1
Case "t"
SB.Append vbTab
Index = Index + 1
Case "u"
Index = Index + 1
Code = Mid(str, Index, 4)
SB.Append ChrW(Val("&h" + Code))
Index = Index + 4
End Select
Case quote
Index = Index + 1
parseString = SB.toString
Set SB = Nothing
Exit Function
Case Else
SB.Append Char
Index = Index + 1
End Select
Loop
parseString = SB.toString
Set SB = Nothing
End Function
{"result":{"contains":[{"cptInMillis":1547531880000,"containerType":"Case
","cpt":"REDACTED
PM","stackingFilter":"REDACTED","associationReason":"-","isEmpty":"-","resourceLabel":"csXP25jMSzG","associatedUser":"REDACTED","cleanupAllowed":false,"isClosed":"-","containerId":"REDACTED","isForcedMove":"No","dwellTime":"REDACTED
: 1"},{"cptInMillis":1547531880000,"containerType":"Case
","cpt":"REDACTED
PM","stackingFilter":"REDACTED","associationReason":"-","isEmpty":"-","resourceLabel":"csXP25jMTHk","associatedUser":"REDACTED","cleanupAllowed":false,"isClosed":"-","containerId":"REDACTED","isForcedMove":"No","dwellTime":"REDACTED
: 2"},{"cptInMillis":1547531880000,"containerType":"Case
","cpt":"REDACTED
PM","stackingFilter":"REDACTED","associationReason":"-","isEmpty":"-","resourceLabel":"csXP25jMTN5","associatedUser":"REDACTED","cleanupAllowed":false,"isClosed":"-","containerId":"REDACTED","isForcedMove":"No","dwellTime":"REDACTED
: 2"},{"cptInMillis":1547445480000,"containerType":"Case
","cpt":"REDACTED
PM","stackingFilter":"REDACTED","associationReason":"-","isEmpty":"-","resourceLabel":"csXP25k9Z5F","associatedUser":"REDACTED","cleanupAllowed":false,"isClosed":"-","containerId":"REDACTED","isForcedMove":"No","dwellTime":"REDACTED
: 2"}],"endToken":null,"startToken":"0"},"ok":true,"message":""}
csXP25jMSzG csXP25jMTHk csXP25jMTN5 csXP25k9Z5F
csXP25jMTHk
csXP25jMTN5
csXP25k9Z5F
最佳答案
除非您的主要目标是编写 JSON 解析器,否则我建议您使用现有的 JSON 转换器。我一直在使用 GitHub 中的那个.使用该转换器,获得resourceLabel
相对容易。 .这是一种方法:
Option Explicit
Sub pj()
Dim strJSON As String
Dim JSON As Dictionary
Dim dRES As Dictionary
Dim oContains As Collection
Dim V
strJSON = Cells(1, 1).Value2
Set JSON = parsejson(strJSON)
Set dRES = JSON("result")
Set oContains = dRES("contains")
For Each V In oContains
Debug.Print V("resourceLabel")
Next V
End Sub
A1
中的 JSON 字符串, 输出:
csXP25jMSzG
csXP25jMTHk
csXP25jMTN5
csXP25k9Z5F
关于json - 正确解析 JSON 响应文本,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54164891/
最近开始学习MongoDB。今天老师教了我们 mongoexport 命令。在练习时,我遇到了一个典型的问题,包括教练在内的其他同学都没有遇到过。我在我的 Windows 10 机器上使用 Mongo
我是 JSON Schema 的新手,读过什么是 JSON Schema 等等。但我不知道如何将 JSON Schema 链接到 JSON 以针对该 JSON Schema 进行验证。谁能解释一下?
在 xml 中,我可以在另一个 xml 文件中包含一个文件并使用它。如果您的软件从 xml 获取配置文件但没有任何方法来分离配置,如 apache/ngnix(nginx.conf - site-av
我有一个 JSON 对象,其中包含一个本身是 JSON 对象的字符串。我如何反序列化它? 我希望能够做类似的事情: #[derive(Deserialize)] struct B { c: S
考虑以下 JSON { "a": "{\"b\": 12, \"c\": \"test\"}" } 我想定义一个泛型读取 Reads[Outer[T]]对于这种序列化的 Json import
关闭。这个问题不满足Stack Overflow guidelines .它目前不接受答案。 想改善这个问题吗?更新问题,使其成为 on-topic对于堆栈溢出。 11 个月前关闭。 Improve
我的旧项目在 MySQL 中有 Standard JSON 格式的数据。 对于我在 JS (Node.js) 和 DynamoDB 中的全新项目,关于 Standard JSON格式: 是否建议将其转
JSON 值字符串、数字、true、false、null 是否是有效的 JSON? 即,是 true 一个有效的 JSON 文档?还是必须是数组/对象? 一些验证器接受这个(例如 http://jso
我有一个 JSON 字符串,其中一个字段是文本字段。这个文本字段可以包含用户在 UI 中输入的文本,如果他们输入的文本是 JSON 文本,也许是为了说明一些编码,我需要对他们的文本进行编码,以便它不会
我正在通过 IBM MQ 调用处理数据,当由 ColdFusion 10 (10,0,11,285437) 序列化时,0 将作为 +0.0 返回,它会导致无效的 JSON并且无法反序列化。 stPol
我正在从三个数组中生成一个散列,然后尝试构建一个 json。我通过 json object has array 成功了。 require 'json' A = [['A1', 'A2', 'A3'],
我从 API 接收 JSON,响应可以是 30 种类型之一。每种类型都有一组唯一的字段,但所有响应都有一个字段 type 说明它是哪种类型。 我的方法是使用serde .我为每种响应类型创建一个结构并
我正在下载一个 JSON 文件,我已将其检查为带有“https://jsonlint.com”的有效 JSON 到文档目录。然后我打开文件并再次检查,结果显示为无效的 JSON。这怎么可能????这是
我正在尝试根据从 API 接收到的数据动态创建一个 JSON 对象。 收到的示例数据:将数据解码到下面给出的 CiItems 结构中 { "class_name": "test", "
我想从字符串转换为对象。 来自 {"key1": "{\n \"key2\": \"value2\",\n \"key3\": {\n \"key4\": \"value4\"\n }\n
目前我正在使用以下代码将嵌套的 json 转换为扁平化的 json: import ( "fmt" "github.com/nytlabs/gojsonexplode" ) func
我有一个使用来自第三方 API 的数据的应用程序。我需要将 json 解码为一个结构,这需要该结构具有“传入”json 字段的 json 标签。传出的 json 字段具有不同的命名约定,因此我需要不同
我想使用 JSON 架构来验证某些值。我有两个对象,称它们为 trackedItems 和 trackedItemGroups。 trackedItemGroups 是组名称和 trackedItem
考虑以下案例类模式, case class Y (a: String, b: String) case class X (dummy: String, b: Y) 字段b是可选的,我的一些数据集没有字
我正在存储 cat ~/path/to/file/blah | 的输出jq tojson 在一个变量中,稍后在带有 JSON 内容的 curl POST 中使用。它运作良好,但它删除了所有换行符。我知
我是一名优秀的程序员,十分优秀!