Ah…long time no blogging. Busy..busy..busy.. :)
Today I’ll post my demo that I’ve presented on an event entitled “Light Up Your Office”, on July 07, 2009 (a day before Indonesia Presidential Election), at Microsoft Indonesia. This all-day event delivered OBA/VSTO-related topics. I delivered a topic entitled “Optimizing OBA Spreadsheet Productivity with Excel and VSTO”.
In Office Business Application (OBA), working with data from Microsoft Office application, directly to database or via XML Web Services is a common thing. Inspired by this blog post, I created a simpler sample using C# (original sample uses VB.NET). This sample demonstrates how we can develop a simple Office Business Application that is based on Microsoft Excel and work with ADO.NET Data Services.
ADO.NET Data Services or codenamed Astoria, has become a hot topic lately. It is a framework to enable creation and consumption of data services for the web. Pak Risman talked a lot about that stuffs, e.g: here.
Our OBA will look like this:
Excel displays a list of unshipped orders (marked by empty shipped date). If we click a cell of a row inside the list, the order detail will be displayed on action pane on the right. Using action pane, we can set the ship date and save it. The ship date then will be saved to order table.
Architecture of the demo:
The sample uses Northwind database (“bing” it to download). Let’s get started…
We’ll create three projects:
- Northwind.Excel: an Excel Workbook project, our Excel-based OBA.
- Northwind.Model: a Class Library project to store our Entity Data Model
- Northwind.Svc: a Web Application project to store our ADO.NET Data Service
1. Create Entity Data Model
This sample uses ADO.NET Entity Framework to work with database. So, we need to create ADO.NET Entity Data Model (EDM) that will be used by ADO.NET Data Services to expose the data.
Let’s create a Class Library project and name it Northwind.Model. To create an EDM, simply add new item from Northwind.Model project and select ADO.NET Entity Data Model. Name it Northwind.edmx. You’ll find a wizard to import model from Northwind database. Select Order, Order_Detail, Product, and Shipper table to be imported. Use Northwind.Model as model classes namespace. When you finish, you should have this models:
Build Nothwind.Model project
2. Create ADO.NET Data Service
Now, let’s create a Web Application project and name it Northwind.Svc. From this project, Add Reference to Northwind.Model project (I don’t have to explain how, do I?). Then Add New Item, select ADO.NET Data Service template, name it NorthwindServices.svc. Change the generated code so it will look like this.
using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;
namespace Northwind.Svc
{
public class NorthwindServices : DataService< Northwind.Model.NorthwindEntities >
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration config)
{
// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
// Examples:
// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
// config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
config.SetEntitySetAccessRule("*", EntitySetRights.All);
}
}
}
Note:
Northwind.Model.NorthwindEntities is a class generated when we generate EDM. Northwind.Model is the namespace we use to group EDM classes.
Now, right click to NorthwindServices.svc and View in Browsers. Browser will display something like this:
<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
- <service xml:base=http://localhost:1344/NorthwindServices.svc/
xmlns:atom=http://www.w3.org/2005/Atom
xmlns:app=http://www.w3.org/2007/app
xmlns="http://www.w3.org/2007/app">
- <workspace>
<atom:title>Default</atom:title>
- <collection href="Order_Details">
<atom:title>Order_Details</atom:title>
</collection>
- <collection href="Orders">
<atom:title>Orders</atom:title>
</collection>
- <collection href="Products">
<atom:title>Products</atom:title>
</collection>
- <collection href="Shippers">
<atom:title>Shippers</atom:title>
</collection>
</workspace>
</service>
You can try to type this URL on browser address field: (port 1344 can be varying)
http://localhost:1344/NorthwindServices.svc/Orders?$top=10&$orderby=OrderDate
And browser will display:
Right click to that page and View Source, you’ll get XML content that contains actual Order data and structure. It contains 10 <entry> elements (means 10 records of Order data), sorted by OrderDate property.
You can refer to ADO.NET Data Services to learn more about how to query the data using REST-like URI.
3. Create Excel Workbook project
So far we’ve created data model and data service that will be consumed by Excel application. Let’s create a Excel 2007 Workbook project and name it Northwind.Excel. You’ll be asked to create a new Excel document, just accept the default.
As the final result depicts on the first picture, a list of unshipped order will be displayed on Excel worksheet. We need somehow to be able to bind the data (retrieved from ADO.NET Data Service) to Excel worksheet cells. There are some alternatives for binding purpose. One of it is to use the combination of Excel’s ListObject, BindingSource, and BindingList. This combination supports list change notification to be communicated to the user interface.
ListObject is Excel’s control that allow us to bind data without having to traverse the Excel object model. It support simple binding from DataTable or IQueryable (returned from LINQ query), also from BindingSource.
3.1. Add Service Reference
Let’s first to add service reference to our ADO.NET Data Service created on point 2. Since ADO.NET Data Service is basically based on WCF, adding reference to the service is the same as general WCF Service. Right click to Northiwind.Excel project, click Add Service Reference. Type service address (e.g. http://localhost:1344/NorthwindServices.svc, port 1344 can be varying) and use Namespace NorthwindSvc.
This action will generate classes those are associated with EDM classes on the server. All generated classes will be resided inside Northwind.Excel.NorthwindSvc namespace.
3.2. Order partial class
To support change notification, we need to partial Order class and implement INotifyPropertyChanged interface. Write Order partial class within Northwind.Excel.NorthwindSvc namespace (note: NorthwindSvc is the Namespace we specify when adding service reference). It will look like this:
using System;
using System.ComponentModel;
namespace Northwind.Excel.NorthwindSvc
{
public partial class Order: INotifyPropertyChanged
{
#region INotifyPropertyChanged Members
public event PropertyChangedEventHandler PropertyChanged;
#endregion
private void FirePropertyChanged(String propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
partial void OnShippedDateChanged()
{
FirePropertyChanged("ShippedDate");
}
partial void OnOrderDateChanged()
{
FirePropertyChanged("OrderDate");
}
partial void OnFreightChanged()
{
FirePropertyChanged("Freight");
}
partial void OnOrderIDChanged()
{
FirePropertyChanged("OrderID");
}
partial void OnRequiredDateChanged()
{
FirePropertyChanged("RequiredDate");
}
partial void OnShipAddressChanged()
{
FirePropertyChanged("ShipAddress");
}
partial void OnShipCityChanged()
{
FirePropertyChanged("ShipCity");
}
partial void OnShipCountryChanged()
{
FirePropertyChanged("ShipCountry");
}
partial void OnShipNameChanged()
{
FirePropertyChanged("ShipName");
}
partial void OnShipPostalCodeChanged()
{
FirePropertyChanged("ShipPostalCode");
}
partial void OnShipRegionChanged()
{
FirePropertyChanged("ShipRegion");
}
}
public partial class Product
{
public override string ToString()
{
return ProductName;
}
}
}
Please note that we also add Product partial class to override ToString() method.
3.3. Create BindingList<Order>
We need to create a subclass of BindingList to hold Order collection. Create a class and name it NorthwindOrderList.cs. Write this code:
using System;
using System.Linq;
using System.ComponentModel;
using Northwind.Excel.NorthwindSvc;
using System.Data.Services.Client;
namespace Northwind.Excel
{
public class NorthwindOrderList: BindingList<Order>
{
private NorthwindSvc.NorthwindEntities DataServiceContext;
private bool hasChanged;
private bool isSaving;
public bool HasChanged { get { return hasChanged; } }
public NorthwindOrderList()
{
DataServiceContext = new NorthwindEntities(
new Uri("http://localhost:1344/NorthwindServices.svc/"));
this.DataServiceContext.MergeOption = MergeOption.OverwriteChanges;
}
protected override void OnListChanged(ListChangedEventArgs e)
{
if (!isSaving)
{
if (e.ListChangedType == ListChangedType.ItemChanged)
{
DataServiceContext.UpdateObject(this[e.NewIndex]);
hasChanged = true;
}
base.OnListChanged(e);
}
}
public int LoadOrders()
{
this.Clear();
this.RaiseListChangedEvents = false;
var orders = from o in DataServiceContext.Orders
where o.ShippedDate == null
orderby o.OrderDate descending
select o;
foreach (var o in orders) { this.Add(o); }
this.RaiseListChangedEvents = true;
this.OnListChanged(new ListChangedEventArgs(ListChangedType.Reset, 0));
return this.Count;
}
public void LoadOrderDetails(Order o)
{
if (o != null)
{
if (o.Shippers == null)
{
DataServiceContext.LoadProperty(o, "Shippers");
}
if (o.Order_Details == null || o.Order_Details.Count == 0)
{
DataServiceContext.LoadProperty(o, "Order_Details");
foreach (var od in o.Order_Details)
{
od.Orders = o;
if (od.Products == null)
{
DataServiceContext.LoadProperty(od, "Products");
}
}
}
}
}
public bool SaveChanges()
{
bool saved = false;
try
{
isSaving = true;
DataServiceContext.SaveChanges();
hasChanged = false;
saved = true;
isSaving = false;
}
catch
{
isSaving = false;
}
return saved;
}
}
}
In this class, we define three public methods:
- LoadOrder(): to explicitly load order data from service and add to BindingList collection
- LoadOrderDetails: to explicitly load Order_Details, Shipper, and Product object
- SaveChanges: to explicitly save changes to ADO.NET Data Service.
Note:
3.4. Add New Data Source
To be able to add ListObject to Excel Worksheet, we can use Data Source. Simply Add New Data Source from Data menu of Visual Studio. On Data Source Configuration wizard, choose Object. Then select NorthwindOrderList as the object to bind to.
After finishing the wizard, we’ll have NorthwindOrderList data source on Data Sources window. Drag that data source to a cell of Excel worksheet Sheet1, and we’ll have a ListObject. By default, the name the ListObject is northwindOrderListListObject. You can change it as you need. A BindingSource named northwindOrderListBindingSource will be generated automatically.
3.5. Write code in Sheet1.cs
Right click to Sheet1 above and View Code. Write some code inside Sheet1_Startup method, that will look like this:
1: private void Sheet1_Startup(object sender, System.EventArgs e)
2: {
3: this.northwindOrderListBindingSource.DataSource = Globals.ThisWorkbook.OrderList;
4: this.northwindOrderListListObject.SetDataBinding(this.northwindOrderListBindingSource, "",
5: "OrderID",
6: "ShippedDate",
7: "Freight",
8: "ShipName",
9: "ShipAddress",
10: "ShipCity",
11: "ShipRegion",
12: "ShipPostalCode",
13: "ShipCountry");
14:
15: Globals.ThisWorkbook.LoadOrders();
16:
17: northwindOrderListListObject.Range.Columns.AutoFit();
18:
19: for (int i = 1; i < northwindOrderListListObject.DataBodyRange.Row; i++)
20: {
21: ((Microsoft.Office.Interop.Excel.Range)northwindOrderListListObject.DataBodyRange.Cells[i, 2]).NumberFormat = "dd/mm/yyyy";
22: }
23: }
At line 4 of above code, we use SetDataBinding of ListObject to set what columns/properties to be bound. You’ll notice that we call Globals.ThisWorkbook.LoadOrders() method that is not yet available. We’ll create it soon.
3.6. Write code in ThisWorkbook.cs
It’s time to write LoadOrder method and other code in ThisWorkbook.cs, so it will have this code:
private NorthwindOrderList orderList;
public NorthwindOrderList OrderList
{
get
{
if (orderList == null)
{
orderList = new NorthwindOrderList();
}
return orderList;
}
}
private OrderActionsPane oActionPane;
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
oActionPane = new OrderActionsPane();
this.ActionsPane.Controls.Add(oActionPane);
}
public int LoadOrders()
{
return orderList.LoadOrders();
}
3.7. Create ActionPane
Add new item and chose Action Pane control. Name the control as OrderActionPane.cs. Add some controls so it will look like this.
View code and write this code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Northwind.Excel.NorthwindSvc;
namespace Northwind.Excel
{
partial class OrderActionsPane : UserControl
{
public OrderActionsPane()
{
InitializeComponent();
}
public void DisplayOrderDetails(Order o)
{
if (o != null)
{
Globals.ThisWorkbook.OrderList.LoadOrderDetails(o);
order_DetailsBindingSource.DataSource = o.Order_Details;
}
}
private void OrderActionsPane_Load(object sender, EventArgs e)
{
Globals.Sheet1.northwindOrderListBindingSource.PositionChanged += new EventHandler(northwindOrderListBindingSource_PositionChanged);
}
void northwindOrderListBindingSource_PositionChanged(object sender, EventArgs e)
{
BindingSource bs = (BindingSource)sender;
if (bs != null)
{
if (bs.Position > -1)
{
DisplayOrderDetails((Order)bs.Current);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
int count = Globals.ThisWorkbook.OrderList.LoadOrders();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
Globals.ThisWorkbook.OrderList.SaveChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
Order o = (Order)Globals.Sheet1.northwindOrderListBindingSource.Current;
if (o != null)
{
o.ShippedDate = dateTimePicker1.Value;
}
}
}
}
Notes:
- OrderActionsPane_Load is form load event handler
- button1_Click and button2_Click are buttons click event handler. You can use more intuitive name
- dateTimePicker1_ValueChanged is DatePicker value changed event handler
The complete source code and the presentation slide can be found at: http://dycode.com/files/folders/other_talks/entry200.aspx
The photo taken during my speaking.
This concludes how we can create a simple Excel-based OBA that work with ADO.NET Data Service. Write comment if you have any problems related to this sample. Enjoy.