Using Excel as the front-end of an OData service

Preparing the project

First we’ll start by creating a WCF Data Service

Once the project is created, we’ll start by deleting Service1.svc and it’s related objects.

We’ll install the following package:

Install-Package Microsoft.OData.EntityFrameworkProvider -Pre

On Nuget Gallery

Ensure to create an EDMX for your database using EF6. On my side, I’m using the Not-Northwind simple database using all the available tables and none of the views.

Once this is created, we’ll start by creating a brand new service that we’ll call Northwind.svc:

The class it will generate your will not be compilable right away without injecting our classes.

The class will be called “Northwind” and inherit from DataService<…>.

We’ll replace this:

public class Northwind : DataService<MySourceClass>

By this:

public class Northwind : EntityFrameworkDataService<NorthwindEntities>

This will allow EF6 to connect with WCF Data Services to create a model on your database.

We can test this by hitting F5. It should return you this:

<?xml version="1.0" encoding="UTF-8"?>
<service xmlns="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="http://localhost:7146/Northwind.svc/">
   <workspace>
      <atom:title>Default</atom:title>
   </workspace>
</service>

We see that we don’t have anything exposed. Let’s fix that now shall we? Going back to the code, we can add the following line to Northwind.svc.cs InitializeService method.

config.SetEntitySetAccessRule("Products", EntitySetRights.AllRead);

If we hit F5 again we should see this:

<?xml version="1.0" encoding="utf-8"?>
<service xml:base="http://localhost:7146/Northwind.svc/" xmlns="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom">
  <workspace>
    <atom:title>Default</atom:title>
    <collection href="Products">
      <atom:title>Products</atom:title>
    </collection>
  </workspace>
</service>

This means we are ready for hooking this up to Excel.

Wiring this up to Excel

This is surprisingly the easiest part. First, start Excel 2010 or higher and go to the Data tab and click on the following:

This will bring up a popup in which we’ll paste our oData URL:

Once you press next you will be prompted to select your table (select Products and click Finish).

It will ask you where you want your data and how.

At that point, it’s up to you to start playing with your data in Excel and expose other endpoint for your business user to start exploiting your data.