How formatting is applied to imported XML data in Excel 2003 (840475)

MORE INFORMATION

When you import XML data into cells that have formatting, Excel 2003 compares the cell formatting to the XML Schema Definition (XSD) data type. If the formatting is compatible, the data is imported and the cell formatting is not changed. For example, the existing cell format is a number that has two decimal places, and you import an element that has a data type of long. If the data type is not compatible, you receive a warning dialog box that prompts you to select one of the following actions:

• Use existing formatting - ignore the XSD data type.
• Match element data type - change the cell formatting to the appropriate type.
• Cancel - do not import the XML data.

When you import XML data into cells that do not have formatting or when you choose to change the formatting to match the data type, Excel 2003 applies a display format that is based on the XML XSD data type. For example, when you import a DateTime date type, the cell format changes to a date format. Some data is imported, and then a general format is applied to the cells.

The following table lists supported and unsupported XSD data types. Data with an XSD format listed in the Unsupported Formats column is imported as text values.

Supported XSD data type	Excel Display Format	Unsupported Formats
Time	h:mm:ss	hh:mm:ssZ
		Hh:mm:ss.f-f
dateTime	m/d/yyyy h:mm	yyyy-mm-ddThh:mm:ssZ
		yyyy-mm-ddThh:mm:ss+/-hh:mm
		yyyy-mm-ddThh:mm:ss.f-f
		Years outside the range between 1900 to 9999
date	Date *3/14/2001	yyyy-mm-ddZ
		yyyy-mm-dd+/-hh:mm
		Years outside the range between 1900 to 9999
gYear	Number, no decimals	yyyy+/-hh:mm
		Years outside the range between 1900 to 9999
gDay	Number, no decimals
gMonth
gYearMonth	Custom mmm-yy	yyyy-mm+/-hh:mm
		Years outside the range between 1900 to 9999
gMonthDay	Custom d-mmm
Anytype anyURI Base64Binary Duration ENTITIES ENTITY HexBinary ID IDREF IDREFS language Name NCName NMTOKEN NMTOKENS normalizedString NOTATION QName String token	Text
Boolean	Boolean
Decimal Float Double Byte Int Integer Long negativeInteger nonNegativeInteger nonPositiveInteger positiveInteger Short unsignedByte unsignedInt unsignedLong unsignedShort	General Leading and trailing zeros are dropped. '-' and '+' signs are respected, although only negative signs appear. Excel stores and calculates with 15 significant digits of precision.