Several characteristics make an XML schema more amenable to being mapped into Excel. First, an XML schema should have one root element. In our example in this chapter, the root element is Order. If a schema supports more than one root element, you must choose which element will be the root element when adding the XML map to the spreadsheet.
Unsupported XML Schema Constructs
Excel does not support several XML schema constructs. Table 21-1 lists these constructs.
Construct |
What It Does |
---|---|
Allows you to include arbitrary elements that are not declared by the schema. |
|
Allows you to include arbitrary attributes that are not declared by the schema. |
|
recursion |
A structure that refers to itself recursively. |
abstract elements |
Allows an element to be declared but never usedalso uses substitution to substitute other elements for the abstract element. |
Allows an element to be substituted for another element. |
|
mixed content |
When XML elements are intermixed with non XML. This proves to be very useful for Word XML mapping. |
Constructs That Can Be Mapped But Not Exported
There are also several things that can be mapped but the generated mappings cannot be exported as XML. You have already seen that if an XML mapping is denormalized, it cannot be exported. You also cannot export a list of items containing a second list of items. Choice elements can also not be exported.
The other general class of issues that prevents exporting is when a mapped element's relationship with another element it is related to cannot be preserved by the mapping. For more information on these types of mapping issues, consult the Excel documentation.
VSTO-Friendly Schemas
VSTO puts some additional requirements on schema mapping if you want to use a schema mapped spreadsheet with VSTO. First, you need to have a schema mapping that can be exported. Second, the schema mapping must all be within a single worksheet. Although Excel will let you map some elements of the schema to Sheet1 and other elements to Sheet2, VSTO requires that all schema mapping for a given schema be on the same sheet.
How XML Schema Data Types Are Mapped to Excel Cell Formats
As you saw earlier in this book when mapping dates, Excel can automatically pick cell formatting based on the type in the schema. When we dragged a date into Excel, Excel prompted to change the cell formatting. Table 21-2 shows how Excel maps schema types to Excel cell formatting settings.
XML Type |
Excel Formatting |
XML Type |
Excel Formatting |
---|---|---|---|
anytype |
Text |
int |
General |
anyURI |
Text |
integer |
General |
base64Binary |
Text |
language |
Text |
boolean |
Boolean |
long |
General |
byte |
General |
Name |
Text |
date |
Date *3/14/2001 |
NCName |
Text |
dateTime |
m/d/yyyy h:mm |
negativeInteger |
General |
decimal |
General |
NMTOKEN |
Text |
double |
General |
NMTOKENS |
Text |
duration |
Text |
nonNegativeInteger |
General |
ENTITIES |
Text |
nonPositiveInteger |
General |
ENTITY |
Text |
normalizedString |
Text |
float |
General |
NOTATION |
Text |
gDay |
Number, no decimals |
positiveInteger |
General |
QName |
Text |
||
gMonth |
Number, no decimals |
short |
General |
string |
Text |
||
gMonthDay |
Custom d-mmm |
time |
h:mm:ss |
gYear |
Number, no decimals |
token |
Text |
unsignedByte |
General |
||
gYearMonth |
Custom mmm-yy |
unsignedInt |
General |
hexBinary |
Text |
unsignedLong |
General |
ID |
Text |
unsignedShort |
General |
IDREF |
Text |
||
IDREFS |
Text |
Part One. An Introduction to VSTO
An Introduction to Office Programming
Introduction to Office Solutions
Part Two. Office Programming in .NET
Programming Excel
Working with Excel Events
Working with Excel Objects
Programming Word
Working with Word Events
Working with Word Objects
Programming Outlook
Working with Outlook Events
Working with Outlook Objects
Introduction to InfoPath
Part Three. Office Programming in VSTO
The VSTO Programming Model
Using Windows Forms in VSTO
Working with Actions Pane
Working with Smart Tags in VSTO
VSTO Data Programming
Server Data Scenarios
.NET Code Security
Deployment
Part Four. Advanced Office Programming
Working with XML in Excel
Working with XML in Word
Developing COM Add-Ins for Word and Excel
Creating Outlook Add-Ins with VSTO