|   If someone sends you an XML file containing data that fits into tables, you don't need to read the text and all its angle brackets. You can load the document into Excel directly, tell Excel how you want to present it, and work with the data through maps . Extensible Markup Language (XML) has become a common interchange format over the past few years , and it's not unusual for people and organizations to send each other XML files. XML's simple core structures make it easy to exchange information with much less concern that all parties are using the same software. Until recently, however, although generic XML tools were widely available, bridging the gap between XML documents and the user interface was difficult. Excel 2003 makes it much easier, at least for data that fits on a grid.  | This hack uses Excel features that are available only in Excel 2003 on Windows. Earlier versions of Excel do not support this, and neither do current or announced Macintosh versions of Excel. | |
We'll start with a sample XML document, shown in Example 8-1. Example 8-1. A simple XML document for analysis in Excel <?xml version="1.0" encoding="UTF-8"?> <sales> <sale> <date>2003-10-05</date> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> <PriceUS>34.95</PriceUS> <quantity>200</quantity> <customer ID="1025">Zork's Books</customer> </sale> <sale> <date>2003-10-05</date> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> <PriceUS>39.95</PriceUS> <quantity>90</quantity> <customer ID="1025">Zork's Books</customer> </sale> <sale> <date>2003-10-05</date> <ISBN>0596002378</ISBN> <Title>SAX2</Title> <PriceUS>29.95</PriceUS> <quantity>300</quantity> <customer ID="1025">Zork's Books</customer> </sale> <sale> <date>2003-10-05</date> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> <PriceUS>34.95</PriceUS> <quantity>10</quantity> <customer ID="1029">Books of Glory</customer> </sale> <sale> <date>2003-10-05</date> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> <PriceUS>39.95</PriceUS> <quantity>25</quantity> <customer ID="1029">Books of Glory</customer> </sale> <sale> <date>2003-10-07</date> <ISBN>0596002378</ISBN> <Title>SAX2</Title> <PriceUS>29.95</PriceUS> <quantity>5</quantity> <customer ID="1029">Books of Glory</customer> </sale> <sale> <date>2003-10-18</date> <ISBN>0596002378</ISBN> <Title>SAX2</Title> <PriceUS>29.95</PriceUS> <quantity>15</quantity> <customer ID="2561">Title Wave</customer> </sale> <sale> <date>2003-10-21</date> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> <PriceUS>39.95</PriceUS> <quantity>15</quantity> <customer ID="9021">Books for You</customer> </sale> </sales> You can open this directly from Excel 2003 by selecting File  Open.... The dialog box shown in Figure 8-1 will appear. Figure 8-1. Opening an XML file in Excel 2003  If you select "As an XML list," you'll first be warned that Excel will be creating its own schema for this schema-free document, as shown in Figure 8-2. After clicking OK, you'll be rewarded with Excel's best guess as to how to present the information in the document as a spreadsheet, as shown in Figure 8-3. Figure 8-2. Excel 2003 warning about lack of schema references  Figure 8-3. XML data presented as an XML list in Excel 2003  Note that Excel expected the date format used by the date element, so it is now displaying dates imported as 2003-10-05 as 10/5/2003. Once you have the document loaded into Excel, you can treat the data much like you would any other data in Excelincorporating it into formulas, creating named ranges for it, making charts based on its contents, etc. To help you, Excel provides some built-in functionality for analyzing the data. The drop-down boxes in the column headers enable you to choose how to sort the data (the default is the order the document had originally). You also can turn on a total row, either from the List toolbar or by right-clicking anywhere on the list and selecting List Total Row from the pop-up menu. Once the total row appears, you can choose what kind of total you prefer from the drop-down menu displayed in Figure 8-4. Figure 8-4. Choosing totals for an XML list in Excel 2003  You also can refresh the data, updating that area with information from an XML document with the same structure. If you had another document with the same structure, you could right-click the list, select XML Import... from the pop-up menu, and choose a different document. With more data, it might look like Figure 8-5. Figure 8-5. The same XML list with updated data  In addition, you can export the data back to an XML file after editing it by right-clicking the list and selecting XML Export... from the pop-up menu. This makes Excel a very convenient editing tool for simple XML documents with tabular structures. For simple data, you can usually trust Excel to guess what a file's contents are and use the default view it provides. As data gets more complicated, however, especially if it contains dates or text that looks like numbers (note the missing initial zeros on the ISBNs in the preceding figures!), you might want to use XML schemas to constrain how Excel reads your data and what kinds of data will fit in a given map. For this document, an XML schema might look like Example 8-2. Example 8-2. A schema for the book sales data <?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:element name="sales"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" ref="sale"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="sale"> <xs:complexType> <xs:sequence> <xs:element ref="date"/> <xs:element ref="ISBN"/> <xs:element ref="Title"/> <xs:element ref="PriceUS"/> <xs:element ref="quantity"/> <xs:element ref="customer"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="date" type="xs:date"/> <xs:element name="ISBN" type="xs:string"/> <xs:element name="Title" type="xs:string"/> <xs:element name="PriceUS" type="xs:decimal"/> <xs:element name="quantity" type="xs:integer"/> <xs:element name="customer"> <xs:complexType mixed="true"> <xs:attribute name="ID" use="required" type="xs:integer"/> </xs:complexType> </xs:element> </xs:schema> Note that the date element is defined as a date, and the ISBN element is defined as a string here, not an integer. If you start by opening this schema rather than the document, you can have Excel load the document while preserving the initial zero on the ISBN s. This time, you'll create a list before loading the XML document, starting from a blank worksheet. You'll need to open the XML Source task pane. If the task pane isn't open already, open it by selecting View Task Pane or by pressing Control-F1. Then select XML Source from the drop-down box at the top of the task pane, and you'll see something such as that shown in Figure 8-6. Figure 8-6. The XML Source task pane  To load the schema, click the XML Maps... button. This will bring up the XML Maps dialog box, shown in Figure 8-7. Figure 8-7. The XML Maps dialog box  Click the Add... button to open the schema, and select the schema as shown in Figure 8-8. Figure 8-8. Selecting an XML schema  If your schema doesn't restrict documents to having only one possible starting element, Excel will ask you which element to use as the root element, as shown in Figure 8-9. Because the documents in this example start with the sales element, pick "sales." Figure 8-9. Selecting a root element for the schema  After you click OK, Excel warns about possible difficulties it might have in interpreting schemas in the dialog box shown in Figure 8-10. XML Schema is an enormous specification that supports a wide variety of structures that don't fit well with Excel's perspective on information, so Excel has some limitations. Figure 8-10. Warning label for schema processing  Excel will show that your schema has been added to the spreadsheet in the XML Maps dialog, which should look like that shown in Figure 8-11. Figure 8-11. A schema loaded as an XML map  If you click OK, you'll be returned to the main Excel interface, and the XML Source task pane will be populated with a diagram of the structure the schema described, such as that shown at the right of Figure 8-12. Now that you have the structure, you can lay out the list. The easiest way to do thisespecially with a small document such as this oneis to drag the sales icon to cell A1, producing the result in Figure 8-12. Figure 8-12. An XML list created from the schema information in the XML Source task pane  | | You also can drag items over individually, if you want to change the order or want to put different pieces in different places on the spreadsheet. | |
Now that you have a home for the data, it's time to populate it. You can either click the Import XML Data button on the list toolbar, or right-click the list and select XML Import. If you choose the file you used earlier (Example 8-1), you'll see a result such as that shown in Figure 8-13. Note the addition of the leading zeros to the ISBNs, which are now text, as they should be. Figure 8-13. The XML list, populated with data  Excel's support for XML maps and lists means you can create spreadsheets that work on data arriving in separate files with more flexibility than prior formats such as CSV or tab-delimited formats provided. Instead of having to be connected to a database to edit data interactively, a user can edit XML files while on an airplane and feed that XML to an appropriate consumer when he lands. Perhaps the best aspect of Excel's new XML features is their flexibility; so long as the data is in a structure that fits on a grid, Excel has very few rules about what kinds of XML it will accept. With a few mouse clicks and no programming, you can integrate XML data with your spreadsheets. Simon St.Laurent | 