Using the Excel XML Toolbox for Microsoft Office Excel 2003

Chris Kunicki
Charles Maxson
OfficeZealot.com

May 2005

Applies to:
    Microsoft Office Excel 2003

Summary: The Excel XML Toolbox for Microsoft Office Excel 2003 provides a number of useful tools for working with XML in Excel 2003. This article walks through a few major features of the toolbox and discusses their benefits. (8 printed pages)

Download xlxmltbx.msi

Contents

Introduction
Viewing Schema and XML Data
Reloading Modified Schemas
Building Schemas
Conclusion
Additional Resources

Introduction

Microsoft Office Excel 2003 includes new and enhanced XML features. With Excel 2003, you can easily import XML and map XML nodes to cells or lists, export XML, use schemas, and connect to XML Web services.

Excel offers a flexible and rich environment for manipulating XML data. To further improve the developer experience, Microsoft has released the Excel XML Toolbox for Microsoft Office Excel 2003. These tools can help to solve a variety of challenges.

The Excel XML Toolbox is an add-in that installs directly into the Excel user interface, as shown in Figure 1. The Excel XML Toolbox toolbar contains the frequently used features of the toolbox. The XML Toolbox menu lists all toolbox features.

Figure 1. The top circle shows how the Excel XML Toolbox integrates into the Excel menu bar; the bottom circle shows the XML Toolbox toolbar (click to see larger image)

The Excel XML Toolbox helps developers in a number of ways:

Working with Custom-defined XML Schema

• Reload schema into workbooks while preserving cell mappings.
• Build a schema or add to an existing schema, directly from Excel.
• View the schema for XML maps stored within workbooks.

Working with XML and the Excel Object Model

• Quickly refresh all XML maps with bound data sources.
• Refresh XML maps with bound data sources and persist cell formulas for mapped cells.
• Use a new XML Range Properties tool to view XML mapped cell properties and to copy the XPath or Microsoft Visual Basic for Applications (VBA) statement to the Clipboard.
• View the exportable XML data without exporting.

Working with SpreadsheetML

• Quickly save the workbook as SpreadsheetML.
• View the SpreadsheetML for the active workbook.

Shortcuts and More

• Quickly open and close the XML Source task pane.
• Turn on and off the borders for XML mapped cells.
• Import and export XML.

The rest of this article describes some of the most useful features of the Excel XML Toolbox. If you have not already done so, install the Excel XML Toolbox now. You can find the sample files in a subdirectory of the installation path for the Excel XML Toolbox, in a folder titled "Samples."

First open the workbook titled "Billing statement - XML Mapped.xls". The fictional company Coho Winery uses this workbook to send billing statement summaries to its customers. The workbook contains information about the customers and line items for paid and unpaid invoices for the past 30 days.

Coho Winery has an XML schema that defines the structure of a billing statement. Recently, the company created an XML map in the Billing Statement template and mapped in the schema for billing statements.

Viewing Schema and XML Data

Take a look at the XML map for this workbook. On the Excel XML Toolbox toolbar, click XML Source, which is a convenient shortcut to the XML Source task pane. As shown in Figure 2, the XML Source task pane shows the XML schema structure for a billing statement. This XML map has been mapped into the workbook. If you click the XML nodes in the XML Source task pane, Excel selects the cell in the workbook where the XML node is mapped.

Figure 2. The XML Source task pane shows the XML schema structure mapped into the Billing Statement workbook (click to see larger image)

Excel presents the XML schema structure in a hierarchical, tree-like structure. Excel also stores the schema that was originally provided (the native schema) in the workbook. The Excel XML Toolbox allows you to view the schema that Excel uses to construct the visual XML map. To view the native schema, on the Excel XML Toolbox toolbar, click View Schema. Figure 3 shows the results.

Figure 3. Native schema in the Schema Viewer window (click to see larger image)

The Schema Viewer shows specific details of the XML that Excel is using. In the Schema Viewer, you can look at other XML maps in the active workbook, copy the contents of the schema to the Clipboard, or open the schema in an external viewer. It is important to note that Excel embeds the schema into the workbook and does not keep a reference to the original schema. If you open the schema in an external viewer and make changes, Excel is not aware of those changes. The next section shows how to get around this.

You can also view the exportable XML for an XML map in the Schema Viewer window. To do this, close the Schema Viewer, and then, on the Excel XML Toolbox toolbar, click View XML Data by XSD Map. Figure 4 shows the results.

Figure 4. Exportable XML in the XML Data Viewer window (click to see larger image)

Without the Excel XML Toolbox, if you wanted to see what Excel would export, you would have to go through the export process, save the XML to disk, and then open the XML in an editor. The XML Data Viewer window gives you a simple way to quickly view the XML before Excel exports it.

Now you can close the XML Data Viewer window.

Reloading Modified Schemas

One great thing about Excel is that it embeds the schema into the workbook, allowing the schema to travel with the workbook. This makes it convenient for others to use the workbook, because you don't have to worry that the schema might be stored somewhere else. This is an advantage, but this approach also has one obstacle. If you modify your schema, you have to create another XML map in Excel, and your existing mapped cells are no longer valid. External schema modifications can result in many hours of work for Excel developers, because they have to manually remap the entire workbook. The Excel XML Toolbox includes a tool called Reload XML Map, which addresses this problem.

To see how this tool works, try modifying the original schema used in the Billing Statement workbook. Open the Schema Viewer as outlined in the previous section. In the Schema Viewer window, in the External Viewer drop-down list, select Notepad and then click Open. This opens Notepad and displays the schema from the Schema Viewer; at the same time, the schema displayed in Notepad is saved to a file named BillingStatement_Map.xsd in the local directory that contains the sample workbook. You can now edit the schema in Notepad.

Next, try making some changes to the schema and then use the Reload XML Map tool to reload the modified schema.

Coho Winery has decided to update the schema with a few changes. First, the company no longer wants to include the last payment date on the billing statement. Second, it wants to add an XML node to track the total amount due from this billing. To do this, follow these steps:

1. Delete the line:

   <xs:element name="LastPaymentDate" type="xs:date" />
   

2. In its place, type the following:

   <xs:element name="TotalDue" type="xs:double" />
   

3. Save the file and return to the Billing Statement workbook in Excel.

4. Close the Schema Viewer window if it is still open.

5. On the Excel XML Toolbox toolbar, click Reload XML Map. See Figure 5.

Figure 5. The Reload XML Map window (click to see larger image)

The Reload XML Map window shows the available XML maps in the active workbook. The Billing Statement workbook contains only one XML map, which is selected by default. Next to the XML map name is the XSD source file. If this is the first time the XML map is reloaded, <Choose source> will appear. Otherwise, you will see the path to the XSD file.

By default, the Backup Workbook check box is selected. It is a good idea to leave it this way, because the Reload XML Map tool uses an advanced technique to remap the modified schema and the existing schema already embedded into Excel. The Reload XML Map tool attempts to remap existing mapped cells to the modified schema. As you can imagine, a modified schema can have many changes, and there is no guarantee that the remapping will produce the anticipated results. If you select the Backup Workbook check box, the original workbook is saved in the same directory as the active workbook, with the name of the active workbook and a .bak extension. If the Reload XML Map tool fails, you can always refer to the backup file.

In the Reload XML Map window, click OK. The Select XML Source dialog box appears. Find the modified schema file, select it, and then click Open. The Reload XML Map tool loads the modified schema into the XML map and attempts to remap the cells in the workbook. When it finishes, the Reload XML Map Complete dialog appears, as shown in Figure 6.

Figure 6. The Reload XML Map Complete dialog box

By default, the Display Reload Report check box is selected. When you click OK, a report opens and summarizes the changes made by the Reload XML Map tool, along with any errors that occurred. In this sample, the Reload report contains the following error message:

XML Mapping Errors
 Worksheet   Cell(s)    XPath
 Statement   $C$6      /ns2:BillingStatement/ns2:LastPaymentDate
Total reportable errors: 1

The report shows that the LastPaymentDate field could not be remapped. This is to be expected, because you deleted it from the modified schema. In the XML Source task pane, you will see that the LastPaymentDate node is gone, and that the TotalDue element you added is available and unmapped.

Remember that the Reload XML Map tool is designed to reload a modified schema that is similar in overall structure to the original schema. If the schema modifications are significant, the effects of the Reload XML Map tool are reduced. To learn more about the types of changes that cannot be remapped, see the "Reload XML Map" topic in the Excel XML Toolbox Help.

Building Schemas

The Excel XML Toolbox also includes a Schema Builder tool that helps you create schemas. This can be useful if you do not have a schema, or if you have just started working with the XML features of Excel and are not yet familiar with the XML syntax for schema definition.

The Schema Builder tool is designed to create a schema and to add XML nodes to the schema from within Excel. In Excel, open the workbook titled "Billing statement - Not Mapped.xls" in the Samples directory. This is the same workbook used earlier, but it does not contain an XML map. Next, you will construct a schema for this workbook by using the Schema Builder tool.

To start, on the Excel XML Toolbox toolbar, click Build Schema. In the Schema Builder: Select an XML Map dialog box, define the namespace, root node name, and the XML map name for the new schema. In the first box, type CohoWinery-BillingStatement. In the second and third boxes, type BillingStatement. Figure 7 shows the initial information for the new schema.

Figure 7. Initial information required for new schema

When you click OK, the Schema Builder creates an XML map and schema. The XML Source task pane shows an XML map named BillingStatement, but no defined XML nodes. Using the floating Schema Builder window, you can add XML nodes to the XML map.

Now try to create some individual nodes. First, click cell C4 next to Statement # in the workbook. In the Schema Builder window, select the Single Node tab if it is not already selected. In the New XML Node Name text box, type StatementNumber as shown in Figure 8.

Figure 8. Schema Builder tool with the Single Node tab selected

Now click Build. The Schema Builder tool builds the StatementNumber XML node and appends it to the schema in the XML map. In addition to creating an XML node in the schema, it maps the selected cell to the new XML node. Repeat this action for the following cells and use the XML node names listed in Table 1.

Table 1. Cell references and XML node names

As you can see, it is easy to create XML nodes in a schema and map them into the workbook.

You can create nodes one at a time, but the Schema Builder also supports creating multiple single nodes and repeating nodes as a list. These options are available on the following tabs:

• Multiple Single Nodes: Add multiple XML nodes to the underlying schema of an XML map and then map the selected cell to the new XML nodes.
• Repeating Nodes (List): Add XML nodes to the underlying schema of an XML map for an existing or new list, and then map that list to the new XML nodes.

Now use the Repeating Nodes (List) tab to turn the line-item section of the billing statement into a list, with new XML nodes for each column in the list. To do this, first select the range of cells that make up the line items (cells B12 through H15). Then, in the Schema Builder, select the Repeating Nodes (List) tab.

For the root XML node name for the list, type LineItems. Next, for the XML node name for the row, type LineItem. Next, select the First row contains column names check box. This indicates that the first row of the current cell selection will be used as column name headers for the list and for the XML node names created by the Schema Builder. Finally, click Build.

The Schema Builder converts the selected range of cells into a list, generates XML node names for each column, and maps each column to the XML map.

At this point you have a schema for your workbook, and you never had to edit any schema XML directly. To view the schema created by the Schema Builder and all the work that you saved, click View Schema.

Conclusion

The Excel XML Toolbox for Microsoft Office Excel 2003 is a valuable resource for XML developers working in Excel. This article discussed just a few of its most useful features. To learn more about the Excel XML Toolbox, see the Help file that accompanies the source files.

Additional Resources

The following articles can help you develop more custom XML solutions using Excel:

• Microsoft Office System and XML: Bringing XML to the Desktop
• Creating XML Mappings in Excel 2003
• Using the Excel 2003 Object Model to Add XML Data Integration
• Developing Word XML Using the Microsoft Office Word 2003 XML Toolbox