使用 Excel JavaScript API 处理工作表

本文中的代码示例展示了如何使用 Excel JavaScript API 对工作表执行常见任务。 有关 和 对象支持的属性和方法Worksheet的完整列表，请参阅 Worksheet Object (JavaScript API for Excel) 和 WorksheetCollection Object (JavaScript API for Excel) 。WorksheetCollection

注意

本文中的信息仅适用于常规工作表；不适用于“图表”或“宏”表。

获取工作表

下面的代码示例获取工作表集合，加载每个工作表的 name 属性，并向控制台写入一条消息。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items/name");

    await context.sync();
    
    if (sheets.items.length > 1) {
        console.log(`There are ${sheets.items.length} worksheets in the workbook:`);
    } else {
        console.log(`There is one worksheet in the workbook:`);
    }

    sheets.items.forEach(function (sheet) {
        console.log(sheet.name);
    });
});

注意

id工作表的 属性唯一标识给定工作簿中的工作表，即使重命名或移动工作表，其值也将保持不变。 从 Excel on Mac 中的工作簿中删除工作表时， id 已删除工作表的 可能会重新分配到随后创建的新工作表。

获取活动工作表

下面的代码示例获取活动工作表，加载其 name 属性，并向控制台写入一条消息。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.load("name");

    await context.sync();
    console.log(`The active worksheet is "${sheet.name}"`);
});

设置活动工作表

下面的代码示例将活动工作表设置为名为 Sample 的工作表，加载其 name 属性，并将消息写入控制台。 如果没有具有该名称的工作表，该方法 activate() 将引发错误 ItemNotFound 。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.activate();
    sheet.load("name");

    await context.sync();
    console.log(`The active worksheet is "${sheet.name}"`);
});

通过相对位置引用工作表

这些示例演示如何通过相对位置来引用工作表。

获取第一个工作表

下面的代码示例获取工作簿中的第一个工作表，加载其 name 属性，并向控制台中写入一条消息。

await Excel.run(async (context) => {
    let firstSheet = context.workbook.worksheets.getFirst();
    firstSheet.load("name");

    await context.sync();
    console.log(`The name of the first worksheet is "${firstSheet.name}"`);
});

获取最后一个工作表

下面的代码示例获取工作簿中的最后一个工作表，加载其 name 属性，并向控制台写入一条消息。

await Excel.run(async (context) => {
    let lastSheet = context.workbook.worksheets.getLast();
    lastSheet.load("name");

    await context.sync();
    console.log(`The name of the last worksheet is "${lastSheet.name}"`);
});

获取下一个工作表

下面的代码示例获取工作簿中活动工作表后面的工作表，加载其 name 属性，并将消息写入控制台。 如果活动工作表后没有工作表，该方法 getNext() 将引发错误 ItemNotFound 。

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    let nextSheet = currentSheet.getNext();
    nextSheet.load("name");

    await context.sync();
    console.log(`The name of the sheet that follows the active worksheet is "${nextSheet.name}"`);
});

获取上一个工作表

下面的代码示例获取工作簿中活动工作表前面的工作表，加载其 name 属性，并将消息写入控制台。 如果活动工作表之前没有工作表，该方法 getPrevious() 将引发错误 ItemNotFound 。

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    let previousSheet = currentSheet.getPrevious();
    previousSheet.load("name");

    await context.sync();
    console.log(`The name of the sheet that precedes the active worksheet is "${previousSheet.name}"`);
});

添加工作表

下面的代码示例将名为 Sample 的新工作表添加到工作簿，加载其 name 和 position 属性，并将消息写入控制台。 新工作表添加在现有全部工作表的后面。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;

    let sheet = sheets.add("Sample");
    sheet.load("name, position");

    await context.sync();
    console.log(`Added worksheet named "${sheet.name}" in position ${sheet.position}`);
});

复制现有工作表

Worksheet.copy 通过复制现有工作表添加新工作表。 新工作表的名称将在末尾附加一个数字，格式与通过 Excel UI 复制工作表一致（例如 MySheet (2)）。 Worksheet.copy 可采用两个参数，且两者都是可选参数：

• positionType - 一个 WorksheetPositionType 枚举，指定在工作簿中添加新工作表的位置。
• relativeTo - 如果 positionType 为 Before 或 After，则需要指定一个参考工作表，新工作表将相对于此工作表进行添加（此参数回答的问题是“在什么之前或之后？”）。

下面的代码示例复制当前工作表，并将新工作表直接插入到当前工作表之后。

await Excel.run(async (context) => {
    let myWorkbook = context.workbook;
    let sampleSheet = myWorkbook.worksheets.getActiveWorksheet();
    let copiedSheet = sampleSheet.copy(Excel.WorksheetPositionType.after, sampleSheet);
    await context.sync();
});

删除工作表

下面的代码示例删除工作簿中的最后一个工作表（前提是它不是工作簿中的唯一工作表），并向控制台写入一条消息。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items/name");

    await context.sync();
    if (sheets.items.length === 1) {
        console.log("Unable to delete the only worksheet in the workbook");
    } else {
        let lastSheet = sheets.items[sheets.items.length - 1];

        console.log(`Deleting worksheet named "${lastSheet.name}"`);
        lastSheet.delete();

        await context.sync();
    }
});

注意

不能使用 delete 方法删除可见性为 VeryHidden 的工作表。 如果仍希望删除工作表，必须先更改可见性。

重命名工作表

下面的代码示例将活动工作表的名称更改为新名称。

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    currentSheet.name = "New Name";

    await context.sync();
});

移动工作表

下面的代码示例将工作表从工作簿中的最后一个位置移动到工作簿中的第一个位置。

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items");
    await context.sync();

    let lastSheet = sheets.items[sheets.items.length - 1];
    lastSheet.position = 0;
    await context.sync();
});

设置工作表可见性

以下示例显示如何设置工作表的可见性。

隐藏工作表

下面的代码示例将名为 Sample 的工作表的可见性设置为隐藏，加载其 name 属性，并向控制台写入一条消息。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.visibility = Excel.SheetVisibility.hidden;
    sheet.load("name");

    await context.sync();
    console.log(`Worksheet with name "${sheet.name}" is hidden`);
});

取消隐藏工作表

下面的代码示例将名为 Sample 的工作表的可见性设置为可见，加载其 name 属性，并向控制台写入一条消息。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.visibility = Excel.SheetVisibility.visible;
    sheet.load("name");

    await context.sync();
    console.log(`Worksheet with name "${sheet.name}" is visible`);
});

获取工作表中的单个单元格

下面的代码示例从名为 Sample 的工作表获取位于第 2 行第 5 列的单元格，加载其 address 和 values 属性，并向控制台写入一条消息。 传递给 getCell(row: number, column:number) 方法的值是要检索的单元格的零索引行号和列号。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let cell = sheet.getCell(1, 4);
    cell.load("address, values");

    await context.sync();
    console.log(`The value of the cell in row 2, column 5 is "${cell.values[0][0]}" and the address of that cell is "${cell.address}"`);
});

检测数据更改

加载项可能需要回应对工作表中的数据进行更改的用户。 若要检测这些更改，可以为工作表的 onChanged 事件注册事件处理程序。 当事件触发时，onChanged 事件的事件处理程序将收到 WorksheetChangedEventArgs 对象。

WorksheetChangedEventArgs 对象提供有关更改和来源的信息。 由于 onChanged 会在数据的格式或值发生变化时触发，因此让加载项检查值是否已实际更改可能很有用。 details 属性以 ChangedEventDetail 的形式封装此信息。 以下代码示例演示如何显示已更改的单元格的之前和之后的值及类型。

// This function would be used as an event handler for the Worksheet.onChanged event.
function onWorksheetChanged(eventArgs) {
    Excel.run(function (context) {
        let details = eventArgs.details;
        let address = eventArgs.address;

        // Print the before and after types and values to the console.
        console.log(`Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),`
            + ` now is ${details.valueAfter}(${details.valueTypeAfter})`);
        return context.sync();
    });
}

检测公式更改

加载项可以跟踪对工作表中公式的更改。 当工作表连接到外部数据库时，这很有用。 当工作表中的公式更改时，此方案中的事件会触发外部数据库中的相应更新。

若要检测对公式的更改， 请为 工作表的 onFormulaChanged 事件注册事件处理程序。 事件触发时， onFormulaChanged 事件的事件处理程序接收 WorksheetFormulaChangedEventArgs 对象。

重要

该 onFormulaChanged 事件检测公式本身何时更改，而不是公式计算产生的数据值。

下面的代码示例演示如何注册 onFormulaChanged 事件处理程序，如何使用 WorksheetFormulaChangedEventArgs 对象检索已更改公式的 formulaDetails 数组，然后使用 FormulaChangedEventDetail 属性输出有关已更改公式的详细信息。

注意

此代码示例仅在更改单个公式时有效。

async function run() {
    await Excel.run(async (context) => {
        // Retrieve the worksheet named "Sample".
        let sheet = context.workbook.worksheets.getItem("Sample");
    
        // Register the formula changed event handler for this worksheet.
        sheet.onFormulaChanged.add(formulaChangeHandler);
    
        await context.sync();
    });
}

async function formulaChangeHandler(event) {
    await Excel.run(async (context) => {
        // Retrieve details about the formula change event.
        // Note: This method assumes only a single formula is changed at a time. 
        let cellAddress = event.formulaDetails[0].cellAddress;
        let previousFormula = event.formulaDetails[0].previousFormula;
        let source = event.source;
    
        // Print out the change event details.
        console.log(
          `The formula in cell ${cellAddress} changed. 
          The previous formula was: ${previousFormula}. 
          The source of the change was: ${source}.`
        );         
    });
}

处理排序事件

onColumnSorted 和 onRowSorted 事件表示工作表数据已排序。 这些事件连接到各 Worksheet 对象和工作簿的 WorkbookCollection 无论是通过编程排序还是通过 Excel 用户界面手动执行排序，它们都会触发。

注意

通过从左到右排序操作对列排序时，触发 onColumnSorted 通过从上到下排序操作对行排序时，触发 onRowSorted 使用列标题上的下拉菜单对表格进行排序时，将触发 onRowSorted 事件。 该事件对应于正在移动的内容，而不是排序条件。

onColumnSorted 和 onRowSorted 事件为它们的回叫分别提供 WorksheetColumnSortedEventArgs 或 WorksheetRowSortedEventArgs 它们提供有关事件的更多详细信息。 特别的一点是，两个 EventArgs 都有 address 属性，表示排序操作移动的行或列。 已添加包含排序内容的所有单元格，即使单元格的值未包含在排序条件中，也是如此。

下图显示了排序事件的 address 属性返回的范围。 首先是排序前的示例数据：

如果在“Q1” (“B”) 中的值执行从上到下排序，则 以下突出显示的行由 WorksheetRowSortedEventArgs.address返回。

如果在原始数据上对“Quinces” (“4”) 中的值执行从左到右排序，则 以下突出显示的列由 WorksheetColumnsSortedEventArgs.address返回。

下面的代码示例演示如何为 Worksheet.onRowSorted 事件注册事件处理程序。 处理程序的回叫会清除该范围的填充颜色，然后填充已移动行的单元格。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // This will fire whenever a row has been moved as the result of a sort action.
    sheet.onRowSorted.add(async (event) => {
        await Excel.run(async (context) => {
            console.log("Row sorted: " + event.address);
            let sheet = context.workbook.worksheets.getActiveWorksheet();

            // Clear formatting for section, then highlight the sorted area.
            sheet.getRange("A1:E5").format.fill.clear();
            if (event.address !== "") {
                sheet.getRanges(event.address).format.fill.color = "yellow";
            }

            await context.sync();
        });
    });

    await context.sync();
});

查找所有包含匹配文本的单元格

Worksheet 对象具有 findAll 方法在工作表内搜索指定字符串。 返回 RangeAreas 对象，也就是可以进行一次性全部编辑的 Range 对象集。

以下代码示例查找值等于字符串 完成 的所有单元格，并标记为绿色。 请注意， findAll 如果工作表中不存在指定的字符串，则会引发 ItemNotFound 错误。 如果不确定工作表中是否存在指定的字符串，请使用 findAllOrNullObject 方法正常处理该方案。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let foundRanges = sheet.findAll("Complete", {
        completeMatch: true, /* Match the whole cell value, not any part of the text. */
        matchCase: false /* Make the search case-insensitive. */
    });

    await context.sync();
    foundRanges.format.fill.color = "green";
});

注意

本部分介绍如何使用 Worksheet 对象的 方法查找单元格和区域。 更多区域检索信息可在特定对象文章中找到。

• 有关演示如何使用 Range 对象获取工作表中的区域的示例，请参阅 使用 Excel JavaScript API 获取区域。
• 有关展示如何从 Table 对象获取区域的示例，请参阅 使用 Excel JavaScript API 处理表。
• 有关显示如何基于单元格特性进行多个子区域的较大区域搜索示例，请参阅 使用 Excel 加载项同时处理多个区域。

筛选数据

自动筛选在工作表的一个范围内应用数据筛选器。 这是使用 创建， Worksheet.autoFilter.apply后者具有以下参数。

• range：应用筛选器的范围，指定为 Range 对象或字符串。
• columnIndex：从零开始的列索引，根据该索引评估筛选条件。
• criteria：FilterCriteria 对象，该对象确定应基于列的单元格筛选哪些行。

第一个代码示例显示如何将筛选器添加到工作表的已使用区域。 此筛选器将基于列 3 中的值，隐藏不在前 25% 内的条目。

// This method adds a custom AutoFilter to the active worksheet
// and applies the filter to a column of the used range.
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let farmData = sheet.getUsedRange();

    // This filter will only show the rows with the top 25% of values in column 3.
    sheet.autoFilter.apply(farmData, 3, { criterion1: "25", filterOn: Excel.FilterOn.topPercent });
    await context.sync();
});

下一个代码示例显示如何使用 reapply 方法刷新 auto-filter。 当范围中的数据更改时，应执行此操作。

// This method refreshes the AutoFilter to ensure that changes are captured.
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.autoFilter.reapply();
    await context.sync();
});

下面的代码示例演示如何使用 clearColumnCriteria 方法仅清除一列中的自动筛选器，同时使筛选器在其他列上保持活动状态。

// This method clears the AutoFilter setting from one column.
await Excel.run(async (context) => {
    // Retrieve the active worksheet.
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // Clear the filter from only column 3.
    sheet.autoFilter.clearColumnCriteria(3);
    await context.sync();
});

最终的自动筛选代码示例显示如何使用 remove 方法将 auto-filter 从工作表移除。

// This method removes all AutoFilters from the active worksheet.
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.autoFilter.remove();
    await context.sync();
});

AutoFilter 也可应用到单个表。 有关详细信息，请参阅使用 Excel JavaScript API 处理表。

数据保护

加载项可以控制用户能否编辑工作表中的数据。 工作表的 protection 属性是包含 protect() 方法的 WorksheetProtection 对象。 下面的示例展示了关于切换活动工作表的完整保护的基本方案。

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

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

protect 方法包含两个可选参数：

• options：定义具体编辑限制的 WorksheetProtectionOptions 对象。
• password：表示用户规避保护并编辑工作表所需使用的密码的字符串。

保护工作表一文详细介绍了工作表保护，以及如何通过 Excel UI 更改保护。

检测工作表保护状态的更改

工作表的保护状态可以通过加载项或通过 Excel UI 进行更改。 若要检测对保护状态的更改，请为onProtectionChanged工作表的 事件注册事件处理程序。 事件的事件处理程序在 onProtectionChanged 事件触发时接收 WorksheetProtectionChangedEventArgs 对象。

下面的代码示例演示如何注册 onProtectionChanged 事件处理程序并使用 WorksheetProtectionChangedEventArgs 对象检索 isProtected事件的 、 worksheetId和 source 属性。

// This function registers an event handler for the onProtectionChanged event of a worksheet.
async function run() {
    await Excel.run(async (context) => {
        // Retrieve the worksheet named "Sample".
        let sheet = context.workbook.worksheets.getItem("Sample");
    
        // Register the onProtectionChanged event handler.
        sheet.onProtectionChanged.add(checkProtection);
        await context.sync();
    });
}

// This function is an event handler that returns the protection state of a worksheet 
// and information about the changed worksheet.
async function checkProtection(event) {
    await Excel.run(async (context) => {
        // Retrieve the protection, worksheet ID, and source properties of the event.
        let protectionStatus = event.isProtected;
        let worksheetId = event.worksheetId;
        let source = event.source;

        // Print the event properties to the console.
        console.log("Protection status changed. Protection status is now: " + protectionStatus);
        console.log("    ID of changed worksheet: " + worksheetId);
        console.log("    Source of change event: " + source);    
    });
}

页面布局和打印设置

加载项可以在工作表级别访问页面布局设置。 这些控制打印工作表的方式。 Worksheet 对象有三个与布局相关的属性：horizontalPageBreaks、verticalPageBreaks、pageLayout。

Worksheet.horizontalPageBreaks 和 Worksheet.verticalPageBreaks 是 PageBreakCollections。 这些是 PageBreaks 的集合，其中指定插入手动分页符的范围。 以下代码示例在第 21 行上方添加了水平分页符。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.horizontalPageBreaks.add("A21:E21"); // The page break is added above this range.
    await context.sync();
});

Worksheet.pageLayout 是 PageLayout 对象。 此对象包含不依赖于任何打印机特定实现的布局和打印设置。 这些设置包括页边距、方向、页码编号、标题行，并打印区域。

以下代码示例使页面居中（垂直和水平），设置将在每页顶部打印的标题行，并将打印区域设置为工作表的子部分。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // Center the page in both directions.
    sheet.pageLayout.centerHorizontally = true;
    sheet.pageLayout.centerVertically = true;

    // Set the first row as the title row for every page.
    sheet.pageLayout.setPrintTitleRows("$1:$1");

    // Limit the area to be printed to the range "A1:D100".
    sheet.pageLayout.setPrintArea("A1:D100");

    await context.sync();
});

另请参阅

• Excel 加载项中的 Word JavaScript 对象模型