NumberingFormat Class
Number Formats.When the object is serialized out as xml, its qualified name is x:numFmt.
Inheritance Hierarchy
System.Object
DocumentFormat.OpenXml.OpenXmlElement
DocumentFormat.OpenXml.OpenXmlLeafElement
DocumentFormat.OpenXml.Spreadsheet.NumberingFormat
Namespace: DocumentFormat.OpenXml.Spreadsheet
Assembly: DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)
Syntax
'Declaration
Public Class NumberingFormat _
Inherits OpenXmlLeafElement
'Usage
Dim instance As NumberingFormat
public class NumberingFormat : OpenXmlLeafElement
Remarks
[ISO/IEC 29500-1 1st Edition]
18.8.30 numFmt (Number Format)
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 can be interpreted differently, depending on the UI language of the implementing application.
Ids not specified in the listing, such as 5, 6, 7, and 8, shall follow the number format specified by the formatCode attribute.
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
m/d/yy h:mm
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:
The cell value shall have at least five digits for xE-xx
If the exponent is bigger than the size allowed, a floating point number cannot fit, so try exponential notation.
Similarly, for negative exponents, check if there is space for even one (non-zero) digit in floating point format.
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.
zh-tw and zh-cn
ID
zh-tw formatCode
zh-cn formatCode
27
[$-404]e/m/d
yyyy"年"m"月"
28
[$-404]e"年"m"月"d"日"
m"月"d"日"
29
[$-404]e"年"m"月"d"日"
m"月"d"日"
30
m/d/yy
m-d-yy
31
yyyy"年"m"月"d"日"
yyyy"年"m"月"d"日"
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
[$-404]e/m/d
yyyy"年"m"月"
50
[$-404]e/m/d
yyyy"年"m"月"
51
[$-404]e"年"m"月"d"日"
m"月"d"日"
52
上午/下午hh"時"mm"分"
yyyy"年"m"月"
53
上午/下午hh"時"mm"分"ss"秒"
m"月"d"日"
54
[$-404]e"年"m"月"d"日"
m"月"d"日"
55
上午/下午hh"時"mm"分"
上午/下午h"时"mm"分"
56
上午/下午hh"時"mm"分"ss"秒"
上午/下午h"时"mm"分"ss"秒"
57
[$-404]e/m/d
yyyy"年"m"月"
58
[$-404]e"年"m"月"d"日"
m"月"d"日"
zh-tw and zh-cn (with unicode values provided for language glyphs where they occur)
ID
zh-tw formatCode
zh-cn formatCode
27
[$-404]e/m/d
yyyy"5E74"m"6708"
28
[$-404]e"5E74"m"6708"d"65E5"
m"6708"d"65E5"
29
[$-404]e"5E74"m"6708"d"65E5"
m"6708"d"65E5"
30
m/d/yy
m-d-yy
31
yyyy"5E74"m"6708"d"65E5"
yyyy"5E74"m"6708"d"65E5"
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
[$-404]e/m/d
yyyy"5E74"m"6708"
50
[$-404]e/m/d
yyyy"5E74"m"6708"
51
[$-404]e"5E74"m"6708"d"65E5"
m"6708"d"65E5"
52
4E0A5348/4E0B5348hh"6642"mm"5206"
yyyy"5E74"m"6708"
53
4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2"
m"6708"d"65E5"
54
[$-404]e"5E74"m"6708"d"65E5"
m"6708"d"65E5"
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
[$-404]e/m/d
yyyy"5E74"m"6708"
58
[$-404]e"5E74"m"6708"d"65E5"
m"6708"d"65E5"
ja-jp and ko-kr
ID
ja-jp formatCode
ko-kr formatCode
27
[$-411]ge.m.d
yyyy"年" mm"月" dd"日"
28
[$-411]ggge"年"m"月"d"日"
mm-dd
29
[$-411]ggge"年"m"月"d"日"
mm-dd
30
m/d/yy
mm-dd-yy
31
yyyy"年"m"月"d"日"
yyyy"년" mm"월" dd"일"
32
h"時"mm"分"
h"시" mm"분"
33
h"時"mm"分"ss"秒"
h"시" mm"분" ss"초"
34
yyyy"年"m"月"
yyyy-mm-dd
35
m"月"d"日"
yyyy-mm-dd
36
[$-411]ge.m.d
yyyy"年" mm"月" dd"日"
50
[$-411]ge.m.d
yyyy"年" mm"月" dd"日"
51
[$-411]ggge"年"m"月"d"日"
mm-dd
52
yyyy"年"m"月"
yyyy-mm-dd
53
m"月"d"日"
yyyy-mm-dd
54
[$-411]ggge"年"m"月"d"日"
mm-dd
55
yyyy"年"m"月"
yyyy-mm-dd
56
m"月"d"日"
yyyy-mm-dd
57
[$-411]ge.m.d
yyyy"年" mm"月" dd"日"
58
[$-411]ggge"年"m"月"d"日"
mm-dd
ja-jp and ko-kr (with unicode values provided for language glyphs where they occur)
ID
ja-jp formatCode
ko-kr formatCode
27
[$-411]ge.m.d
yyyy"5E74" mm"6708" dd"65E5"
28
[$-411]ggge"5E74"m"6708"d"65E5"
mm-dd
29
[$-411]ggge"5E74"m"6708"d"65E5"
mm-dd
30
m/d/yy
mm-dd-yy
31
yyyy"5E74"m"6708"d"65E5"
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
m"6708"d"65E5"
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
[$-411]ggge"5E74"m"6708"d"65E5"
mm-dd
52
yyyy"5E74"m"6708"
yyyy-mm-dd
53
m"6708"d"65E5"
yyyy-mm-dd
54
[$-411]ggge"5E74"m"6708"d"65E5"
mm-dd
55
yyyy"5E74"m"6708"
yyyy-mm-dd
56
m"6708"d"65E5"
yyyy-mm-dd
57
[$-411]ge.m.d
yyyy"5E74" mm"6708" dd"65E5"
58
[$-411]ggge"5E74"m"6708"d"65E5"
mm-dd
th-th
ID
th-th 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
d/m/bb
th-th (with unicode values provided for language glyphs where they occur)
ID
th-th 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
d/m/bb
Parent Elements |
|---|
dxf (§18.8.14); ndxf (§18.11.1.4); numFmts (§18.8.31); odxf (§18.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 (§22.9.2.19). |
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 (§18.18.47). |
[Note: The W3C XML Schema definition of this element’s content model (CT_NumFmt) is located in §A.2. end note]
© ISO/IEC29500: 2008.
Thread Safety
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.