Bind the Selected Data in Excel Sheet Using Visual Studio 2012

Megha Goyal
12y
13.1k
0
0

Article

In this article we will discuss how to create a binding of the currently selected data in the Excel sheet and read the bound data and show it in the application's UI. In it we will add an event handler to read and show the data whenever data binding will be modified.

Let's have a look at the following applications:

First start Visual Studio 2012 RC.
Now click on the File menu and choose New project.
A New Project Dialog box opens like this:
In this just expand the Visual C# node and under it just expand the Office/SharePoint node; see:
Now select the Apps option from it and select the App for Office 2013 in the center pane.
Now give the name to the application and click on the ok button.
The Create App for office dialog box will display. In it by default the Task pane app option is selected just click on the Finish button.
Now Visual Studio creates the project and it will display in Solution Explorer.
Now develop the application and to design the appearance of the app we will add HTML code in the default page of the project.
In it just remove the HTML code under the body tag and replace it with the following code to add the Bind Selected content, Read Bound content and Add Event Handler in the task pane.

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=Edge"/>
    <title>contentapp</title>
    <link rel="stylesheet" type="text/css" href="../Content/Office.css" />
    <link rel="stylesheet" type="text/css" href="../Content/App.css" />
    <script src="../Scripts/jquery-1.6.2.js"></script>
    <script src="../Scripts/Office/MicrosoftAjax.js"></script>
    <script src="../Scripts/Office/Office.js"></script>
    
    <script src="../Scripts/contentapp.js"></script>
</head>
<body>
    <div id="Content">
    <button onclick="bindData()">Bind Selected content </button><br/>
    <button onclick="readBoundData()"> Read Bound content </button><br/>
    <button onclick="addEvent()">Add Event Handler </button><br/>
    <span>Outputs: </span><div id="outputs"></div>
    </div>
</body>
</html>
Now to handle the event for those buttons; in other words, we will write the following function for binding the selected data and read that bound data. Just expand the Scripts option in the Solution Explorer window.
Now click the Office option and under it just open the contentapp.js to show the default JavaScript file for the application.
Now add the following functions to the contentapp.js file like this:

// Add any initialization logic to this function.
Office.initialize = function (reason) {

    // Checks for the DOM to load.
    $(document).ready(function () {
        $("#getDatabtn").click(function () { getData("selectedData"); });

        // Checks if setSelectedDataAsync is supported and adds appropriate click handler
        if (Office.context.document.setSelectedDataAsync) {
            $("#setDatabtn").click(function () { setData("Sample data"); });
        }
        else {
            $("#setDatabtn").remove();
        }
    });
}

// Writes data to current selection.
function setData(dataToInsert) {
    Office.context.document.setSelectedDataAsync(dataToInsert);
}

// Reads data from current selection.
function getData(elementIdToUpdate) {
    Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
    function (result) {
        if (result.status == "succeeded") {
            document.getElementById(elementIdToUpdate).value = result.value;
        }
    });
}
function writeToPage(text) {
    document.getElementById('outputs').innerText = text;
}
function bindData() {
    Office.context.document.bindings.addFromSelectionAsync("matrix", { id: 'bindingdata' },        function (asyncResult) {

            if (asyncResult.status === "failed") {

                writeToPage('Error: ' + asyncResult.error.message);

            } else {

                writeToPage('Added binding with type: ' + asyncResult.value.type + ' and id: ' +

                    asyncResult.value.id);

            }

        });

function readBoundData() {
    Office.select("bindings#bindingdata").getDataAsync({ coercionType: "matrix" },
        function (asyncResult) {
            if (asyncResult.status == "failed") {
                writeToPage('Error: ' + asyncResult.error.message);
            } else {
                writeToPage('Selected data: ' + asyncResult.value);

            }
        });
}
function addEvent() {
    Office.select("bindings#bindingdata").addHandlerAsync("bindingDataChanged", myHandler, function (asyncResult) {
        if (asyncResult.status == "failed") {
            writeToPage('Error: ' + asyncResult.error.message);
        } else {
            writeToPage('Added event handler');
        }
    });
}
function myHandler(eventArgs) {
    eventArgs.binding.getDataAsync({ coerciontype: "matrix" }, function (asyncResult) {

        if (asyncResult.status == "failed") {
            writeToPage('Error: ' + asyncResult.error.message);
        } else {
            writeToPage('Bound data: ' + asyncResult.value);
        }
    });
}

In it the bindcontent() method will call to the bindings.addFromSelectionAsync() method to create a binding of the matrix with an id called bindingdata which is associated with the cells the user selects.
Now on the Debug menu just select the start debugging option from it or press the F5 key.
Now the following output window will appear.
Just write some content in some cells on the Excel sheet and click on the Bind Selected content button and the output will be as follows:
Now while selecting the content click on the Add Event Handler button; the output will be like this:
Now click somewhere else in the Excel sheet and click on the Read Bound content button; the output will be like this:
If we change the previous data and press the enter key the output will be like this:

This is because we have created the event handler for the datachanged event and when the data is changed it will handle this event and will show the changed data associated with the particular id bound by the selected data.

Up Next

Ebook Download

View all

Office Development

Read by 0 people

Download Now!

Learn

View all