使用自定义函数接收和处理数据

项目
07/17/2024

自定义函数增强 Excel 功能的方法之一是通过从工作簿以外的位置接收数据，例如 Web 或服务器 (通过 WebSocket) 。可以通过 API（一种发出 HTTP 请求以与服务器交互的标准 Web API） (XHR) 等 FetchXmlHttpRequest API 请求外部数据。

重要

请注意，以下平台上可以使用 Excel 自定义函数。

Office 网页版
Windows 版 Office
- Microsoft 365 订阅
- 零售永久 Office 2016 及更高版本
- 批量许可永久 Office 2021 及更高版本
Mac 版 Office

以下各项当前不支持 Excel 自定义函数：

iPad 版 Office
Windows 上 Office 2019 或更早版本的批量许可永久版本

从 API 流式传输时间的自定义函数的 GIF。

从外部源返回数据的函数

如果自定义函数从外部源（如 Web）检索数据，则必须：

将 JavaScript Promise 返回到 Excel。
Promise使用回调函数解析具有最终值的。

Fetch 示例

在以下代码示例中，函数 webRequest 会访问一个假想的外部 API，该 API 跟踪当前国际空间站上的人数。函数返回 JavaScript Promise ，并使用 fetch 从假设 API 请求信息。生成的数据将转换为 JSON， names 属性将转换为字符串，该字符串用于解析承诺。

在开发自己的函数时，可能需要在相应 Web 请求没有及时完成时执行某个操作，或者需要考虑批处理多个 API 请求。

/**
 * Requests the names of the people currently on the International Space Station.
 * Note: This function requests data from a hypothetical URL. In practice, replace the URL with a data source for your scenario.
 * @customfunction
 */
function webRequest() {
  let url = "https://www.contoso.com/NumberOfPeopleInSpace"; // This is a hypothetical URL.
  return new Promise(function (resolve, reject) {
    fetch(url)
      .then(function (response){
        return response.json();
        }
      )
      .then(function (json) {
        resolve(JSON.stringify(json.names));
      })
  })
}

注意

使用 fetch 可以避免嵌套回调，在某些情况下可能优于 XHR。

XHR 示例

在以下代码示例中，函数 getStarCount 调用 Github API 以发现给定给特定用户的存储库的星数。这是一个异步函数，它返回 JavaScript Promise。从 Web 调用获取数据时，会解析承诺，从而将数据返回到单元格。

/**
 * Gets the star count for a given Github organization or user and repository.
 * @customfunction
 * @param userName string name of organization or user.
 * @param repoName string name of the repository.
 * @return number of stars.
 */
async function getStarCount(userName: string, repoName: string) {

  const url = "https://api.github.com/repos/" + userName + "/" + repoName;

  let xhttp = new XMLHttpRequest();

  return new Promise(function(resolve, reject) {
    xhttp.onreadystatechange = function() {
      if (xhttp.readyState !== 4) return;

      if (xhttp.status == 200) {
        resolve(JSON.parse(xhttp.responseText).watchers_count);
      } else {
        reject({
          status: xhttp.status,

          statusText: xhttp.statusText
        });
      }
    };

    xhttp.open("GET", url, true);

    xhttp.send();
  });
}

生成流式处理函数

流式处理自定义函数使用户能够在不需要用户显式刷新数据的情况下，向重复更新的单元格输出数据。这对于检查联机服务中的实时数据非常有用，如自定义函数教程中的函数。

若要声明流式处理函数，可以使用以下两个选项之一。

@streaming JSDoc 标记。
CustomFunctions.StreamingInvocation调用参数。

以下代码示例是一个自定义函数，它每秒向结果添加一个数字。对于此代码，请注意以下事项。

Excel 使用 setResult 方法自动显示每个新值。
当最终用户从自动完成菜单中选择函数时，不会在 Excel 中向其显示第二个输入参数 invocation。
回调 onCanceled 定义在取消函数时运行的函数。
流式处理不一定与发出 Web 请求相关联。在这种情况下，函数不会发出 Web 请求，但仍以设置的间隔获取数据，因此需要使用流式处理 invocation 参数。

/**
 * Increments a value once a second.
 * @customfunction INC increment
 * @param {number} incrementBy Amount to increment
 * @param {CustomFunctions.StreamingInvocation<number>} invocation
 */
function increment(incrementBy, invocation) {
  let result = 0;
  const timer = setInterval(() => {
    result += incrementBy;
    invocation.setResult(result);
  }, 1000);

  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

注意

有关如何从流式处理函数返回动态溢出数组的示例，请参阅从自定义函数返回多个结果：代码示例。

取消函数

在以下情况下，Excel 会取消函数的执行。

用户编辑或删除引用函数的单元格。
函数的参数（输入）之一发生变化。在这种情况下，取消之后还会触发新的函数调用。
用户手动触发重新计算。在这种情况下，取消之后还会触发新的函数调用。

你还可以考虑设置默认流式处理值，以在发出请求但你处于脱机状态时处理案例。

注意

还有一类函数称为可取消函数，它们使用 @cancelable JSDoc 标记。可取消函数允许在请求中间终止 Web 请求。

流式处理函数不能使用 @cancelable 标记，但流式处理函数可以包含 onCanceled 回调函数。只有返回一个值的异步自定义函数可以使用 @cancelable JSDoc 标记。请参阅 Autogenerate JSON metadata: @cancelable 了解有关标记的详细信息 @cancelable 。

使用调用参数

默认情况下，invocation 参数是任何自定义函数的最后一个参数。参数 invocation 提供有关单元格 (的上下文，例如其地址和内容) ，并允许使用 setResult 方法和 onCanceled 事件来定义函数在流式传输 () setResult 或 () 取消 onCanceled 时执行的操作。

调用处理程序的类型必须为 CustomFunctions.StreamingInvocation 或 CustomFunctions.CancelableInvocation 才能处理 Web 请求。

请参阅 Invocation 参数，了解参数的其他潜在用途 invocation ，以及它如何与 Invocation 对象相对应。

通过 WebSocket 接收数据

在自定义函数内，可使用 WebSocket 来通过与服务器的持久连接交换数据。使用 WebSocket，自定义函数可以打开与服务器的连接，然后在发生某些事件时自动接收来自服务器的消息，而无需显式轮询服务器中的数据。

WebSocket 示例

下面的代码示例建立了一个 WebSocket 连接，然后记录来自服务器的每一条传入消息。

let ws = new WebSocket('wss://bundles.office.com');

ws.onmessage(message) {
    console.log(`Received: ${message}`);
}

ws.onerror(error){
    console.err(`Failed: ${error}`);
}

后续步骤

了解你的函数可以使用的不同参数类型。
发现如何批处理多个 API 调用。

通过