Windows Presentation Foundation (WPF) is a vector-based presentation system for building Windows client applications with visually stunning user experiences. WPF is an alternative to Windows Forms for designing user interfaces that uses a markup language called Extensible Application Markup Language (XAML) to provide new techniques for incorporating UI, media, and documents.
Although Office solutions you build with Visual Studio are designed to work with Windows Forms controls, you can also use WPF controls in your solutions as well. Any UI element that can host Windows Forms controls in a Visual Studio Tools for Office solution can also host WPF controls. One major benefit of using WPF controls in Office is that you are able to provide world-class data visualizations that are not possible with Windows Forms controls in an instantly familiar end-user application. Even the simplest controls that display data are often better off as WPF controls in Office applications because they better match the UI styles used in the latest versions of Office. Using WPF can make your add-ins look built into the Office applications themselves, providing a better user experience.
In this article I will create a document customization for an Excel 2007 Workbook that is used by the Northwind Traders shipping department that pulls Order data from the Northwind database through an ADO.NET Data Service. The customization will display the order details in an Action Pane built with WPF and will allow users to update the order data through the data service.
Even the simplest controls that display data are often better off as WPF controls in Office applications
Exposing Line-of-Business (LOB) data to Office clients
Before jumping into building the WPF control you need to think about how you can access data in your databases. There are a lot of options when thinking about how to expose your LOB data to Office clients. For instance, you may already have a service-oriented architecture at the enterprise that exposes data contracts and processes that you can consume from Office clients. Or maybe you have a small business and have decided to expose a simple service that returns and consumes n-tier data setsdirectly. Or you already have a custom LOB data entry system using custom business objects and you want to reuse the business layer in the Office client. Because you can consume data in Office clients the same way you do in other Windows applications the same types of decisions need to be made.
Because there is only going to be simple CRUD (Create-Retrieve-Update-Delete) operations in this example, I will expose an Entity Data Model via ADO.NET Data Services. This allows you to get a secure service up and running in minutes. I will not spend much time on this but I’ll show the basics. For more information on ADO.NET Data Services please read the article “Using Microsoft ADO.NET Data Services” in the MSDN library.
To quickly build a data service for this example, create a new ASP.NET Web Application named NorthwindDataService and add a new item ADO.NET Entity Data Model (EDM) to the project that is generated from the Northwind database called NorthwindModel. Next add a new item and select ADO.NET Data Service and name it Northwind. (Please note: You will need Visual Studio 2008 Service Pack 1 in order to get these new item templates.) For testing, set the service to allow full access to all the entities in the model (but do not forget to lock it down later if you deploy to production). For this example I also am passing detailed errors which you will not want to do in production either.
Public Class Northwind
Inherits DataService(Of NorthwindEntities)
' This method is called only once
' to initialize service-wide policies.
Public Shared Sub InitializeService( _
ByVal config As IDataServiceConfiguration)
config.SetEntitySetAccessRule("*", _
EntitySetRights.All)
config.UseVerboseErrors = True
End Sub
End Class
Now that the data is exposed as an ADO.NET Data Service you can build the Office client which will consume this service.
The Excel document customization
To create a document customization for Excel, add a new project to the solution and select Office 2007 Excel Workbook which is available in Visual Studio 2008 Professional and higher. Since I am building a document customization and not an Add-In this means that code will only run on the specific document as opposed to running in the Excel application over any document that is opened. After the project is added, Visual Studio asks if you want to import an existing Excel workbook or create a new one. For this example create a new one named the same name as the project, i.e. NorthwindClientExcel.xlsx. After you specify this, the project is created and the Excel designer opens.
The next step is to add the data service reference. Right-click on the project, select Add Service Reference. Click the Discover button and it should find the ADO.NET data service in the solution called NorthwindDataService. Name the service reference NorthwindService. This will generate the proxy to the service and the entity types on the client.
Data binding in Excel
Now that the service reference is created you can retrieve a list of Order entities that have not been shipped yet and display them in the first sheet of the workbook. But you will also want to show the Order Details and display the Quantity and Product that should be shipped. You will do this using an Excel Action Pane by setting up a master-detail view of the data. So as the user scrolls through the Order rows, the Action Pane will display the Order Details in a WPF GridView. The user only needs to make changes to the Order rows so this design will work well.
The magic control you use in Excel to bind data to rows is called the ListObject. This object can easily bind to a Winforms BindingSource just like any other Winforms control. To set up the data source so it can be bound to a BindingSource, you need to create a BindingList(Of Order) so that list change notifications are communicated to the UI. You also need to add code to the Order partial class on the client so that property change notifications are also sent.
So first create a new class named Order and place it in the same Namespace as the NorthwindService service reference that was just added. This is where the client proxy objects are generated for you. In order to extend these classes with your own code, just place it into the same Namespace. (Here's a Visual Basic tip, you know you've got it right if you see the Order properties and methods in the Declarations drop down above the editor for the Order class.) You need to fire a change notification on all of the properties of the Order so I've placed code to do this in all the xxxChanged() partial methods. Take a look at the code download for how to do this.
Next you need to create a BindingList(Of Order). I'm going to create a class called MyOrdersList that inherits from this which will also encapsulate the calls to the ADO.NET Data Service. MyOrdersList will use the data service context on the client (NorthwindService in this case) to retrieve and update the Orders. In this example you do not want to allow adds or deletes because orders are going through a workflow and our shipping department is only updating the information on an order, specifically the ShippedDate. So you need to override AddNewCore and RemoveItem to prevent this. Also override the OnListChanged so that the Order entity can be marked as updated.
There are also couple housekeeping fields for saving and for exposing a HasChanges property. The interesting code to notice here is that the MergeOption is specified on the data service client to OverwriteChanges after the save, as opposed to the default AppendOnly. This means that after the changes are saved it will refresh the existing Order entity instances with changes from the database. Here is the beginning of the MyOrdersList class that handles this:
Imports NorthwindExcelClient.NorthwindService
Imports System.ComponentModel
Public Class MyOrderList
Inherits BindingList(Of Order)
Private DataServiceContext As New _
NorthwindEntities(New _
Uri(My.Settings.ServiceURI))
Private _hasChanges As Boolean
Private _isSaving As Boolean
Public ReadOnly Property HasChanges() As Boolean
Get
Return _hasChanges
End Get
End Property
Sub New()
MyBase.New()
Me.DataServiceContext.MergeOption = _
Services.Client.MergeOption.OverwriteChanges
End Sub
Protected Overrides Function AddNewCore() As Object
Throw New NotSupportedException( _
"You cannot add items to the list.")
Return Nothing
End Function
Protected Overrides Sub RemoveItem( _
ByVal index As Integer)
Throw New NotSupportedException( _
"You cannot remove items from the list.")
End Sub
Protected Overrides Sub OnListChanged( _
ByVal e As System.ComponentModel.ListChangedEventArgs)
If Not _isSaving Then
'The Order partial class is implementing
' INotifyPropertyChanged so this will work
If e.ListChangedType = _
ListChangedType.ItemChanged Then
Me.DataServiceContext.UpdateObject(Me(e.NewIndex))
_hasChanges = True
End If
MyBase.OnListChanged(e)
End If
End Sub
Before I write the code that will retrieve and save the orders, I will bind this to a ListObject and get it onto the Excel worksheet. Since you will have to interact with the list of orders from Sheet1 as well as the Action Pane displaying the details, it is easiest to create a property on the Workbook itself that exposes the MyOrdersList. Double-click on the ThisWorkbook in the project and switch to the Code view and create a property to expose the OrderList.
Now build the project and open the Data Sources window. Add a new Object Data Source and select MyOrderList then Finish. Drag the MyOrderList onto the designer for Sheet1, just drop it into the first cell, and it will generate all the columns for every property on the Order. You can also change the name of it on the Design tab and I'll change this one to OrderListObject as shown in figure1.
Fig. 1: Drop data sources directly onto Excel sheets to set up data binding automatically
Unfortunately it drops all the columns regardless if you select a drop target of none or not in the data sources window. Right-click on the column and select Delete from the drop-down menu to delete a column (do not press the delete key, use the context menu). For this example remove the first three and the last three columns that it dropped. Another quirk of the ListObject is that you will need to set any date fields to Text format. The ListObject will not let you change date values if the formatting isn't set to text. You do this by selecting the column then right-click, select Format Cells, and then select Text from the list. This is usually OK to do because the data source, in this case the Order, enforces the data type to be a date.
You can be explicit about setting the data source and binding to columns in the startup event handler of the sheet as shown here:
Public Class Sheet1
Private Sub Sheet1_Startup() Handles Me.Startup
Dim myApp = NorthwindExcelClient.Globals.ThisWorkbook
Me.OrderBindingSource.DataSource = myApp.OrderList
Me.OrderListObject.SetDataBinding( _
Me.OrderBindingSource, "", _
"ShippedDate", _
"Freight", _
"ShipName", _
"ShipAddress", _
"ShipCity", _
"ShipRegion", _
"ShipPostalCode", _
"ShipCountry")
Me.OrderListObject.AutoSelectRows = True
myApp.LoadOrders()
End Sub
You should notice that the last line calls a method on ThisWorkbook to load the orders. In order to load the order entities you will write a query against the ADO.NET data service next.
Querying and saving orders via the data service
Looking back to the MyOrdersList class you can now write a method for returning the list of orders that have not yet been shipped. Call the service to return a list of unshipped orders, add them all to MyOrderList and then raise the list changed event to notify the Excel ListObject to refresh its UI as shown here:
Public Function LoadOrders() As Integer
Me.Clear()
Me.RaiseListChangedEvents = False
Dim orders = From o In Me.DataServiceContext.Orders _
Where o.ShippedDate Is Nothing _
Order By o.OrderDate
For Each o In orders
Me.Add(o)
Next
Me.RaiseListChangedEvents = True
Me.OnListChanged(New _
ListChangedEventArgs(ListChangedType.Reset, 0))
Return Me.Count
End Function
You will also need a way to load the order details, shipper and product entities when requested. This will happen as the user selects rows of data and you need to display these details in the Action Pane. By default these entity references and collections are not loaded so you need to do this explicitly. This is a good thing in this case because it avoids transferring unnecessary data if the user doesn't select the Order row in the sheet. Here is the code to load the details:
Public Sub LoadOrderDetails(ByVal o As Order)
If (o IsNot Nothing) Then
If o.Shipper Is Nothing Then
Me.DataServiceContext.LoadProperty(o, _
"Shipper")
End If
If o.Order_Details Is Nothing OrElse _
o.Order_Details.Count = 0 Then
Me.DataServiceContext.LoadProperty(o, _
"Order_Details")
For Each detail In o.Order_Details
detail.Order = o
If detail.Product Is Nothing Then
Me.DataServiceContext.LoadProperty(detail, _
"Product")
End If
Next
End If
End If
End Sub
Finally you need a method that submits changes back to the data service. This is easily done by calling SaveChanges on the service context which sends all the changes that were made in the client through the service, i.e. Me.DataServiceContext.SaveChanges(). In this example they are not sent in a single batch, therefore if one order fails to update the ones before the failure will still be saved. Depending on your scenario you can opt to use a batch request by passing that flag in the call to SaveChanges. You also may need to put more robust error handling here depending on what kind of failures you anticipate in your applications. Simple error handling is done for this example in the Action Pane which you will build next.
Creating an Excel action pane using WPF
Now you are ready to design the Action Pane that will display the order details. The control will display this in a simple read-only GridView that matches the Excel blue gradients and fonts. It will also provide a status message area and a Save and Refresh button. Refresh will re-load the orders from the service and Save will send the changes back. (Later I’ll show how you can hook up Excel's save button to do the same thing.) It will also run a couple animations when rows are selected in the sheet to draw the user’s attention to the Action Pane. This is because by default Action Panes are displayed on the right-hand side of the spreadsheet.
In order to display WPF in a Windows Form control you use the Winforms ElementHost as a container for your WPF control
In order to display WPF in a Windows Form control, including Office solutions built with Visual Studio, you use the Winforms ElementHost as a container for your WPF control. This is found in the WPF Interoperability Tab on your toolbox. When you drop this control onto the designer you can immediately select the WPF user control to display. So first we need to build a WPF user control.
Add a new item to the project and select WPF category on the left and then choose WPF User Control, I named it WPFActionPane for this example, and click OK. Take a look at the code sample for the full XAML source code which describes the layout and data bindings of the controls as well as the styles used to make the GridView have a nice Excel-friendly look. The following code listing highlights the basics:
<UserControl x:Class="WPFActionPane"...
<UserControl.Resources>
<Storyboard x:Key="LoadingStoryBoard">...
<Storyboard x:Key="UpdateStoryBoard">...
<Style x:Key="GridViewStyle" TargetType="TextBox">...
<Style x:Key="ListViewStyle" TargetType="ListView">...
<Style x:Key="ListViewItemStyle"
TargetType="ListViewItem">...
<Style x:Key="GridViewColumnHeaderStyle"
TargetType="GridViewColumnHeader">...
</UserControl.Resources>
<Grid Height="Auto" Width="Auto" Name="MainGrid">
<Grid.RowDefinitions>...
<StackPanel Orientation="Horizontal">
<Button Height="40" Name="cmdSave" ...
<Button Height="40" Name="cmdRefresh" ...
</StackPanel>
<StackPanel Grid.Row="1">
<TextBlock Name="txtShipper"
Width="Auto" Height="25"
Text="{Binding
Path=Shipper.CompanyName,
StringFormat=Ship via \{0\}}"
Foreground="DarkBlue" />
<TextBlock Name="txtOrderDate"
Width="Auto" Height="25"
Text="{Binding
Path=OrderDate,
StringFormat=Ordered \{0:D\}}"
Foreground="DarkBlue"/>
</StackPanel>
<Image Grid.Row="2" Source="northwindlogo.gif" />
<Grid Grid.Row="2" Background="White" Name="ListGrid">
<ListView ItemsSource="{Binding Path=Order_Details}"
Style="{StaticResource ListViewStyle}"
ItemContainerStyle="{StaticResource ListViewItemStyle}"
Name="ListView1" Margin="4" IsEnabled="True"
BorderThickness="0">
<ListView.View>
<GridView ColumnHeaderContainerStyle=
"{StaticResource GridViewColumnHeaderStyle}">
<GridViewColumn Header="Product">
<GridViewColumn.CellTemplate>
<DataTemplate>
<TextBox
Text="{Binding Path=Product.ProductName}"
Style="{StaticResource GridViewStyle}"/>
</DataTemplate>
</GridViewColumn.CellTemplate>
</GridViewColumn>
<GridViewColumn Header="Quantity">
<GridViewColumn.CellTemplate>
<DataTemplate>
<TextBox Text="{Binding Path=Quantity,
StringFormat='n0'}"
Style="{StaticResource GridViewStyle}" />
</DataTemplate>
</GridViewColumn.CellTemplate>
</GridViewColumn>
</GridView>
</ListView.View>
</ListView>
</Grid>
<Grid Grid.Row="3">
<TextBox Name="txtStatus"...
</Grid>
</Grid>
</UserControl>
The code that calls the load and save on the orders will be here since this control will interact directly with the list of Orders. In the code-behind of the WPFActionPane it also animates the control by running the StoryBoards defined in the XAML when either the order details are displayed or when the status messages at the bottom of the pane changes. Note that I also included the images referred by the XAML above into the project and set their Build Action to Resource in the properties for the files in the Solution Explorer.
First pass the reference to the list of Orders into the WPF control and then set it as the DataContext. This will trigger all the data bindings to hook up to the currently selected Order’s details as noted by the data bindings in the XAML above. For instance, if you want to display the shipper’s company name then specify the Shipper.CompanyName path on the Order object contained in the list. The code for the WPF user control is as follows:
Imports NorthwindExcelClient.NorthwindService
Imports System.Windows.Data
Imports System.Windows.Media
Partial Public Class WPFActionPane
Private _orderList As MyOrderList
Public Property OrderList() As MyOrderList
Get
Return _orderList
End Get
Set(ByVal value As MyOrderList)
_orderList = value
Me.DataContext = _orderList
End Set
End Property
Private Sub cmdRefresh_Click() Handles cmdRefresh.Click
Me.LoadOrders()
End Sub
Private Sub cmdSave_Click() Handles cmdSave.Click
Me.SaveOrders()
End Sub
Public Function SaveOrders() As Boolean
Dim msg = “Orders could not be saved.” & vbCrLf
Try
‘Save changes back to the data service
If Me.OrderList.SaveChanges() Then
msg = “Orders saved.”
End If
Me.SetMessage(msg)
Catch ex As Exception
Me.SetMessage(ex.ToString())
End Try
End Function
Public Sub LoadOrders()
Dim msg = “”
Try
Dim count = Me.OrderList.LoadOrders()
msg = String.Format(“{0} orders returned.”, count)
Me.SetMessage(msg)
Me.ButtonStack.Visibility = _
Windows.Visibility.Visible
Catch ex As Exception
Me.SetMessage(ex.ToString())
End Try
End Sub
Public Sub DisplayOrderDetails(ByVal o As Order)
If o IsNot Nothing Then
Me.OrderList.LoadOrderDetails(o)
Dim view = _
CollectionViewSource.GetDefaultView(Me.DataContext)
view.MoveCurrentTo(o)
Me.ListGrid.Visibility = Windows.Visibility.Visible
Me.ListView1.SelectedIndex = -1
Dim story = CType(Me.Resources(“LoadingStoryBoard”),_
Animation.Storyboard)
story.Begin()
End If
End Sub
Public Sub SetMessage(ByVal msg As String)
Me.txtStatus.Text = msg
Dim story = CType(Me.Resources(“UpdateStoryBoard”), _
Animation.Storyboard)
story.Begin()
End Sub
End Class
Now you can create the Action Pane. Add a new item to the project and select Actions Pane Control from the Office group. I named it OrdersActionPane. This opens up the Windows Forms User Control designer and you can drop any Windows Forms controls on here. To add the WPF user control open the WPF Interoperability category in the toolbox and you should see the ElementHost control. Drag that onto the Actions Pane user control, click the smart tag in the upper right, and then select the WPFActionPane control as the hosted content and dock it in the parent container. You also need to set the font of the OrdersActionPane to Calibri 11 point.
In the code behind for the OrdersActionPane set the OrderList for the WPF control and add a handler to listen to the position changed event on the OrderBindingSource that is hooked up as the DataSource of the ListObject on Sheet1. When the position changes the WPF control is notified to display the selected Order’s details. There are many ways to integrate WPF and Windows Forms to manage data source currency but for this example let’s keep it simple. The code for the OrdersActionPane is below:
Imports System.Data.Services.Client
Imports NorthwindExcelClient.NorthwindService
Public Class OrdersActionsPane
Private Sub OrdersActionsPane_Load() Handles Me.Load
‘Pass in our data source to the WPF control
Me.WpfActionPane1.OrderList = _
NorthwindExcelClient.Globals.ThisWorkbook.OrderList
‘When the position changes in the spreadsheet,
‘ display the order details in the WPF control
AddHandler _
NorthwindExcelClient.Globals.Sheet1. _
OrderBindingSource.PositionChanged,
AddressOf Me.DisplayOrderDetails
End Sub
Private Sub DisplayOrderDetails( _
ByVal sender As Object, _
ByVal e As EventArgs)
Dim source = TryCast(sender, BindingSource)
If source IsNot Nothing Then
If source.Position > -1 Then
Me.WpfActionPane1.DisplayOrderDetails( _
TryCast(source.Current, Order))
End If
End If
End Sub
End Class
You are almost ready to test this out. The last thing you need to do is load the Actions Pane when the application starts as well as expose a LoadOrders to our Sheet. This is because if you are adding charts, pivot tables or other types of controls for data analysis you cannot rely on the Action Pane loading the data, you need the sheet to be able to call upon it explicitly. You will also hook up the Excel BeforeSave event so that save can be called on the orders when users click the normal save button on the Excel toolbar. So back in the ThisWorkbook class enter the following code under the code you wrote previously:
Private _orderActions As New OrdersActionsPane
Private Sub ThisWorkbook_Startup( _
ByVal sender As Object,_
ByVal e As System.EventArgs) Handles Me.Startup
'Load the action pane
Me.ActionsPane.Controls.Add(_orderActions)
End Sub
Public Sub LoadOrders()
_orderActions.WpfActionPane1.LoadOrders()
End Sub
Private Sub ThisWorkbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
ByRef Cancel As Boolean) Handles Me.BeforeSave
_orderActions.WpfActionPane1.SaveOrders()
End Sub
End Class
Hit F5 and try it out. You will see all the Orders that have not shipped load into the sheet. As you select a row, the details are displayed in the Action Pane and an animation that shows the Northwind logo fades into and out of view so that it draws the user's attention to the Action Pane on the far right. Modify any of the fields in the Sheet and click the save button to save your changes back to the service. Figure 2 shows the Excel client as a new row is being selected and the transition is taking place on the WPF control on the Action Pane.
Fig. 2: The Excel client with a WPF control on the Action Pane as a new row is being selected
This example is included in a complete Office Business Application (OBA) sample that is available online at http://code.msdn.microsoft.com/OBANorthwind. The Excel client in that example also includes how to create a pivot table and chart that refreshes when the data comes back from the service. The other pieces of the sample show how to build a complete OBA solution using Outlook, Word and a SharePoint workflow and can be used independently so I encourage you to have a look.
Using WPF in business applications built for Office is easy to do and provides a much better user experience and look-and-feel than traditional Windows Forms controls. You can easily make your add-ins and document customizations appear like they are truly part of the Office application itself. WPF makes it possible to provide stunning visualizations on top of your line-of-business data in an application that is very familiar to the end user. Enjoy!
De sources die bij dit artikel horen kun je downloaden via massi_usingWPFinofficeclients_SRC.zip.