Skip to main content

Excel JavaScript API

This demo focuses on the JavaScript API included with Excel. For reading and writing Excel files, other demos cover a wide variety of use cases此演示主要介绍Excel附带的JavaScript API。对于读取和写入Excel文件，其他演示涵盖了各种各样的用例

Office 2016 introduced a JavaScript API for interacting with the application. It offers solutions for custom functions as well as task panes.Office 2016引入了用于与应用程序交互的JavaScript API。它提供了自定义功能和任务窗格的解决方案。

Excel currently does not provide support for working with Apple Numbers files and some legacy file formats. SheetJS fills the gap.Excel目前不支持使用Apple Numbers文件和一些旧文件格式。SheetJS填补了这一空白。

This demo creates a new custom function SHEETJS.EXTERN() which tries to fetch an external spreadsheet and insert the data into the worksheet.这个演示创建了一个新的自定义函数SHEETJS.EXTERN()，它试图获取一个外部电子表格并将数据插入到工作表中。

`SHEETJS.EXTERN` output

This demo focuses on the basic mechanics. Advanced topics like Excel Custom Function parameters are covered in the official Office JavaScript API docs.此演示侧重于基本机制。高级主题，如Excel自定义函数参数，包含在官方Office JavaScript API文档中。

note

This demo was last tested on 2023 September 03 against Excel 365 (version 2308)此演示最后一次测试是在2023年9月3日，针对Excel 365（版本2308）

Excel Bugs

There was a binary data bug affecting fetch and Excel. 存在影响fetch和Excel的二进制数据错误。It was resolved in version 2303. It is strongly encouraged to upgrade to the latest version of Excel 365 before running the demo.在版本2303中解决了此问题。强烈建议在运行演示之前升级到Excel 365的最新版本。

Integration Details集成细节​

The NodeJS module can be imported in an Excel Custom Functions project.NodeJS模块可以在Excel自定义函数项目中导入。

The sheet_to_json helper function can generate arrays of arrays of values based on the worksheet data. Excel custom functions transparently treat these as Dynamic Arrays.sheet_to_json辅助函数可以根据工作表数据生成值数组。Excel自定义函数透明地将这些函数视为动态数组。

This example fetches a file, parses the data, and extracts the first worksheet:此示例获取文件，解析数据，并提取第一个工作表：

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

/**
 * Download file and write data
 * @customfunction
 * @param {string} url URL to fetch and parse
 * @returns {any[][]} Worksheet data
 */
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 }); // array of arrays
    return aoa;
  } catch(e) { return [[e.message || e]]; } // pass error back to Excel
}

Complete Demo完整演示文档​

0) Clear the functions cache. For the tested version of Excel:清除函数缓存。对于Excel的测试版本：

  • Open File Explorer打开文件资源管理器
  • Select the address bar and enter 选择地址栏并输入%LOCALAPPDATA%\Microsoft\Office\16.0\Wef
  • Delete the CustomFunctions folder (if it exists) and empty Recycle Bin.删除CustomFunctions文件夹（如果存在）并清空回收站。

This will delete all custom functions associated with the user account!这将删除与用户帐户关联的所有自定义功能！

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.要保留用户帐户上的自定义函数，请在测试前将现有文件夹重命名为CustomFunctionsBackup，并在测试完成后重命名回CustomFunctions。

1) Install NodeJS LTS.安装NodeJS LTS。

2) Install dependencies in a new PowerShell window:在新的PowerShell窗口中安装依赖项：

npm i -g yo bower generator-office

Creating a new Add-in创建新的加载项​

3) Run yo office from the command line. It will ask a few questions:从命令行运行yo office。它将提出几个问题：

  • "Choose a project type": "Excel Custom Functions using a Shared Runtime"“选择项目类型”：“使用共享运行时的Excel自定义函数”

  • "Choose a script type": "JavaScript",“选择脚本类型”：“JavaScript”，

  • "What do you want to name your add-in?": "SheetJSImport"“您想给加载项命名什么？”：“SheetJSImport”

4) Start the dev process:启动开发过程：

cd SheetJSImport
npm run build
npm start

Running npm start will open up a terminal window and a new Excel window with the loaded add-in. 运行npm start将打开一个终端窗口和一个带有加载的加载项的新Excel窗口。Keep the terminal window open.保持终端窗口打开。

5) In manifest.xml , search for Functions.NameSpace . 在manifest.xml中，搜索Functions.NameSpace。There will be an XML element with name bt:String. Change the DefaultValue attribute to SHEETJS:将有一个名为bt:String的XML元素。将DefaultValue属性更改为SHEETJS：

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

6) Close the Excel window and the terminal window, then run npm start again.关闭Excel窗口和终端窗口，然后再次运行npm start。

Integrating the SheetJS Library集成SheetJS库​

7) Install the SheetJS library in the project在项目中安装SheetJS库

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

8) Replace src\functions\functions.js with the following:将src\functions\functions.js替换为以下内容：

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

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

9) After making the change, save the files. Close the terminal window and the Excel window (do not save the Excel file). Re-run npm start.进行更改后，请保存文件。关闭终端窗口和Excel窗口（不要保存Excel文件）。重新运行npm start。

10) In the new Excel window, enter the formula =SHEETJS.VERSION() in cell D1. You should see something similar to the following screenshot:在新的Excel窗口中，在单元格D1中输入公式=SHEETJS.VERSION()。您应该看到类似于以下屏幕截图的内容：

`SHEETJS.VERSION` output

This indicates that the SheetJS library has been loaded.这表示SheetJS库已经加载。

Fetching Files from the Internet从Internet获取文件​

11) Add the following code snippet to src\functions\functions.js:将以下代码段添加到src\functions\functions.js中：

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
 */
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
}

12) After making the change, save the files. Close the terminal window and the Excel window (do not save the Excel file). Re-run npm start.进行更改后，请保存文件。关闭终端窗口和Excel窗口（不要保存Excel文件）。重新运行npm start。

13) Enter the text https://sheetjs.com/pres.numbers in cell D1. 在单元格D1输入文本https://sheetjs.com/pres.numbers。Enter the formula =SHEETJS.EXTERN(D1) in cell D2 and press Enter. Excel should pull in the data and generate a dynamic array.在单元D2中输入公式=SHEETJS.EXTERN(D1)，然后按Enter键。Excel应该提取数据并生成一个动态数组。

SheetJS Pro offers additional features that can be used in Excel Custom Functions and Add-ins提供了可在Excel自定义函数和加载项中使用的其他功能