如何让Excel下拉列表单元格从API的JSON字符串数组获取数据？

要实现像=FetchList("http://localhost:8080/api/v1/list")这样用自定义公式生成下拉列表，你需要借助VBA编写自定义函数，再配合Excel的数据验证功能，具体步骤如下：

1. 编写VBA自定义函数FetchList #

Excel原生公式无法直接调用API，所以我们用VBA实现API请求和JSON解析：

• 打开Excel，按Alt + F11打开VBA编辑器。
• 右键左侧工程窗口里的工作簿名称，选择「插入」→「模块」。
• 在模块中粘贴以下代码：

Function FetchList(apiUrl As String) As Variant
    Dim http As Object
    Dim jsonResponse As String
    Dim jsonObj As Object
    
    ' 初始化HTTP请求对象
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    On Error GoTo ErrorHandler
    http.Open "GET", apiUrl, False
    http.Send
    
    ' 处理成功响应
    If http.Status = 200 Then
        jsonResponse = http.ResponseText
        ' 解析JSON数组（Office 365推荐用Microsoft JSON库）
        Set jsonObj = CreateObject("Microsoft.JSON")
        FetchList = jsonObj.Parse(jsonResponse)
    Else
        FetchList = Array("请求失败，状态码：" & http.Status)
    End If

Cleanup:
    Set http = Nothing
    Set jsonObj = Nothing
    Exit Function

ErrorHandler:
    FetchList = Array("请求出错：" & Err.Description)
    Resume Cleanup
End Function

注意：如果找不到Microsoft.JSON对象，你可以导入VBA-JSON模块（将模块代码复制到VBA编辑器即可），然后把解析部分替换为：

Set jsonObj = JsonConverter.ParseJson(jsonResponse)
FetchList = Application.Transpose(jsonObj)

2. 配置数据验证生成下拉列表 #

• 选中要设置下拉列表的单元格（比如A1）。
• 切换到「数据」选项卡，点击「数据验证」。
• 在弹出的窗口中，「允许」选择「序列」，然后在「来源」框中输入：

  =FetchList("http://localhost:8080/api/v1/list")
  

  （务必加上完整的http://或https://前缀）
• 点击「确定」，下拉列表就生成了。

3. 数据刷新方式 #

• 手动刷新：按F9刷新整个工作簿，或右键单元格选择「刷新」。
• 自动刷新：如果需要打开工作簿时自动刷新，可在工作簿的ThisWorkbook模块中添加代码：

  Private Sub Workbook_Open()
      Application.CalculateFull
  End Sub
  

关键注意事项 #

• API返回的JSON必须是标准字符串数组格式（如["Oranges","Apples","Mangoes"]），如果格式调整，需要对应修改VBA的解析逻辑。
• 确保Excel所在环境能正常访问目标API（无防火墙/跨域限制）。

内容的提问来源于stack exchange，提问作者Sagar Agarwal