Real time data streaming from WCF to Excel using a Macro

  • Article

I had come across this scenario with one of my customers. It was pretty challenging at first to figure out the best way to get this done, but eventually it seemed simple enough.

Scenario:

The customer has a web application talking to a SQL server database and they have a WCF service hosted which is exposed to their users.

The customer needs data to be given to their users in a standard format. The tricky part is the users could use Excel to view the data and sync it. Now this is straight forward since we can easily stream the data to an xml format. How can we ensure that everytime the user opens an excel sheet it syncs with the service and gets the data?

 Solution:

An Excel file(containing the macro) which calls the WCF Service to generate this XML file. The Excel file would have the data in the active sheet. This sheet needs to be saved by the client separately. 

The tricky part here was to get the Excel macro to talk to the WCF service

Steps I did from the start:

  1. 1.       Creating the EF model with the Views in the database.

 

 

  1. 2.       Creating the WCF Service to expose these views. Create this in a separate tier or keep it in the same EF tier. Wouldn’t matter if you keep it in the same tier, it would reduce the communication between the 2 tiers.

 

This shows a single method called GetViewData() that returns the data from the single view available inside the EF model.

 

  1. 3.       Host the service in the IIS. Here it has been hosted by a console application using the ServiceHost class.

 

 

 

 

  1. 4.       Use the Excel sheet which needs to get the data in it and write a macro. The macro opens the data in the active Excel sheet and also creates an xml file on the client side. The file at the client side can be used to display data in access and other tools like Crystal reports.

 

 

 

 

 

 

 

The code written in the workbook open method in the macro would write the data that has been streamed from the server into the active sheet in the excel workbook.

Files created on the Client:

  1. 1.       An XML file with all the data which can be used by the client tools
  2. 2.       An Excel file(containing the macro) which calls the WCF Service to generate this XML file. The Excel file would have the data in the active sheet. This sheet needs to be saved by the client.

And it's done! Hope this helps some of you wanting to do something on similar lines.