Adding XML to a Workbook Manually

 < Day Day Up > 

Once you have created an XML schema and an associated XML data file, you need to add the structure of the schema to a worksheet. To do so, you establish a data map, or pattern of data represented in a file, in your worksheet.


The words map and schema are often used interchangeably.

To associate a data map with a worksheet, follow these steps:

  1. Click Data, XML, XML Source to display the XML Source task pane.

  2. Click the XML Maps button to display the XML Maps dialog box.

  3. Click Add to display the Select XML Source dialog box.

  4. Navigate to the folder that contains the schema (a file with an .xsd extension) you want to map to the worksheet.

  5. Click the file, and then click Open to add an outline of the schema to the XML Source task pane.

You can now drag individual elements from the map to the worksheet cells where you want the data to appear. After you drag an element to the worksheet, an AutoFilter down arrow will appear at the right edge of the cell. Click the down arrow, and select where you want the element label (the element's name, such as CompanyName) to appear.


The names of elements you have mapped to a cell in a worksheet appear in bold type in the XML Source task pane.

If you want to add an entire element with all its subelements, you can do so by dragging the main element to a cell in the worksheet. Figure 26-5 shows the result of dragging the Supplier element from the MySuppliers.xsd schema to cell A1.

click to expand
Figure 26-5: You can map an entire element (with all its subelements) to a worksheet at once.


When you add a complex element (an element with subelements) to a worksheet, Excel treats the element as a single list, not a series of lists, which occurs when you drag simple elements to the worksheet individually.

After you've added the desired elements to your workbook, you can delete them as you would delete any other workbook contents. You can also remove an element from a worksheet by right-clicking the element in the XML Source task pane and clicking Remove Element on the shortcut menu.

If you want to change how the XML data is inserted into your worksheet, click the Options button in the XML Source task pane. You can use the options to preview your data in the task pane, hide help messages in the task pane, and let Excel know you have already added data labels so that it won't ask every time you insert a field. You can also change how Excel imports XML data by opening the Data menu, pointing to XML, and clicking XML Map Properties. You can use the controls in the XML Map Properties dialog box, shown in Figure 26-6, to change whether Excel resizes the columns to reflect the size of the data, preserves number formatting, and validates XML data against the associated schema before importing or exporting that data.

Figure 26-6: The XML Map Properties dialog box gives you more tools to control your XML data.

Importing XML Data Manually

Once you have assigned the schema elements to worksheet cells, you can import data into the worksheet from the XML data file that matches the structure of the schema.

To import data into an existing XML schema, follow these steps:

  1. Click Data, XML, Import.

  2. In the Import XML dialog box, navigate to the folder that contains the XML data file, click it, and click Import.


    You don't need to map every element of a schema to a worksheet for the data import to work correctly.

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: