[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
This element specifies number format properties which indicate how to format and render the numeric value of a cell.
Following is a listing of number formats whose formatCode value is implied rather than explicitly saved in the file. In this case a numFmtId value is written on the xf record, but no corresponding numFmt element is written. Some of these Ids are interpreted differently, depending on the UI language of the implementing application.
All Languages
|
ID |
formatCode |
|
0 |
General |
|
1 |
0 |
|
2 |
0.00 |
|
3 |
#,##0 |
|
4 |
#,##0.00 |
|
9 |
0% |
|
10 |
0.00% |
|
11 |
0.00E+00 |
|
12 |
# ?/? |
|
13 |
# ??/?? |
|
14 |
mm-dd-yy |
|
15 |
d-mmm-yy |
|
16 |
d-mmm |
|
17 |
mmm-yy |
|
18 |
h:mm AM/PM |
|
19 |
h:mm:ss AM/PM |
|
20 |
h:mm |
|
21 |
h:mm:ss |
|
22 | |
|
37 |
#,##0 ;(#,##0) |
|
38 |
#,##0 ;[Red](#,##0) |
|
39 |
#,##0.00;(#,##0.00) |
|
40 |
#,##0.00;[Red](#,##0.00) |
|
45 |
mm:ss |
|
46 |
[h]:mm:ss |
|
47 |
mmss.0 |
|
48 |
##0.0E+0 |
|
49 |
@ |
"General" Format
Some additional comments about the "General" number format are appropriate.
The primary goal when a cell is using "General" formatting is to render the cell content without user-specified guidance to the best ability of the application.
Alignment
(Specified for Left-to-Right mode)
• Strings: left aligned
• Boolean/error values: centered
• Numbers: right aligned
• Dates: do not follow the "General" format, instead automatically convert to date formatting.
Numbers
The application shall attempt to display the full number up to 11 digits (inc. decimal point). If the number is too large, the application shall attempt to show exponential format. If the number has too many significant digits, the display shall be truncated. The optimal method of display is based on the available cell width. If the number cannot be displayed using any of these formats in the available width, the application shall show "#" across the width of the cell.
Conditions for switching to exponential format:
1. The cell value must have at least five digits for xE-xx
1. If the exponent is bigger than the size allowed, a floating point number cannot fit, so try exponential notation.
2. Similarly, for negative exponents, check if there is space for even one (non-zero) digit in floating point format.
3. Finally, if there isn't room for all of the significant digits in floating point format (for a negative exponent), exponential format shall display more digits if the exponent is less than -3. (The 3 is because E-xx takes 4 characters, and the leading 0 in floating point takes only 1 character. Thus, for an exponent less than -3, there is more than 3 additional leading 0's, more than enough to compensate for the size of the E-xx.)
Floating point rule:
For general formatting in cells, max overall length for cell display is 11, not including negative sign, but includes leading zeros and decimal separator.
CHT and CHS
|
ID |
CHT formatCode |
CHS formatCode |
|
27 |
yyyy"年"m"月" | |
|
28 | ||
|
29 | ||
|
30 |
m-d-yy | |
|
31 | ||
|
32 |
hh"時"mm"分" |
h"时"mm"分" |
|
33 |
hh"時"mm"分"ss"秒" |
h"时"mm"分"ss"秒" |
|
34 |
上午/下午hh"時"mm"分" |
上午/下午h"时"mm"分" |
|
35 |
上午/下午hh"時"mm"分"ss"秒" |
上午/下午h"时"mm"分"ss"秒" |
|
36 |
yyyy"年"m"月" | |
|
50 |
yyyy"年"m"月" | |
|
51 | ||
|
52 |
上午/下午hh"時"mm"分" |
yyyy"年"m"月" |
|
53 |
上午/下午hh"時"mm"分"ss"秒" | |
|
54 | ||
|
55 |
上午/下午hh"時"mm"分" |
上午/下午h"时"mm"分" |
|
56 |
上午/下午hh"時"mm"分"ss"秒" |
上午/下午h"时"mm"分"ss"秒" |
|
57 |
yyyy"年"m"月" | |
|
58 |
CHT and CHS (with unicode values provided for language glyphs where they occur)
|
ID |
CHT formatCode |
CHS formatCode |
|
27 |
yyyy"5E74"m"6708" | |
|
28 | ||
|
29 | ||
|
30 |
m-d-yy | |
|
31 | ||
|
32 |
hh"6642"mm"5206" |
h"65F6"mm"5206" |
|
33 |
hh"6642"mm"5206"ss"79D2" |
h"65F6"mm"5206"ss"79D2" |
|
34 |
4E0A5348/4E0B5348hh"6642"mm"5206" |
4E0A5348/4E0B5348h"65F6"mm"5206" |
|
35 |
4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2" |
4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2" |
|
36 |
yyyy"5E74"m"6708" | |
|
50 |
yyyy"5E74"m"6708" | |
|
51 | ||
|
52 |
4E0A5348/4E0B5348hh"6642"mm"5206" |
yyyy"5E74"m"6708" |
|
53 |
4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2" | |
|
54 | ||
|
55 |
4E0A5348/4E0B5348hh"6642"mm"5206" |
4E0A5348/4E0B5348h"65F6"mm"5206" |
|
56 |
4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2" |
4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2" |
|
57 |
yyyy"5E74"m"6708" | |
|
58 |
JPN and KOR
|
ID |
JPN formatCode |
KOR formatCode |
|
27 |
[$-411]ge.m.d |
yyyy"年" mm"月" dd"日" |
|
28 |
mm-dd | |
|
29 |
mm-dd | |
|
30 |
mm-dd-yy | |
|
31 |
yyyy"년" mm"월" dd"일" | |
|
32 |
h"時"mm"分" |
h"시" mm"분" |
|
33 |
h"時"mm"分"ss"秒" |
h"시" mm"분" ss"초" |
|
34 |
yyyy"年"m"月" |
yyyy-mm-dd |
|
35 |
yyyy-mm-dd | |
|
36 |
[$-411]ge.m.d |
yyyy"年" mm"月" dd"日" |
|
50 |
[$-411]ge.m.d |
yyyy"年" mm"月" dd"日" |
|
51 |
mm-dd | |
|
52 |
yyyy"年"m"月" |
yyyy-mm-dd |
|
53 |
yyyy-mm-dd | |
|
54 |
mm-dd | |
|
55 |
yyyy"年"m"月" |
yyyy-mm-dd |
|
56 |
yyyy-mm-dd | |
|
57 |
[$-411]ge.m.d |
yyyy"年" mm"月" dd"日" |
|
58 |
mm-dd |
JPN and KOR (with unicode values provided for language glyphs where they occur)
|
ID |
JPN formatCode |
KOR formatCode |
|
27 |
[$-411]ge.m.d |
yyyy"5E74" mm"6708" dd"65E5" |
|
28 |
mm-dd | |
|
29 |
mm-dd | |
|
30 |
mm-dd-yy | |
|
31 |
yyyy"B144" mm"C6D4" dd"C77C" | |
|
32 |
h"6642"mm"5206" |
h"C2DC" mm"BD84" |
|
33 |
h"6642"mm"5206"ss"79D2" |
h"C2DC" mm"BD84" ss"CD08" |
|
34 |
yyyy"5E74"m"6708" |
yyyy-mm-dd |
|
35 |
yyyy-mm-dd | |
|
36 |
[$-411]ge.m.d |
yyyy"5E74" mm"6708" dd"65E5" |
|
50 |
[$-411]ge.m.d |
yyyy"5E74" mm"6708" dd"65E5" |
|
51 |
mm-dd | |
|
52 |
yyyy"5E74"m"6708" |
yyyy-mm-dd |
|
53 |
yyyy-mm-dd | |
|
54 |
mm-dd | |
|
55 |
yyyy"5E74"m"6708" |
yyyy-mm-dd |
|
56 |
yyyy-mm-dd | |
|
57 |
[$-411]ge.m.d |
yyyy"5E74" mm"6708" dd"65E5" |
|
58 |
mm-dd |
THA
|
ID |
THA formatCode |
|
59 |
t0 |
|
60 |
t0.00 |
|
61 |
t#,##0 |
|
62 |
t#,##0.00 |
|
67 |
t0% |
|
68 |
t0.00% |
|
69 |
t# ?/? |
|
70 |
t# ??/?? |
|
71 |
ว/ด/ปปปป |
|
72 |
ว-ดดด-ปป |
|
73 |
ว-ดดด |
|
74 |
ดดด-ปป |
|
75 |
ช:นน |
|
76 |
ช:นน:ทท |
|
77 |
ว/ด/ปปปป ช:นน |
|
78 |
นน:ทท |
|
79 |
[ช]:นน:ทท |
|
80 |
นน:ทท.0 |
|
81 |
THA (with unicode values provided for language glyphs where they occur)
|
ID |
THA formatCode |
|
59 |
t0 |
|
60 |
t0.00 |
|
61 |
t#,##0 |
|
62 |
t#,##0.00 |
|
67 |
t0% |
|
68 |
t0.00% |
|
69 |
t# ?/? |
|
70 |
t# ??/?? |
|
71 |
0E27/0E14/0E1B0E1B0E1B0E1B |
|
72 |
0E27-0E140E140E14-0E1B0E1B |
|
73 |
0E27-0E140E140E14 |
|
74 |
0E140E140E14-0E1B0E1B |
|
75 |
0E0A:0E190E19 |
|
76 |
0E0A:0E190E19:0E170E17 |
|
77 |
0E27/0E14/0E1B0E1B0E1B0E1B 0E0A:0E190E19 |
|
78 |
0E190E19:0E170E17 |
|
79 |
[0E0A]:0E190E19:0E170E17 |
|
80 |
0E190E19:0E170E17.0 |
|
81 |
|
Parent Elements |
|
dxf (§3.8.14); ndxf (§3.11.1.4); numFmts (§3.8.31); odxf (§3.11.1.6) |
|
Attributes |
Description |
|
formatCode (Number Format Code) |
The number format code for this number format.
The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96). |
|
numFmtId (Number Format Id) |
Id used by the master style records (xf's) to reference this number format.
The possible values for this attribute are defined by the ST_NumFmtId simple type (§3.18.49). |
The following XML Schema fragment defines the contents of this element:
<complexType name="CT_NumFmt">
<attribute name="numFmtId" type="ST_NumFmtId" use="required"/>
<attribute name="formatCode" type="ST_Xstring" use="required"/>
</complexType>