网页进去了是空白 浅析Excel爬取网页表格数据

Excel居然还有“爬虫”功能？

话说还真的有，它只是一种相对简单的爬取，实现的方法就是靠内嵌其中的Power Query。不过话说回来，这里的“简单”是相对的，相对于有专业爬取工具Python来说它确实比较弱，但对于初学者来说还是蛮难的。

我们先从最简单的例子讲起。

比如说，我要获取证券之星这个网站中A股市场的数据。

这个网页是一个静态的网页，也无需登录就可以查看数据，所以我把它当作最简单的例子来讲。方法就是：复制该网页网址，转到Excel中，选择“数据”选项卡，再点击“自网站”。

把网站粘贴进对话框，点击“确定”。

Power Query非常智能地帮我们获取到该页面的表格元素。

这个表格非常简单，看似无需进行多余的数据清洗，所以我们不必进入Power Query编辑器，直接点击“加载”就可以把数据导入到Excel中。

但是导入后你会发现，最后一行是无用数据，而且右边有许多多余的空列！所以，我们还是再导入Power Query编辑器处理一下吧（翻车讲解）。方法也很简单，双击右边栏中的查询链接即可重新进入Power Query编辑器。

删除的方法如图所示：

对于最后一步可能你会比较疑惑，直接在Excel中删除不就可以了，为啥还要到这里来删？而且删除最后一行居然还有专门的菜单命令？这也太繁琐了吧？

原因就在于Power Query几乎所有的操作都是批量进行的，它是没有单元格概念的，每一步的操作实际上都依靠M函数，行在它这里是不可以直接删除的，而是要用到函数：= Table.RemoveLastN(删除的其他列,1)。至于不在Excel中操作是因为我想建立起数据与网站的动态链接，一旦网站数据更新了，我只要刷新一下，数据就能随之更新！如果直接在Excel中删除的话，刷新一下，原来删掉的数据和空列又跑回来。

最后将修改后的数据传回Excel也比较简单，选择“主页-关闭并上载”就可以。

接下来，来讲解更复杂一点的。此时，我们只是获取第一页的数据，那如果我们想获取前面20页的数据呢？

对于静态网页来说，方法相对容易一些，我们翻页时发现网址有如下变化规律：

第一页：1.html

第二页：2.html

第三页：3.html

.....

我们很容易发现页数是由网址最后一个数字来确定的，我们就要想办法把这个变化的数值构造成一个变量，然后让它依次从1-20进行取值。

我们回到Power Query的高级编辑器中看下上面例子中最简单的网页抓取都用了哪些M函数，其中最核心就下面两条：

源 = Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1.html")),    Data0 = 源{0}[Data],

在M语言中，实现网抓的核心函数是Web.Contents，它能够对指定的URL向服务器发出request并接受返回的response，HTML源码中包含table标签，使用Web.Page能够直接解析成表格，再深化出table即可。

所以抓取前20页的代码是这样子的：

let    get_data =(x)=>Table.RemoveLastN(Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_"&Text.From(x)&".html")){0}[Data],1),    result = Table.Combine(List.Transform({1..20},get_data))in    result

解释如下：

构造一个自定义函数get_data，其变量为x，由于Web.Contents后面的参数只能是文本，所以先用Text.From(x)将其转为文本再用&连接成一个动态的网址，由于每一页最后一行数据是无效数据，用函数Table.RemoveLastN移除它，然后构造一个从1-20的列表，即{1..20}，然后用List.Transform进行遍历，对于每次取值都放置到构造的函数get_data中，最后将获取的所有table进行合并。

没有M函数基础的同学对于以上的解释肯定听得云里雾里。其实要做的很简单，把以上代码粘贴到Power Query的高级编辑器中就行。

直接进入ower Query高级编辑器的方法是：数据-获取数据-其他来源-空白查询。

然后是：视图-高级编辑器。

粘贴进来，再点击完成。

第一次可能会出现这样的提示，点击继续然后勾选忽略。

稍等片刻数据就都全进来啦！

你瞧！只需要两行代码就实现多网页的数据抓取！ps:想获取多少页，就把20改成相应的页数。

这样呢，我们就不必每次都打开网页，然后一页页复制了。这时，我们要做的仅仅只是刷新而已！

本例中，是无法直接用可视化操作来实现的，所以用M函数。

最后，来讲解一下需要登陆的表格数据抓取。

这里举一个实际的例子：公司通讯录所在的EKP网页不允许直接复制，而我又常常需要用这里的数据来进行数据匹配。问题的难点就在于：需要登录EKP账号之后才能获取到数据，而在Power Query是没有直接可以输入密码的地方的。这也是困扰我很久的一个难题。

直到最近，我才找到它的解决方案：当我们登录网站后，网站会产生一个Cookie，所以再次访问时浏览器同时提交了这个Cookie完成账号和密码的验证，从而不需要再进行重复的登录验证。因此，如果Power Query提交request的时候把cookie也给提交上去的话，那就避开输入账号和密码的问题了。

所以本例的中的核心代码如下：

这里实际上是多一个headers，也就是网页访问中请求头，包含Cookie和Referer，其中Cookie=CK[Cookie]{0}，CK代表我引用Excel表格中保存的cookie数据，这样的写法可以做到不用进入Power Query就能够刷新数据！

需要注意的是，核心函数Web.Contents对于引用外部数据很敏感，为了防止引用失败，必须提前设置查询隐私级别。

这里呢，我着重讲一下获取cookie的方法。

登陆进入我们需要抓取的网页后按住F12进入开发者模式，切换到：Network-doc（网络-文档）。第一次进入是空白页面，因为此时我们才开始监听浏览器网络连接活动，按F5刷新网页即可。

选择其中一个，在预览中确认我们需要导入的链接：

然后切换至标头就可以找到Cookie啦，把这串东西复制到Cookie=“”中即可。当然，你也可以像我一样先复制到表格中，然后引用连接至代码，最后直接在表格刷新就可以获取数据！

let    url="",             //Requset URL中?前面的部分    headers=[Cookie=""],             //如果不需要登录请删除整行，同时删除下一行中的Headers=headers    query=[],               //Query String Parameters，即Requset URL中?后面的部分    web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query]))in    web

因为cookie是有生命周期的，如果刷新报错，重新复制一下cookie就行。毕竟，Power Query不是专业的爬取工具，没办法做到自动获取cookie。