Another Technique for Creating Data-Bound Spreadsheets

Unlike our previous example, in this case we do not define ahead of time where the back-end data store is located; you have to write a few lines of code to obtain the data.

Create a new Excel project, and choose Data > Microsoft Office Excel Data > XML > XML Source to display the XML Source pane. As you can see, no XML schemas are mapped into this document, so click the XML Maps button, and add the schema file shown in Listing 17.3.

Listing 17.3. A Schema for a Two-Table Dataset

<?xml version="1.0"?> <xs:schema      targetNamespace="http://myschemas/Order.xsd"   xmlns="http://myschemas/Order.xsd"   xmlns:xs="http://www.w3.org/2001/XMLSchema">   <xs:element name="Order">     <xs:complexType>       <xs:sequence>         <xs:element name="Customer" type="xs:string"         minOccurs="0" maxOccurs="1" />         <xs:element name="Book" minOccurs="0"           maxOccurs="unbounded">           <xs:complexType>             <xs:sequence>               <xs:element name="Title" type="xs:string"                 minOccurs="0" />               <xs:element name="ISBN" type="xs:string"                 minOccurs="0" />               <xs:element name="Price" type="xs:double"                 minOccurs="0" />             </xs:sequence>           </xs:complexType>         </xs:element>       </xs:sequence>     </xs:complexType>   </xs:element> </xs:schema> 

This is a dataset schema that defines an Order as consisting of a single Customer and any number of Books, where each book has a Title, ISBN, and Price. In a database, this would be organized as two related tables, as you will see.

The structure of the XML schema then appears in the XML Source pane, and you can drag and drop elements of the schema onto the spreadsheet. Try dragging the Customer node onto a cell. The single datum creates a named range host control. If you then drag over the Book node, you get a List object. Also, Visual Studio has again created a dataset source file. Visual Studio knows nothing about what the source of the data will be, however, so it does not generate any adapters.

Next, let's add a binding source. From the Toolbox, find the binding source component in the Data category. Drag it onto the spreadsheet. A binding source component appears in the component tray. Rename the binding source OrderBookBindingSource, using the properties window. Then click the list object you created by dragging the Book node onto the worksheet. In the Properties window, set the DataSource of the list object to the OrderBookBindingSource you created.

If you compile and run the customization, not much will happen. The data binding source is just a dummy; no actual data is in there. Also, there is no instance of the dataset on the components tray, so there is no chance that data will ever be associated with this binding source as things stand now.

Notice in Figure 17.13 that the project system has added the schema to the project automatically; it will generate a typed dataset for this schema and add it to the project as well. But that class is just source code; the project system does not know yet what it is going to look like when compiled. Well, then, let's compile it. Build the project, but do not run it.

Figure 17.13. The XML Source pane.

Now if you pop open the toolbox, you will see a new set of tools under the name of the project. There should be an OrderDataSet item. Drag it over onto the spreadsheet's component tray, and drop it; doing so adds an instance of the typed dataset to the customized worksheet class.

Tip

Alternatively, you can add this typed dataset to your project before compiling if you open the ToolBox tab on the left side of the designer, and drag and drop the dataset component onto the design surface. When you drag and drop a dataset component, Visual Studio shows you a combo box that enables you to choose among all available typed datasets referenced by or in your project. This combo box shows you the new dataset even if you have not compiled your project.

We have gotten most of the parts we need: The binding source is connected to the list object, but the binding source does not yet know that the dataset we have just dropped onto the component tray is important.

Click the book binding source in the component tray, and take a look at its Property pane. Start by clicking the DataSource drop-down list, and navigate the tree view to select Other Data Sources > Sheet1 List Instances > OrderDataSet1. Then click the DataMember property drop-down list, and select the Book table, as shown in Figure 17.4.

Figure 17.14. Setting the DataSource property of the binding source.

Note

Do not forget to set the DataMember property when binding to a table. Without it, the binding data source will attempt to extract the columns for the table from the dataset itself, not from the Book table. This will fail at runtime.

We have gotten almost everything we need; the only thing left is to put some data in the typed dataset instance we have added. Typically, we would fill the dataset by creating an adapter to talk to some external database. For this example, we just fill the typed dataset manually, using the code in Listing 17.4. (You could also fill it by loading XML out of a file or downloading XML from a Web service.)

Listing 17.4. Filling a Typed Dataset from Scratch

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     ' An order has a customer column     Me.OrderDataSet1.Order.AddOrderRow("Vlad the Impaler")     ' A book has a title, ISBN and price, and is associated with     ' a particular order.     Me.OrderDataSet1.Book.AddBookRow("Blood For Dracula", _       "0-123-45678-9", 34.95, Me.OrderDataSet1.Order(0))     Me.OrderDataSet1.Book.AddBookRow("Fang Attack!", _       "9-876-54321-0", 14.44, Me.OrderDataSet1.Order(0))   End Sub End Class 

Now build and execute the customized spreadsheet. You'll see in Figure 17.15 that when the Startup event runs and creates the new row in the book table, the data binding layer automatically updates the list object.

Figure 17.15. The list object is bound to the data table.

Furthermore, data binding to list objects goes both ways; updating the data in the host control propagates the changes back to the data table.

Complex and Simple Data Binding

What you have just seen is an example of complex data binding, so named not because it is particularly difficult, but because many pieces of data are bound at once to a relatively complicated host control. Controls must be specially written to support complex data binding. By contrast, simple data binding binds a single datum to a single property of a host control.

Note that nothing happened to the Customer cell when we ran the code. Back in the designer, click the single-celled range you mapped to the Customer property earlier, and take a look at its Properties pane. If you click the Advanced DataBinding property in the Properties pane, the dialog box shown in Figure 17.16 displays.

Figure 17.16. Creating a simple data binding.

Select the property you want to bindValueand in the Binding drop-down list, you can select Other Data Sources > Sheet1 List Instances > OrderDataSet1 > Order > Customer. Now we have binding information that associates the Value property on the host control with the Customer field in the dataset. When we run the code, the value from the dataset is copied automatically into the host control, and when the dataset is changed, the binding manager keeps the host control up to date. Note in this example that we have not created a master-details relationship between the customer and the orders; the list object will show all orders created by all customers. For an example of creating a master-details relationship, see Chapter 21, "Working with XML in Excel."

It does not work the other way, however. Unlike in our earlier list object example, changing the value in the cell does not propagate that change automatically back to the dataset. Why not?

In the Data Source Update Mode drop-down list in the top-left corner of the dialog box we just looked at, there are three choices: Never, OnValidation, and OnPropertyChanged. The last choice certainly seems like a sensible choice; when a property on the control changes, update the data source to keep it in sync.

Unfortunately, that does not work with Excel host controls. Why? Because you can create a binding to any old property of a host control, but we cannot change the fact that the aggregated Range objects do not source any "some property just changed" event that the binding manager can listen to. Windows Forms Controls do source such an event, but Word and Excel host controls do not.

This means that you need to tell the binding manager explicitly that the data source needs to be updated instead of relying on the control to inform its binding manager for you. Fortunately, doing this is simple. Double-click the mapped range in the designer to create a Change event handler automatically, and fill it in with code that forces the binding to update the source:

Private Sub OrderCustomerCell_Change(ByVal Target _   As Excel.Range) Handles OrderCustomerCell.Change   Me.OrderCustomerCell.DataBindings("Value").WriteValue() End Sub 

This line of code gets the changed data from the named range in this case back to the dataset. Next, you need to tell the bound dataset to accept changes made to it (because of WriteValue) by calling AcceptChanges on the dataset:

   MyDataSet.AcceptChanges() 

Alternatively, if you are using a BindingSource object, you can call EndEdit on the BindingSource object.

Data Binding in Word Documents

Word also supports creating XML mapped documents. Unlike the Excel designer, however, the Word designer does not create typed datasets automatically from mapped schemas. If you want to create a typed dataset from a schema mapped into Word, you have to add it to the project system yourself. Just add the schema XSD file to the project and then ensure that in its Properties pane, the Custom Tool property is set to MSDataSetGenerator. Then the build system will create the typed dataset for you.

Because simple data binding in Word is essentially the same as in Excel, and because Excel supports complex data binding in the list object host control, this chapter does not talk much more about data binding in Word.