This article describes how formatting is applied to imported
XML data in Microsoft Office Excel 2003.
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.
Collapse this tableExpand this table
| 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. |