Excel中的网络爬虫：Power Query、VBA和API方法

TL;DR：

• 当页面暴露稳定的 HTML 表格或 JSON 端点时使用 Power Query；它为 Excel 提供了可刷新的、文档化的转换。
• 当工作簿用户需要按钮驱动的导入或受控的 CSV 交接时使用 VBA。避免让 VBA 解析变化的页面标记。
• 对于用 JavaScript 渲染或经过流量验证的页面，首先通过 API 获取内容，然后让 Excel 转换返回的数据。
• 最具可维护性的设计是将获取和分析分开：源服务返回稳定的模式，而 Excel 负责过滤、连接、透视和图表。

在 Excel 中的网页抓取可以意味着四种非常不同的工作：一次性复制表格、刷新公共表格、调用 JSON API，或从需要浏览器渲染的页面喂入工作簿。根据页面行为选择工具比根据熟悉程度选择更可靠。

本指南涵盖 Power Query、VBA 和先用 API 的路径。示例仅限于项目被允许收集的公共数据。未经明确授权，请勿使用它们访问私人、需认证的、个人或受限的信息。

在构建工作簿之前选择方法

实用规则很简单：让 Excel 负责表格转换，而不是浏览器仿真。当网页充当展示层而非数据源时，寻找文档化的端点或在工作簿前放置获取服务。

方法 1：用 Power Query 导入可见表格

微软支持的流程是 数据 > 从网页，然后选择导航器中检测到的表格并将其加载到工作簿中。完整流程见 微软网页导入指南。

步骤

1. 打开工作簿并选择 数据。
2. 在获取和转换数据组中选择 从网页。
3. 粘贴允许的公共页面 URL。
4. 在导航器中选择表格。
5. 如果需要清理列，则选择 转换数据，否则选择 加载 直接写入工作表。
6. 重命名查询和表格，以便其用途显而易见。
7. 当需要再次抓取源时，选择 刷新。

Power Query 存储转换步骤。因为可重现的查询可以删除列、设置类型、拆分文本以及用相同的方式连接参考数据进行每次刷新，这很重要。微软还文档化了已加载的网页表格可以使用 查询刷新 在同一 Excel 刷新工作流程 中进行更新。

当从网页返回无可用表格时

页面可能在初始 HTML 到达后通过 JavaScript 渲染其数据。它也可能通过 XHR 或 fetch 请求而不是 HTML 表格暴露值。在这些情况下，尽管浏览器显示行，导航器结果可能为空。

打开浏览器开发者工具仅用于识别允许的、文档化的数据端点——而不是为了绕过访问控制。如果存在稳定的 JSON 端点，Power Query 可以直接调用。如果需要流量验证或浏览器渲染，请使用本指南后面的 API 优先方法。

方法 2：用 Power Query M 调用 JSON

Power Query 的 Web.Contents 函数执行 HTTP 请求，并返回可以传递给 Json.Document 的二进制响应。微软在 Web.Contents 参考 中记录了其请求选项，包括 Headers、Content、RelativePath、Query 和 ApiKeyName。

先决条件

• 带有 Power Query 的 Excel
• 通过工作簿的 Web API 凭据对话框输入的 Scrapeless API 令牌
• 被允许的公共目标 URL
• 已知的 JSON 响应封装

以下 Power Query M 块是一个先决条件差距示例，因为它需要读者的令牌和目标。它调用通用抓取 API，从 data 读取返回的 HTML，并生成可以喂入后续解析器或审计表的一行表格。

let
    Endpoint = "https://api.scrapeless.com",
    TargetUrl = Excel.CurrentWorkbook(){[Name="AuthorizedTargetUrl"]}[Content]{0}[Column1],
    Payload = Json.FromValue([
        actor = "unlocker.webunlocker",
        proxy = [country = "ANY"],
        input = [
            url = TargetUrl,
            jsRender = [
                enabled = true,
                response = [type = "html", options = []]
            ]
        ]
    ]),
    Raw = Web.Contents(
        Endpoint,
        [
            RelativePath = "api/v2/unlocker/request",
            Headers = [
                #"Content-Type" = "application/json"
            ],
            Content = Payload,
            ApiKeyName = "x-api-token"
        ]
    ),
    Envelope = Json.Document(Raw),
    Checked = if Envelope[code] = 200 and Envelope[data] <> null
        then Envelope[data]
        else error "The acquisition response did not contain page data",
    Output = #table(
        {"source_url", "html"},
        {{TargetUrl, Checked}}
    )
in
    Output

创建查询后，当 Excel 要求凭据时选择 Web API 并将令牌粘贴在那里。ApiKeyName 指定参数名称，同时将密钥保留在 M 源之外。凭据行为在微软的 安全 API 密钥示例 中进行了说明。

当前 API 请求形式和 JavaScript 选项也可以在 Scrapeless JS 渲染文档 中找到。

将返回的 HTML 转换为稳定的 Excel 列

将完整的 HTML 文档加载到一个单元格中是一个诊断桥，而不是最终数据集。生产工作簿应接收紧凑的模式。

例如：

有两种干净的方法可以达到这个合约：

• 要求获取层返回结构化字段。
• 在 Excel 外部解析返回的 HTML，然后将 JSON 或 CSV 暴露给 Power Query。

这两种方法都减少了工作簿对页面标记的依赖。期望包含六个命名字段的工作簿比一个包含数十个选择器和文本清理步骤的工作簿更容易测试。

如果页面在这些字段存在之前需要 JavaScript 渲染，请在重新设计工作簿之前用一个授权的 URL 测试 Universal Scraping API。

方法 3：使用 VBA 进行受控的 CSV 交接

当分析师需要一个按钮将文件导入已知工作表时，VBA 是有用的。将网页获取保持在宏之外，让宏消耗一个稳定的 CSV URL。这避免了将工作簿与变化的 HTML 绑定。

前提条件

• 启用宏的桌面 Excel，符合组织政策
• 一个返回 UTF-8 CSV 的允许 HTTPS 端点
• 名为 ImportedData 的工作表
• 包含端点 URL 的命名范围 CsvEndpoint

该块是前提条件缺口，因为端点和工作簿策略是特定于读者的环境。

Option Explicit

Public Sub ImportCsv()
    Dim endpoint As String
    Dim target As Worksheet
    Dim query As QueryTable

    endpoint = ThisWorkbook.Names("CsvEndpoint").RefersToRange.Value
    Set target = ThisWorkbook.Worksheets("ImportedData")

    target.Cells.ClearContents

    Set query = target.QueryTables.Add( _
        Connection:="TEXT;" & endpoint, _
        Destination:=target.Range("A1"))

    With query
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFilePlatform = 65001
        .RefreshStyle = xlOverwriteCells
        .Refresh BackgroundQuery:=False
    End With
End Sub

这个宏故意狭窄：它导入一个 CSV 合约，并将获取、渲染和访问逻辑留给拥有它们的服务。如果端点需要自定义身份验证，Power Query 的凭据存储通常比在 VBA 中嵌入令牌更合适。

微软在 Excel VBA RefreshAll 参考 中记录了 Workbook.RefreshAll 刷新外部数据范围和数据透视表报告。这可以支持在配置和测试每个查询后进行工作簿级别的刷新按钮。

API 优先的 Excel 网络抓取

API 优先设计分为两个阶段：

1. 获取和验证页面： 在必要时渲染 JavaScript，处理流量验证，并确认返回了预期的内容。
2. **在Excel中转换和分析：**将响应整理为列，连接查找表，构建数据透视表，并发布图表。

Universal Scraping API是为第一阶段构建的。Power Query 仍然是与工作簿接触的连接器。这个边界让团队能够在不重新构建每个公式和图表的情况下更改获取方法。

使用Scrapeless定价页面对比服务成本与运营浏览器基础设施所需的工程时间。要了解更广泛的适合Excel管道的响应格式，请参阅Universal Scraping API响应格式更新。

常见问题及解决方案

导航器不显示表格

该页面可能在初始响应中未公开稳定的HTML表格。寻找允许的JSON或CSV来源。如果值仅在JavaScript之后出现，请使用返回完整内容的获取层。

刷新返回隐私级别错误

Power Query根据隐私设置隔离数据源。检查工作簿的源配置，避免在政策允许的情况下将私有组织数据与公共来源结合。

一个查询在一台计算机上有效，但在另一台上无效

检查Excel版本、身份验证设置、命名范围、宏策略和网关要求。凭证是环境特定的，不应在工作簿文件中传递。

工作簿加载挑战页面

在转换之前添加内容级验证。要求预期的JSON字段、CSV头部或页面标记。拒绝任何不满足该约定的响应。

刷新后列类型更改

在源步骤后应用显式数据类型于Power Query。将货币符号、特定于地区的分隔符和缺失值排除在数值列之外。

刷新所需时间过长

在数据到达Excel之前进行缩减。根据日期或标识符在源头进行过滤，仅请求所需字段，当工作表不需要时仅加载中间查询作为连接。

从工作簿原型到生产源

有用的进展是：

1. 手动证明一个URL和一行。
2. 创建具有显式列名和类型的Power Query。
3. 添加内容验证和错误工作表。
4. 将渲染和HTML解析移入获取服务。
5. 发布具有版本化架构的JSON或CSV。
6. 在Excel外部安排收集，并保持工作簿刷新的焦点在分析上。

这让电子表格在不变成无人值守的浏览器的情况下保持有用。工作簿成为干净数据的消费者，Excel能够很好地处理这个角色。

结论：让Excel分析，而不是模拟浏览器

Power Query是可刷新的HTML表格和JSON API的默认选择。VBA对于受控的用户触发导入很有价值。当允许的来源依赖于JavaScript或流量验证时，请使用获取API，并给予Excel一个稳定的数据契约。

要建立该边界， 创建Scrapeless帐户，使用一个公共URL测试Universal Scraping API，定义工作簿需要的字段，并使这些字段成为刷新契约。

常见问题

Excel可以在没有代码的情况下抓取网站吗？

可以。Power Query的网页连接器可以通过图形化工作流导入许多可见的HTML表格。它最佳适合稳定的公共页面，这些页面在其初始HTML中公开表格。

为什么Power Query显示空页面？

这些值可能是在初始响应后由JavaScript呈现的，从单独的端点加载，或被验证流量页面替换。检查源行为并根据需要选择获取方法。

在网页抓取中，Power Query是否比VBA更好？

Power Query通常在可刷新的数据转换和凭证管理方面表现更好。VBA对于工作簿按钮和受控文件导入很有用，但在维护HTML解析方面是一个脆弱的地方。

Power Query可以调用JSON API吗？

可以。Web.Contents可以获取响应，Json.Document可以将其解析为记录、列表和表格。使用凭证对话框，而不是在M代码中存储API密钥。

工作簿如何处理JavaScript渲染的页面？

使用能够渲染的获取服务返回HTML、JSON或CSV，然后将Power Query连接到该输出。这将浏览器行为与电子表格分开。

Excel应该多久刷新一次抓取的数据？

匹配刷新频率以满足业务需求、源权限和服务容量。对于计划的或高容量的采集，在线外部进行获取，并让工作簿从准备好的数据集中刷新。