7.4. Importing and exporting XML data Once you have set up the map, you can import and export XML data from the worksheet. In this scenario, the Excel worksheet serves as a report template that you can regularly update with new data. You can also use Excel as a tool for entering data that you can then export in a format that is reusable and accessible to other applications. 7.4.1 Exporting XML You can export the data that is currently in the worksheet as an XML document by clicking Export on the XML submenu. This will save a document that conforms to the expenses.xsd schema. Unlike Word, Excel is not constantly validating your data against the schema as you edit it. When you export it, though, it does validate it and it reports any errors it finds. These errors might include: -
required elements that are missing, because they were not mapped -
values that do not conform to the datatypes declared in the schema -
elements that appear more times than allowed by the schema Excel will inform you about the first of the validation errors it encounters (not all of them), but it will save the exported XML anyway. An example of what is saved is shown in Example 7-3. Example 7-3. Exported XML fragment (expenses exported.xml) <?xml version="1.0" encoding="UTF-8"?> <ns1:expenseReport xmlns:ns1="http://xmlinoffice.com/expenses"> <ns1:empName>Ellen Sandler</ns1:empName> <ns1:empNum>305</ns1:empNum> <ns1:purpose>Sales meeting with ABC Corp.</ns1:purpose> <ns1:deptCode>305</ns1:deptCode> <ns1:billToCode>1104</ns1:billToCode> <ns1:periodFrom>2004-04-16</ns1:periodFrom> <ns1:periodTo>2004-04-18</ns1:periodTo> <ns1:expense> <ns1:date>2004-04-16</ns1:date> <ns1:explanation>Phone call from hotel</ns1:explanation> <ns1:mileage/> <ns1:airFare/> <ns1:perDiem/> <ns1:lodging/> <ns1:auto/> <ns1:taxi/> <ns1:phone>0.75</ns1:phone> <ns1:businessMeals/> <ns1:other> <ns1:code/> <ns1:amount/> </ns1:other> </ns1:expense> ... <ns1:expense> <ns1:date>2004-04-16</ns1:date> <ns1:explanation>Dry Cleaning</ns1:explanation> <ns1:mileage/> <ns1:airFare/> <ns1:perDiem/> <ns1:lodging/> <ns1:auto/> <ns1:taxi/> <ns1:phone/> <ns1:businessMeals/> <ns1:other> <ns1:code>887</ns1:code> <ns1:amount>16.75</ns1:amount> </ns1:other> </ns1:expense> </ns1:expenseReport>
Notice that all possible elements are included in the exported XML. If a cell is empty, an empty element appears, rather than the element simply being omitted. This can sometimes cause a problem for elements with numeric or date types, for which an empty element is not valid. 7.4.2 Importing XML data New XML data can also be imported into the worksheet. This is accomplished by clicking Import on the XML submenu, and selecting the name of the XML file to import. Importing XML data into an existing worksheet is quite different from simply opening an XML document in Excel, as we will see in 7.5.1, "Using a schema", on page 148. When XML data is imported, Excel will place all the data from the XML document into the correct cells based on the maps. For example, we can import a new expense file called new expenses.xml to see how the import works. To do this: - 1. Click Import from the XML submenu.
- 2. Select the file new expenses.xml and click Import.
You will now see the new data in the worksheet. Note that the number of lines in the list range has been reduced, as shown in Figure 7-5. Figure 7-5. Imported data 
7.4.2.1 Preserving data and formatting By default, importing XML data will overwrite the data that is currently in the cells, and it will alter the size of the list range (by adding or deleting rows) to accommodate the new data. It is also possible to append repeating data to what already exists, adding new rows to the list range while preserving the existing ones. This process can be controlled by a number of options that are available by clicking XML Map Properties from the XML submenu. The resulting dialog is shown in Figure 7-6. Figure 7-6. The XML Map Properties dialog 
The following options are available from this dialog: -
Validate data against schema for import and export. If this box is checked, Excel will validate imported and exported XML documents against the schema. -
Adjust column width. The column widths will be automatically updated to accommodate the width of the data that is imported. If you have carefully formatted your worksheet, you may wish to uncheck this box. -
Preserve column filter. The current options specified for the list range will not be overwritten on import. -
Preserve number formatting. The formatting of the numbers in cells will not be overwritten on import. -
Overwrite existing data with new data. All existing data will be deleted and new data inserted. -
Append new data to existing XML lists. New data will be appended to the existing data in the list range. This does not affect non-repeating elements, which will be overwritten. 7.4.2.2 Importing and validation The imported XML file must be well-formed XML. If you have checked the Validate data against schema for import and export box on the XML Map Properties dialog, Excel will validate the document upon import. The imported document must have the same namespace as the target namespace of the schema. If not, Excel will prompt you to indicate where you want the data imported, and it will not import it according to the map. Otherwise, Excel will report any errors it finds in the document. It is very forgiving if the document is not valid and it will import the invalid data into the worksheet. Missing elements are represented as empty cells, and invalid values are simply shown as they appear in the document. | 
