Bind the Selected Data in Excel Sheet Using Visual Studio 2012

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:

  1. First start Visual Studio 2012 RC.
     
  2. Now click on the File menu and choose New project.
     
  3. A New Project Dialog box opens like this:

    vs12.jpg
     
  4. In this just expand the Visual C# node and under it just expand the Office/SharePoint node; see:

    vs3.jpg
     
  5. Now select the Apps option from it and select the App for Office 2013 in the center pane.

    vs4.jpg
     
  6. Now give the name to the application and click on the ok button.

    vs5.jpg
     
  7. 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.

    vs6.jpg
     
  8. Now Visual Studio creates the project and it will display in Solution Explorer.

    vs133.jpg
     
  9. Now develop the application and to design the appearance of the app we will add HTML code in the default page of the project.
     
  10. 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> 
       
    <!-- Add your JavaScript to the following file -->
       
    <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>
     
     
  11. 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.

    vs144.jpg
     
  12. Now click the Office option and under it just open the contentapp.js to show the default JavaScript file for the application.

    vs145.jpg
     
  13. 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.
     
  14. Now on the Debug menu just select the start debugging option from it or press the F5 key.

    vs10.jpg
     
  15. Now the following output window will appear.

    contentapp1.jpg
     
  16. 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:

    contentapp2.jpg
     
  17. Now while selecting the content click on the Add Event Handler button; the output will be like this:

    contentapp3.jpg
     
  18. Now click somewhere else in the Excel sheet and click on the Read Bound content button; the output will be like this:

    contentapp4.jpg
     
  19. If we change the previous data and press the enter key the output will be like this:

    contentapp5.jpg

    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
    Learn
    View all