Muokkaa

Jaa

Work with Events using the Excel JavaScript API

  • Artikkeli

This article describes important concepts related to working with events in Excel and provides code samples that show how to register event handlers, handle events, and remove event handlers using the Excel JavaScript API.

Events in Excel

Each time certain types of changes occur in an Excel workbook, an event notification fires. By using the Excel JavaScript API, you can register event handlers that allow your add-in to automatically run a designated function when a specific event occurs. The following events are currently supported.

Event Description Supported objects
onActivated Occurs when an object is activated. Chart, ChartCollection, Shape, Worksheet, WorksheetCollection
onActivated Occurs when a workbook is activated. Workbook
onAdded Occurs when an object is added to the collection. ChartCollection, CommentCollection, TableCollection, WorksheetCollection
onAutoSaveSettingChanged Occurs when the autoSave setting is changed on the workbook. Workbook
onCalculated Occurs when a worksheet has finished calculation (or all the worksheets of the collection have finished). Worksheet, WorksheetCollection
onChanged Occurs when the data of individual cells or comments has changed. CommentCollection, Table, TableCollection, Worksheet, WorksheetCollection
onColumnSorted Occurs when one or more columns have been sorted. This happens as the result of a left-to-right sort operation. Worksheet, WorksheetCollection
onDataChanged Occurs when data or formatting within the binding is changed. Binding
onDeactivated Occurs when an object is deactivated. Chart, ChartCollection, Shape, Worksheet, WorksheetCollection
onDeleted Occurs when an object is deleted from the collection. ChartCollection, CommentCollection, TableCollection, WorksheetCollection
onFormatChanged Occurs when the format is changed on a worksheet. Worksheet, WorksheetCollection
onFormulaChanged Occurs when a formula is changed. Worksheet, WorksheetCollection
onMoved Occurs when a worksheet is moved within a workbook. WorksheetCollection
onNameChanged Occurs when the worksheet name is changed. Worksheet, WorksheetCollection
onProtectionChanged Occurs when the worksheet protection state is changed. Worksheet, WorksheetCollection
onRowHiddenChanged Occurs when the row-hidden state changes on a specific worksheet. Worksheet, WorksheetCollection
onRowSorted Occurs when one or more rows have been sorted. This happens as the result of a top-to-bottom sort operation. Worksheet, WorksheetCollection
onSelectionChanged Occurs when the active cell or selected range is changed. Binding, Table, Workbook, Worksheet, WorksheetCollection
onSettingsChanged Occurs when the Settings in the document are changed. SettingCollection
onSingleClicked Occurs when left-clicked/tapped action occurs in the worksheet. Worksheet, WorksheetCollection
onVisibilityChanged Occurs when the worksheet visibility is changed. Worksheet, WorksheetCollection

Events in preview

Note

The following events are currently available only in public preview. To use this feature, you must use the preview version of the Office JavaScript API library from the Office.js content delivery network (CDN). The type definition file for TypeScript compilation and IntelliSense is found at the CDN and DefinitelyTyped. You can install these types with npm install --save-dev @types/office-js-preview. For more information on our upcoming APIs, please visit Excel JavaScript API requirement sets.

Event Description Supported objects
onFiltered Occurs when a filter is applied to an object. Table, TableCollection, Worksheet, WorksheetCollection

Event triggers

Events within an Excel workbook can be triggered by:

  • User interaction via the Excel user interface (UI) that changes the workbook
  • Office Add-in (JavaScript) code that changes the workbook
  • VBA add-in (macro) code that changes the workbook

Any change that complies with default behavior of Excel will trigger the corresponding events in a workbook.

Lifecycle of an event handler

An event handler is created when an add-in registers the event handler. It is destroyed when the add-in unregisters the event handler or when the add-in is refreshed, reloaded, or closed. Event handlers do not persist as part of the Excel file, or across sessions with Excel on the web.

Caution

When an object to which events are registered is deleted (e.g., a table with an onChanged event registered), the event handler no longer triggers but remains in memory until the add-in or Excel session refreshes or closes.

Events and coauthoring

With coauthoring, multiple people can work together and edit the same Excel workbook simultaneously. For events that can be triggered by a coauthor, such as onChanged, the corresponding Event object will contain a source property that indicates whether the event was triggered locally by the current user (event.source == Local) or was triggered by the remote coauthor (event.source == Remote).

Register an event handler

The following code sample registers an event handler for the onChanged event in the worksheet named Sample. The code specifies that when data changes in that worksheet, the handleChange function should run.

await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getItem("Sample");
    worksheet.onChanged.add(handleChange);

    await context.sync();
    console.log("Event handler successfully registered for onChanged event in the worksheet.");
}).catch(errorHandlerFunction);

Handle an event

As shown in the previous example, when you register an event handler, you indicate the function that should run when the specified event occurs. You can design that function to perform whatever actions your scenario requires. The following code sample shows an event handler function that simply writes information about the event to the console.

async function handleChange(event) {
    await Excel.run(async (context) => {
        await context.sync();        
        console.log("Change type of event: " + event.changeType);
        console.log("Address of event: " + event.address);
        console.log("Source of event: " + event.source);       
    }).catch(errorHandlerFunction);
}

Remove an event handler

The following code sample registers an event handler for the onSelectionChanged event in the worksheet named Sample and defines the handleSelectionChange function that will run when the event occurs. It also defines the remove() function that can subsequently be called to remove that event handler. Note that the RequestContext used to create the event handler is needed to remove it.

let eventResult;

async function run() {
  await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getItem("Sample");
    eventResult = worksheet.onSelectionChanged.add(handleSelectionChange);

    await context.sync();
    console.log("Event handler successfully registered for onSelectionChanged event in the worksheet.");
  });
}

async function handleSelectionChange(event) {
  await Excel.run(async (context) => {
    await context.sync();
    console.log("Address of current selection: " + event.address);
  });
}

async function remove() {
  // The `RequestContext` used to create the event handler is needed to remove it.
  // In this example, `eventContext` is being used to keep track of that context.
  await Excel.run(eventResult.context, async (context) => {
    eventResult.remove();
    await context.sync();
    
    eventResult = null;
    console.log("Event handler successfully removed.");
  });
}

Enable and disable events

The performance of an add-in may be improved by disabling events. For example, your app might never need to receive events, or it could ignore events while performing batch-edits of multiple entities.

Events are enabled and disabled at the runtime level. The enableEvents property determines if events are fired and their handlers are activated.

The following code sample shows how to toggle events on and off.

await Excel.run(async (context) => {
    context.runtime.load("enableEvents");
    await context.sync();

    let eventBoolean = !context.runtime.enableEvents;
    context.runtime.enableEvents = eventBoolean;
    if (eventBoolean) {
        console.log("Events are currently on.");
    } else {
        console.log("Events are currently off.");
    }
    
    await context.sync();
});

See also