Office 2013 Excel Task pane App – Interaction with external WCF Data service / SQL Server

Posted: December 7, 2013 in All, Miscellaneous, WCF
Tags: , , , , , , ,

Introduction:

An app for Office is basically a webpage that is hosted inside an Office client application.  It enables users to run the same solutions across applications, platforms and devices.  The applications are developed by using the powerful web technologies such as HTML5, XML, CSS3, JavaScript and REST API’s.    Refer MSDN documentation on Office Apps for more details.

The three basic types of Office apps are task pane, content and mail apps.  In this article I am going to explain how to develop an Office 2013 Excel Task pane app that acts as a live data work book.  The app will have following functionalities:

  • Communicating with an external WCF service to fetch live data
  • Write data into excel work sheet from Task pane app.
  • Track data modifications in excel work sheet.
  • Save modifications to Database through WCF service.

pic1

Connecting to a WCF data service:

Here I am going to start with WCF communication part.  However if you are looking for instructions on how to create a basic Excel task pane app, please refer MSDN.  The task pane app can communicate with an external WCF data service in following ways:

  1. Office Client (JavaScript) to External WCF service
  2. Web Service to WCF Service

1)     Office Client (JavaScript) to External WCF data service:

In this approach the communication part is written in java script, which is executed at client side.

Use JQuery to communicate with WCF data service as shown below.    The first step is construct an URI which should be understandable to WCF Data service.  Writing complex URI queries is tricky however a tool called LINQPad can be used for URI construction.  In this example Dataservice is hosted at http://localhost:17203/MyService end point.

pic2

One of the major issue with java script client side communication is same-origin policy limitation.  Generally browsers don’t allow clients talking to servers which are not in same domain.  One of the way to overcome this issue is enabling CORS.  The following setting will do that.

$.support.cors = true;

Tip: When you add a project of type “Office 2013 App”, visual studio creates a web project for you for developing UI for the app.   If you run app in debug mode from Visual Studio, the web project will be hosted on IIS Express on SSL.  In that case, above code may throw following exception “Access denied”.  To resolve this issue, host the web application on IIS (basic http) or use a self-hosted Web service.

The other approach is using JayData. JayData library comes with a utility called JaySvcUtil.exe which can be used to generate a context file in java script.  Use following command to generated the context file.pic3

Once context.js file is created, add it to the project.  Below is the command to generate java script context.

Tip: WCF data service by default enables metadata on $metadata end point.  So make sure to append ‘$metadata’ to the service end point while generating context.

pic4

Generating static context with JaySvcUtil.exe may have maintenance issues.  Whenever service contracts are changed we need to regenerate proxy and add it to the project.  We can avoid that by creating context dynamically with following command.

pic5

Another approach is to use JasonP. I am not covering this topic here however it can be used to resolve Cross domain communication issues.

2)     Web Server to WCF Service:

As mentioned earlier, due to same-origin policy limitations we may encounter issues like ‘Access Denied’ exceptions while trying to communicate with external WCF service in java script.  So this is an alternate approach to overcome that issue.

The communication happens in two steps in this approach:

Office Client <-> Webserver (server side Code) <-> External WCF service

Office client (java script) calls a Web Method which is executed at ASP server side.  Then Web Service makes a call to WCF service.  Here are the steps to achieve it.

Add a service reference (using WCF data service end point) to the project.  Add an ASP .Net page to the project.  Go to the class in aspx.cs file and write following lines of code.

pic6

We are creating a context using Service reference by specifying the service end point. In this example it returns a list of accounts.   Make sure the method is decorated with WebMethod attribute so that it can be accessed from client’s JavaScript.  Using PageMethods we can call the server side method as shown below.

pic7

Writing data into Excel sheet:

Till now we have focused on WCF service communication and fetching the data from service.    Now we are going to see how we can populate the excel sheet with the data.  setSelectedDataAsync method is used for writing data into excel sheet in selected location. This is defined in Office JavaScript library.  We can write data in 3 formats such as matrix, table, and text.  I will use table data type so that we can display headers along with data.  First create table data. In below example, ‘res’ is the collection which is received from WCF service.  First we specify the headers and then push the data rows.

pic8

Now write the table data in excel sheet at current selection.  Most of the Office java script library methods are asynchronous so that it doesn’t block the Excel interaction with end user.

pic9

Tracking changes in the excel sheet:

Now we want to track changes made by user in excel sheet so that modifications can be automatically sent to WCF service for saving them in Database.  We can use databindings and datachanged event hanlder.

Office.context.document.bindings is a collection of bindings specific to the document.  Add a new binding to this collection and specify the type as table data.  On success, we can add a handler of type ‘BidningDataChanged’.  When data is changed we receive a parameter called eventArgs.   We can use eventArgs.binding.getDataAsync method to reading current data from excel.  One of the drawback with datachanged is, it doesn’t specify what is changed.  It could be an update to a cell or addition/deletion of new row/column, so we need to write custom logic to identify the change and act on it accordingly.

pic10

Please provide your valuable feedback/comments/suggestions that will help me improve my writing.

Advertisements
Comments
  1. […] Office 2013 Excel Task pane App – Interaction with external WCF Data service / SQL Server. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s