Section 7.3.  Worksheets maps and schemas

Prev don't be afraid of buying books Next

7.3. Worksheets, maps and schemas

Excel handles the relationship between schemas, XML documents and their presentation somewhat differently from Word. A worksheet should be considered as a reporting template that you can reuse for various different sets of data, rather than a container for the data itself.

You create a map that causes the items of XML data to be connected with the cells where they will appear in the worksheet. Each cell may only be mapped once.

Each map is associated with one schema. Excel allows you to map your schema to a worksheet using a drag-and-drop facility. It is possible to have more than one map associated with a worksheet. This is useful if data is coming from two or more different sources, using different schemas.

Both the map and the schema are stored in the .xls file along with the worksheet. In the case of the schema, it is a copy of the schema file that you selected for mapping. Therefore, changing or deleting the schema file after you have selected it has no effect on the worksheet.

Caution

Excel makes it very difficult to change the map, and almost impossible to change the schema once you have created the map. Make sure your schema is the way you want it before you start mapping it.




A map causes a worksheet to be linked to the source of its data. If you import XML into a worksheet, the map maintains a link to that source. However, when you save a worksheet, Excel will save the data that is currently in the cells. If you reopen the worksheet and the source data has changed, Excel will not refresh the data automatically. You have to refresh the data explicitly to get the changes.

7.3.1 Creating a map

Let's see how Ellen created the map for the expenses.xls worksheet, using the expenses.xsd schema. She might have started from scratch, defining a map between the schema and a blank worksheet. However, since World-wide already had a worksheet design, she started with that.

To associate the worksheet with the schema:

1. Open expenses.xls in Excel.

2. On the Data menu, point to XML, then click XML Source. This brings up the XML Source task pane.

3. At the bottom of the XML Source task pane, click XML Maps. This brings up the XML Maps dialog, shown in Figure 7-2.

Figure 7-2. The XML Maps dialog




4. Click Add to add a map.

5. Browse to the location of expenses.xsd, select it from the list, and click Open. The XML Maps dialog will now list the new map.

6. Click OK to return to the main window. The XML Source task pane will now show the expense report structure from the schema.

7.3.2 The XML Source task pane

The XML Source task pane is used during the mapping process to create the links between the XML element types and the worksheet cells. The top part of the XML Source task pane shows the general structure allowed by the schema, as shown in Figure 7-3.[2]

[2] The XML Source pane is different from the top part of the XML Structure pane in Word. Word only shows the individual elements of the current document, while Excel shows the element types allowed by the document's schema.

Figure 7-3. The XML Source task pane




The task pane shows all the XML element types and attributes, organized into the hierarchical structure allowed by the schema. There are several things to notice about the way element types and attributes are displayed:

  • Required element types and attributes, such as empName and explanation, are displayed with a red asterisk.

  • Element types that can have children have a folder icon, while element types that can only have character data content have a document icon. Attributes have a slightly darker icon.

    Repeating element types have a blue rectangle at the base of the folder or document icon.

  • Bold font indicates element types that have already been mapped to the worksheet.

Once an element type or attribute is mapped, selecting it in the XML Structure task pane causes Excel to select its mapped cell(s) in the worksheet. Likewise, when you select a mapped cell in the worksheet, Excel selects the element type or attribute to which it is mapped in the task pane.

7.3.3 The mapping process

Element types and attributes can be mapped to cells by dragging them from the task pane to the worksheet. It is also possible to right-click the element type or attribute in the task pane. From the resulting context menu, click Map element.

During the mapping process, Excel makes a distinction between repeating and non-repeating element types. The latter only occur once within the XML structure, according to the schema. In our example, they include the element types that make up the header of the expense report, such as empName, empName, and billToCode.

Repeating element types may appear more than once. In our example, the expense items, such as explanation and mileage, are repeating. Even though they may only appear once in their parent element, expense, the parent may appear more than once, so the children are therefore considered repeating.

Excel looks at the maxOccurs attributes in the schema to determine whether an element type is repeating. The default for this attribute is 1. If an element type, or any of its ancestors, is declared with a maxOccurs value of more than 1 (including "unbounded"), it is considered to be repeating.

Attributes are considered repeating if they belong to repeating element types; otherwise they are non-repeating.

The mapping process differs depending on whether the element type or attribute is repeating or non-repeating, so we will discuss each separately.

7.3.3.1 Mapping non-repeating element types and attributes

You generally map a non-repeating element type or attribute to a single cell in the worksheet.[3]

[3] Sometimes you may want it to appear in a list; we describe that later in the chapter.

Let's map the non-repeating element types of the schema to our expense report worksheet. To do this, drag each one to the cell that is to contain the data. For example, drag the empName element type onto cell A4.

This will cause a smart tag to appear next to the cell. Clicking it reveals three options, as shown in Figure 7-4:

  • My Data Already Has a Heading

  • Place XML Heading to the Left

  • Place XML Heading Above

Figure 7-4. Heading-related options




These choices allow you to insert a heading for your cell. Since we already have column headers in our worksheet, we can choose the first option (the default). If you choose either of the other two options, the XML name will be placed in the cell to the left of or above the chosen cell.

Notice that the cell that we have mapped now has a blue border around it. Continue to map the rest of the non-repeating element types and attributes.

To change the map, you can right-click the name in the XML Structure task pane, and click Remove element. This will remove the link between that element type or attribute and the cell. You can then remap the element type or attribute to another cell.

7.3.3.2 Mapping repeating element types and attributes

Dragging a repeating element type to the worksheet has a somewhat different result. Instead of associating the element type with a single cell, Excel creates a list range. A list range is a vertical column (or columns) of data that can be manipulated and sorted as a group.

The element type should be dragged to the cell that contains the header for the column. In our example, you simply drag the element types to the existing header cells. If you drag the element type to an empty cell, for example in a blank worksheet, Excel will insert the XML name as the header in the empty cell you selected, and assume that the actual data starts in the cell below the header.

For example, drag the date element type to cell A6, which contains the word DATE. Excel will create a list range and outline it in blue. Excel makes an assumption about where the list range ends based on the data that is currently in the worksheet (if any). In our example, it takes the list range all the way to the last expense item. If Excel assumes the size of the list range incorrectly, you can adjust the size by selecting a cell in the list, clicking List on the Data menu, and clicking Resize List on the List menu.

Drag the rest of the repeating element types to the appropriate header cells. When the columns are adjacent, as they are in our case, Excel expands the list range to add the additional columns.

List ranges are a handy tool for analyzing data. The drop-down menus on each of the column headers allow you to perform operations on the range quickly. You can sort the rows by any of the columns, or filter the data to include only rows matching a particular value. You can also select the "top 10" values or write custom queries to filter out other rows.

Amazon


XML in Office 2003. Information Sharing with Desktop XML
XML in Office 2003: Information Sharing with Desktop XML
ISBN: 013142193X
EAN: 2147483647
Year: 2003
Pages: 176

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