ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 65705|回复: 67

[分享] 【网页采集教程】【高级篇】第二课-使用VBA解析JSON格式的网页

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2012-11-4 22:07 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:网页交互
本帖最后由 liucqa 于 2012-11-6 19:20 编辑

第一课:http://club.excelhome.net/thread-893760-1-1.html
第二课:http://club.excelhome.net/thread-894527-1-1.html
第三课:http://club.excelhome.net/thread-896608-1-1.html
第四课:http://club.excelhome.net/thread-897117-1-1.html
第五课:http://club.excelhome.net/thread-899268-1-1.html

高级篇:
第一课:http://club.excelhome.net/thread-902266-1-1.html
第二课:http://club.excelhome.net/thread-939881-1-1.html


前言:

在网页采集的过程中,我们通常会碰到三种格式的网页文件:HTML、XML、JSON
HTML通常通过字符串+数组进行解析,部分网页也可以采用正则。高级的可以用HtmlDocument对象解析。
XML可以通过XMLHTTP的.ResponseXML得到文件,并通过XMLDOM或DomDocument对象进行解析。
JOSN却比较另类,这个格式非常方便JS脚本解析,但如果用VBA的字符串处理语句来解析它,那就是一场噩梦。

本文分别介绍JS脚本和VBA类模块工具的两个方法,说明如何使用VBA代码来解析JOSN格式的文本。

顺便声明,由于是高级教程,本文不提供详细的代码解释,也不提供实用示例。还是老规矩:只指路,不登山!


*****************************俺是上课的分割线*************************************

    JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。它基于JavaScript(Standard ECMA-262 3rd Edition - December 1999)的一个子集。 JSON采用完全独立于语言的文本格式,但是也使用了类似于C语言家族的习惯(包括C, C++, C#, Java, JavaScript, Perl, Python等)。这些特性使JSON成为理想的数据交换语言。易于人阅读和编写,同时也易于机器解析和生成。
JSON建构有两种结构:
  1. “名称/值”对的集合(A collection of name/value pairs)。不同的语言中,它被理解为对象(object),记录(record),结构(struct),字典(dictionary),哈希表(hash table),有键列表(keyed list),或者关联数组 (associative array)。
  2.值的有序列表(An ordered list of values)。在大部分语言中,它被理解为数组(array)。

    简单地说,JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在函数之间轻松地传递这个字符串,或者在异步应用程序中将字符串从 Web 客户机传递给服务器端程序。这个字符串看起来有点儿古怪,但是 JavaScript 很容易解释它,而且 JSON 可以表示比"名称 / 值对"更复杂的结构。例如,可以表示数组和复杂的对象,而不仅仅是键和值的简单列表。


1、表示名称 / 值对
  按照最简单的形式,可以用下面这样的 JSON 表示"名称 / 值对":
  { "firstName": "Brett" }
  这个示例非常基本,而且实际上比等效的纯文本"名称 / 值对"占用更多的空间:
  firstName=Brett
  但是,当将多个"名称 / 值对"串在一起时,JSON 就会体现出它的价值了。首先,可以创建包含多个"名称 / 值对"的 记录,比如:
  { "firstName": "Brett", "lastName":"McLaughlin", "email": "aaaa" }
  从语法方面来看,这与"名称 / 值对"相比并没有很大的优势,但是在这种情况下 JSON 更容易使用,而且可读性更好。例如,它明确地表示以上三个值都是同一记录的一部分;花括号使这些值有了某种联系。

2、表示数组
  当需要表示一组值时,JSON 不但能够提高可读性,而且可以减少复杂性。例如,假设您希望表示一个人名列表。在 XML 中,需要许多开始标记和结束标记;如果使用典型的名称 / 值对(就像在本系列前面文章中看到的那种名称 / 值对),那么必须建立一种专有的数据格式,或者将键名称修改为 person1-firstName这样的形式。
  如果使用 JSON,就只需将多个带花括号的记录使用方括号分组在一起:
  { "people": [
  { "firstName": "Brett", "lastName":"McLaughlin", "email": "aaaa" },
  { "firstName": "Jason", "lastName":"Hunter", "email": "bbbb"},
  { "firstName": "Elliotte", "lastName":"Harold", "email": "cccc" }
  ]}
  这不难理解。在这个示例中,只有一个名为 people的变量,值是包含三个条目的数组,每个条目是一个人的记录,其中包含名、姓和电子邮件地址。上面的示例演示如何用括号将记录组合成一个值。
       当然,可以使用相同的语法表示多个值(每个值包含多个记录):
  { "programmers": [
  { "firstName": "Brett", "lastName":"McLaughlin", "email": "aaaa" },
  { "firstName": "Jason", "lastName":"Hunter", "email": "bbbb" },
  { "firstName": "Elliotte", "lastName":"Harold", "email": "cccc" }
  ],
  "authors": [
  { "firstName": "Isaac", "lastName": "Asimov", "genre": "science fiction" },
  { "firstName": "Tad", "lastName": "Williams", "genre": "fantasy" },
  { "firstName": "Frank", "lastName": "Peretti", "genre": "christian fiction" }
  ],
  "musicians": [
  { "firstName": "Eric", "lastName": "Clapton", "instrument": "guitar" },
  { "firstName": "Sergei", "lastName": "Rachmaninoff", "instrument": "piano" }
  ] }
  这里最值得注意的是,能够表示多个值,每个值进而包含多个值。但是还应该注意,在不同的主条目(programmers、authors 和 musicians)之间,记录中实际的名称 / 值对可以不一样。JSON 是完全动态的,允许在 JSON 结构的中间改变表示数据的方式。
  在处理 JSON 格式的数据时,没有需要遵守的预定义的约束。所以,在同样的数据结构中,可以改变表示数据的方式,甚至可以以不同方式表示同一事物。




以上信息来自百度百科


评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-4 22:08 | 显示全部楼层
本帖最后由 liucqa 于 2012-11-4 22:55 编辑

顺便说一下JSON和XML的比较

  ◆可读性
  JSON和XML的可读性可谓不相上下,一边是简易的语法,一边是规范的标签形式,很难分出胜负。

  ◆可扩展性
  XML天生有很好的扩展性,JSON当然也有,没有什么是XML能扩展,而JSON却不能扩展的。不过JSON在Javascript主场作战,可以存储Javascript复合对象,有着xml不可比拟的优势。

  ◆编码难度
  XML有丰富的编码工具,比如Dom4j、JDom等,JSON也有提供的工具。无工具的情况下,相信熟练的开发人员一样能很快的写出想要的xml文档和JSON字符串,不过,xml文档要多很多结构上的字符。

  ◆解码难度
  XML的解析方式有两种:
  一是通过文档模型解析,也就是通过父标签索引出一组标记。例如:xmlData.getElementsByTagName("tagName"),但是这样是要在预先知道文档结构的情况下使用,无法进行通用的封装。
  另外一种方法是遍历节点(document 以及 childNodes)。这个可以通过递归来实现,不过解析出来的数据仍旧是形式各异,往往也不能满足预先的要求。
  凡是这样可扩展的结构数据解析起来一定都很困难。
  JSON也同样如此。如果预先知道JSON结构的情况下,使用JSON进行数据传递简直是太美妙了,可以写出很实用美观可读性强的代码。如果你是纯粹的前台开发人员,一定会非常喜欢JSON。但是如果你是一个应用开发人员,就不是那么喜欢了,毕竟xml才是真正的结构化标记语言,用于进行数据传递。
  而如果不知道JSON的结构而去解析JSON的话,那简直是噩梦。费时费力不说,代码也会变得冗余拖沓,得到的结果也不尽人意。但是这样也不影响众多前台开发人员选择JSON。因为json.js中的toJSONString()就可以看到JSON的字符串结构。当然不是使用这个字符串,这样仍旧是噩梦。常用JSON的人看到这个字符串之后,就对JSON的结构很明了了,就更容易的操作JSON。
  以上是在Javascript中仅对于数据传递的xml与JSON的解析。在Javascript地盘内,JSON毕竟是主场作战,其优势当然要远远优越于xml。如果JSON中存储Javascript复合对象,而且不知道其结构的话,我相信很多程序员也一样是哭着解析JSON的。

以上信息来自百度百科

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-4 22:09 | 显示全部楼层
本帖最后由 liucqa 于 2012-11-4 22:17 编辑

对我们初学者来说,得到JSON数据并不难。然而,要从复杂的数据中,找到需要的数据节点,有时候真的是一场噩梦。

幸好,下面的网站提供的JSON格式化校验器,可以帮助大家方便的解析数据了。
www.bejson.com


下面给大家提供一个JSON文件的参考示例
金宝博网站的JSON数据示例.rar (20.97 KB, 下载次数: 1598)

诸位可以将此数据粘贴到上面的网站上,看看格式

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-4 22:10 | 显示全部楼层
本帖最后由 liucqa 于 2012-11-4 22:41 编辑

JSON是 JavaScript 原生格式,这意味着在 JavaScript 中处理 JSON数据不须要任何特殊的 API 或工具包。

所以,在vba中处理JSON,最简单的方法就是使用ScriptControl对象的javascript脚本语句

  1.     Option Explicit
  2.     Public Const strJOSN As String = "{ ""myname"":""liucqa"", ""myid"":""007"" }"
  3.     Sub test1()
  4.         Dim objSC, strJSON, objJS
  5.         Set objSC = CreateObject("MSScriptControl.ScriptControl")    '调用ScriptControl对象
  6.         strJSON = "var o=" & strJOSN & ";"
  7.         objSC.Language = "javascript"
  8.         objSC.AddCode (strJSON)
  9.         Set objJS = objSC.CodeObject.o
  10.         MsgBox CallByName(objJS, "myname", VbGet) & "=" & CallByName(objJS, "myid", VbGet)
  11.     End Sub

  12.     Sub test2()
  13.         Dim strFunc, objSC, objJSON
  14.         Set objSC = CreateObject("ScriptControl")
  15.         objSC.Language = "JScript"
  16.         strFunc = "function getjson(s) { return eval('(' + s + ')'); }"
  17.         objSC.AddCode strFunc
  18.         Set objJSON = objSC.CodeObject.getjson(strJOSN)
  19.         MsgBox objJSON.myname & "=" & objJSON.myid
  20.     End Sub
复制代码


上面是两个例子,第一个例子使用CallByName来得到对象的属性(或者说是以文本字符为下标的数组数据)。第二个例子直接通过javascript的属性输出。


网络文章参考
http://www.cnblogs.com/worfdream/articles/1956449.html



补充内容 (2013-4-18 15:52):
http://blog.csdn.net/klarclm/article/details/7629880   JS控件的方法和属性

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-4 22:11 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 liucqa 于 2012-11-6 19:24 编辑

通过在VBA中使用上面的JS语句,我们可以得到简单的JSON文本数据。
然而,在实际应用中,上述语句会出现许多问题,诸如关键字冲突、JSON数组不能直接使用等问题。

这就需要我们将JS函数和CallByName函数两者结合到一起来使用。

请看下面的例子,来自figfig大神的帖子 http://club.excelhome.net/thread-483942-1-1.html
在这段代码中,就存在关键字冲突。修改如下:
  1. Sub Sample()
  2. Dim aa, x, y As Object, s
  3.     aa = "{ ""people"": [{ ""firstName"": ""Brett"", ""lastName"":""McLaughlin"", ""Email"": ""brett@newInstance.com"" },{ ""firstName"": ""Jason"", ""lastName"":""Hunter"", ""email"": ""jason@servlets.com"" }, { ""firstName"": ""Elliotte"", ""lastName"":""Harold"", ""email"": ""elharo@macfaq.com"" }]}"
  4.     Set x = CreateObject("ScriptControl")
  5.     x.Language = "JScript"

  6.     s = "function j(s) { return eval('(' + s + ').people[1]'); }"
  7.     x.AddCode s
  8.     Set y = x.Run("j", aa)
  9.     MsgBox y.firstName
  10.     'MsgBox y.Email        '由于关键字冲突,在2007下无法执行
  11.     MsgBox CallByName(y, "email", VbGet)
  12. End Sub
复制代码
经过修改之后的代码,可以正常运行了。


另一个例子:
在3楼给出的金宝博JSON格式的数据中,如果你使用JS脚本来读取它,会发现在读取出来的对象中嵌套着许多对象和数组。
VBA中,我们不能把JS出来的带有下标的对象直接当作数组来读取数据(前引用也不行)。


要读取JSON数组里面的数据,就需要我们采用将JS函数和CallByName函数结合到一起的方法。

例子如下(部分代码,仅供参考)

  1.     Dim strFunc1 As String, strFunc2 As String, strFunc3 As String
  2.     Dim objSC As Object      
  3.     Dim objJS1 As Object, objJS2, objJS3

  4.     Set objSC = CreateObject("ScriptControl")
  5.     objSC.Language = "JScript"

  6.     strFunc1 = "function getjson1(s) { return eval('(' + s + ')'); }"
  7.     strFunc2 = "function getjson2(s) { return eval('(' + s + ').d[0].c[0].e[0].o.ah'); }"
  8.     strFunc3 = "function getjson3(s) { return eval('(' + s + ').d[0].c[0].e[0].o'); }"         
  9.     's = "function j(s) { return eval('(' + s + ').people[1]'); }"
  10.     objSC.AddCode strFunc1
  11.     objSC.AddCode strFunc2
  12.     objSC.AddCode strFunc3
  13.     Set objJS1 = objSC.CodeObject.getjson1(strText)
  14.     Set objJS2 = objSC.CodeObject.getjson2(strText)
  15.     Set objJS3 = objSC.CodeObject.getjson3(strText)

  16.     Dim s1, s2
  17.     s1 = CallByName(objJS2, "1", VbGet)      
  18.     s2 = CallByName(objJS3, "1x2", VbGet)   
复制代码
由于在JS脚本中,也存在语法冲突问题,因此,要读取“o.1x2”的数据,我只能采用CallByName方法。(o.ah可以直接读取,无冲突)
如果哪位大神,对JS语法比较熟悉的话,烦请帮助指出直接从JS里面得到"1x2"属性数组的方法。

附图如下,可以供大家参考这个网站的数据结构。
1.JPG

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-4 22:28 | 显示全部楼层
本帖最后由 liucqa 于 2014-8-1 21:28 编辑

前面讲了使用JS脚本语言来解析JSON格式文本的例子,那么到底有没有办法直接采用VBA自己的代码来解析JSON呢?

感谢国外的高人们,他们为我们提供了这样一个源代码,通过字典来解析JSON。
代码需要引用Scripting Runtime库和ADO库

链接地址如下:
http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html
http://code.google.com/p/vba-json/

在水文工具集中,也提供了一个修改过的类模块(bug未修正),这个版本无须做前引用。
http://www.cnhup.com/index.php/archives/vba-json-class/


还是以金宝博网站为例,部分代码如下
  1. Dim objJSON As New clsJOSN, dicJSON As Object

  2.     Set dicJSON = objJSON.parse(strRespText)
  3.     MsgBox dicJSON("d")(1)("c")(1)("n") & vbCrLf & _
  4.            dicJSON("d")(1)("c")(1)("e")(1)("i")(1) & "-" & dicJSON("d")(1)("c")(1)("e")(1)("i")(2) & vbCrLf & _
  5.            "1x2 " & _
  6.            dicJSON("d")(1)("c")(1)("e")(1)("o")("1x2")(2) & " " & _
  7.            dicJSON("d")(1)("c")(1)("e")(1)("o")("1x2")(4) & " " & _
  8.            dicJSON("d")(1)("c")(1)("e")(1)("o")("1x2")(6) & vbCrLf & _
  9.            "让球 " & _
  10.            dicJSON("d")(1)("c")(1)("e")(1)("o")("ah")(6) & " " & _
  11.            dicJSON("d")(1)("c")(1)("e")(1)("o")("ah")(8) & vbCrLf & _
  12.            "大小盘 " & _
  13.            dicJSON("d")(1)("c")(1)("e")(1)("o")("ou")(6) & " " & _
  14.            dicJSON("d")(1)("c")(1)("e")(1)("o")("ou")(8)
复制代码
采用国外高手提供的类模块解析JSON,其格式与JS的类似,好处是可以得到数组的下界,方便循环语句的使用。
输出见下图

捕获.JPG



使用VBA解析JSON格式文本的教程就到这里,本人对JSON了解很肤浅,如有错误,欢迎大家不吝指出!谢谢诸位!

备注:
具体JSON格式的描述可以在JSON网站[www.json.org]了解


使用JS获取系统时间戳
Function GetTimestamp()
    GetTimestamp = DateDiff("s", "01/01/1970 00:00:00", Now())
    GetTimestamp = GetTimestamp - 480 * 60                              '东八区
End Function


Sub GetTimestamp()
    Dim objSC, ts$, ts1$
    Set objSC = CreateObject("msscriptcontrol.scriptcontrol")
    objSC.Language = "jscript"
    objSC.addcode "function gt(){return new Date().getTime();}"
    ts = objSC.eval("gt()")

    objSC.addcode "function gt1(a){return new Date().getTime();}"         '传递一个参数
    ts1 = objSC.codeobject.gt1(1)
    MsgBox ts1 & vbCrLf & ts1
End Sub

    Function UnixTime()
        Set objWMIService = _
        GetObject("winmgmts:\\.\root\cimv2")
        Set colItems = objWMIService.ExecQuery _
        ("Select * from Win32_OperatingSystem",,48)
        For Each objItem in colItems
            TimeZone = objItem.CurrentTimeZone
        Next
        UnixTime = DateDiff("s", "01/01/1970 00:00:00", Now())
        UnixTime = UnixTime - TimeZone * 60
    End Function

'提取并执行网页中的js函数
'http://vip.bet007.com/history/odds.aspx?date=2013-4-16
Set objSC = CreateObject("ScriptControl")
objSC.Language = "JScript"

jsFuncode = Split(Split(strRespText, "script>")(1), "window")(0)
objSC.AddCode jsFuncode
decodeUrl = objSC.eval("decoder()")
objSC.Reset

点评

好教材,填补了对常规对象之外的脚本型网页的抓取。  发表于 2012-11-4 23:56

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-11-4 23:38 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 引子玄 于 2012-11-4 23:42 编辑

这个教材好,实用型的课程。{:soso_e179:}在原来的XmlHttp+InternetExplorer+QueryTables三种对象法抓取的基础上,又多学了一门ScriptControl 对象法抓取

TA的精华主题

TA的得分主题

发表于 2012-11-5 02:16 来自手机 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
还是把高级二字去掉吧,会的人这是普通的数据格式,不会的想看看又被高级二字吓住了。个人见解

点评

呵呵,高二是因为类模块的原因,你看看6楼链接的代码就知道了,蛮复杂的。  发表于 2012-11-5 09:00

TA的精华主题

TA的得分主题

发表于 2012-11-5 08:13 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-5 09:02 | 显示全部楼层
hyy514 发表于 2012-11-5 02:16
还是把高级二字去掉吧,会的人这是普通的数据格式,不会的想看看又被高级二字吓住了。个人见解

你看看能否解决我在5楼提出的问题---直接从JS里面得到"1x2"属性数组的方法。

在VBA里面使用JS代码,对EH的会员来说,都算高级了。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 06:26 , Processed in 0.054951 second(s), 16 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表