Write and Read Data in Spreadsheet by User Specified Range in Office App

Introduction 

 

This article explains how to write and read data in an Excel spreadsheet by a user specifying a range using a matrix and table type binding. 

 

Binding to regions in spreadsheet

 

Binding-based data access enables content and task pane apps to consistently access a specific region of a document or spreadsheet through an identifier.

 

When to use a matrix versus table bindings

 

Use table bindings: If you need the binding for your tabular data to grow dynamically when rows and columns are added and you must work with table headers then you should use the table data type. To do so, specify the coercionType parameter of an "addFrom" method as "table" or Office.CoercionType.Table. The apps for Office API supports adding rows and columns within the data structure for both table and matrix bindings, but only supports appending rows and columns to table bindings.

 

Use matrix bindings: If you are you aren't planning on adding rows and columns and your data doesn't require header functionality, then you should use matrix bindings. The apps for Office API provides a simpler model of interacting with the data when using matrix bindings. To do so, specify the coercionType parameter of an "addFrom" method as "matrix" or Office.CoercionType.Matrix. 

 

How to create a task pane Office app in C#

 

Please read my previous article about how to create a task pane Office app in Excel using C#.

 

Create Task Pane Office App in C# 

 

Write data using matrix binding type

 

In an Office app, we can write data in Excel using various binding types. In this article, we will write data in Excel. Matrix binding represents a binding in the two dimensions of rows and columns. 

 

The following is the procedure to write and read data using a matrix and table binding type.

 

Step 1 : Create a task pane app for Excel. Please refer to my previous article for creating a task pane app. Add three input buttons in Home.html as in the following:

1. The first button is for writing the data by table binding
2. The second button is for writing the data by matrix binding
3. The third button is for reading the data from the current selection 

<div id="content-main">
        <div class="padding">
            <p><strong>Write Data</strong></p>
            <button id="btnwriteDataFromRangeTable" class="positive">WriteData By TableType</button>
            <button id="btnwriteDataFromRangeMatrix" class="positive">WriteData By MatrixType</button>
            <button id="btnGetDataFromSelection" class="positive">GetDataFromSelection</button>
        </div>
        <div id="message"></div>
    </div>

 Step 2 : Once the buttons are added, write code for adding data by specifying a user name range in the spreadsheet using the addFromNamedItemAsync method. You can add this method in the home.js file.

 

The addFromNamedItemAsync method is used for adding a binding to a named item in the document. We need to pass the following parameters.

 

Office.context.document.bindings.addFromNamedItemAsync(itemName, bindingType [, options], callback);

 

Pass the range as a string (such as "A1:C7"), specify the binding type as "matrix", specify the unique name("DataMatrixId") to be used to identify the binding object. You can get a response as an asyncResult parameter in the callback function.

 

The setDataAsync method writes data to the bound section of the spreadsheet represented by the specified binding object. We can set data as the first parameter. I have added the three columns Name, State and City and added data in relevant columns and pass coercionType as a matrix as shown in the following code snippet.

 

bindingObj.setDataAsync(data [, options] ,callback);

// Write data from predefine range using matrix type
   function writeDatabySpecifiedRangeBymatrix() {
       Office.context.document.bindings.addFromNamedItemAsync("A1:C7", "matrix", { id: "DataMatrixId" },
           function (asyncResult) {
               if (asyncResult.status == "failed") {
                   write('Error: ' + asyncResult.error.message);
               }
               else {
                   // Write data to the new binding.
                   Office.select("bindings#DataMatrixId").setDataAsync([['Name', 'State', 'City'], ['Kim', 'New York',
'New York'], ['Junmin', 'California', 'Los Angeles'], ['Toni', 'Illinois', 'Chicago'], ['David', 'Texas', 'Houston'],
['Mark', 'Pennsylvania', 'Philadelphia'], ['Jules', 'Florida', 'Jacksonville']], { coercionType: "matrix" },
                       function (asyncResult) {
                           if (asyncResult.status == "failed") {
                               write('Error: ' + asyncResult.error.message);
                           }
                       });
               }
           });
   }

We will call the writeDatabySpecifiedRangeBymatrix method under the app.initialize() method as shown in the following code snippet. Once the user clicks the button, the method will be invoked.

// The initialize function must be run each time a new page is loaded
   Office.initialize = function (reason) {
       $(document).ready(function () {
           app.initialize();

           // Call WriteData by Specified Range by matrix
           $('#btnwriteDataFromRangeMatrix').click(writeDatabySpecifiedRangeBymatrix);

           // Call WriteData by Specified Range by table
           $('#btnwriteDataFromRangeTable').click(writeDatabySpecifiedRangeBytable);

           // Get Data from sheet
           $('#btnGetDataFromSelection').click(getDataFromSelection);
       });
   };

Build your project and run it. Click on the WriteData By MatrixType button in the task pane. You can see the data render in the "A1:C7" range that we specified in the addFromNamedItemAsync method.

 

 

 

Step 3 : We will do the same procedure for the table binding type. We will discuss how to write data as a table binding type. Data in a table binding is written or read as a TableData object. The TableData object exposes the data through the headers and rows properties. Write the method in home.js as shown in the following code snippet. 

// Write data from predefine range using table type
    function writeDatabySpecifiedRangeBytable() {
        Office.context.document.bindings.addFromNamedItemAsync("Table1", "table", { id: "DataTableId" },
            function (asyncResult) {
                if (asyncResult.status == "failed") {
                    write('Error: ' + asyncResult.error.message);
                }
                else {

                    //Create a TableData object.
                    var dataTable = new Office.TableData();
                    dataTable.headers = ['Name', 'State', 'City'];
                    dataTable.rows = [['Kim', 'New York', 'New York'], ['Junmin', 'California',
'Los Angeles'], ['Toni', 'Illinois', 'Chicago'], ['David', 'Texas', 'Houston'], ['Mark', 'Pennsylvania',
'Philadelphia'], ['Jules', 'Florida', 'Jacksonville']];

                    // Write data to the new binding.
                    Office.select("bindings#DataTableId").setDataAsync(dataTable, { coercionType: "table" },
                        function (asyncResult) {
                            if (asyncResult.status == "failed") {
                                write('Error: ' + asyncResult.error.message);
                            }
                        });
                }
            });
    }

You need to pass the table name as the name parameter in the addFromNamedItemAsyn method. Pass table as the binding type in the second parameter, specify the unique name ("DataTableId") to be used to identify the binding object. To create a table in Excel, select cells where the user wants to render the data. Go to the Insert tab then click on Table. A Popup will be opened as shown in the following screen shot to assign a meaningful name for a table in the Excel UI.  

 

 

  

After adding the method in home.js, now call this method on a button click under the app.initialize() method. 

// Call WriteData by Specified Range by table
            $('#btnwriteDataFromRangeTable').click(writeDatabySpecifiedRangeBytable);

Build the project then press F5. Click on the writeData By TableType button in the task pane. Ensure that your table should be created in Excel before the button click. You can see the data in the table type binding as shown in the following screen shot. 

 

 

Step 4 : After complete write operation. We will read data from the current cell selection and display as notification in the task pane app. Add method for read current document selection in home.js as shown in the following code snippet. 

// Reads data from current document selection and displays a notification
    function getDataFromSelection() {
        Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
            function (result) {
                if (result.status === Office.AsyncResultStatus.Succeeded) {
                    app.showNotification('The selected text is:', '"' + result.value + '"');
                } else {
                    app.showNotification('Error:', result.error.message);
                }
            }
        );
    }

Office.context.document.getSelectedDataAsync(coercionType [, options], callback);

 

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. 

 

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).

 

Call this method on button click event under the app.initialize() method as shown in the following code snippet. Once the user clicks on the button, the method would be invoked.

            // Get Data from sheet
            $('#btnGetDataFromSelection').click(getDataFromSelection);

Build the project. Press F5. Select cells from the Excel sheet. Ensure you select the cell that has a binding type assigned otherwise it cannot read the selected data. Click on the last button (GetDataFromSelection) in the task pane. You can see the current selected data as a notification in the task pane app.

 

 

 

Summary

 

This article explained how to write data using a matrix and table binding type. Read the current selected data and display as a notification in a task pane app. I have attached a sample along with this article. I hope it will be helpful to others.