Excel-Friendly XML Schemas

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.

Table 21-1. XML Schema Constructs That Are Not Supported by Excel

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.

Table 21-2. XML Schema Types and Their Corresponding Excel Cell Formatting

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



Visual Studio Tools for Office(c) Using C# with Excel, Word, Outlook, and InfoPath
Visual Studio Tools for Office(c) Using C# with Excel, Word, Outlook, and InfoPath
ISBN: 321334884
EAN: N/A
Year: N/A
Pages: 214

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