How to use XML in Excel 2003

Applies to
Microsoft Office Excel 2003

 Note   The information in this article assumes that you have a basic knowledge of Extensible Markup Language (XML). If you'd like more basic information, see Manage Information with XML in Microsoft Office Professional Edition 2003.

One of the most powerful features of Microsoft Office Excel 2003 is one you can't really see: support for user-defined XML schemas. In plain English, that means you can add XML schemas (a schema is just a structure) and data to your workbooks, regardless of where the XML originated. Excel automates most of the processes for you. All you need to do is open the file and decide how you want to use the data.

You can:

  • View the data in an XML list     Use this option when you want to see all your data in one list. Lists act as cohesive units. If it helps, think of them as worksheets within larger worksheets. You can manipulate the data in a list separately from the surrounding worksheet. For example, you can sort data, rearrange rows, and, to a limited extent, rearrange the columns in a list without affecting the surrounding cells.
  • View the data in a read-only workbook     Use this option when you or others need to view the data in a file but don't need—or want—to manipulate it. Read-only workbooks can provide a fast way to create business reports and other types of files that you don't want users to change. When you select this option, Excel displays the data in a static grid.
  • Create a map from the structure and import data into the map     When you use the XML Source task pane to create a map, you link or bind a cell in a worksheet to an element in the XML file. Importing data into the map populates the cells with data. This is the most flexible and powerful option for working with XML data. For example, say your sales department stores its data as XML, and you need to create a report that summarizes sales activity for each month. When the sales department posts new data, you can import that data into a map or set up a system that updates your worksheets automatically. This figure shows a worksheet with a populated map. Excel surrounds the mapped cells with a blue border.

An Excel workbook with a populated XML map

Open and work with an XML file

Follow these basic steps to view an XML file in Excel by using one of the methods described earlier.

 Note   If you'd like to follow this procedure but don't have an XML file handy, you can download this sample XML file. Follow the on-screen instructions, and place it in a convenient place on your network or your computer's hard disk.

  1. On the File menu, click Open.
  2. In the Files of type list, select XML files (*.xml).
  3. In the Look in box, navigate to the XML file you want to open, select the file, and then click Open.

The Open XML dialog box appears.

Open XML dialog box

  1. Click one of the options in the Open XML dialog box, and then click OK.

Information in the following sections explains how to use each option:

ShowAs an XML list

If a message box appears, click OK to close it. Excel creates the list. Notice that Excel adds all the data in the file to the list.

Depending on the structure of your source file, you may see a lot of repeated data, such as names and dates. You can eliminate redundancies by using the Use the XML Source task pane option when you open the file.

More information

The following links take you to more information about Excel and XML lists:

 

ShowAs a read-only workbook

Excel adds all the data in the file to the list and flattens the file, meaning it displays the data in a standard grid instead of a list.

Depending on the structure of your source file, you may see a lot of repeated data, such as names and dates. You can eliminate redundancies by using the Use the XML Source task pane option when you open the file.

 

ShowUse the XML Source task pane

The XML Source task pane appears and displays the schema (the structure) of your XML file.

  1. Drag the items (they're called elements) that you want to map from the task pane to blank cells on your worksheet.
  2. Select any one of the mapped cells. On the Data menu, point to XML, and then click Import.

The Import XML dialog box appears.

  1. Use the Look in list to locate the source file you want to import. Select the file, and then click Import.
  2. Click OK to display the map in the task pane.

More information

The following links take you to more information about XML maps:

ShowTip

The following steps demonstrate another way you can open the XML Source task pane and use it to create an XML map:

  1. On the Data menu, point to XML and then click XML Source.
  2. In the XML Source task pane, click XML Maps, and then click Add.
  3. Use the Look in list to locate the XML file that you want to open, and then click Open.

If a message box appears, click OK to close it.

  1. In the XML Maps dialog box, click OK to display the map in the XML Source task pane.
  2. Drag the items (they're called elements) that you want to map from the task pane to blank cells on your worksheet.
  3. Select any one of the mapped cells. On the Data menu, point to XML, and then click Import.

The Import XML dialog box appears.

  1. Use the Look in list to locate the source file. Select the file, and then click Import.

About nonrepeating and repeating XML elements

When you create an XML map, you use two types of XML elements: single, nonrepeating elements and repeating list elements. Single, nonrepeating elements occur once in a given XML file, while repeating list elements can occur any number of times. For example, if you create the expense report in the Practice exercise at the end of this article, you'll use a combination of nonrepeating elements (such as an employee name) and repeating list elements (such as a description of each expense item and the cost of each item).

Keep these facts in mind:

  • The XML Source task pane uses several icons to denote repeating list elements. For the Practice exercise, pay attention to these:

Icon image  Denotes a repeating parent element.

Icon image  Denotes a repeating child element.

  • Excel automatically formats repeating list elements as XML lists when you drag the elements onto the worksheet. That makes it easy to distinguish between repeating and nonrepeating elements, because lists automatically provide the AutoFilter button.
  • If you place repeating list elements next to each other on the same row, Excel includes each adjacent element in a single large XML list.
  • If you place repeating elements by themselves, or next to nonrepeating elements, Excel formats them as individual XML lists.

Import, export, and save files as XML

If you've added an XML map to a worksheet, you can import data into that map at any time. You can also export the data for others to use. Those users can apply their own schemas to your data to suit their needs. For example, a user can create a chart that uses and links to your data, so that the chart updates automatically whenever you update the data.

 Note   To follow the steps in this section, your worksheet must contain an XML map. For information about maps, see the steps in Use the XML Source task pane.

Import data

  1. Select one of the mapped cells in a worksheet.
  2. On the Data menu, point to XML, and then click Import. The Import XML dialog box appears.
  3. Use the Look in list to locate the file that you want to import, and then click Import.

Export data

  1. On the Data menu, point to XML, and then click Export. The Export XML dialog box appears.
  2. Use the Save in list to navigate to the folder to which you want to export the data, and then click Export.

Save data

  1. On the File menu, click Save As.
  2. From the Save as type list, select XML data (*.xml).
  3. Click Save.

 Note   When you use the Save As command, Excel also makes available the older XML Spreadsheet file format. If you save your data using that format, other users must have Microsoft Excel 2002 or later to work with your data.

More information

The following links take you to more information about importing and exporting data:

Practice exercise: Create and populate a map

Need something a bit more concrete? No problem. If you haven't already, download the sample XML file. You'll use the file to create a fictitious expense report. You'll add a map to a worksheet and then populate the map with imported data.

Start by preparing the worksheet

  1. Start Excel and open the sample file.

When the Open XML dialog box appears, click Use the XML Source task pane, and then click OK.

  1. In cell A1, type Employee, and in cell A3, type Manager.
  2. In cell C1, type Department, and in cell C3, type Date Submitted.
  3. Format the text as you like to give it emphasis.

Add the map

  1. Drag the ns1:EmployeeName element from the XML Source task pane to cell B1, and then drag the ns1:Department element to cell D1.
  2. Drag the ns1:ManagerName element to cell B3, and then drag the ns1:Date element to cell D3.
  3. Under ns1:ExpenseItem, select ns1:Description and drag it to cell B5.
  4. Still under ns1:ExpenseItem: Drag the ns1:Account element to cell C5, drag ns1:Date to cell D5, and then drag ns1:Total to cell E5.
  5. Save the file. This adds the map to the file and binds the data in the source file to the map.

You're now ready to...

Import data into the map

  1. Select any one of the mapped cells. On the Data menu, point to XML, and then click Import.

The Import XML dialog box appears.

  1. Use the Look in list to locate the sample file, and then click Import.

Excel populates the mapped fields. Notice that Excel automatically applies XML list formatting to the repeating elements (in this case, the expense items) in the map.

 
 
Applies to:
Excel 2003