XML Hacks

Work with XML in Microsoft Excel 2003

Using table-structured data or spreadsheets? Open, format, and save XML documents with Excel 2003.

Microsoft Excel 2003 offers unprecedented XML support. As with Word, full XML features are not available except with the Microsoft Office Professional Edition 2003, Enterprise Edition 2003, and the individual version. Other versions (the Standard or Small Business editions) won’t have XML support except for the ability to save a file in SpreadsheetML format.

Excel 2003 allows you to open an XML document and then save or export data as XML. Choose File → Open and then navigate to the file time.xml in the Open dialog box. Select the file and then click Open. You can open the document in one of three ways: as an XML list, as a read-only workbook, or by using the XML Source task pane.

When you open an XML file as an XML list, Excel automatically creates an XML Schema that corresponds with the XML (it warns you of that). It also maps each of the attribute values and the content from each of the elements to a cell in the spreadsheet. The XML Source task pane lists each of the elements in the imported document in a tree view. As a cell is highlighted in the spreadsheet, the corresponding element or attribute is highlighted in the task pane.

Tip

If the task pane does not appear automatically, choose Data → XML → XML Source.

In Figure 2-12, notice that in the XML Source pane, the hour element is highlighted; it is associated through a mapping with cell B1, ...