Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath [Electronic resources] نسخه متنی

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 the 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
id="OrderDataSet"
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. However, Visual Studio knows nothing about what the source of the data will be, 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 to OrderBookBindingSource using the properties window. Then click on 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"there is no actual data in there. Also, there is no instance of the dataset on the components tray, so there is no chance that there will ever be data associated with this binding source as things stand now.

Notice that the project system has automatically added the schema to the project; 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.

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.

We have gotten most of the parts we need: The binding source is hooked up 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, and navigate the tree view to select Other Data Sources > Sheet1 List Instances > OrderDataSet1. Then click the DataMember property drop-down and select the Book table.

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"

private void Sheet1_Startup(object sender, System.EventArgs e)
{
// An order has a customer column
this.orderDataSet1.Order.AddOrderRow("Vlad the Impaler");
// A book has a title, ISBN and price, and is associated with
// a particular order.
this.orderDataSet1.Book.AddBookRow("Blood For Dracula",
"0-123-45678-9", 34.95, this.orderDataSet1.Order[0]);
this.orderDataSet1.Book.AddBookRow("Fang Attack!",
"9-876-54321-0", 14.44, this.orderDataSet1.Order[0]);
}

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

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 rather because many 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 shown in Figure 17-16 displays.

private void OrderCustomerCell_Change(Excel.Range Target)
{
this.OrderCustomerCell.DataBindings["Value"].WriteValue();
}