Section 3.6. Use XML Maps


3.6. Use XML Maps

If all of the XSLT in the preceding sections intimidated you, relax a bit. Excel also provides graphical tools for importing XML into workbooks through XML maps. XML maps are the way Excel represents XML schemas within a workbook. Excel can generate these maps from the structure of an imported XML file, or Excel can load an XML schema as an XML map.

Excel uses XML maps as a way to bind data from an XML file to cells and list columns on a worksheet. You create this binding by selecting items from an XML map displayed in the XML Source task pane and dragging them to locations on a worksheet.


Note: You don't have to know XSLT to read or write XML from Excel. XML maps let you work with XML at a higher level and ignore some of the complexities.

3.6.1. How it works

To see how this works, follow these steps:

  1. Open the sample file SimpleOrder.xml in Excel using the regular File Open menu. Excel displays the Open XML dialog box (Figure 3-5).

    Figure 3-5. The Open XML dialog box


  2. Select the Use the XML source task pane option and click OK. Excel creates a new, blank workbook and informs you that the file did not contain a schema (Figure 3-6), so Excel will infer one from the XML.

    Figure 3-6. Excel inferring a schema for you


  3. Click OK. Excel displays the XML map it created in the task pane (Figure 3-7).

    Figure 3-7. XML structure in the XML Source pane


  4. Import XML nodes into a worksheet by selecting the nodes in the XML map and then dragging them onto a worksheet. Excel creates these new items as a list, so select multiple nodes to include multiple items in one list (Figure 3-8).

  5. Choose Data XML Refresh XML Data to import the data from the XML file into the list (Figure 3-9).

This tutorial works well for the summary information imported above. The Order ID, Name , SubTotal , and Tax nodes occur once per Order. You can sort the list, filter it to see only a specific Order ID, and so on. However, if you want to include the detail lines of the order, the list can become hard to interpret, because non-repeating items are repeated (Figure 3-10).

Figure 3-8. A list of nodes in Excel


Figure 3-9. A list, populated with data from the XML document


Figure 3-10. Mapped XML with summary and detail combined


3.6.2. What you lose and how to keep it

The preceding tutorial demonstrates a subtle limitation of XML mapsoptional nodes, such as Street2 in SimpleOrder.xml , are sometimes not included in the XML map. This occurs because Excel generates the schema from the first instance of each node it encounters.

To correct this, add an empty Street2 node to the first Address nodes, as shown below, and open the XML as a new workbook:

      <Address>          <Name>Joe Magnus</Name>          <Street1>1234 Made Up Place</Street1>          <Street2 />          <City>Somewhere</City>          <State>FL</State>          <Zip>33955</Zip>      </Address>

You can't update an existing XML map, you can only create new ones and delete existing ones from within Excel. This means that lists created from XML maps must be recreated any time the source XML schema changes.

Since XML maps are row-based, you can't conditionally omit optional nodes as you can with XSLT. For example, the sample transformation OrderToExcel.xslt omits the optional Street2 node if it is empty using the following xsl:if element:

      <xsl:if test="./Address/Street2 != ''">          <xsl:element name="Row">              <xsl:element name="Cell" />              <xsl:element name="Cell">                  <xsl:element name="Data">                 <xsl:attribute name="ss:Type">String</xsl:attribute>                              <xsl:value-of select="Street2" />                    </xsl:element>              </xsl:element>          </xsl:element>      </xsl:if>

You can't do that type of conditional processing with XML maps.

Another limitation is that calculated elements, such as Total , import from XML as data values rather than as formulas. The sample OrderToExcel.xslt creates formulas to calculate line item totals:

      <xsl:element name="Cell">          <xsl:attribute name="ss:StyleID">Currency</xsl:attribute>         <xsl:attribute name="ss:Formula">=RC[-2]*RC[-1]           </xsl:attribute>          <xsl:element name="Data">              <xsl:attribute name="ss:Type">Number</xsl:attribute>              <xsl:value-of select="Total" />          </xsl:element>      </xsl:element>

Such calculations must be created manually on the worksheet when using XML maps.

3.6.3. How it works

When Excel imports an XML file that does not reference an XML schema, it infers a schema from the nodes in the XML file. The preceding section explains one of the limitations of inferring a schemaoptional nodes are sometimes omitted from the resulting XML map.

Another solution to this problem is to include a schema with your XML file. For example, the following XML fragment references a schema for the SimpleOrder.xml file:

      <Orders xmlns="http://www.mstrainingkits.com"        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"        xsi:schemaLocation="http://www.mstrainingkits.com SimpleOrder.xsd">

When Excel imports an XML file that references a schema, it copies that schema into the workbook. If the XML is valid according to that schema, you can drag nodes from the XML map onto the worksheet to create lists and import data, as shown previously.

If the XML is not valid for the schema, however, no data will appear in the lists you create. Excel does not automatically validate XML against schemas or display errors if the XML is invalid. To validate XML within Excel:

  1. Choose the Data XML XML Map Properties. Excel displays the XML Map Properties dialog box (Figure 3-11).

    Figure 3-11. Properties of an XML map


  2. Select Validate data against schema for import and export, and click OK to close the dialog box.

Now, Excel will display an error if the XML doesn't conform to the schema. Excel checks the XML against the schema whenever the XML data is imported, exported, or refreshed. You can get detailed information about validation errors by clicking Details on the XML Import Error dialog box (Figure 3-12).

Unfortunately, Excel copies the referenced XML schema into the XML map the first time it loads an XML file, rather than referencing the schema as an external file. Subsequent changes to the schema do not affect the XML map in the workbook. Again, you can only add or delete XML maps, you can't update them from Excel.

Figure 3-12. Detailed XML validation errors when refreshing a list


3.6.4. Manage XML Maps

Excel adds an XML map for each unique XML file you import into a workbook. If you import the same file twice, Excel simply reuses the XML map it already created for that file.

To view a list of the XML maps, click the drop-down listbox in the XML Source task pane. To add or delete XML maps, click the XML maps button at the bottom of the XML Source task pane.

Items in an XML map may or may not be bound to cells on a worksheet. Items that are bound (or mapped ) to cells import data to those cells. Items that repeat in an XML map are bound to Excel lists (see Chapter 2 for information about using lists). Items that don't repeat may be bound to individual cells, however if a non-repeating item is placed adjacent to a list, Excel adds that item to the list and repeats the item on the worksheet even though the item is not repeated in the source XML file!

There are a couple of important points to note here:

  • Items in an XML map can bind to only one location in a workbook.

  • If an item in an XML map is not bound to a cell or list, its data won't be imported to, or exported from, the workbook.

  • If you include a non-repeating item from an XML map in an Excel list, the data Excel saves on the worksheet becomes denormalized and can't be exported.

These points are spelled out in more detail in the next section.



    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net