Creating Structured XML from an Excel Document

Although SpreadsheetML is easy to understand and navigate, it is likely to be more useful to apply your own structures to Excel XML documents before you use them in Flash. You can do this by using an XML map in the document. We saw examples of XML maps earlier in this chapter.

Youll need to start by creating an XML schema for Excel to use as a document map. The schema specifies the valid elements and their relationship to each other. A schema document lists the rules for creating your XML document, and Excel uses this to generate structured XML data. You can find out more about XML schemas in Chapter 3.

Creating an XML map in Excel

An XML map is equivalent to a schema for a list of XML data within Excel 2003. Excel automatically generates the map from either a schema file or the structure of an existing XML document.

Before you create an XML map, youll need to display the XML Source pane by choosing View Task Pane and selecting XML Source from the drop-down list at the top. Click the XML Maps button at the bottom right of the pane to bring up the XML Maps dialog box. You can see this in Figure 6-27.

Figure 6-27: Displaying the XML Maps dialog box

You can add a map by clicking the Add button. Excel will prompt you for the location of an existing schema or XML file. It will then add the map to the XML Maps dialog box, as Figure 6-28 shows.

Figure 6-28: The completed XML Maps dialog box

Click OK to display the XML map in the XML Source pane. You cant modify the structure of the map, although you can rename and delete the map by clicking the XML Maps button in the XML Source pane. To change the structure, youll need to alter the external file and then add it again.

Adding XML elements to Excel data

Once youve added the XML map to a document, you can drag the elements to sections of the worksheet. This creates the document structure. You can import data from an external XML document as described earlier in this section. You can also drag the elements to existing content. Whichever approach you use, its useful to add headings to the worksheet to describe the contents.

To add an element to existing text, click the element name and drag it to the worksheet, as shown in Figure 6-29. You dont have to map all text within a worksheet to XML elements.

Figure 6-29: Dragging elements from the XML map to the worksheet

Excel provides options for dealing with headings, as shown in Figure 6-30. If you didnt enter a heading, you can ask Excel to insert one for you. You can also mark an existing heading row.

Figure 6-30: Selecting a heading option

You need to drag map elements to each area of data that you want to mark up. If you drag a nonrepeating element, Excel maps the element to a single cell . If you drag a repeating element, Excel will map the element to a list. Your document can have a mix of both repeating and nonrepeating elements. Make sure there arent any blank rows and columns within your list.

You can modify the properties of the XML map by right-clicking within the list area and choosing XML XML Map Properties . This is shown in Figure 6-31.

Figure 6-31: Choosing XML Map Properties

You can use the XML Map Properties dialog box to turn validation on and off within the document. Validation will check if new data that you add conforms to the XML map. It also checks existing data when you first map elements. To enable validation, check the Validate data against schema for import and export option. Figure 6-32 shows the option in the XML Map Properties dialog box.

Figure 6-32: Enabling the validation option

You may need to turn this option on and off as you change data structures and experiment with different worksheet layouts for your XML data.

Saving a structured XML document

Once youve added XML map elements to an Excel document, you can export structured XML data. Right-click within your data and choose XML Export , as shown in Figure 6-33.

Figure 6-33: Exporting XML data from Excel

Exporting creates a document based on your schema or XML map. It will create a much smaller document than the equivalent SpreadsheetML content. The export process generates an XML declaration and XML element names from the document map. It is equivalent to selecting the Save data only option in Word 2003. Excel discards information about formatting and document properties during the export. Figure 6-34 shows the exported data opened in XMLSpy.

Figure 6-34: XML content exported from Excel

Because this document is considerably shorter than the SpreadsheetML equivalent, its likely to be much easier to work with in Flash. As I pointed out earlier, youll need to create an XML schema first. Each time you open the XML document in Excel, youll be prompted about how the document should be opened, that is, as a list, as a read-only workbook, or using the XML Source pane.

You can create the same document by using File Save As and selecting the XML Data option. This is shown in Figure 6-35.

Figure 6-35: Using Save As with the XML Data option

When you click the Save button, youll see the warning shown in Figure 6-36.

Figure 6-36: Loss of features warning when saving as XML data

Excel warns that youll lose formatting information during the save process. Youll need to click the Continue button to save the data in XML format.

Youve seen two methods of generating XML content from Excel. The first method used Save As to create a SpreadsheetML document from an Excel spreadsheet. Youd use this approach if you wanted to be able to preserve formatting and document properties in the XML document. Excel can open this document natively, as if it was an Excel spreadsheet.

The second approach used an XML schema to create a document map for the Excel file. This approach is useful if you need to have more control over the XML document created by Excel. Document maps allow you to structure elements and choose which content to export. You can also exclude unnecessary information such as cell and number formatting. If youre using the XML document in Flash, the second approach is usually the better option as it generates streamlined documents. The disadvantage is that Excel will prompt each time about how to open the XML document.

Lets work through an exercise so you can practice exporting XML data from Excel.

Exercise 4: Generating structured XML content from Excel

In this exercise, well add an XML map to an existing Excel document. Youll mark up elements within the spreadsheet and export the content.

1. Open Excel 2003 if necessary.

2. Choose File Open and select catalog.xls from your resource files.

3. If you cant see the XML Source pane, choose View Task Pane and select XML Source from the drop-down list at the top of the pane.

4. Click the XML Maps button in the XML Source pane.

5. Click the Add button in the XML Maps dialog box.

6. Select the file catalogSchema.xsd and click the Open button. Click OK . Figure 6-37 shows how the Excel document should appear at this point.

   
   Figure 6-37: The catalog.xml file with XML map

7. Drag the <itemCode> element from the XML map to the Item Code heading in the spreadsheet. Repeat with the remaining elements. When you work with the <itemTaxRate> element, youll see the message shown in Figure 6-38. Click the Use existing formatting button. The message occurred because the schema defines the content as a String datatype whereas Excel has formatted the cells as numbers .

   
   Figure 6-38: When a datatype mismatch is detected , Excel asks about cell formatting.

You can see the Excel file completed to this point in Figure 6-39.

Figure 6-39: The catalog.xml file with XML elements mapped

8. Choose File Save As and choose the XML Data option. Change the file name to catalogExport.xml and click the Save button. You can see my completed file within your resources.

9. Click the Continue button and close the file. You can find the Excel file saved as catalog_XMLtags.xls with your resource files.

10. Open the catalogExport.xml file in an XML or text editor. Figure 6-40 shows the file in XMLSpy.

    
    Figure 6-40: The structured XML file saved from Excel

Using an XML schema creates a much simpler, data-only version of the Excel worksheet. In case youre interested, the schema that generated the content is included as follows . You can also see it in the resource file catalogSchema.xsd.

 <?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">   <xsd:element name="catalog">     <xsd:complexType>       <xsd:sequence maxOccurs="unbounded">         <xsd:element name="catalogItem">           <xsd:complexType>             <xsd:sequence>               <xsd:element name="itemCode" type="xsd:string"/>               <xsd:element name="itemDescription" type="xsd:string"/>               <xsd:element name="itemNetPrice" type="xsd:decimal"/>               <xsd:element name="itemTaxRate" type="xsd:string"/>               <xsd:element name="itemTaxIncPrice" type="xsd:decimal"/>             </xsd:sequence>           </xsd:complexType>         </xsd:element> 

 </xsd:sequence>     </xsd:complexType>   </xsd:element> </xsd:schema> 

The schema specifies the structure, element names, and datatypes for the catalog XML data. You can find out more about schemas in Chapter 3.

 

Editing XML content in Excel

If you have an XML list within Excel, you can edit the data by modifying the cell contents or by adding and deleting rows from the list. To add content at the end of the list, start typing in the last row, where the asterisk ( * ) appears. You can add rows within the list by selecting the row and choosing Insert Row . You can do the same with columns. Choose the Insert Column command after selecting the position for the column. Once youve edited the content within Excel, you can generate an XML document. You can either create a SpreadsheetML document or generate your own element structures.

Excel works a little differently from Word. As you can see, its easier for users to add new content to the document, and you dont run the same risk of deleting or overwriting XML tags. You dont need to protect the document before you provide it to users, as you would with a Word document.