|
3.6. Use XML MapsIf 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 worksTo see how this works, follow these steps:
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 ExcelFigure 3-9. A list, populated with data from the XML documentFigure 3-10. Mapped XML with summary and detail combined3.6.2. What you lose and how to keep itThe 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 worksWhen 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:
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 list3.6.4. Manage XML MapsExcel 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:
These points are spelled out in more detail in the next section. |
|