详解 WEBSERVICE 与 FILTERXML 函数提取 XML 数据

Office 2013版中,微软为Excel 增加了50个函数,不过由于大部分函数功能过于冷门,很少见到关于这些新增函数的讨论和用例。不过其中有两个函数还是比较引人注意的,就是WEBSERVICE和FILTERXML。近年来随着云计算、云应用的兴起,需要与web服务进行数据交互的情境也越来越多,微软也与时俱进地为Excel增加了相应的功能。那么这两个函数是做什么用的呢?

首先WEBSERVICE函数的功能是通过向目标网址发送http请求获取网页或数据,也就是获取未经浏览器解析的网页源代码或数据文件,使得原来需要VBA代码才能实现的http抓取操作用函数也能实现。不过遗憾的是,由于该函数并没有提供更多可选参数,因此我们无法自定义发起请求的方式和文件头参数等等。

FILTERXML函数则是针对XML文件的数据解析函数,简单说就是将数据从XML文件中取出来。XML(Extensible Markup Language)是目前web数据交换中使用最广泛的数据载体格式之一(另一个是JSON),当我们试图从web获取数据时,所需的数据有很大概率使用的是这两种格式之一,而其余的情况则可能是数据直接存在于html页面中。编写严谨的html也可看作XML的变体。无论是XML还是严谨的html,都可以使用XPath(路径语言XML Path Language)便捷地提取数据。篇幅有限,XPath不展开讲解,如需了解可以看这个教程 。FILTERXML吸引人之处便在于函数原生支持了XPath,让数据的提取变得方便直观。

在本例中,我们将用WEBSERVICE函数获取一份xml数据文件,并使用FILTERXML函数将其中的数据解析到工作表中。

首先,这是我们要获取的XML文件的基本结构(节选),完整文件在这里

<CATALOG>
<CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
<CD>
<TITLE>Hide your heart</TITLE>
<ARTIST>Bonnie Tyler</ARTIST>
<COUNTRY>UK</COUNTRY>
<COMPANY>CBS Records</COMPANY>
<PRICE>9.90</PRICE>
<YEAR>1988</YEAR>
</CD>

如图,url地址在B1单元格,我们在B2单元格写入函数=WEBSERVICE(B1)

2.png
3.png

回车之后,XML文件的内容便被获取并存入了B2单元格(确保你的设备能够联网,否则会返回错误值)

接下来,我们要将所需字段的数据解析到表格中。以下是数组公式的操作方法:选中B5:B29,在公式栏中输入公式=FILTERXML(B2,"//CD/TITLE") 然后别忘了ctrl + alt+ enter 我们就得到了所有Title字段的数据
4.png
5.png

重复同样的操作获取其他字段,完成后效果如图所示
6.png

如果只需获取特定某个专辑的Title,则可使用公式=FILTERXML(B2,"//CD[3]/TITLE"),此处数字3代表xml中的第3个CD节点下的Title字段。(如果此处有疑问请参看上文提到的xpath教程)

好了,到这里关于用WEBSERVICE和FILTERXML函数获取和解析网络XML文件的例子就演示完了,希望对您有帮助!

本文为原创,excelhome论坛首发



知识共享许可协议
除非注明,本博客文章均为原创
并采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可。转载请以URL链接形式标注源地址。

标签: excel

添加新评论