An End-to-End Scenario


This section deals with a more complex end-to-end scenario that puts together the schema-creation capabilities of Visual Studio and the schema-mapping capabilities of Excel. When you take a schema and map it into Excel using the XML Source task pane, you enable the exporting and importing of XML data in the spreadsheet. We are going to create an Excel spreadsheet that can be used to record a customer's book order. The spreadsheet will support the import and export of XML that conforms to our book-order schema. The spreadsheet will look like Figure 21.8.

Figure 21.8. An Excel spreadsheet for processing a book order.


Listing 21.5 shows the XML that this spreadsheet will be able to import and export.

Listing 21.5. XML File Generated from Book-Order Spreadsheet

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ns1:Order xmlns:ns1="http://tempuri.org/XMLSchema.xsd">      <ns1:CustomerName>Eric Carter</ns1:CustomerName>      <ns1:Date>2005-02-19</ns1:Date>      <ns1:Book>           <ns1:Title>Windows Forms Programming in C#</ns1:Title>           <ns1:ISBN>0-321-11620-8</ns1:ISBN>           <ns1:Publisher>Addison-Wesley</ns1:Publisher>           <ns1:Price>49.99</ns1:Price>      </ns1:Book>      <ns1:Book>           <ns1:Title>Effective C#</ns1:Title>           <ns1:ISBN>0-321-24566-0</ns1:ISBN>           <ns1:Publisher>Addison-Wesley</ns1:Publisher>           <ns1:Price>39.99</ns1:Price>      </ns1:Book>      <ns1:Book>           <ns1:Title>The C# Programming Language</ns1:Title>           <ns1:ISBN>0-321-15491-6</ns1:ISBN>           <ns1:Publisher>Addison-Wesley</ns1:Publisher>           <ns1:Price>29.99</ns1:Price>      </ns1:Book>      <ns1:Subtotal>119.97</ns1:Subtotal>      <ns1:Tax>10.7973</ns1:Tax>      <ns1:Total>130.7673</ns1:Total> </ns1:Order> 


Creating the Schema Using Visual Studio

To create this schema using Visual Studio, follow these steps:

1.

Start Visual Studio 2005.

2.

Create a new XSD file by choosing File > New > File or by pressing Ctrl+N.

3.

Choose XML Schema from the list of Visual Studio installed templates, as shown in Figure 21.4 earlier in this chapter; then click the Open button.

The schema design view appears, as shown in Figure 21.5 earlier in this chapter.

4.

Drag an element object off the toolbox onto the design surface.

5.

Type Order, and press the Enter key.

6.

In the * row, type CustomerName, and press Enter.

7.

In the * row, type Date, and press the Tab key; then type date for the data type, and press Enter.

8.

In the * row, type Subtotal, and press the Tab key; then type float for the data type, and press Enter.

9.

In the * row, type Tax, and press the Tab key; then type float for the data type, and press Enter.

10.

In the * row, type Total, and press the Tab key; then type float for the data type, and press Enter.

11.

Now right-click the Order element box, and choose New Element from the Add menu.

12.

Type Book, and press Enter.

13.

In the * row of the newly created Book element, type Title, and press Enter.

14.

In the * row of the newly created Book element, type ISBN, and press Enter.

15.

In the * row of the newly created Book element, type Publisher, and press Enter.

16.

In the * row of the newly created Book element, type Price, and press the Tab key; then type float for the data type, and press Enter. Now we now want to specify that multiple books can be included in an order.

17.

Click the Book row in the Order element box, and show the Properties window by choosing Properties Window from the View menu.

18.

For the property maxOccurs, type unbounded; for the property minOccurs, type 1.

19.

Save the schema, using the Save As command from the File menu.

20.

In the Save File As dialog box, drop down the Save As Type combo box, and pick XML Schema Files (*.xsd); for the filename, type BookOrder.xsd; and save it to a convenient place, such as the desktop.

Figure 21.9 shows what the final schema in Visual Studio should look like.

Figure 21.9. The book-order schema in Visual Studio.


Listing 21.6 shows the generated XSD file. Note that the sequence of Book elements in an Order element is now a sequence with a minimum (minOccurs) of one Book element and a maximum (maxOccurs) of unbounded Book elements. This will allow our schema to represent one or more Books in an Order. Also, having a sequence where maxOccurs is greater than 1 or unbounded will help Excel know that it needs to represent the Books in an Order using an Excel list.

Listing 21.6. Book-Order Schema XSD File

<?xml version="1.0" encoding="utf-8"?> <xs:schema targetNamespace="http://tempuri.org/XMLSchema.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema.xsd" xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">     <xs:element name="Order">         <xs:complexType>             <xs:sequence>                 <xs:element name="CustomerName"                  type="xs:string" />                 <xs:element name="Date" type="xs:date" />                 <xs:element name="Book"                  maxOccurs="unbounded" minOccurs="1">                     <xs:complexType>                         <xs:sequence>                             <xs:element name="Title"                              type="xs:string" />                             <xs:element name="ISBN"                              type="xs:string" />                             <xs:element name="Publisher"                              type="xs:string" />                             <xs:element name="Price"                              type="xs:float" />                         </xs:sequence>                     </xs:complexType>                 </xs:element>                 <xs:element name="Subtotal" type="xs:float" />                 <xs:element name="Tax" type="xs:float" />                 <xs:element name="Total" type="xs:float" />             </xs:sequence>         </xs:complexType>     </xs:element> </xs:schema> 


Adding a Schema to the Excel Workbook

Now that we have created a schema, let's add it to an Excel workbook. Launch Excel, and create a new, empty workbook. Bring up the Excel XML Source task pane, as described in the first section of this chapter. You should see the XML Source task pane with no mappings as yet in the task pane. To add an XML map, click the XML Maps button in the XML Source task pane. Doing so brings up the dialog box shown in Figure 21.10.

Figure 21.10. The XML Maps dialog box.


Click the Add button, and browse to wherever you saved your book-order schema. Select the schema, and click the Open button. The XML map now appears as a loaded XML map in the workbook. Using this dialog box, you can delete and rename an XML map. For now, we will just click the OK button to exit this dialog box.

The Excel XML Source task pane shows the XML map we just added, as shown in Figure 21.11.

Figure 21.11. The XML Source task pane with an XML map.


Mapping the Schema to the Excel Workbook

The XML Source task pane represents our book-order schema in a tree view. The icon associated with Order indicates a required parent element. The icons associated with CustomerName, Date, Title, ISBN, Publisher, Price, Subtotal, Tax, and Total indicate required child elements. The icon associated with Book indicates a required repeating parent element. Excel also supports other schema constructs, such as attributes and nonrequired elements and attributes. These constructs also have their own icons.

Let's try a few different ways of mapping the schema into the workbook. The first approach we will take is to click the root ns1:Order node in the XML Source task pane and drag it to cell A1 in the workbook. Excel creates one list to contain all the data, as shown in Figure 21.12.

Figure 21.12. The list created when ns1:Order is dragged to cell A1.


The XML Source task pane now indicates that all the elements have been mapped by bolding each element that has been mapped, as shown in Figure 21.13. Parent elements such as Order and Book are not mapped explicitly in the Workbook because these containing relationships do not need to be mapped directly to an Excel cell or list. You can remove a mapping by selecting the mapped cell or list in the Workbook and pressing the Delete key. You can also right-click the elements in the XML Source task pane that are in bold and choose Remove Element to remove the mapping.

Figure 21.13. Mapped elements are bolded in the XML Source task pane.


At the bottom of the XML Source task pane, click the Verify Map for Export link. Excel displays the dialog box shown in Figure 21.14.

Figure 21.14. Mapping cannot be exported because of denormalized data.


To consider why this mapping cannot be exported, let's import the XML in Listing 21.5 into our current mapping. In Excel, choose Data > XML > Import. Browse to an XML file containing the XML in Listing 21.5, and click the Import button. Because of the mapping we have established, Excel knows how to bring the XML into the list defined in the worksheet. Figure 21.15 shows the resulting worksheet.

Figure 21.15. Result of importing the XML in Listing 21.5.


The error we got when we clicked Verify Map for Export was that the mapping contained denormalized data. In Figure 21.15, we have highlighted the data that is denormalized and redundant. If we were to try to export this list by choosing Data > XML > Export, Excel would fail to export because it would not know how to deal with the redundant data.

Let's clear out this mapping and try to create a mapping that can be exported successfully as XML. Select the whole worksheet by pressing Ctrl+A and then press the Delete key. This time, we are going to drag in CustomerName, Date, Subtotal, Tax, and Total as individual cell mappings, and we will map the Book element sequence as a list.

To prepare the spreadsheet for mapping, let's put in some labels in advance. Figure 21.16 shows the resulting spreadsheet.

Figure 21.16. Preparing the spreadsheet for mapping.


Now do the following to map the nonrepeating elements to cells in the spreadsheet:

  1. Drag the CustomerName element from the XML Source task pane to cell C3.

  2. Drag the Date element from the XML Source task pane to cell C4. You will be prompted that the formatting in the cell does not match the format of the data. This is Excel noticing that the format of the cell you are mapping to is not formatted to contain a date.

  3. Click the Match Element Data Type button to continue and format the mapped cell as a date.

  4. Drag the Subtotal element from the XML Source task pane to cell C9.

  5. Drag the Tax element from the XML Source task pane to cell C10.

  6. Drag the Total element from the XML Source task pane to cell C11.

Finally, let's map the repeating elements to a list by dragging the Book element to cell B6. Because Book is a repeating element in a sequence with 1 to unbounded elements, this will create a list containing the elements Title, ISBN, Publisher, and Price as column headers.

The column headers created by Excel have the format ns1:Title rather than Title. You can edit these columns in the spreadsheet without breaking the XML mapping.

We are also going to use some features of Excel in our spreadsheet:

  1. Right-click the list object that was created, and from the pop-up menu, choose Total Row from the List menu.

  2. Click the bottom-right cell of the List object in the total row (cell E8).

  3. Pick Sum from the drop-down list that appears next to the cell.

  4. Click cell C10, and in the formula bar, type the formula =E8. This causes the total created in the total row to be saved in the Subtotal element as well.

  5. Click cell C11, and in the formula bar, type the formula =C10*.09 to calculate a 9 percent sales tax.

  6. Click cell C12, and in the formula bar, type the formula =SUM(C10:C11).

    This sums the cost of the books and the sales tax. Let's also do some formatting.

  7. Click the cells C10 through C12, and click the $ button to format these cells as currency.

  8. Also click the column header for the Price column in the list, and format this column as currency, because it is the column where book prices will go.

Now the spreadsheet will look like the one shown in Figure 21.17. Note the blue borders around all the mapped cells or lists. You can hide these blue borders by using the Options button in the XML Source task pane. Click the Options button and then choose the Hide Border of Inactive Lists command from the pop-up menu.

Figure 21.17. The final mapped spreadsheet.


Now fill out the spreadsheet to make it look like Figure 21.8 earlier in this chapter. Note that when you have the list selected, a new row marked with * displays; you can enter new items in that row. Also note that as you type prices, the total row sums the prices in the list, and the formulas in the spreadsheet calculate the Tax and Total.

With the spreadsheet filled out, let's export the data in the spreadsheet as XML conforming to the schema we have mapped. We have assumed that this mapping will be exportable. To verify that, click the Verify Map for Export link in the XML Source task pane. A dialog box should appear that says that our mapping is exportable.

Choose Data > XML > Export. Type the name of the XML file you want to export tosomething like bookorder.xml. Then, after exporting the file, open it in a text editor such as Notepad. You should see the XML very similar to that shown in Listing 21.5 earlier in this chapter.




Visual Studio Tools for Office(c) Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
ISBN: 0321411757
EAN: 2147483647
Year: N/A
Pages: 221

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net