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

VSTO Support for Excel Schema Mapping

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 from the New menu in the File menu choose Project. In the New Project dialog, choose a C# Excel Application project. Give the project a name and location then click the OK button. A dialog then appears asking for a document to be used for the application. Click the Copy an existing document radio button. Then click the "..." 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 hook up 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.

For each nonrepeating element or attribute mapped to a cell in the Excel spreadsheet, VSTO creates an XMLMappedRange control. For example, we mapped the CustomerName element from the Order element into a cell. VSTO created an XMLMappedRange corresponding to this cell called OrderCustomerNameCell. An XMLMappedRanged 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 bool 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 bool 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 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 oncefor example, if the user dragged the lower-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 once.

• 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 once.

• 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 once.

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 XMLMappedRange

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.OfficeTools.Interop.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
namespace ExcelWorkbook1
{
public partial class Sheet1
{
private void Sheet1_Startup(object sender, EventArgs e)
{
this.OrderCustomerNameCell.BeforeDoubleClick += new
Excel.DocEvents_BeforeDoubleClickEventHandler(
OrderCustomerNameCell_BeforeDoubleClick);
this.OrderCustomerNameCell.BeforeRightClick += new
Excel.DocEvents_BeforeRightClickEventHandler(
OrderCustomerNameCell_BeforeRightClick);
this.OrderCustomerNameCell.Change += new
Excel.DocEvents_ChangeEventHandler(
OrderCustomerNameCell_Change);
this.OrderCustomerNameCell.Deselected += new
Excel.DocEvents_SelectionChangeEventHandler(
OrderCustomerNameCell_Deselected);
this.OrderCustomerNameCell.Selected += new
Excel.DocEvents_SelectionChangeEventHandler(
OrderCustomerNameCell_Selected);
this.OrderCustomerNameCell.SelectionChange += new
Excel.DocEvents_SelectionChangeEventHandler(
OrderCustomerNameCell_SelectionChange);
}
#region VSTO Designer generated code
private void InternalStartup ()
{
this.Startup += new EventHandler(Sheet1_Startup);
}
#endregion
string GetAddress(Excel.Range target)
{
return target.get_Address(missing, missing,
Excel.XlReferenceStyle.xlA1, missing, missing);
}
void OrderCustomerNameCell_BeforeDoubleClick(
Excel.Range target, ref bool cancel)
{
MessageBox.Show(String.Format(
"{0} BeforeDoubleClick.",
GetAddress(target));
}
void OrderCustomerNameCell_BeforeRightClick(
Excel.Range target, ref bool cancel)
{
MessageBox.Show(String.Format(
"{0} BeforeRightClick.",
GetAddress(target));
}
void OrderCustomerNameCell_Change(Excel.Range target)
{
MessageBox.Show(String.Format(
"{0} Change.",
GetAddress(target));
}
void OrderCustomerNameCell_Deselected(Excel.Range target)
{
MessageBox.Show(String.Format(
"{0} Deselected.",
GetAddress(target));
}
void OrderCustomerNameCell_Selected(Excel.Range target)
{
MessageBox.Show(String.Format(
"{0} Selected.",
GetAddress(target));
}
void OrderCustomerNameCell_SelectionChange(Excel.Range target)
{
MessageBox.Show(String.Format(
"{0} SelectionChange.",
GetAddress(target));
}
}
}

ListObject Controls

As you saw in Chapter 17, "VSTO Data Programming."

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

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.OfficeTools.Interop.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
namespace ExcelWorkbook1
{
public partial class Sheet1
{
private void Sheet1_Startup(object sender, EventArgs e)
{
this.BookList.BeforeAddDataboundRow += new
Microsoft.Office.Tools.Excel.BeforeAddDataboundRowHandler(
BookList_BeforeAddDataboundRow);
this.BookList.BeforeDoubleClick += new
Excel.DocEvents_BeforeDoubleClickEventHandler(
BookList_BeforeDoubleClick);

this.BookList.BeforeRightClick += new
Excel.DocEvents_BeforeRightClickEventHandler(
BookList_BeforeRightClick);
this.BookList.Change += new
Microsoft.Office.Tools.Excel.ListObjectChangeHandler(
BookList_Change);
this.BookList.DataBindingFailure += new
EventHandler(BookList_DataBindingFailure);
this.BookList.DataMemberChanged += new
EventHandler(BookList_DataMemberChanged);
this.BookList.DataSourceChanged += new
EventHandler(BookList_DataSourceChanged);
this.BookList.Deselected += new
Excel.DocEvents_SelectionChangeEventHandler(
BookList_Deselected);
this.BookList.ErrorAddDataboundRow += new
Microsoft.Office.Tools.Excel.ErrorAddDataboundRowHandler(
BookList_ErrorAddDataboundRow);
this.BookList.OriginalDataRestored += new
Microsoft.Office.Tools.Excel.OriginalDataRestoredEventHandler(
BookList_OriginalDataRestored);
this.BookList.Selected += new
Excel.DocEvents_SelectionChangeEventHandler(
BookList_Selected);
this.BookList.SelectedIndexChanged += new
EventHandler(BookList_SelectedIndexChanged);

this.BookList.SelectionChange += new
Excel.DocEvents_SelectionChangeEventHandler(
BookList_SelectionChange);
}
#region VSTO Designer generated code
private void InsternalStartup()
{
this.Startup += new EventHandler(Sheet1_Startup);
}
#endregion
string GetAddress(Excel.Range target, string event)
{
return String.Format("{0} {1}.",
target.get_Address(missing, missing,
Excel.XlReferenceStyle.xlA1, missing, missing),
event);
}
void BookList_BeforeAddDataboundRow(object sender,
Microsoft.Office.Tools.Excel.BeforeAddDataboundRowEventArgs e)
{
MessageBox.Show("BeforeAddDataboundRow");
}
void BookList_BeforeDoubleClick(Excel.Range target,
ref bool cancel)
{
MessageBox.Show(GetAddress(target, "BeforeDoubleClick"));
}
void BookList_BeforeRightClick(Excel.Range target,
ref bool cancel)
{
MessageBox.Show(GetAddress(target, "BeforeRightClick"));
}
void BookList_Change(Excel.Range targetRange,
Microsoft.Office.Tools.Excel.ListRanges changedRanges)
{
MessageBox.Show(GetAddress(targetRange, "Change"));
}
void BookList_DataBindingFailure(object sender, EventArgs e)
{
MessageBox.Show("DataBindingFailure");
}
void BookList_DataMemberChanged(object sender, EventArgs e)
{
MessageBox.Show("DataMemberChanged");
}
void BookList_DataSourceChanged(object sender, EventArgs e)
{
MessageBox.Show("DataSourceChanged");
}
void BookList_Deselected(Excel.Range target)
{
MessageBox.Show(GetAddress(target, "Deselected"));
}
void BookList_ErrorAddDataboundRow(object sender,
Microsoft.Office.Tools.Excel.ErrorAddDataboundRowEventArgs e)
{
MessageBox.Show("ErrorAddDataboundRow");
}
void BookList_OriginalDataRestored(object sender,
Microsoft.Office.Tools.Excel.OriginalDataRestoredEventArgs e)
{
MessageBox.Show("OriginalDataRestored");
}
void BookList_Selected(Excel.Range target)
{
MessageBox.Show(GetAddress(target, "Selected"));
}
void BookList_SelectedIndexChanged(object sender, EventArgs e)
{
MessageBox.Show("SelectedIndexChanged");
}
void BookList_SelectionChange(Excel.Range target)
{
MessageBox.Show(GetAddress(target, "SelectionChange"));
}
}
}

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.

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 then have to save the schema, remove the XML map corresponding to the schema from the Excel worksheet, re-add the XML map by browsing to the updated schema in your project directory, and then re-apply your XML mappings.

To add and remove XML mappings without leaving Visual Studio, VSTO provides a toolbar button for quickly displaying the XML Source task pane as shown in Figure 21-23. The button that displays the XML Source task pane is the second button in the toolbar. As you map schemas using the XML Source task pane, VSTO automatically adds XMLMappedRange or ListObject member variables for new mappings.

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 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 a typed dataset being created for the order schema called NewDataSet. After you have built the project, make sure 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 additional component to the component tray that we will use 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.

Drag the DataSet component from the toolbox into the component traythe empty area below the Excel worksheet. The dialog 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.

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 Properties window. Let's change the name for the typed data set 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, we know that the dataset contains two tables. The two tables are the Order table and the Book table. We now 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 to OrderConnector because it will be used to connect the Order table from the BookOrderDataSet to the XMLMapped Range controls in the workbook.

Using the Properties window, set the DataSource property of OrderConnector to BookOrderDataSet. Figure 21-26 shows the drop-down that appears. Note that we have to expand out the Other Data Sources and Form List Instances nodes to find the BookOrderDataSet that we have already added to the component tray.

With the DataSource property set to BookOrderDataSet, we now need to set the DataMember property to the Order table. Figure 21-27 shows the drop-down that appears. Note that the only options available are the Order table or the Book table. Pick the Order table.

Now we are ready to connect individual XMLMappedRange controls to OrderConnector. Click the cell mapped to the CustomerName element in the Excel spreadsheetdoing so selects the XMLMappedRange associated with CustomerName called OrderCustomerNameCell. Expand out the (DataBindings) node in the Properties window and click the drop-down arrow associated with the property Value. You will see the drop-down shown in Figure 21-28. Expand out the OrderConnector node and click CustomerName. You have now data bound the Value property of OrderCustomerNameCell to OrderConnector's CustomerName.

Now click the cell associated with Date, expand out 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 on the ListObject in the spreadsheet, and in the Properties window, set the ListObject's DataSource property to OrderBookConnector. We next 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 BookOrderDataSet to contain multiple book orders, and as we move from row to row in the Order table via OrderConnector, we want to only show 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.

To create this relationship between the OrderConnector and the OrderBook Connector, VSTO creates a third BindingSource, called orderBookBindingSource, which acts as an intermediate connector between OrderConnector and OrderBookConnector. Figure 21-30 shows the resulting configuration of the DataSet and the three BindingSource components.

Now let's add some code to Sheet1's Startup event so that this application does something interesting. 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

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.OfficeTools.Interop.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
namespace ExcelWorkbook1
{
public partial class Sheet1
{
private void Sheet1_Startup(object sender, System.EventArgs e)
{
NewDataSet.OrderRow order1 = bookOrderDataSet.Order.
AddOrderRow("Eric Carter", DateTime.Now,
39.99f, 1.00f, 40.99f);
NewDataSet.BookRow order1book1 = bookOrderDataSet.Book.
AddBookRow("Effective C#", "0-321-24566-0",
"Addison-Wesley", 39.99f, order1);
NewDataSet.OrderRow order2 = bookOrderDataSet.Order.
AddOrderRow("Andrew Clinick", DateTime.Now,
49.99f, 1.00f, 50.99f);
NewDataSet.BookRow order2book1 = bookOrderDataSet.Book.
AddBookRow("Windows Forms Programming in C#",
"0-321-11620-8", "Addison-Wesley", 49.99f, order2);
NewDataSet.OrderRow order3 = bookOrderDataSet.Order.
AddOrderRow("Eric Lippert", DateTime.Now,
29.99f, 1.00f, 30.99f);
NewDataSet.BookRow order3book1 = bookOrderDataSet.Book.
AddBookRow("The C# Programming Language",
"0-321-15491-6", "Addison-Wesley", 29.99f, order3);
BeforeDoubleClick += new
Excel.DocEvents_BeforeDoubleClickEventHandler(
Sheet1_BeforeDoubleClick);
}
#region VSTO Designer generated code
private void InternalStartup()
{
this.Startup += newEventHandler(Sheet1_Startup);
}
void Sheet1_BeforeDoubleClick(Excel.Range target,
ref bool cancel)
{
OrderConnector.MoveNext();
}
}
}