Advanced XML Features in Excel

We will use the mapped spreadsheet we have created to consider some other XML features in Excel.

Importing XML and Refresh XML Data

To import XML from an XML file into our mapped spreadsheet, follow these steps. First, clear out the rows in the Excel list and some of the mapped fields so that you can see that XML is being imported in subsequent steps. Select one of the mapped cells or the list. Note that because you can map multiple XML schemas into one workbook, you must let Excel know which of the mappings you want to import to by selecting a cell or list corresponding to that mapping.

From the XML menu of the Data menu, choose Import. Browse to the file you exported to previously (bookorder.xml) and click the Import button. Note that Excel brings the XML back into the spreadsheet. Now, go edit the bookorder.xml file directly with Notepad. Change the CustomerName element to a different value. Then save the bookorder.xml file. Then, select the cell where CustomerName is mapped. From the XML menu of the Data menu, choose Refresh XML Data. Excel remembers the XML file you last imported and it reimports the XML data from that file. Excel also stores this information in the document, so you can save, close, and then reopen the document at a later time and choose Refresh XML data. Note that Excel does not remember the XML file you last imported if you uncheck Save data source definition in Workbook in the XML Map Properties dialog (discussed next).

The XML Map Properties Dialog

Figure 21-18 shows the XML Map Properties dialog that can be shown by choosing XML Map Properties from the XML menu in the Data menu. Note that you must select a cell in the worksheet that is mapped to XML for this menu item and some of the other menu items in the XML menu to not be grayed out.

Figure 21-18. The XML Map Properties dialog.

 

XML Schema Validation

The first setting we consider in this dialog is the XML schema validation setting. With this setting unchecked, set the price of one of the books to a value such as cat. This is clearly not a valid floating-point number. From the XML menu in the Data menu, choose Export and export the XML to a file. No error will occur. Now check the Validate data against schema for import and export check box in the XML Map properties dialog. Export the XML again. This time you will get the error dialog shown in Figure 21-19 for using the value of cat in a place where a number was expected.

Figure 21-19. A schema validation error on export.

If you try to import XML that has the value cat for a floating-point number, you also get errors with the Validate data option checked. Figure 21-20 shows the first error dialog that appears.

Figure 21-20. A schema validation error on import.

The first line warns that some data was imported as textnamely, the value cat was imported as text rather than as a floating-point number. When you click the second error line and click the Details button, the dialog shown in Figure 21-21 displays.

Figure 21-21. Details of the validation error on import.

 

Data Formatting and Layout

The XML Map Properties dialog provides settings for controlling the data formatting and layout of lists that are XML mapped. The Adjust column width check box when checked will make it so that an import of XML into a list will automatically adjust the column width to fit the data that is imported. Excel will make a column wider up to two thirds the width of the screen. To prevent automatically adjusting the column width of a list when XML is imported, uncheck this check box.

The Preserve column filter check box when checked will preserve the filtering settings for a list when XML is imported into the list. For example, if you have the list set to only show books whose publisher is Addison-Wesley, importing new XML will preserve that setting. If you uncheck this check box, whenever XML is imported into a list any existing filters will be cleared.

The preserve number formatting check box when checked will preserve any number formatting in the list that the XML is imported into. For example, if a column is set to display the book price in red if it is greater than $20, this setting will be preserved when XML data is imported into the list. If this check box is not checked, any number formatting in the list will be cleared when XML data is imported into the list.

Appending Data to Lists

The XML Map Properties dialog provides for two different behaviors when importing XML or refreshing XML and updating a mapped list. If you choose Overwrite existing data with new data, a mapped list will be cleared of its data before loading data from the XML data file on import or refresh. If you choose Append new data to existing XML lists, the data in the list will be preserved and the data from the XML data file will be appended on import or refresh. So with the Append setting set, importing the XML in Listing 21-5 into a blank list generates three book orders on the first import and on refresh it appends the three book orders to the list for a total of six book orders.