| This section examines VSTO's support for Excel schema mapping. Let's create a new VSTO Excel project based on the book-order spreadsheet we created in this chapter. Launch VSTO, and choose File > New > Project. In the New Project dialog box, choose a Visual Basic Excel Workbook project. Give the project a name and location; then click the OK button. A dialog box appears, asking for a document to be used for the application. Click the Copy an Existing Document radio button. Then click the ellipsis (...) button to browse to the spreadsheet you created in this chapter that has the book-order schema mapped in it. Click the Finish button to create the project. We want to consider several features of the generated VSTO project. First is the creation of XMLMappedRange controls. Second is the creation of ListObject controls. Third is the addition of the schema mapped to our spreadsheet to the VSTO project. Finally, we will consider how to use the controls that are created and the schema that is added to the VSTO project to connect data binding in the project. XMLMappedRange Controls Use the class view to browse the members associated with Sheet1. Notice as you browse that the member variables listed in Table 21.3 have been created automatically based on the XML mapping in the spreadsheet to the book-order schema. Table 21.3. Sheet1 Member Variables Created from Schema MappingName | Type |
|---|
BookList | Microsoft.Office.Tools.Excel.ListObject | OrderCustomerNameCell | Microsoft.Office.Tools.Excel.XmlMappedRange | OrderDateCell | Microsoft.Office.Tools.Excel.XmlMappedRange | OrderSubtotalCell | Microsoft.Office.Tools.Excel.XmlMappedRange | OrderTaxCell | Microsoft.Office.Tools.Excel.XmlMappedRange | OrderTotalCell | Microsoft.Office.Tools.Excel.XmlMappedRange |
For each nonrepeating element or attribute mapped to a cell in the Excel spreadsheet, VSTO creates an XMLMappedRange control. We mapped the CustomerName element from the Order element into a cell, for example. VSTO created an XMLMappedRange corresponding to this cell called OrderCustomerNameCell. An XMLMappedRange control has all the properties and methods of an Excel Range object. In addition, it has several events that are not found on the Excel Range object: XMLMappedRange.BeforeDoubleClick is raised when the cell corresponding to the mapped element or attribute is double-clicked. Excel passes a target parameter of type Range for the range of cells that was double-clicked and a Boolean cancel parameter passed by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default double-click behavior. XMLMappedRange.BeforeRightClick is raised when the cell corresponding to the mapped element or attribute is right-clicked. Excel passes a target parameter of type Range for the range of cells that was right-clicked. The target parameter is provided so you can determine whether multiple cells were selected when the right-click occurred. Excel also passes a Boolean cancel parameter by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default right-click behavior. XMLMappedRange.Change is raised when the cell corresponding to the mapped element or attribute is changed by a user editing the cell or when a cell is linked to external data and is changed as a result of refreshing the cell from the external data. Change events are not raised when a cell is changed as a result of a recalculation. They are also not raised when the user changes the formatting of the cell without changing the value of the cell. Excel passes a target parameter of type Range for the range of cells that was changed. The target parameter is provided so you can determine whether multiple cells were changed at the same timefor example, if the user dragged the bottom-right corner of a particular cell to drag that value across multiple cells. XMLMappedRange.Deselected is raised when the cell corresponding to the mapped element or attribute is deselected. Excel passes a target parameter of type Range for the range of cells that was deselected. The target parameter is provided so you can determine whether multiple cells were deselected at the same time. XMLMappedRange.Selected is raised when the cell corresponding to the mapped element or attribute is selected. Excel passes a target parameter of type Range for the range of cells that was selected. The target parameter is provided so you can determine whether multiple cells were selected at the same time. XMLMappedRange.SelectionChange is raised when the cell corresponding to the mapped element or attribute is deselected or selected. Excel passes a target parameter of type Range for the range of cells that was deselected or selected. The target parameter is provided so you can determine whether multiple cells were deselected or selected at the same time.
Listing 21.7 shows a VSTO customization that handles all the events associated with an XMLMappedRange. In this case, we choose to handle events associated with the XMLMappedRange called OrderCustomerNameCell, which corresponds to the CustomerName element from our book-order schema that we mapped to Sheet1 in the Excel workbook. Listing 21.7. A VSTO Excel Customization That Handles All Events Associated with an XML-MappedRange Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Public Class Sheet1 Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup End Sub Private Function GetAddress( _ ByVal target As Excel.Range) As String Return target.Address( _ ReferenceStyle:=Excel.XlReferenceStyle.xlA1) End Function Private Sub OrderCustomerNameCell_BeforeDoubleClick( _ ByVal target As Excel.Range, ByRef cancel As Boolean) _ Handles OrderCustomerNameCell.BeforeDoubleClick MsgBox(String.Format("{0} BeforeDoubleClick.", _ GetAddress(target))) End Sub Private Sub OrderCustomerNameCell_BeforeRightClick(_ ByVal target As Excel.Range, ByRef cancel As Boolean) _ Handles OrderCustomerNameCell.BeforeRightClick MsgBox(String.Format("{0} BeforeRightClick.", _ GetAddress(target))) End Sub Private Sub OrderCustomerNameCell_Change(_ ByVal target As Excel.Range) _ Handles OrderCustomerNameCell.Change MsgBox(String.Format("{0} Change.", GetAddress(target))) End Sub Private Sub OrderCustomerNameCell_Deselected( _ ByVal target As Excel.Range) _ Handles OrderCustomerNameCell.Deselected MsgBox(String.Format("{0} Deselected.", GetAddress(target))) End Sub Private Sub OrderCustomerNameCell_Selected( _ ByVal target As Excel.Range) _ Handles OrderCustomerNameCell.Selected MsgBox(String.Format("{0} Selected.", GetAddress(target))) End Sub Private Sub OrderCustomerNameCell_SelectionChange( _ ByVal target As Excel.Range) _ Handles OrderCustomerNameCell.SelectionChange MsgBox(String.Format("{0} SelectionChange.", _ GetAddress(target))) End Sub End Class
|
ListObject Controls As you saw in Table 21.3 earlier in this chapter, a ListObject control was created for the repeating Book element in our mapped schema. A ListObject control is created for any repeating element. A ListObject control has all the properties and methods of an Excel ListObject object. In addition, it has several events that are not found on the Excel ListObject object: ListObject.BeforeAddDataboundRow is described in Chapter 17, "VSTO Data Programming." ListObject.BeforeDoubleClick is raised when any cell contained by the ListObject is double-clicked. Excel passes a target parameter of type Range for the range of cells that was double-clicked and a Boolean cancel parameter passed by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default double-click behavior. ListObject.BeforeRightClick is raised when any cell contained by the ListObject is right-clicked. Excel passes a target parameter of type Range for the range of cells that was right-clicked. The target parameter is provided so you can determine whether multiple cells were selected when the right-click occurred. Excel also passes a Boolean cancel parameter by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default right-click behavior. ListObject.Change is raised when any cell contained by the ListObject is changed by a user editing the cell or when a cell is linked to external data and is changed as a result of refreshing the cell from the external data. Change events are not raised when a cell is changed as a result of a recalculation. They are also not raised when the user changes the formatting of the cell without changing the value of the cell. Excel passes a target parameter of type Range for the range of cells that was changed. The target parameter is provided so you can determine whether multiple cells were changed at the same timefor example, if the user dragged the bottom-right corner of a particular cell to drag that value across multiple cells. ListObject.DataBindingFailure is described in Chapter 17. ListObject.DataMemberChanged is described in Chapter 17. ListObject.DataSourceChanged is described in Chapter 17. ListObject.Deselected is raised when any cell contained by the ListObject is deselected. Excel passes a target parameter of type Range for the range of cells that was deselected. The target parameter is provided so you can determine whether multiple cells were deselected at the same time. ListObject.ErrorAddDataboundRow is described in Chapter 17. ListObject.OriginalDataRestored is described in Chapter 17. ListObject.Selected is raised when any cell contained by the ListObject is selected. Excel passes a target parameter of type Range for the range of cells that was selected. The target parameter is provided so you can determine whether multiple cells were selected at the same time. ListObject.SelectedIndexChanged is described in Chapter 17. ListObject.SelectionChange is raised when any cell contained by the ListObject is deselected or selected. Excel passes a target parameter of type Range for the range of cells that was deselected or selected. The target parameter is provided so you can determine whether multiple cells were deselected or selected at the same time.
Listing 21.8 shows a VSTO customization that handles all the events associated with a ListObject. In this case, we choose to handle events associated with the ListObject called BookList, which corresponds to the repeating Book element from our book-order schema that we mapped to a list in Sheet1 in the Excel workbook. Listing 21.8. A VSTO Excel Customization That Handles All Events Associated with a ListObject Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Public Class Sheet1 Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup End Sub Private Function GetAddress(ByVal target As Excel.Range, _ ByVal event1 As String) As String Return String.Format("{0} {1}.", _ target.Address( _ ReferenceStyle:=Excel.XlReferenceStyle.xlA1), _ event1) End Function Private Sub BookList_BeforeAddDataBoundRow( _ ByVal sender As System.Object, _ ByVal e As Excel.BeforeAddDataBoundRowEventArgs) _ Handles BookList.BeforeAddDataBoundRow MsgBox("BeforeAddDataBoundRow") End Sub Private Sub BookList_BeforeDoubleClick( _ ByVal Target As Excel.Range, _ ByRef Cancel As System.Boolean) _ Handles BookList.BeforeDoubleClick MsgBox(GetAddress(Target, "BeforeDoubleClick")) End Sub Private Sub BookList_BeforeRightClick( _ ByVal Target As Excel.Range, _ ByRef Cancel As System.Boolean) _ Handles BookList.BeforeRightClick MsgBox(GetAddress(Target, "BeforeRightClick")) End Sub Private Sub BookList_BindingContextChanged( _ ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles BookList.BindingContextChanged MsgBox("BindingContextChanged") End Sub Private Sub BookList_Change( _ ByVal targetRange As Excel.Range, _ ByVal changedRanges As Excel.ListRanges) _ Handles BookList.Change MsgBox(GetAddress(targetRange, "Change")) End Sub Private Sub BookList_DataBindingFailure( _ ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles BookList.DataBindingFailure MsgBox("DataBindingFailure") End Sub Private Sub BookList_DataMemberChanged( _ ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles BookList.DataMemberChanged MsgBox("DataMemberChanged") End Sub Private Sub BookList_DataSourceChanged( _ ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles BookList.DataSourceChanged MsgBox("DataSourceChanged") End Sub Private Sub BookList_Deselected(ByVal Target As Excel.Range) _ Handles BookList.Deselected MsgBox(GetAddress(Target, "Deselected")) End Sub Private Sub BookList_ErrorAddDataBoundRow( _ ByVal sender As System.Object, _ ByVal e As Excel.ErrorAddDataBoundRowEventArgs) _ Handles BookList.ErrorAddDataBoundRow MsgBox("ErrorAddDataBoundRow") End Sub Private Sub BookList_OriginalDataRestored( _ ByVal sender As System.Object, _ ByVal e As Excel.OriginalDataRestoredEventArgs) _ Handles BookList.OriginalDataRestored MsgBox("OriginalDataRestored") End Sub Private Sub BookList_Selected(ByVal Target As Excel.Range) _ Handles BookList.Selected MsgBox(GetAddress(Target, "Selected")) End Sub Private Sub BookList_SelectedIndexChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles BookList.SelectedIndexChanged MsgBox("SelectedIndexChanged") End Sub Private Sub BookList_SelectionChange( _ ByVal Target As Excel.Range) _ Handles BookList.SelectionChange MsgBox(GetAddress(Target, "SelectionChange")) End Sub End Class
|
Schema Added to the VSTO Project The final thing to notice about our generated VSTO project is that VSTO automatically adds the schema that was mapped into the workbook as a project item in the project, as shown in Figure 21.22. This schema is added to support the data binding features discussed in the next section. The schema is a copy of your original schema file that is copied to the project directory of the newly created project. Figure 21.22. The VSTO Excel project with the Order schema. 
When you create an XML map, Excel grabs the schema you add and keeps a copy of it in the Excel workbook. If the schema file you created the XML map from is changed, Excel does not detect it. So if you edit the schema in Visual Studio, you have to save the schema, remove the XML map corresponding to the schema from the Excel worksheet, add the XML map again by browsing to the updated schema in your project directory, and then reapply your XML mappings. To add and remove XML mappings without leaving Visual Studio, VSTO provides a toolbar button for displaying the XML Source task pane quickly, as shown in Figure 21.23. The button that displays the XML Source task pane is the second button from the left in the toolbar. As you map schemas using the XML Source task pane, VSTO automatically adds XMLMappedRange or ListObject member variables for new mappings. Figure 21.23. The VSTO Excel toolbar with the XML Source task pane button. 
Combining XML Mapping with VSTO Data Binding Given an XML mapping in a worksheet, you can programmatically import and export XML conforming to the schema associated with the mapping using the Excel object model. You may also want to combine this functionality with VSTO's support for data binding. Data binding will allow you to connect the worksheet to not just one book order, but also to a database with many book orders. You can easily move a cursor in the database from row to row in the database and update the contents of the worksheet. The first step is to build the project. This will result in the creation of a typed dataset for the order schema called NewDataSet. After you have built the project, make sure that the toolbox is showing, and expand the Data tab, as shown in Figure 21.24. Note the component tray in Figure 21.24the empty area below the Excel worksheet. We will add one component to the component tray and use it later to data-bind the ListObject that was created when the schema was mapped into the workbook. From the Data tab, drag a BindingSource component to the component tray. Name this BindingSource OrderBookConnector. We are going to ignore this component for the time being, because our initial goal is to data-bind the XMLMappedRange controls in our worksheet. Figure 21.24. The DataSet component and the component tray. 
Drag the DataSet component from the toolbox into the component traythe empty area below the Excel worksheet. The dialog box in Figure 21.25 will display. Pick the Typed Dataset option; then pick the NewDataSet. This is the dataset that was created from our Order schema. Finally, click the OK button. Figure 21.25. The Add Dataset dialog box. 
This will create a component called newDataSet1 in the component tray. Right-click the newly added component, and choose Properties from the pop-up menu. Doing so will show and activate the Visual Studio Properties window. Let's change the name for the typed dataset component from newDataSet1 to the more descriptive name BookOrderDataSet by typing this new name in the (Name) row in the Properties window and pressing the Enter key. Because BookOrderDataSet is a typed dataset created from our Orders schema, as shown in Figure 21.22 earlier in this chapter, we know that the dataset contains two tables: Order and Book. Now we want to connect the fields that come from the Order table to the corresponding XMLMappedRange controls in Sheet1. To do that, we must add a BindingSource component by dragging a BindingSource from the Data tab in the toolbox to the component tray. This creates a BindingSource called bindingSource1, which we will rename OrderConnector because it will be used to connect the Order table from the BookOrderDataSet to the XMLMappedRange controls in the workbook. Using the Properties window, set the DataSource property of OrderConnector to BookOrderDataSet. Figure 21.26 shows the drop-down list that appears. Note that we have to expand the Other Data Sources and Form List Instances nodes to find the BookOrderDataSet that we have already added to the component tray. Figure 21.26. Setting a DataSource for OrderConnector using the Properties window. 
With the DataSource property set to BookOrderDataSet, now we need to set the DataMember property to the Order table. Figure 21.27 shows the drop-down list that appears. Note that the only options available are the Order table and the Book table. Pick the Order table. Figure 21.27. Setting the DataMember for OrderConnector using the Properties window. 
Now we are ready to connect individual XMLMappedRange controls to OrderConnector. Click the cell mapped to the CustomerName element in the Excel spreadsheet; doing so selects the XMLMappedRange associated with CustomerName called OrderCustomerNameCell. Expand the (DataBindings) node in the Properties window, and click the drop-down arrow associated with the property Value. You will see the drop-down list shown in Figure 21.28. Expand the OrderConnector node, and click CustomerName. You have data-bound the Value property of OrderCustomerNameCell to OrderConnector's CustomerName. Figure 21.28. Setting a data binding connecting OrderCustomerNameCell.Value to OrderConnector.CustomerName. 
Now click the cell associated with Date, expand the (DataBindings) node in the Properties window for the XMLMappedRange OrderDateCell, and data-bind the Value property to the Date field coming from OrderConnector. Continue to do this for the cells associated with Subtotal, Tax, and Total. Now let's connect the ListObject. Earlier, you created a BindingSource that you named OrderBookConnector. Click the ListObject in the spreadsheet, and in the Properties window, set the ListObject's DataSource property to OrderBookConnector. Next, we need to connect the OrderBookConnector to our data. We could connect OrderBookConnector directly to the Book table in BookOrderDataSet, but this would not give us the behavior we want for this example. We want to allow BookOrder DataSet to contain multiple book orders, and as we move from row to row in the Order table via OrderConnector, we want to show only the books for that particular order. If we connect OrderBookConnector to the Book table in BookOrderDataSet, this will result in all books in the books table being shown, no matter what row is being shown from the Order table by OrderConnector. What we need is a way to tie OrderBookConnector to OrderConnector. Instead of connecting the OrderBookConnector to BookOrderDataSet, we connect it to the existing OrderConnector corresponding to our Order table. Doing so causes what is sometimes called a master-details relationship. As the OrderDataConnector moves from row to row in the Order table, our OrderBookConnector will display only the Books that correspond to the order row that OrderConnector is displaying. In the Properties window, set the DataSource property by expanding the OrderConnector node and selecting Order_Book, as shown in Figure 21.29. Figure 21.29. Connecting OrderBookConnector to OrderConnector. 
To create this relationship between the OrderConnector and the OrderBookConnector, VSTO creates a third BindingSource, called orderBookBindingSource, that acts as an intermediate connector between OrderConnector and OrderBookConnector. Figure 21.30 shows the resulting configuration of the DataSet and the three BindingSource components. Figure 21.30. The relationship among the dataset, binding sources, and data bindings. 
Now let's add some code to Sheet1's Startup event so that this application does something interesting, as shown in Listing 21.9. We are going to populate our dataset with three orders. When the user double-clicks the Excel spreadsheet, we will call the MoveNext method on the OrderConnector to move to the next order or row in the Order table in the BookOrderDataSet. Listing 21.9. A VSTO Excel Customization That Populates a Dataset and Uses the MoveNext Method Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Public Class Sheet1 Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup Dim order1 As NewDataSet.OrderRow = _ BookOrderDataSet.Order.AddOrderRow( _ "Eric Carter", DateTime.Now, 39.99F, 1.0F, 40.99F) Dim order1book1 As NewDataSet.BookRow = _ BookOrderDataSet.Book.AddBookRow( _ "Effective C#", "0-321-24566-0", _ "Addison-Wesley", 39.99F, order1) Dim order2 As NewDataSet.OrderRow = _ BookOrderDataSet.Order.AddOrderRow( _ "Andrew Clinick", DateTime.Now, 49.99F, 1.0F, 50.99F) Dim order2book1 As NewDataSet.BookRow = _ BookOrderDataSet.Book.AddBookRow( _ "Windows Forms Programming in C#", "0-321-11620-8",_ "Addison-Wesley", 49.99F, order2) Dim order3 As NewDataSet.OrderRow = _ BookOrderDataSet.Order.AddOrderRow( _ "Eric Lippert", DateTime.Now, 29.99F, 1.0F, 30.99F) Dim order3book1 As NewDataSet.BookRow = _ BookOrderDataSet.Book.AddBookRow( _ "The C# Programming Language", "0-321-15491-6",_ "Addison-Wesley", 29.99F, order3) End Sub Private Sub Sheet1_BeforeDoubleClick( _ ByVal Target As Excel.Range, _ ByRef Cancel As System.Boolean) _ Handles Me.BeforeDoubleClick OrderConnector.MoveNext() End Sub End Class
|
|
