Skip to main content

Excel JavaScript APIExcel JavaScript API

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

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

This demo creates a new custom function to add much-needed functionality:此演示创建了一个新的自定义函数，以添加急需的功能：

  • SHEETJS.EXTERN() tries to fetch an external spreadsheet and insert the data into the worksheet.尝试获取外部电子表格并将数据插入工作表。

This demo focuses on the basic mechanics. 本演示侧重于基本力学。Advanced topics like Excel Custom Function parameters are covered in the official Office JavaScript API docs. Office JavaScript API文档中涵盖了Excel自定义函数参数等高级主题。SheetJS worksheet metadata and other properties are covered in this doc site.SheetJS工作表元数据和其他属性包含在本文档网站中。

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

Initial Platform Setup (click to show)

The tool for generating Office Add-ins depends on NodeJS and various libraries. 生成Office插件的工具取决于Node.js和各种库。Install NodeJS and the required dependencies:安装Node.js和所需的依赖项：

npm install -g yo bower generator-office
Creating a new Project创建新项目 (click to show)

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

  • "Choose a project type": "Excel Custom Functions Add-in project"“选择项目类型”：“Excel自定义函数加载项项目”

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

  • "What do you want to name your add-in?": "SheetJSImport"“您想将外接程序命名为什么？”：“SheetJSImport”

You will see a screen like你会看到这样的屏幕

? Choose a project type: Excel Custom Functions Add-in project
? Choose a script type: JavaScript
? What do you want to name your add-in? SheetJSImport

----------------------------------------------------------------------------------

      Creating SheetJSImport add-in for Excel using JavaScript and Excel-functions
at C:\Users\SheetJS\Documents\SheetJSImport

----------------------------------------------------------------------------------

It helpfully prints out the next steps:它有助于打印出接下来的步骤：

cd SheetJSImport
npm run build
npm start

If you have VSCodium installed, the folder can be opened with如果安装了VSCodium，则可以使用打开文件夹

codium .

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 (it can be minimized). 保持终端窗口打开（可以最小化）。When you make a change, close both the Excel window and the terminal window before running npm start again.进行更改时，请关闭Excel窗口和终端窗口，然后再次运行npm start。

Integrating the SheetJS Library集成SheetJS库​

The library can be installed like any other NodeJS module:该库可以像任何其他Node.js模块一样安装：

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

To be sure the library is loaded, remove all of the existing functions from src\functions\functions.js. 为了确保库已加载，请从src\functions\functions.js中删除所有现有函数。The new contents should be新内容应该是

var XLSX = require("xlsx");

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

The manifest.xml should also be updated to reflect the function namespace:manifest.xml也应更新以反映函数命名空间：

        <bt:String id="Functions.Namespace" DefaultValue="SHEETJS"/>

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

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

`SHEETJS.VERSION` output

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

Dynamic Arrays and SheetJS Array of Arrays动态数组和SheetJS数组​

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

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

For the next step, we will try to fetch data from an external resource. 下一步，我们将尝试从外部资源获取数据。https://sheetjs.com/pres.numbers is an Apple Numbers file. 是一个Apple数字文件。Excel does not understand Numbers files and it will not open them.Excel不理解数字文件，也不会打开它们。

Excel bug related to `fetch` (click to show)

fetch is available to custom functions:可用于自定义函数：

async function extern() {
  try {
    const url = "https://sheetjs.com/pres.numbers"; // URL to download
    const res = await fetch(url); // fetch data
    const ab = await res.arrayBuffer(); // get data as an array buffer

    // DO SOMETHING WITH THE DATA HERE

  } catch(e) { return e; } // pass error back to Excel
}

When fetching data, functions typically receive an ArrayBuffer which stores the file data. 在提取数据时，函数通常会接收一个存储文件数据的ArrayBuffer。This is readily parsed with read:这很容易用read进行解析：

var wb = XLSX.read(ab); // parse workbook

This is how it should work.这就是它应该如何工作。

There are outstanding bugs in Excel.Excel中有一些突出的错误。

For the purposes of this demo, a Base64-encoded file will be used. 在本演示中，将使用Base64编码文件。The workaround involves fetching that Base64 file, getting the text, and parsing with the base64 type:解决方法包括获取该Base64文件、获取文本以及使用base64类型进行解析：

async function extern() {
  try {
    const url = "https://sheetjs.com/pres.numbers.b64"; // URL to download
    const res = await fetch(url); // fetch data
    const text = await res.text(); // get data as an array buffer

    var wb = XLSX.read(text, { type: "base64" });
    // DO SOMETHING WITH THE DATA HERE

  } catch(e) { return e; } // pass error back to Excel
}

Base64-encoded files can be generated with PowerShell:可以使用PowerShell生成Base64编码文件：

[convert]::ToBase64String([System.IO.File]::ReadAllBytes((Resolve-Path "path\to\file"))) > file.b64

The .Sheets property of the workbook object holds all of the worksheets and the .SheetNames property is an array of worksheet names. 工作簿对象的.Sheets属性包含所有工作表，.SheetNames属性是工作表名称的数组。Picking the first worksheet is fairly straightforward:选择第一个工作表相当简单：

var ws = wb.Sheets[wb.SheetNames[0]]; // get first worksheet

This data can be converted to an Array of Arrays in one line:这些数据可以转换为一行中的数组：

var aoa = XLSX.utils.sheet_to_json(ws, {header: 1}); // get data as array of arrays

To demonstrate the parsing ability, a Base64-encoded version of the file will be used. 为了演示解析能力，将使用文件的Base64编码版本。This file contains no binary characters and should "just work". 该文件不包含二进制字符，应该“正常工作”。Once the aforementioned Excel bug is fixed, the non-Base64 version can be used.一旦上述Excel错误修复，就可以使用非Base64版本。

This new function should be added to src\functions\functions.js:此新函数应添加到src\functions\functions.js中：

/**
 * Download file and write data
 * @customfunction
 * @returns {any[][]} Worksheet data
 */
async function extern() {
  try {
    /* URL */
    // const url = "https://sheetjs.com/pres.numbers"; // Once Excel bug is fixed
    const url = "https://sheetjs.com/pres.numbers.b64"; // workaround

    /* Fetch Data */
    const res = await fetch(url);

    /* Get Data */
    // const ab = await res.arrayBuffer(); // Once Excel bug is fixed
    const b64 = await res.text(); // workaround

    /* Parse Data */
    // var wb = XLSX.read(ab); // Once Excel bug is fixed
    var wb = XLSX.read(b64, { type: "base64" }); // workaround

    /* 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 [[url]];
  } catch(e) { return [[e]]; } // pass error back to Excel
}

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

Enter the formula =SHEETJS.EXTERN() in cell D1 and hit Enter. 在单元格D1中输入公式=SHEETJS.EXTERN()，然后按Enter键。Excel should pull in the data and generate a dynamic array:Excel应拉入数据并生成动态数组：

`SHEETJS.VERSION` output

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