|
除非注明，文章均为 战战如疯 原创，转载请保留链接: http://www./cat3/472.html，VBA交流群273624828。
之前网抓到的数据都直接用split处理了，今天好好研究了下处理Json形式的数据，拿个案例来分享一下。
要抓的数据是http://www.okooo.com/soccer/match/680903/odds/，这个网页取数据很简单，GET就可以，主要是数据取下来的怎么处理。
Private Sub GetData1()
Dim tt As String, winhttp, t1, arr, i, v
Set winhttp = CreateObject("Microsoft.XMLHTTP")
With winhttp
.Open "GET", "http://www.okooo.com/soccer/match/680903/odds/ajax/?page=0&all=1&companytype=BaijiaBooks&type=1 ", False
.send
t1 = .responsetext
Debug.Print t1
i = 1
arr = Split(Split(Split(t1, "data_str = '")(1), "';var pageData")(0), "';")
strJSON = arr(0)
Set objSC = CreateObject("ScriptControl")
objSC.Language = "JScript"
strFunc = "function getjson(s) { return eval('(' + s + ')'); }"
objSC.AddCode strFunc
Set objJSON = objSC.CodeObject.getjson(strJSON)
For Each v In objJSON
n = n + 1
Cells(n, 1) = v.CompanyName
Cells(n, 2) = v.End.home: Cells(n, 3) = v.End.draw: Cells(n, 4) = v.End.away
Cells(n, 5) = v.Radio.home: Cells(n, 6) = v.Radio.draw: Cells(n, 7) = v.Radio.away
Cells(n, 8) = v.Kelly.home: Cells(n, 9) = v.Kelly.draw: Cells(n, 10) = v.Kelly.away
Next
End With
End Sub
上面是取所有的数据。
Private Sub GetData()
Dim tt As String, winhttp, t1, arr, i, v
'Set winhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
Set winhttp = CreateObject("Microsoft.XMLHTTP")
With winhttp
'.SetProxy 2, "14.18.16.67:80"
.Open "GET", "http://www.okooo.com/soccer/match/680903/odds/ajax/?page=0&companytype=BaijiaBooks&type=1", False
.send
t1 = .responsetext
With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText t1
.PutInClipboard
End With
i = 1
arr = Split(Split(Split(t1, "data_str = '")(1), "';var pageData")(0), "';")
strJSON = arr(0)
Set objSC = CreateObject("ScriptControl")
objSC.Language = "JScript"
strFunc = "function getjson(s) { return eval('(' + s + ')'); }"
objSC.AddCode strFunc
Set objJSON = objSC.CodeObject.getjson(strJSON)
Set oRow = CallByName(objJSON, 0, VbGet)
With oRow
Cells(1, 1) = .CompanyName
Cells(1, 2) = .End.home
Cells(1, 3) = .End.draw
Cells(1, 4) = .End.away
Cells(1, 5) = .Radio.home
End With
End With
End Sub
这是只取某一行的数据。
Private Sub GetData2()
Dim tt As String, winhttp, t1, arr, i, v
'Set winhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
Set winhttp = CreateObject("Microsoft.XMLHTTP")
With winhttp
'.SetProxy 2, "14.18.16.67:80"
.Open "GET", "http://www.okooo.com/soccer/match/680903/odds/ajax/?page=0&companytype=BaijiaBooks&type=1", False
.send
t1 = .responsetext
i = 1
arr = Split(Split(Split(t1, "data_str = '")(1), "';var pageData")(0), "';")
strJSON = arr(0)
Debug.Print strJSON
Set objSC = CreateObject("ScriptControl"): objSC.Language = "JScript"
'strFunc = objSC.eval(" eval('" & strJSON & " ')[0].CompanyName")
Set strFunc = objSC.eval(" eval('" & strJSON & " ')[0]")
'Set strFunc = objSC.eval(" eval('" & strJSON & " ')")
MsgBox strFunc.CompanyName
End With
' Stop
End Sub
这是处理Json的另一种方式，只抓某行，抓所有还是用for each循环，不再赘述。
|
