Skip to main content

Excel JavaScript API

此演示侧重于 Excel JavaScript API。

¥This demo focuses on the Excel JavaScript API.

对于读取和写入 Excel 电子表格，其他演示 涵盖了各种各样的用例和部署。

¥For reading and writing Excel spreadsheets, other demos cover a wide variety of use cases and deployments.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

Office 2016 引入了用于与应用交互的 JavaScript API。它提供了自定义功能和任务窗格的解决方案。

¥Office 2016 introduced a JavaScript API for interacting with the application. It offers solutions for custom functions as well as task panes.

Excel 目前不支持使用 Apple Numbers 文件和某些旧文件格式。SheetJS 填补了这一空白。

¥Excel currently does not provide support for working with Apple Numbers files and some legacy file formats. SheetJS fills the gap.

在 "完整演示" 中，我们将创建一个新的自定义函数 SHEETJS.EXTERN()，它尝试获取外部电子表格并将数据插入到工作表中。

¥In the "Complete Demo", we'll create a new custom function SHEETJS.EXTERN() which tries to fetch an external spreadsheet and insert the data into the worksheet.

SHEETJS.EXTERN output

测试部署

该演示在以下部署中进行了测试：

¥This demo was tested in the following deployments:

操作系统和版本架构Excel日期
macOS 14.5darwin-arm16.812024-12-22
视窗 11win11-x64365 (2501)2025-01-19
Excel 错误

存在影响 fetch 和 Excel 的二进制数据错误。在 2303 版本中已解决。强烈建议在运行演示之前升级到最新版本的 Excel 365。

¥There was a binary data bug affecting fetch and Excel. It was resolved in version 2303. It is strongly encouraged to upgrade to the latest version of Excel 365 before running the demo.

遥测

Office 加载项 CLI 默认收集遥测数据。可以禁用它：

¥The Office Add-in CLI collects telemetry by default. It can be disabled:

npx office-addin-usage-data off

可以通过运行以下命令来验证该设置：

¥The setting can be verified by running:

npx office-addin-usage-data list

集成详情​

¥Integration Details

SheetJS NodeJS 模块 可以从 Excel 自定义函数项目中的脚本导入。

¥The SheetJS NodeJS module can be imported from scripts in an Excel Custom Functions project.

sheet_to_json 辅助函数可以根据工作表数据生成值数组的数组。Excel 自定义函数透明地将数组的数组视为动态数组。

¥The sheet_to_json helper function can generate arrays of arrays of values based on the worksheet data. Excel custom functions transparently treat arrays of arrays as Dynamic Arrays.

此示例使用 fetch API 获取文件，使用 SheetJS read[^1] 方法解析二进制数据并从第一个工作表返回数据。

¥This example fetches a file using the fetch API, parses the binary data using the SheetJS read[^1] method and returns data from the first worksheet.

src\functions\functions.js
const XLSX = require("xlsx");

/**

 * Download file and write data

 * @customfunction

 * @param {string} url URL to fetch and parse

 * @returns {any[][]} Worksheet data
 */
export async function extern(url) {
  try {
    /* Fetch Data */
    const res = await fetch(url);

    /* Get Data */
    const ab = await res.arrayBuffer();

    /* Parse Data */
    const wb = XLSX.read(ab);

    /* Translate Data */
    const ws = wb.Sheets[wb.SheetNames[0]]; // get first worksheet
    const aoa = XLSX.utils.sheet_to_json(ws, { header: 1 }); // array of arrays

    /* Return Data */
    return aoa;
  } catch(e) {
    /* Pass error message back */
    return [[e.message || e]];
  }
}

完整演示​

¥Complete Demo

  1. 清除函数缓存。对于测试的 Windows 版 Excel 版本：

    ¥Clear the functions cache. For the tested version of Excel for Windows:

  • 打开文件资源管理器

    ¥Open File Explorer

  • 选择地址栏并输入 %LOCALAPPDATA%\Microsoft\Office\16.0\Wef

    ¥Select the address bar and enter %LOCALAPPDATA%\Microsoft\Office\16.0\Wef

  • 删除 CustomFunctions 文件夹（如果存在）并清空回收站。

    ¥Delete the CustomFunctions folder (if it exists) and empty Recycle Bin.

这将删除与该用户账户关联的所有自定义功能！

¥This will delete all custom functions associated with the user account!

要保留用户账户上的自定义功能，请在测试之前将现有文件夹重命名为 CustomFunctionsBackup，并在测试完成后重命名回 CustomFunctions。

¥To preserve the custom functions on the user account, rename the existing folder to CustomFunctionsBackup before testing and rename back to CustomFunctions after testing is finished.

  1. 安装 NodeJS 长期支持。

    ¥Install NodeJS LTS.

  2. 启动新的 PowerShell（Windows）或终端（MacOS）窗口。

    ¥Launch a new PowerShell (Windows) or Terminal (MacOS) window.

  3. 禁用遥测：

    ¥Disable telemetry:

npx office-addin-usage-data off

  1. 安装依赖：

    ¥Install dependencies:

npm i -g yo bower generator-office

创建新的加载项​

¥Creating a new Add-in

  1. 运行生成器：

    ¥Run the generator:

npx yo office

生成器会问几个问题：

¥The generator will ask a few questions:

  • "选择项目类型"："使用共享运行时的 Excel 自定义函数"

    ¥"Choose a project type": "Excel Custom Functions using a Shared Runtime"

  • "选择脚本类型"："JavaScript",

    ¥"Choose a script type": "JavaScript",

  • "你想为你的加载项命名什么？"："SheetJS 导入"

    ¥"What do you want to name your add-in?": "SheetJSImport"

生成器将创建项目并安装依赖。

¥The generator will create the project and install dependencies.

  1. 开始开发流程：

    ¥Start the development process:

cd SheetJSImport
npm run build
npm start

如果提示 Allow localhost loopback for Microsoft Edge WebView，请键入 N 并按 Enter。

¥If prompted to Allow localhost loopback for Microsoft Edge WebView, type N and press Enter.

如果提示安装 "Microsoft Office 加载项的开发者 CA" 证书，选择 "是的"

¥If prompted to install "Developer CA for Microsoft Office Add-ins" certificate, select "Yes"

如果 Windows 防火墙提示允许在专用网络上使用 Node.js，请选择 "是的" 将创建一个运行 NodeJS 的新终端窗口。保持窗户打开。

¥If Windows Firewall prompts to allow Node.js on private networks, select "Yes" A new terminal window running NodeJS will be created. Keep the window open.

将启动一个包含已加载加载项的新 Excel 窗口。

¥A new Excel window with the loaded add-in will launch.

在某些测试中，任务窗格显示错误：

¥In some tests, the task pane showed an error:

Script error.

Webview2 应手动安装。

¥Webview2 should be installed manually.

  1. 在 manifest.xml 中，搜索 Functions.Namespace。 将有一个名为 bt:String 的 XML 元素。将 DefaultValue 属性更改为 SHEETJS：

    ¥In manifest.xml , search for Functions.Namespace . There will be an XML element with name bt:String. Change the DefaultValue attribute to SHEETJS:

manifest.xml (change highlighted line)
      <bt:ShortStrings>
        <bt:String id="Functions.Namespace" DefaultValue="SHEETJS"/>
        <bt:String id="GetStarted.Title" DefaultValue="Get started with your sample add-in!" />

  1. 关闭 Excel 窗口和终端窗口。不要保存 XLSX 文件。

    ¥Close the Excel window and the terminal window. Do not save the XLSX file.

  2. 在终端窗口中，再次启动开发过程：

    ¥In the terminal window, start the development process again:

npm run stop
npm start

集成 SheetJS 库​

¥Integrating the SheetJS Library

  1. 在项目中安装 SheetJS 库

    ¥Install the SheetJS library in the project

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

  1. 将 src\functions\functions.js 替换为以下内容：

    ¥Replace src\functions\functions.js with the following:

src\functions\functions.js
var XLSX = require("xlsx");

/**

 * Print SheetJS Library Version

 * @customfunction

 * @returns {string[][]} The SheetJS Library Version.
 */
export function version() {
  return [[XLSX.version]];
}

  1. 关闭终端窗口和 Excel 窗口。不要保存 Excel 文件。

    ¥Close the terminal window and the Excel window. Do not save the Excel file.

  2. 在新的终端窗口中，再次启动开发过程：

    ¥In a new terminal window, start the development process again:

npm run stop
npm start

  1. 在新的 Excel 窗口中，在单元格 E1 中输入公式 =SHEETJS.VERSION()。你应该看到类似于以下屏幕截图的内容：

    ¥In the new Excel window, enter the formula =SHEETJS.VERSION() in cell E1. You should see something similar to the following screenshot:

SHEETJS.VERSION output

这表明 SheetJS 库已经加载。

¥This indicates that the SheetJS library has been loaded.

在某些 MacOS 测试中，插件安装了 CONTOSO.VERSION。要强制刷新清单：

¥In some MacOS tests, the add-in installed CONTOSO.VERSION. To force a refresh of the manifest:

  1. 停止开发过程：

    ¥Stop the development process:

npm run stop

  1. 通过右键单击 Dock 中的 Excel 并选择 "退出" 来关闭 Excel 应用。

    ¥Close the Excel app by right-clicking Excel in the Dock and selecting "Quit".

  2. 重新启动开发过程：

    ¥Restart the development process:

npm start

  1. 激活插件的任务窗格（单击功能区中的 "显示任务窗格"）。

    ¥Activate the Task Pane for the addin (click "Show Task Pane" in the ribbon).

  2. 将鼠标悬停在插件的右上角附近，然后单击 i 图标。

    ¥Hover near the top-right corner of the addin and click the i icon.

  3. 单击 "清除 Web 缓存" 并等待片刻。

    ¥Click "Clear Web Cache" and wait a few moments.

从 Internet 获取文件​

¥Fetching Files from the Internet

  1. 将以下代码片段添加到 src\functions\functions.js 并保存：

    ¥Add the following code snippet to src\functions\functions.js and save:

src\functions\functions.js (add to end)
/**

 * Download file and write data

 * @customfunction

 * @param {string} url URL to fetch and parse

 * @returns {any[][]} Worksheet data
 */
export async function extern(url) {
  try {
    /* Fetch Data */
    const res = await fetch(url);

    /* Get Data */
    const ab = await res.arrayBuffer();

    /* Parse Data */
    var wb = XLSX.read(ab);

    /* get and return data */
    var ws = wb.Sheets[wb.SheetNames[0]]; // get first worksheet
    var aoa = XLSX.utils.sheet_to_json(ws, { header: 1 }); // get data as array of arrays
    return aoa;
  } catch(e) { return [[e.message || e]]; } // pass error back to Excel
}

  1. 关闭终端窗口和 Excel 窗口（不保存 Excel 文件）。

    ¥Close the terminal window and the Excel window (do not save the Excel file).

  2. 在新的终端窗口中，再次启动开发过程：

    ¥In a new terminal window, start the development process again:

npm run stop
npm start

  1. 在单元格 D1 中输入文本 https://xlsx.nodejs.cn/pres.numbers。在单元格 D2 中输入公式 =SHEETJS.EXTERN(D1)，然后按 Enter。

    ¥Enter the text https://xlsx.nodejs.cn/pres.numbers in cell D1. Enter the formula =SHEETJS.EXTERN(D1) in cell D2 and press Enter.

Excel 应提取数据并生成动态数组。工作表应与本页顶部的屏幕截图相符。

¥Excel should pull in the data and generate a dynamic array. The worksheet should match the screenshot at the top of this page.

SheetJS 专业版 提供可在 Excel 自定义函数和加载项中使用的附加功能。

¥SheetJS Pro offers additional features that can be used in Excel Custom Functions and Add-ins.

[^1]: 见 read 于 "读取文件"

¥See read in "Reading Files"