教程：在 Excel 中创建自定义函数

项目
02/13/2024

用户可以借助自定义函数向 Excel 添加新函数，方法是在 JavaScript 中将这些函数定义为加载项的一部分。 Excel 中的用户可以访问自定义函数，就像他们访问 Excel 中的任何本机函数一样，比如 SUM()。可以创建自定义函数，以执行简单的任务（如计算）或更复杂的任务（如将实时数据从 Web 传送到工作表中）。

在本教程中，你将：

使用适用于 Office 加载项的 Yeoman 生成器来创建自定义函数加载项。
使用预生成的自定义函数来执行简单计算。
创建从 Web 获取数据的自定义函数。
创建从 Web 传送实时数据的自定义函数。

先决条件

Node.js（最新LTS 版本）。访问 Node.js 站点，下载并安装适合你的操作系统的版本。
最新版本的 Yeoman 和适用于 Office 加载项的 Yeoman 生成器。若要全局安装这些工具，请从命令提示符处运行以下命令。
```
npm install -g yo generator-office
```
注意

即便先前已安装了 Yeoman 生成器，我们还是建议你通过 npm 将包更新为最新版本。
已连接到 Microsoft 365 订阅的 Office (包括 Office 网页版)。

注意

如果你还没有 Office，你可能有资格通过 Microsoft 365 开发人员计划获得Microsoft 365 E5开发人员订阅;有关详细信息，请参阅常见问题解答。或者，可以注册 1 个月的免费试用版或购买 Microsoft 365 计划。

已连接到 Microsoft 365 订阅的 Office (包括 Office 网页版)。

注意

如果你还没有 Office，你可能有资格通过 Microsoft 365 开发人员计划获得用于开发的Microsoft 365 E5开发人员订阅;有关详细信息，请参阅常见问题解答。或者，可以注册 1 个月的免费试用版或购买 Microsoft 365 计划。

创建自定义函数项目

首先，创建代码项目，构建自定义函数加载项。 Office 加载项的 Yeoman 生成器将使用一些预生成的自定义函数（可以试用这些函数）来设置项目。如果已运行自定义函数快速启动并生成了项目，请继续使用该项目，然后改为跳到此步骤。

注意

如果重新创建 Yo Office 项目，可能会收到错误，因为 Office 缓存已具有同名函数的实例。在运行 npm run start 之前，可以通过清除 Office 缓存来阻止此操作。

运行以下命令，使用 Yeoman 生成器创建加载项项目。包含项目的文件夹将添加到当前目录。
```
yo office
```
注意

运行该yo office命令时，可能会收到有关 Yeoman 和 Office 加载项 CLI 工具的数据收集策略的提示。根据你的需要，使用提供的信息来响应提示。

出现提示时，请提供以下信息以创建加载项项目。
- 选择项目类型：Excel Custom Functions using a Shared Runtime
- 选择脚本类型：JavaScript
- 要为外接程序命名什么名称？My custom functions add-in
Yeoman 生成器将创建项目文件并安装支持的 Node 组件。

注意

如果使用 Node.js 20.0.0 或更高版本，则当生成器运行安装时，可能会看到一条警告，指出你的引擎不受支持。我们正在努力解决此问题。同时，警告不会影响生成的生成器或项目，因此可以忽略它。

提示

创建加载项项目后，可忽略 Yeoman 生成器提供的后续步骤指南。本文中的分步说明提供了完成本教程所需的全部指南。
导航到项目的根文件夹。
```
cd "My custom functions add-in"
```
生成项目。
```
npm run build
```
注意

Office 加载项应使用 HTTPS，而不是 HTTP（即便是在开发时也是如此）。如果系统在运行 npm run build 后提示你安装证书，请接受提示以安装 Yeoman 生成器提供的证书。
启动在 Node.js 中运行的本地 Web 服务器。可以在 Excel 中试用自定义函数加载项。

Windows 版或 Mac 版 Excel
Excel 网页版

若要在 Windows 版或 Mac 版 Excel 中测试加载项，请运行以下命令。运行此命令时，本地 Web 服务器将启动，Excel 将打开并载入加载项。

npm run start:desktop

注意

即使在开发过程中，Office 外接程序也应使用 HTTPS，而不是 HTTP。如果在运行以下命令之一后系统提示安装证书，请接受安装 Yeoman 生成器提供的证书的提示。你可能还必须以管理员身份运行命令提示符或终端才能进行更改。

若要在Excel 网页版中测试加载项，请运行以下命令。运行此命令时，本地 Web 服务器将启动。将 "{url}" 替换为你拥有权限的 OneDrive 或 SharePoint 库上 Excel 文档的 URL。

注意

如果在 Mac 上进行开发，请将括 {url} 在单引号中。请勿在 Windows 上执行此操作。

npm run start:web -- --document {url}

示例如下。

npm run start:web -- --document https://contoso.sharepoint.com/:t:/g/EZGxP7ksiE5DuxvY638G798BpuhwluxCMfF1WZQj3VYhYQ?e=F4QM1R
npm run start:web -- --document https://1drv.ms/x/s!jkcH7spkM4EGgcZUgqthk4IK3NOypVw?e=Z6G1qp
npm run start:web -- --document https://contoso-my.sharepoint-df.com/:t:/p/user/EQda453DNTpFnl1bFPhOVR0BwlrzetbXvnaRYii2lDr_oQ?e=RSccmNP

如果外接程序未在文档中旁加载，请按照手动旁加载加载项中的说明手动旁加载到Office web 版。

注意

尝试使用预生成的自定义函数

创建的自定义函数项目中包含一些预生成的自定义函数，这些函数在 ./src/functions/functions.js 文件中定义。 ./manifest.xml 文件指定所有自定义函数均属于 CONTOSO 命名空间。你将使用 CONTOSO 命名空间来访问 Excel 中的自定义函数。

接下来，通过完成以下步骤，尝试使用 ADD 自定义函数。

在 Excel 中，转至任意单元格并输入 =CONTOSO。请注意，自动完成菜单将显示 CONTOSO 命名空间中所有函数的列表。
通过在单元格中指定值 =CONTOSO.ADD(10,200) 并按 Enter 来运行 CONTOSO.ADD 函数，并将数字 10 和 200 作为输入参数。

ADD 自定义函数将计算你提供的两个数字的总和，并返回结果 210。

如果 CONTOSO 命名空间在自动完成菜单中不可用，请执行以下步骤在 Excel 中注册加载项。

Excel 网页版
Windows 或 Mac 上的 Excel

在 Excel 中，选择“ 主页>加载项”，然后选择“ 更多加载项”。
在“ 我的外接程序 ”选项卡中，选择“ 管理我的外接程序 ”，然后选择 “上传我的外接程序”。
选择“浏览...”，并导航到 Yeoman 生成器创建的项目的根目录。
依次选择文件“manifest.xml”，“打开”，然后选择“上载”。
尝试使用新函数。在单元格 B1 中，键入文本 =CONTOSO.GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions")，然后按 Enter。你会看到，单元格 B1 中的结果便是 Excel-Custom-Functions Github 存储库所获得的星星的当前数目。

注意

如果旁加载加载项时遇到错误，请参阅本文的故障排除部分。

创建从 Web 请求数据的自定义函数

集成来自 Web 的数据是通过自定义函数来扩展 Excel 的好方法。接下来，需要创建一个名为“getStarCount”的自定义函数，显示给定 Github 存储库所拥有的星星数量。

在“我的自定义函数加载项”项目中，找到文件 ./src/functions/functions.js，并在代码编辑器中将其打开。

在 function.js 中，添加以下代码。

/**
  * Gets the star count for a given Github repository.
  * @customfunction 
  * @param {string} userName string name of Github user or organization.
  * @param {string} repoName string name of the Github repository.
  * @return {number} number of stars given to a Github repository.
  */
  async function getStarCount(userName, repoName) {
    try {
      //You can change this URL to any web request you want to work with.
      const url = "https://api.github.com/repos/" + userName + "/" + repoName;
      const response = await fetch(url);
      //Expect that status code is in 200-299 range
      if (!response.ok) {
        throw new Error(response.statusText)
      }
        const jsonResponse = await response.json();
        return jsonResponse.watchers_count;
    }
    catch (error) {
      return error;
    }
  }

运行以下命令以重新生成项目。
```
npm run build
```
完成以下步骤（针对 Excel 网页版或者 Windows 版或 Mac 版 Excel），以在 Excel 中重新注册加载项。必须完成这些步骤，才能使用新函数。

Windows 版或 Mac 版 Excel
Excel 网页版

关闭 Excel，然后重新打开 Excel。
在 Excel 功能区中，选择“ 主页>加载项”。
在“ 开发人员加载项 ”部分下，选择“ 我的自定义函数加载项 ”进行注册。
尝试使用新函数。在单元格 B1 中，键入文本 =CONTOSO。GETSTARCOUNT (“OfficeDev”、“Office-Add-in-Samples”) 然后按 Enter。应会看到单元格 B1 中的结果是当前提供给 Office-Add-in-Samples 存储库的星数。

注意

如果旁加载加载项时遇到错误，请参阅本文的故障排除部分。

创建流式处理异步自定义函数

getStarCount 函数返回存储库在特定时刻所拥有的星星数量。自定义函数也会返回不断变化的数据。这些函数称为流式处理函数。它们必须包含一个 invocation 参数，该参数引用调用该函数的单元格。 invocation 参数用于随时更新该单元格的内容。

在下面的代码示例中，请注意，有两个函数：currentTime 和 clock。 currentTime 函数是不使用流式处理的静态函数。它将以字符串形式返回日期。 clock 函数使用 currentTime 函数每秒向 Excel 中的单元格提供一次新时间。它使用 invocation.setResult 将时间传递给 Excel 单元格，并使用 invocation.onCanceled 处理函数取消。

我的自定义函数加载项 项目已在 ./src/functions/functions.js 文件中包含以下两个函数。

/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}
    
/**
 * Displays the current time once a second
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);
    
  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

若要试用函数，请在单元格 C1 中键入文本 =CONTOSO.CLOCK()，然后按 Enter。此时会显示当前日期，该日期每秒更新一次。虽然此时钟只是一个循环计时器，但利用这一理念，你可以在更复杂的函数上设置计时器，以便执行对实时数据的 Web 请求。

疑难解答

如果多次运行本教程，可能会遇到问题。如果 Office 缓存已具有同名函数的实例，则加载项在旁加载时会收到错误。

可以通过在运行 npm run start之前清除 Office 缓存来防止此冲突。如果 npm 进程已在运行，请输入 npm stop，清除 Office 缓存，然后重启 npm。

Excel 中标题为“安装函数时出错”的错误消息。它包含文本“未安装此加载项，因为已存在同名的自定义函数”。

后续步骤

恭喜！你已经创建新的自定义函数项目，试用了预生成的函数，创建了从 Web 请求数据的自定义函数，并创建了流式传输数据的自定义函数。接下来，了解如何与任务窗格共享自定义函数数据。

Share via