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

测试部署

此演示最后一次于 2024 年 8 月 11 日针对 Excel 365（版本 2407）进行测试。

¥This demo was last tested on 2024 August 11 against Excel 365 (version 2407).

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) { return [[e.message || e]]; } // pass error back to Excel
}

完整演示​

¥Complete Demo

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

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

  • 打开文件资源管理器

    ¥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. 安装 NodeJS 后，启动一个新的 PowerShell 窗口。

    ¥After installing NodeJS, launch a new PowerShell 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 taskpane 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. 在 PowerShell 窗口中，再次启动开发过程：

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

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. 在 PowerShell 窗口中，再次启动开发过程：

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

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.

从 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. 在 PowerShell 窗口中，再次启动开发过程：

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

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"