从嵌入式Google地图中提取标记坐标

尼克尔

这很新,请耐心等待。我需要从嵌入式Google地图中提取标记坐标-示例链接为http://www.picknpay.co.za/store-search,我想在搜索时提取在地图中生成的所有标记位置。考虑使用诸如ParseHub之类的服务,但在走那条路线之前,我想我会通过SO /我自己介绍一下。

与手动遍历所有标记并分别搜索其坐标相比,必须有一种更简便的方法来查找地图中存储的标记的坐标?

煎蛋卷

通过http://www.picknpay.co.za/store-search提供的链接提供的网页源HTML不包含必要的数据,它使用AJAX。网站http://www.picknpay.co.za提供了sorta API。响应以JSON格式返回。导航页面(例如在Chrome中),然后打开“开发人员工具”窗口(F12),“网络”选项卡,重新加载(F5)页面并检查记录的XHR。最相关的数据是URL返回的JSON字符串:

http://www.picknpay.co.za/picknpay/json/picknpay/zh-CN/modules/store_finder/findStores.json

XHR预览

XHR标头

您可以使用下面的VBA代码来检索信息,如上所述。JSON.bas模块导入VBA项目以进行JSON处理。

Option Explicit

Sub Scrape_picknpay_co_za()

    Dim sResponse As String
    Dim sState As String
    Dim vJSON As Variant
    Dim aRows() As Variant
    Dim aHeader() As Variant

    ' Retrieve JSON data
    XmlHttpRequest "POST", "http://www.picknpay.co.za/picknpay/json/picknpay/en/modules/store_finder/findStores.json", "", "", "", sResponse
    ' Parse JSON response
    JSON.Parse sResponse, vJSON, sState
    If sState <> "Array" Then
        MsgBox "Invalid JSON response"
        Exit Sub
    End If
    ' Convert result to arrays for output
    JSON.ToArray vJSON, aRows, aHeader
    ' Output
    With ThisWorkbook.Sheets(1)
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aRows
        .Columns.AutoFit
    End With

    MsgBox "Completed"

End Sub

Sub XmlHttpRequest(sMethod As String, sUrl As String, arrSetHeaders, sFormData, sRespHeaders As String, sContent As String)

    Dim arrHeader

    'With CreateObject("Msxml2.ServerXMLHTTP")
    '    .SetOption 2, 13056 ' SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
    With CreateObject("MSXML2.XMLHTTP")
        .Open sMethod, sUrl, False
        If IsArray(arrSetHeaders) Then
            For Each arrHeader In arrSetHeaders
                .SetRequestHeader arrHeader(0), arrHeader(1)
            Next
        End If
        .send sFormData
        sRespHeaders = .GetAllResponseHeaders
        sContent = .responseText
    End With

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

对我来说输出如下:

输出

顺便说一句,类似的方法适用于其他答案

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章