使用 Excel JavaScript API 处理工作簿

项目
03/26/2024

本文提供了代码示例，介绍如何使用 Excel JavaScript API 对工作簿执行常见任务。有关对象支持的属性和方法 Workbook 的完整列表，请参阅 Workbook Object (JavaScript API for Excel) 。此外，本文还介绍了通过 Application 对象执行的工作簿级别的操作。

Workbook 对象是加载项与 Excel 交互的入口点。它用于维护工作表、表、数据透视表等的集合，通过这些集合可以访问并更改 Excel 数据。加载项可以通过 WorksheetCollection 对象访问单个工作表内的所有工作簿数据。具体来说，加载项可以借助它添加工作表、在工作表间导航并向工作表分配处理程序。使用 Excel JavaScript API 处理工作表一文介绍了如何访问并编辑工作表。

获取活动单元格或选定范围

Workbook 对象包含两种获取用户或加载项所选定单元格范围的方法：getActiveCell() 和 getSelectedRange()。 getActiveCell() 将活动单元格作为 Range 对象来从工作簿中获取它。下列示例演示对 getActiveCell() 的调用，紧随其后的是打印到控制台的单元格地址。

await Excel.run(async (context) => {
    let activeCell = context.workbook.getActiveCell();
    activeCell.load("address");
    await context.sync();

    console.log("The active cell is " + activeCell.address);
});

getSelectedRange() 方法返回当前选定的单个范围。若选定多个范围，将引发 InvalidSelection 错误。下列示例演示对 getSelectedRange() 的调用，并且此方法随后会将相应范围的填充颜色设置为黄色。

await Excel.run(async (context) => {
    let range = context.workbook.getSelectedRange();
    range.format.fill.color = "yellow";
    await context.sync();
});

创建工作簿

加载项可以新建一个工作簿，并独立于当前运行加载项的 Excel 实例。 Excel 对象包含的 createWorkbook 方法可用于实现此目的。调用此方法时，会立即打开新的工作簿，并在新的 Excel 实例中显示它。加载项保持打开状态，并随之前的工作簿一起运行。

Excel.createWorkbook();

此外，createWorkbook 方法还可以创建现有工作簿的副本。此方法接受 .xlsx 文件的 base64 编码字符串表示形式作为可选参数。若字符串参数为有效的 .xlsx 文件，则生成的工作簿为该文件的副本。

可以使用文件切片将加载项的当前工作簿作为 base64 编码的字符串获取。可以使用 FileReader 类将文件转换为所需的 base64 编码字符串，如以下示例所示。

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (function (event) {
    Excel.run(function (context) {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);

        Excel.createWorkbook(externalWorkbook);
        return context.sync();
    });
});

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

将现有工作簿副本插入到当前工作簿中

上一示例显示从现有工作簿创建的新工作簿。此外，还可以将所有或部分现有工作簿复制到当前与加载项关联的工作簿中。 Workbook 具有insertWorksheetsFromBase64将目标工作簿的工作表副本插入到自身中的方法。另一个工作簿的文件作为 base64 编码的字符串传递，就像调用一 Excel.createWorkbook 样。

insertWorksheetsFromBase64(base64File: string, options?: Excel.InsertWorksheetOptions): OfficeExtension.ClientResult<string[]>;

重要

insertWorksheetsFromBase64 Windows、Mac 和 Web 上的 Excel 支持方法。 iOS 不支持它。此外，在Excel web 版此方法不支持具有数据透视表、图表、注释或切片器元素的源工作表。如果存在这些对象，则 insertWorksheetsFromBase64 方法将在 Excel web 版中返回UnsupportedFeature错误。

以下代码示例演示如何将另一个工作簿中的工作表插入当前工作簿。此代码示例首先处理具有 FileReader 对象的工作簿文件，并提取 base64 编码的字符串，然后将此 base64 编码的字符串插入当前工作簿。新工作表插入到名为 Sheet1 的工作表之后。请注意， [] 作为 InsertWorksheetOptions.sheetNamesToInsert 属性的参数传递。这意味着目标工作簿中的所有工作表都插入到当前工作簿中。

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (event) => {
    Excel.run((context) => {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);
            
        // Retrieve the current workbook.
        let workbook = context.workbook;
            
        // Set up the insert options. 
        let options = { 
            sheetNamesToInsert: [], // Insert all the worksheets from the source workbook.
            positionType: Excel.WorksheetPositionType.after, // Insert after the `relativeTo` sheet.
            relativeTo: "Sheet1" // The sheet relative to which the other worksheets will be inserted. Used with `positionType`.
        }; 
            
         // Insert the new worksheets into the current workbook.
         workbook.insertWorksheetsFromBase64(externalWorkbook, options);
         return context.sync();
    });
};

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

保护工作簿的结构

加载项可以控制用户编辑工作簿结构的能力。 Workbook 对象的 protection 属性是一个包含 protect() 方法的 WorkbookProtection 对象。下列示例演示切换对工作簿结构的保护的基本方案。

await Excel.run(async (context) => {
    let workbook = context.workbook;
    workbook.load("protection/protected");
    await context.sync();

    if (!workbook.protection.protected) {
        workbook.protection.protect();
    }
});

protect 方法接受一个可选字符串参数。此字符串表示用户要绕过保护并更改工作簿结构所需的密码。

此外，还可以在工作表级别设置保护，来防止不希望发生的数据编辑。有关详细信息，请参阅使用 Excel JavaScript API 处理工作表一文的“数据保护”部分。

注意

有关 Excel 中工作簿保护的详细信息，请参阅保护工作簿一文。

访问文档属性

Workbook 对象可以访问 Office 文件元数据，即文档属性。 Workbook 对象的 properties 属性是包含其中一些元数据值的 DocumentProperties 对象。以下示例演示如何设置 author 属性。

await Excel.run(async (context) => {
    let docProperties = context.workbook.properties;
    docProperties.author = "Alex";
    await context.sync();
});

此外，还可以定义自定义属性。 DocumentProperties 对象保护 custom 属性，它表示用户定义的属性的键值对集合。有关设置自定义属性的示例，请参阅保留加载项状态和设置一文中的 Excel 和 Word 自定义 XML 数据部分。

访问文档设置

工作簿的设置类似于自定义属性集合。区别在于：设置对于单个 Excel 文件和加载项配对而言是唯一的，而属性只是连接到文件。下列示例演示如何创建并访问设置。

await Excel.run(async (context) => {
    let settings = context.workbook.settings;
    settings.add("NeedsReview", true);
    let needsReview = settings.getItem("NeedsReview");
    needsReview.load("value");

    await context.sync();
    console.log("Workbook needs review : " + needsReview.value);
});

访问应用程序区域性设置

工作簿具有影响某些数据显示方式的语言和区域性设置。当加载项的用户跨不同语言和区域性共享工作簿时，这些设置可帮助本地化数据。外接程序可以使用字符串分析根据系统区域性设置本地化数字、日期和时间的格式，以便每个用户都能看到其所在区域性格式的数据。

Application.cultureInfo 将系统区域性设置定义为 CultureInfo 对象。这包含数字小数分隔符或日期格式等设置。

可以通过 Excel UI 更改某些区域性设置。系统设置保留在对象中 CultureInfo 。任何本地更改都保留为应用程序级属性，例如 Application.decimalSeparator。

以下示例将数字字符串的小数分隔符字符从“”更改为系统设置使用的字符。

// This will convert a number like "14,37" to "14.37"
// (assuming the system decimal separator is ".").
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let decimalSource = sheet.getRange("B2");

    decimalSource.load("values");
    context.application.cultureInfo.numberFormat.load("numberDecimalSeparator");
    await context.sync();

    let systemDecimalSeparator =
        context.application.cultureInfo.numberFormat.numberDecimalSeparator;
    let oldDecimalString = decimalSource.values[0][0];

    // This assumes the input column is standardized to use "," as the decimal separator.
    let newDecimalString = oldDecimalString.replace(",", systemDecimalSeparator);

    let resultRange = sheet.getRange("C2");
    resultRange.values = [[newDecimalString]];
    resultRange.format.autofitColumns();
    await context.sync();
});

控制计算行为

设置计算模式

默认情况下，当引用的单元格发生更改时，Excel 会重新计算公式结果。调整此计算行为可以改进加载项的性能。 Application 对象包含一个 CalculationMode 类型的 calculationMode 属性。可以将其设置为以下值。

automatic：默认的重新计算行为，每当相关数据发生更改时 Excel 都会计算新的公式结果。
automaticExceptTables：与 automatic 相同，但会忽略对表中值的任何更改。
manual：仅在用户或加载项请求计算时，才会进行计算。

设置计算类型

Application 对象提供了一个用于强制立即进行重新计算的方法。 Application.calculate(calculationType) 将基于指定的 calculationType 启动手动重新计算。可以指定以下值。

full：重新计算所有打开的工作簿中的所有公式，无论它们自上次重新计算后是否发生了更改。
fullRebuild：检查从属的公式，然后重新计算所有打开的工作簿中的所有公式，无论它们自上次重新计算后是否发生了更改。
recalculate：重新计算所有活动工作簿中自上次计算后发生更改（或已以编程方式将其标记为重新计算目标）的公式，以及从属于它们的公式。

注意

有关重新计算的详细信息，请参阅更改公式重新计算、迭代或精度一文。

暂停计算

借助 Excel API，加载项还可以在调用 RequestContext.sync() 前禁用计算。此操作通过 suspendApiCalculationUntilNextSync() 完成。加载项在编辑较大范围且无需访问两次编辑之间的数据时，使用此方法。

context.application.suspendApiCalculationUntilNextSync();

检测工作簿激活

加载项可以检测工作簿何时激活。当用户将焦点切换到另一个工作簿、另一个应用程序或 (Excel web 版) 到 Web 浏览器的另一个选项卡时，工作簿将变为非活动状态。当用户将焦点返回到工作簿时，将激活工作簿。工作簿激活可以触发外接程序中的回调函数，例如刷新工作簿数据。

若要检测工作簿何时激活，请为工作簿的 onActivated 事件注册事件处理程序。事件触发时， onActivated 事件的事件处理程序接收 WorkbookActivatedEventArgs 对象。

重要

该 onActivated 事件不会检测到工作簿何时打开。此事件仅检测用户何时将焦点切换回已打开的工作簿。

下面的代码示例演示如何注册 onActivated 事件处理程序和设置回调函数。

async function run() {
    await Excel.run(async (context) => {
        // Retrieve the workbook.
        let workbook = context.workbook;
    
        // Register the workbook activated event handler.
        workbook.onActivated.add(workbookActivated);
        await context.sync();
    });
}

async function workbookActivated(event) {
    await Excel.run(async (context) => {
        // Retrieve the workbook and load the name.
        let workbook = context.workbook;
        workbook.load("name");        
        await context.sync();

        // Callback function for when the workbook is activated.
        console.log(`The workbook ${workbook.name} was activated.`);
    });
}

保存工作簿

Workbook.save 会将工作簿保存到永久存储区。方法 save 采用单个可选 saveBehavior 参数，该参数可以是以下值之一。

Excel.SaveBehavior.save（默认）：保存文件，但不提示用户指示文件名和保存位置。如果之前未保存文件，则文件保存到默认位置。如果之前保存过文件，则保存到之前的位置。
Excel.SaveBehavior.prompt：如果之前未保存文件，则将提示用户指示文件名和保存位置。如果之前已保存文件，则保存到之前的位置且不提示用户。

警告

如果提示用户保存并取消操作，则 save 将引发异常。

context.workbook.save(Excel.SaveBehavior.prompt);

关闭工作簿

Workbook.close 会关闭工作簿，一并关闭与该工作簿关联的加载项（Excel 应用程序仍保持打开状态）。方法 close 采用单个可选 closeBehavior 参数，该参数可以是以下值之一。

Excel.CloseBehavior.save（默认）：在关闭前保存文件。如果之前未保存文件，则将提示用户指示文件名和保存位置。
Excel.CloseBehavior.skipSave：立即关闭文件但不保存。所有未保存的更改均将丢失。

context.workbook.close(Excel.CloseBehavior.save);