Read and write data to the active selection in a document or spreadsheet

The Document object exposes methods that let you read and write to the user's current selection in a document or spreadsheet. To do that, the Document object provides the getSelectedDataAsync and setSelectedDataAsync methods. This topic also describes how to read, write, and create event handlers to detect changes to the user's selection.

The getSelectedDataAsync method only works against the user's current selection. If you need to persist the selection in the document, so that the same selection is available to read and write across sessions of running your add-in, you must add a binding using the Bindings.addFromSelectionAsync method (or create a binding with one of the other "addFrom" methods of the Bindings object). For information about creating a binding to a region of a document, and then reading and writing to a binding, see Bind to regions in a document or spreadsheet.

Read selected data

The following example shows how to get data from a selection in a document by using the getSelectedDataAsync method.

Office.context.document.getSelectedDataAsync(Office.CoercionType.Text, function (asyncResult) {
    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
        write('Action failed. Error: ' + asyncResult.error.message);
    }
    else {
        write('Selected data: ' + asyncResult.value);
    }
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

In this example, the first coercionType parameter is specified as Office.CoercionType.Text (you can also specify this parameter by using the literal string "text"). This means that the value property of the AsyncResult object that is available from the asyncResult parameter in the callback function will return a string that contains the selected text in the document. Specifying different coercion types will result in different values. Office.CoercionType is an enumeration of available coercion type values. Office.CoercionType.Text evaluates to the string "text".

Tip When should you use the matrix versus table coercionType for data access? If you need your selected tabular data to grow dynamically when rows and columns are added, and you must work with table headers, you should use the table data type (by specifying the coercionType parameter of the getSelectedDataAsync method as "table" or Office.CoercionType.Table). Adding rows and columns within the data structure is supported in both table and matrix data, but appending rows and columns is supported only for table data. If you are you aren't planning on adding rows and columns, and your data doesn't require header functionality, then you should use the matrix data type (by specifying the coercionType parameter of getSelecteDataAsync method as "matrix" or Office.CoercionType.Matrix), which provides a simpler model of interacting with the data.

The anonymous function that is passed into the function as the second callback parameter is executed when the getSelectedDataAsync operation is completed. The function is called with a single parameter, asyncResult, which contains the result and the status of the call. If the call fails, the error property of the AsyncResult object provides access to the Error object. You can check the value of the Error.name and Error.message properties to determine why the set operation failed. Otherwise, the selected text in the document is displayed.

The AsyncResult.status property is used in the if statement to test whether the call succeeded. Office.AsyncResultStatus is an enumeration of available AsyncResult.status property values. Office.AsyncResultStatus.Failed evaluates to the string "failed" (and, again, can also be specified as that literal string).

Write data to the selection

The following example shows how to set the selection to show "Hello World!".

Office.context.document.setSelectedDataAsync("Hello World!", function (asyncResult) {
    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
        write(asyncResult.error.message);
    }
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

Passing in different object types for the data parameter will have different results. The result depends on what is currently selected in the document, which application is hosting your add-in, and whether the data passed in can be coerced to the current selection.

The anonymous function passed into the setSelectedDataAsync method as the callback parameter is executed when the asynchronous call is completed. When you write data to the selection by using the setSelectedDataAsync method, the asyncResult parameter of the callback provides access only to the status of the call, and to the Error object if the call fails.

Note: Starting with the release of the Excel 2013 SP1 and the corresponding build of Excel Online, you can now set formatting when writing a table to the current selection.

Detect changes in the selection

The following example shows how to detect changes in the selection by using the Document.addHandlerAsync method to add an event handler for the SelectionChanged event on the document.

Office.context.document.addHandlerAsync("documentSelectionChanged", myHandler, function(result){} 
);

// Event handler function.
function myHandler(eventArgs){
write('Document Selection Changed');
}

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

The first eventType parameter specifies the name of the event to subscribe to. Passing the string "documentSelectionChanged" for this parameter is equivalent to passing the Office.EventType.DocumentSelectionChanged event type of the Office.EventType enumeration.

The myHander() function that is passed into the function as the second handler parameter is an event handler that is executed when the selection is changed on the document. The function is called with a single parameter, eventArgs, which will contain a reference to a DocumentSelectionChangedEventArgs object when the asynchronous operation completes. You can use the DocumentSelectionChangedEventArgs.document property to access the document that raised the event.

Note You can add multiple event handlers for a given event by calling the addHandlerAsync method again and passing in an additional event handler function for the handler parameter. This will work correctly as long as the name of each event handler function is unique.

Stop detecting changes in the selection

The following example shows how to stop listening to the Document.SelectionChanged event by calling the document.removeHandlerAsync method.

Office.context.document.removeHandlerAsync("documentSelectionChanged", {handler:myHandler}, function(result){});

The myHandler function name that is passed as the second handler parameter specifies the event handler that will be removed from the SelectionChanged event.

Important: If the optional handler parameter is omitted when the removeHandlerAsync method is called, all event handlers for the specified eventType will be removed.

results matching ""

    No results matching ""