O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Linking Spreadsheets to XML

Because XML is so flexible, there's no single-step solution to importing and exporting XML. You can't, for example, just perform an Open XML command since Excel doesn't know which XML format you're using. Instead, you first need to give Excel some information about the specific format you're using and tell Excel how to extract the data you need.

Excel makes this possible through a set of features called XML mapping . XML mapping lets you link a specific XML format to a specific spreadsheet. Once you set up this link, you can use it in two ways: to export data from your worksheet into an XML document, or to import the contents of an XML document into your worksheet.

Mapping a Simple Document

The simplest way to map an XML document is to link each element in the XML document to a single cell in a worksheet. Then, when you import the document, the data flows out of the elements and into the linked cells.

Note

You can find all the XML files used in this chapter on the "Missing CD" page at MissingManuals.com. You can then use these files to map your own worksheets.

To try this out, you can use the simple Student.xml document shown below, which stores the test and assignment scores for a single student.

<?xml version="1.0" ?>
<Student>
    <Name>Lisa Chen</Name>
    <StudentID>45349920</StudentID>
    <Test1_Score>75</Test1_Score>
    <Test2_Score>63.23</Test2_Score>
    <Assignment1_Score>94</Assignment1_Score>
    <Assignment2_Score>90</Assignment2_Score>
</Student>

Keep in mind that in ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required