Excel 也可以玩 REST

系列文章索引

Excel 也可以玩 REST
Excel 也可以玩 REST (2)
Excel 也可以玩 REST (3)

随着 RESTFul API 的逐渐普及，如果能在 Excel 中直接调用这些 API，将会给我们处理和分析数据带来无尽好处。我们先来看一个在线的 OData Service 数据：https://services.odata.org/V3/OData/OData.svc/Products?$format=json，在 Chrome 浏览器中打开，显示结果如下图：

这个 url 以 OData 规范提供的 Rest 服务，json 数据能直接将数据导入到 Excel 中吗？答案是肯定的。Excel 提供了两种方法：方法一是在数据页面中的获取外部数据

方法二是通过 Power Query 工具。Power Query 在 Excel 2016 中已经是内置的。之前的版本需要在微软官网下载、安装并且加载（作为 COM 加载项）。下图展示了 Power Query 如何将数据加载到 Excel 中：

本篇不对 Power Query 导入 OData 的细节展开说明。主要试图解决一个一般性问题：如何将 Rest Web API 的数据导入到 Excel，尤其是 json 格式的数据。从我的了解来说，貌似 Excel 并没有提供 json 格式数据的直接导入，OData 数据才可以！

当然，如果只是为了把网络上 Rest API 的数据放到 Excel 中，可以选择很多其他工具，比如本人喜欢的 Python。Python + pandas 可以这样来做：

# encoding: utf8

import requests
import pandas as pd 

url = 'https://services.odata.org/V3/OData/OData.svc/Products?$format=json'
products = requests.get(url).json()['value']

df = pd.DataFrame(products)
# re-order columns
df = df[['ID','Name','Description','ReleaseDate','DiscontinuedDate','Rating','Price']]

writer = pd.ExcelWriter('products.xlsx')
df.to_excel(writer, 'Sheet1')
writer.save()

输出结果如下，虽然不是很美观，但相当简单：

如果不借助外部工具，Excel 自身能做到吗？答案也是肯定的。以下就是 HOW 了。

1、Alt + F11 进入 VBE 界面，添加对 Microsoft WinHTTP Service 5.1 的引用。

2、编写测试代码，测试数据获取：

Public Sub test_get_rest_data()
    Dim req As WinHttp.WinHttpRequest
    Set req = CreateObject("WinHttp.WinHttpRequest.5.1")
    req.SetTimeouts 60000, 60000, 60000, 60000

    Dim url As String
    url = "https://services.odata.org/V3/(S(rsgp3gk40ractowux3driji4))/OData/OData.svc/Products?$format=json"
    req.Open "GET", url, False
    req.Send

    Dim resText As String
    resText = req.ResponseText
    
    Debug.Print resText
End Sub

WinHTTPReq 对象大家自行参考微软的帮助：https://docs.microsoft.com/en-us/windows/desktop/winhttp/winhttp-start-page。测试没有任何问题，那我就将数据获取封装到函数，方便复用：

Public Function doGet(url As String) As String
    Dim req As WinHttp.WinHttpRequest
    Set req = CreateObject("WinHttp.WinHttpRequest.5.1")

    req.SetTimeouts 60000, 60000, 60000, 60000
    req.Open "GET", url, False
    req.Send
    
    doGet = req.ResponseText
End Function

Public Sub test_doGet()
    Dim url As String
    url = "https://services.odata.org/V3/OData/OData.svc/Products?$format=json"
    
    Dim resText As String
    resText = doGet(url)
    
    Debug.Print resText
End Sub

接下来还要解决两个问题：

json 格式解析
数据导入到工作表

VBA 解析 json 我在 Github 上找到了一个解决方案：https://github.com/VBA-tools/VBA-JSON。将代码拷贝到 VBA 一个专门模块中，模块命名为 JsonConverter。这个模块的 parseJson() 方法将获取的 json 字符串转换成 Dictionary。注意 Dictionary 并不是 VBA 内置对象，需要添加对 Microsoft Scripting Runtime 的引用。

以下是 parseJson() 的测试代码：

Public Sub parseJson()
    Dim jsonData As String
    jsonData = doGet("https://services.odata.org/V3/OData/OData.svc/Products?$format=json")
    Dim parsedDict As Dictionary
    Set parsedDict = JsonConvertor.parseJson(jsonData)
    
    Dim val As Dictionary
    For Each val In parsedDict("value")
        Debug.Print val("ID"), val("Name"), val("Description")
    Next
End Sub

数据导入到 Excel 工作表：

Public Sub writeToSheet()

    Dim jsonText As String
    jsonText = doGet("https://services.odata.org/V3/OData/OData.svc/Products?$format=json")
    
    Dim parsedDict As Dictionary
    Set parsedDict = JsonConvertor.parseJson(jsonText)
    
    Dim Values As Variant
    ReDim Values(parsedDict("value").Count, 6)    

    Dim headerDict As Dictionary
    Set headerDict = parsedDict("value").Item(1)
    Dim k As Variant
    
    Dim i As Long
    For Each k In headerDict.Keys
        Sheet1.Range("A1").Offset(0, i).Value = k
        i = i + 1
    Next    

    Dim valueDict As Dictionary
    Dim vk As Variant
    i = 0
    For Each valueDict In parsedDict("value")
        Values(i, 0) = valueDict("ID")
        Values(i, 1) = valueDict("Name")
        Values(i, 2) = valueDict("Description")
        Values(i, 3) = valueDict("ReleaseDate")
        Values(i, 4) = valueDict("DiscontinuedDate")
        Values(i, 5) = valueDict("Rating")
        Values(i, 6) = valueDict("Price")
      
        i = i + 1
    Next
    
    Sheet1.Range(Cells(2, 1), Cells(parsedDict("value").Count + 1, 7)) = Values
End Sub

运行后界面如下：

后续将继续讲解 WinHttp 如何执行 POST, PUT 和 DELETE 请求，从而实现 CRUD 操作，也将介绍 WinHttp 处理 cookie 的方法。