主要参考资料1.Excel自定义格式详解_小鸿1983的博客-CSDN博客

                         2.Excel自定义数字格式详解(很全面、完整) - 百度文库

        上次写openpyxl设置excel单元格填充格式时，由于没有深入了解，觉得网上对其他格式设置的介绍够详细了。但使用查阅中发现网上关于Excel单元格自定义数值格式的介绍虽然很多也够详细，但一来很多比较凌乱，不成系统。二来，部分介绍语焉不详。三来有的介绍有误。故特地逐一测试后重新整理说明。如有错漏之处，敬请指正！

第一部分——Excel单元格自定义数值格式详解说明

一、格式代码元素

格式代码的组成元素分类（本人按自己理解的进行分类）

1.代码分隔符英文“;”或中文“；”。后者会被自动修改成“;”

2.文本占位符“@”

3.数字格式占位符数字“0”和英文符号“#”、“?”、“.”、“/”、“,”、“:”。

其中数字“0”和英文符号“#”、“?”为数字占位符，其他为格式占位符。

4.格式转化及占位符“%”、“”、“”、“_”、“!”、“"”、不分大小写的字母a、b、d、e、h、m、s、y

5.特殊格式符“[ ]”

6.普通非格式字符文本包括汉字及中文标点符号、非0数字、非数字格式符的英文字母和符号、格式符经转化而成的普通文本字符。

二、格式代码分类

1.文本格式代码指包含文本占位符@的格式代码。可以用于任何原始数据的显示格式设定。

2.数字格式代码不含文本占位符@的格式代码。只能对纯数字型的单元格原始数据显示格式起作用。

三、格式代码结构

代码最多可分为四个节，中间用英文分号“;”分隔。

【注意】多节代码中，文本格式代码只能在一个节，否则会报错。四节代码中，第四节若为数字格式代码也会报错。

根据给出的代码结构情况，各节代码的作用示例如下

1.只有一节的“文本格式代码”将任何形式的数据原始内容均按该文本格式代码的格式显示。

2.只有一节的“数字格式代码”文本数据按原始内容显示；正数和0用该代码格式显示；负数显示在该格式基础上前面加“-”号。

3.两节的“数字格式代码1;数字格式代码2”文本数据按原始内容显示；正数和0用代码1格式显示；负数用代码2格式显示。

4.三节的“数字格式代码1;数字格式代码2;”文本数据按原始内容显示；正数用代码1格式显示；负数用代码2格式显示；0值隐藏。

5.三节的“数字格式代码1;;数字格式代码2”文本数据按原始内容显示；正数用代码1格式显示；负数隐藏；0值用代码2格式显示。

6.三节的“;数字格式代码1;数字格式代码2”文本数据按原始内容显示；正数隐藏；负数用代码1格式显示；0值用代码2格式显示。

7.两节或三节代码，且一节为文本格式代码文本按文本格式代码指定格式显示；数字按去掉一节后对应的2至6相应的规则显示。

例如“数字格式代码;;文本格式代码”去掉一节后的代码为“数字格式代码;”，对应第3种情况，此时数字格式代码2为空节，故正数和0用数字格式代码指定格式显示；负数隐藏。

8.四节代码各节代码的作用对应顺序为“正数格式代码;负数格式代码;零格式代码;文本格式代码”，用相应的代码格式显示对应的数据类型。隐藏空节对应的数据类型。

例如“数字格式代码;;;文本格式代码”隐藏负数和0，按对应格式显示正数和文本。

再如“;;;”任何数据均隐藏。

四、各代码符号含义

1.“G/通用格式”。以常规的数字显示，相当于分类列表中的“常规”选项。

代码G/通用格式

显示效果10显示为“10”；10.1显示为“10.1”

2.“.”小数点占位符。可单独使用，也可配合数字占位符使用（配合使用方法在其他格式符中介绍）。

代码.

显示效果123显示为“123.”；1.23显示为“1.”。

3.“#”数字占位符。只显非0数字和有意义的零，不显示无意义的零。配合小数点使用时，实际数据小数点后数字位数如果大于小数点后“#”的数量,则按“#”的位数四舍五入。

代码###.##

显示效果12.1显示为“12.1”；12.1263显示为“12.13”。

4.“0”数字占位符。如果实际数据的小数点前后的数字位数小于占位符个数，则分别在前后用0补足。如果小数位数大于占位符数量，则按占位符位数四舍五入。

代码00000

显示效果1234567显示为“1234567”；123显示为“00123”；123.45678显示为“00123”。

代码00.000

显示效果100.14显示为“100.140”；1.1显示为“01.100”。

【注意】“0”和“#”的区别

代码#.###

显示效果12显示为“12.”

代码#.000

显示效果12显示为“12.000”

代码#.##0或#.0##

显示效果12显示为“12.0”

5.“?”数字占位符

在小数点两边为无意义的零添加空格，以便使一列数据按小数点对齐。使用“?”占位符与使用“#”占位符的区别就是最终显示的宽度可能不同。

代码???.???

显示效果A1至A4分别输入12.3456、12.34、123.45678、12，显示为

 

6.“/”分数占位符。用于将数字以分数的形式显示，必需要和数字占位符配合使用，单独使用会报错。

代码#/#

显示效果12.3456显示为“37/3”；

代码#/##

显示效果12.3456显示为“1000/81”；

代码#/###

显示效果12.3456显示为“7716/625”；

代码# #/#

显示效果12.3456显示为“12 1/3”；

代码# #/##

显示效果12.3456显示为“12 28/81”；

“/”和“?”配合可用于将分数按“/”的位置对齐

代码# ???/???

显示效果A1至A4分别输入12.3456、12.34、123.45678、12，显示为

 用“#”则不行

代码# #/###（# ###/###和# #/###效果一样）

显示效果A1至A4分别输入12.3456、12.34、123.45678、12，显示为

 

7.“,”千位分隔符。需配合数字占位符使用，单独的“,”会被视为普通文本字符。

代码#,###（#,#或#,##会被自动修改为#,###）

显示效果12000显示为“12,000”

代码#,##0（#,0或#,#0会被自动修改为#,##0，该格式为系统默认无货币符号的货币类型，确定后再打开单元格格式设置查看，自定义类型会变成货币类型）

显示效果12000显示为“12,000”

如果代码中“，”之后没有数字占位符，则把原来的数字缩小1000倍。

代码#，

显示效果10000显示为“10”。

代码#，，

显示效果1000000显示为“1”。

8.“%”百分数转换和占位符。将数字转化为百分比的形式，需配合数字占位符及小数点来使用，单独的“%”将被自动转换成普通文本字符。

若设置格式前单元格中已经有数据，则将其进行百分比转换（先乘以100，再在添加%符号）后按格式显示。若先设置格式，再输入数据，则在输入的数字后直接添加%符号后按格式显示。

代码#%

显示效果原有的12.3456转化为1234.56%，并显示为“1235%”；设置格式后输入12.3456，先转化为12.3456%，并显示为“12%”

代码#.#%

显示效果原有的12.3456转化为1234.56%，并显示为“1234.6%”；设置格式后输入12.3456，先转化为12.3456%，并显示为“12.3%”

9.“E+”科学记数符。必须配合数字占位符等使用，单独使用会报错。

代码0.00E+00

显示效果123显示为“1.23E+02”

代码0.000E+000

显示效果123显示为“1.230E+002”

10.“@”文本占位符。单个@的作用是引用单元格内输入的原始内容，将其以文本格式显示出来。多个@则可以重复显示所引用的文本。

代码@

显示效果123显示为文本格式的“123”，格式类型自动改为文本，并有“以文本形式存储的数字”错误提示

代码@@@

显示效果123显示为文本格式的“123123123”；12.3显示为文本格式的“12.312.312.3”

代码;;;@，@，@

显示效果ABC显示为“ABC，ABC，ABC”；12.3不显示。

【注意】这里的“，”若是换成中文的“；”，则必须加""成为;;;@"；"@"；"@形式。否则会被当成6节的代码报错了。

11.非格式字符文本。格式代码中有占位符存在的情况下，非格式字符文本会按照其与占位符的位置关系被附加到显示数据的相应位置。格式代码中无占位符的情况下，对于文本数据的显示没有影响，对于数字数据来说，显示效果相当于用格式代码中的非格式字符文本替换了数据中的数字。若数据为正数或0，将只显示格式代码中的非格式字符文本，若数据为负数，则在非格式字符文本前也会显示有“-”号。

代码5#.##

显示效果12显示为“512.”；-12.345显示为“-512.35”

代码哈哈

显示效果12显示为“哈哈”；-12.345显示为“-哈哈”

代码#万#千#百#十#

显示效果1234567显示为“123万4千5百6十7”；123.45显示为“万千1百2十3”；-1.2显示为“-万千百十1”

12.“""”:文本转换符。将“""”中间的任何内容转化为普通非格式字符文本，附加到占位符相对的位置。

代码"#"#

显示效果12显示为“#12”；12.34显示为“#12”

代码#.##0"元"

显示效果12显示为“12.0元”；12.34显示为“12.340元”

代码"高一（"#"）班"

显示效果1显示为“高一（1）班”；12.34显示为“高一（12）班”

13.“!”格式字符转文本符。将紧跟其后的一个格式字符转换为普通字符显示，若紧跟其后的是非格式字符，则不起作用，代码中的“!”会被自动删掉。

代码!%

显示效果123显示为“%”

代码#!"

显示效果10显示为“10"”

代码#!.#,万

显示效果1234567显示为“123.5万”；-12345.67显示为“-1.2万”；-1.2显示为“.万”

14.“”:格式字符转文本符。作用等同于“!”，且若其之后紧跟的是格式字符，“”会被自动修改为“!”。若紧跟其后的是非格式字符，则不起作用，代码中的“”会被自动删掉。

代码#.###

显示效果12显示为“#12.”；12.34显示为“#12.34”

不少文章里都误以为“”的作用类似于“""”是起到显示普通文本的作用，其实测试一下就知道了。，非格式字符并不需要专门转化，直接输入格式代码为5、k、哈哈等非格式字符，照样能正常起作用，并且再打开设置查看时，5、k等非中文字符原封不动，只有汉字和除了，；“‘等之外的中文字符会自动加上“""”。，输入格式代码"5",确定后再打开设置，仍然是"5"，输入格式字符5或!5后确定，再打开设置“”或“!”已经被自动删掉了。再比如

代码"人民币"#,###,,"百万" 或 人民币#,###,,百万 或 人民币#,###,,百万

显示效果1234567890显示为“人民币1,235百万”；1230000000显示为“人民币1,230百万”

上述三种代码都会被自动修改为"人""民""币"#,###,,"百""万"

15.“_”转换空格符。将“_”之后紧跟着的一个任意字符转换成等宽的空格。

代码_ #,##0

显示效果123显示为“ 123”；

代码_#,##0

显示效果123显示为“ ,123”（第一个#被转换成空格）；

代码_1#,##0

显示效果123显示为“ 123”；

代码_12#,##0

显示效果123显示为“ 2123”；

代码_中国@（代码会被自动修改成_中"国"@）

显示效果ABC显示为“　国ABC”（“中”被转换成全角空格）；123显示为“　国123”，并有“以文本形式存储的数字”错误提示

16.“”字符复制符。重复下一个字符,直到充满列宽

代码@-

显示效果ABC显示为“ABC-------------”（“-”字符的个数由单元格宽度决定）

代码

显示效果123显示为“”（可用于仿真密码保护，“”字符的个数由单元格宽度决定）

17.“[ ]”使用颜色、判断条件、特殊格式。

（1）使用颜色可以在四节代码的任一节中的格式代码前用“[]”设置颜色，则单元格数据符合该节条件时，在以该节的格式代码指定格式显示数据的，使显示内容以指定颜色作为前景色来显示。

第一种颜色指定方式[颜色名]

此处颜色名有8个可选值[黑色]、[白色]、[红色]、[蓝色]、[绿色]、[黄色]、[蓝绿色]、[洋红]。（网上有[黑色]、[白色]、[红色]、[蓝色]、[绿色]、[黄色]、[青色]、[洋红]8种颜色的说法，本人在excel2007中测试时，使用[青色]出错，使用[蓝绿色]可正常设置。）

对A1单元格设置格式代码#;[黑色]@，对B1-H1单元格分别用[白色]、[红色]、[蓝色]、[绿色]、[黄色]、[蓝绿色]、[洋红]替换该格式代码中的[黑色]设置格式，效果如图（其中白色和黄色为了颜色对比，另行设置了单元格填充灰色）

第二种颜色指定方式[颜色N]，N为颜色色号，经测试N可使用的色号范围为1-56，效果如下

 

（2）使用判断条件可以在格式代码前设置使用该格式的判断条件，根据单元格数据与设置的条件对比判断，符合所给条件后设置对应格式。

判断条件只能是针对数字格式代码进行设置，不能用于文本格式代码；判断条件只能是针对数字型数据的大小进行简单比较，可用比较符有6种“=”等于、“<>”不等于、“<”小于、“<=”小于等于、“>”大于、“>=”大于等于；判断条件最多只能设置2个并且只能设置于数字格式代码的前两节。

①三节数字格式代码自定义的不带条件三节数字格式代码前两节是有隐含设置条件的，我们输入的“数字格式代码1;数字格式代码2;数字格式代码3;文本格式代码”其实相当于[>0]数字格式代码1;[<0]数字格式代码2;数字格式代码3;文本格式代码。其中数字格式代码3用于所有不满足前两节条件的情况，即等于0的情况。当自行设置判断条件后，自设条件就替换了前两节原来隐含的[>0]和[<0]的条件。

例如对于以下表格数据的A2-D2四个单元格均设置以下格式代码

代码:[蓝色][>=85]#.#0分,优秀;[蓝绿色][>=60]#.#0分,及格;[绿色]#.#0分,不及格;[红色]@

显示效果

 此时，相当于用[>=85]条件替换了原本第一节的隐含条件[>0]，用[>=60]替换了原本第二节的隐含条件[<0]。A2的87按“[蓝色]#.#0分,优秀”的格式显示，B2单元格的72使用“[蓝绿色]#.#0分,及格”的格式显示，C2单元格的56不满足前两节的条件，所以使用第三节的“[绿色]#.#0分,不及格”的格式显示，D1单元格的“缺考”使用文本格式代码“[红色]@”的格式显示。

再如在A1输入5，B1输入-123.45，C1输入1.22，D1输入abc。四个单元格均设置以下格式代码

代码:[红色][=5]#;[蓝色]-#.##0;[绿色]#0 ??/??;[洋红]文本@

显示效果

 此时，相当于用[=5]条件替换了原本第一节的[>0]，则A1按“[红色]#”的格式显示，第二节的隐含条件[<0]不变，B1单元格的负数使用“[蓝色]-#.##0”的格式显示，C1单元格的1.22为不等于5的非负数，使用“[绿色]#0 ??/??”的格式显示，D1单元格的abc使用“[洋红]文本@”的格式显示。

再如对以下数据的A1-D1单元格设置代码

代码:[红色]#;[蓝色][=-5]-#.##0;[绿色]#0 ??/??;[洋红]文本@

显示效果

 此时，相当于第一节的隐含条件[>0]不变，用[=-5]条件替换了原本第二节的[<0]，所有不等于-5的非正数均按第三节“[绿色]#0 ??/??”的格式显示。

【注意】在应用条件时是按各节顺序依次匹配的。若设置格式代码为“[红色]#;[蓝色][=5]-#.##0;[绿色]#0 ??/??;[洋红]文本@”，对于单元格数值为5的情况，由于已经匹配了第一节的[>0]，所以会按“[红色]#”的格式显示，第二节的代码实际上是不起作用的。

②两节数字格式代码

自定义的不带条件两节数字格式代码第一节隐含条件[>=0]，不满足第一节条件的使用第二节格式代码。要设置条件则应只在第一节设置。

例如对以下数据使用条件格式

 代码:[红色][>1000]#,##0;;[蓝色]文本@

显示效果

 若两节均设置了条件，两节代码条件均不符合的单元格显示会以“#”填满整个单元格。如上例中设置

代码:[红色][>1000]#,##0;[绿色][<-10]#.#;[蓝色]"文""本"@

显示效果

 

③单节数字格式代码

一节数字格式代码本应是针对所有数字数据的，不应设置条件。若设置了条件，且有文本格式代码，则所有不满足条件的数字数据单元格显示会以“#”填满整个单元格。若不含文本格式代码，则所有不满足条件的单元格均按常规格式显示。

例如对以下数据使用条件格式

 代码:[红色][>1000]#,##0;[蓝色]文本@

显示效果

 代码:[红色][>1000]#,##0

显示效果

 

（3）使用特殊格式包括数字的汉字大小写显示、部分的特殊时间格式等。

“[DBnum1]”数值的大写格式1

代码[DBnum1]G/通用格式

显示效果1235显示为“一千二百三十五”

“[DBnum2]”数值的大写格式2

代码[DBnum2]G/通用格式"圆整"

显示效果1235显示为“壹仟贰佰叁拾伍圆整”

代码[DBnum2]#.#0

显示效果123.456显示为“壹贰叁.肆陆”

18.日期和时间格式转换占位符。不分大小写，输入的大写会自动转为小写。

（1）日期

“yy”或“YY”:按两位（00至99）显示年

“yyyy”或“YYYY”或“e”按四位（1900至9999）显示年。（不能用E，会报错）

“m”或“M”:以1至12的格式显示月份。

“mm”或“MM”以01至12的格式显示月份。

“mmm”或“MMM”将月份显示为英文前三位缩写。

“mmmm”或“MMMM”将月份显示为英文全称。

“mmmmm”或“MMMMM”将月份显示为英文的首字母大写。

“d”或“D”:以1至31的格式显示日期中的日。

“dd”或“DD”以01至31的格式显示日期中的日。

“ddd”或“DDD”显示日期星期几的英文前三位缩写。

“dddd”或“DDDD”显示日期星期几的英文全称。

“aaa”或“AAA”以汉字显示日期星期几的“几”。

“aaaa”或“AAAA”以汉字显示日期“星期几”。

“bbbb”或“BBBB”:显示四位佛历年份，即以公元前543年为纪年元年，对1900年以后的日期有效。

“bb”或“BB”:显示两位佛历年份。

例如对于2022年8月15日

代码YYYY-MM-DD。显示为“2022-08-15”

代码YYYY年MM月DD日。显示为“2022年08月15日”

代码YYYY年MM月DD日 aaaa。显示为“2022年08月15日 星期一”

代码mmm-dd-yyyy ddd。显示为“Aug-15-2022 Monday”

代码[DBNum1]yyyy年m月d日。显示为“二○二二年八月十五日”

（2）时间

“h”或“H”0至23的格式显示小时

“hh”或“HH”以01至23的格式显示小时

“m”或“M”以0至59的格式显示分钟

“mm”或“MM”以01至59的格式显示分钟

“s”或“S”以0至59的格式显示秒

“ss”或“SS”以01至59的格式显示秒

“s.00”或“ss.00”在秒之后保留小数点后数据到百分秒。

“a/p”、“A/P”、“am/pm”、“AM/PM”将时间以12小时制显示，并以“a”、“A”、“am”、“AM”表示上午，“p”、“P”、“pm”、“PM”表示下午。

“上午/下午”将时间以12小时制显示，并以“上午”表示上午，“下午”表示下午。

例如，对于数据15:41:46

代码h:mm:ss。显示为“15:41:46”

代码h:mm:ss AM/PM。显示为“3:41:46 PM”

代码上午/下午h"时"mm"分"ss.00"秒"。显示为“下午3时41分45.60秒”

[h]或[hh]、[hhh]等显示时间数据中的小时，若该时间数据包含日期，则显示的是自1899年12月31日0时至数据时刻之间的小时数，若计算结果的小时数数字位数小于h的个数，则在前面补0。例如

代码:[h]。数据2022-8-16  13:56:00显示为“1074949”；数据8:30:15显示为“8”；

代码:[hhhh]。数据8:30:15显示为“0008”

[m]或[mm]、[mmm]等显示时间数据换算成分钟后的整数部分，其他显示规则同[h]等等。

代码:[mmmm]。数据8:30:15显示为“0510”，即860+30=510分钟。

[s]或[ss]或[sss]等显示时间数据换算成秒后的整数部分，其他显示规则同[h]、[m]等等。

代码:[sssss]。数据1:10:01显示为“04201”

【注意】

“m”或“mm”在紧跟小时之后，或后跟秒的情况下才显示的是时间中的分钟，单独的使用显示的是日期中的月份，但[m]中的m只表示分钟。

与“%”符号会改变原始数据格式相似，以上日期时间格式中使用的含a、b、d、e、h、m、s、y的代码都会将普通数字型的单元格数据转换类型，也就是单元格的原始数据会改动，转换规则是将原数据视为从1899年12月31日0时起向后的天数，其中的整数部分转换为形如“1900/1/1”的日期数据，如果有小数部分，将其转换为形如“0:00:00”的时间数据，并在日期数据和时间数据之间加两个空格。例如原数据123.45，设置任何一种日期时间格式后，会被转换成“1900/5/2  10:48:00”，从1899/12/31到1900/5/2共计123天，对应原数据整数部分，10:48:00=10.8小时=0.45天，对应原数据的小数部分。如果将单元格格式改回“G/通用格式”即“常规”格式，“1900/5/2  10:48:00”也会被转换为123.45。

excel只显示1900年以后的格式日期时间，负值或数字太大，将显示为“########”。若设置单元格格式为yyyy-mm-dd  h:mm:ss，再输入0.1，将会显示为“1900-01-00 2:24:00”，输入60，将会显示“1900-02-29 0:00:00”这是excel著名的日期BUG。

第二部分—— openpyxl设置Excel单元格数值格式

了解了Excel中设置单元格数值格式的方法后，在openpyxl中设置单元格数值格式就简单多了。

在openpyxl中，单元格数值格式的设置就是将cell对象的一个字符串类型的number_format属性的赋值自定义格式代码字符串。如果不需要用系统内置格式的话，连格外的导入都不用。

语法cell.number_format = "格式字符串"

比如

cell.number_format = 'General'  # 设置为“常规”格式

cell.number_format = '0.00%'  # 设置为保留两位小数的百分比格式

再如

import openpyxl

b = openpyxl.open('test.xlsx')

s = b.active

colors = ('BLACK', 'WHITE', 'RED', 'BLUE', 'GREEN', 'YELLOW', 'CYAN', 'MAGENTA')

names = ('黑色', '白色', '红色', '蓝色', '绿色', '黄色', '蓝绿色', '洋红')

for i in range(8):

    s.cell(1, i + 1).value = names[i]

    s.cell(1, i + 1).number_format = f'#;[{colors[i]}]@'

b.save('test.xlsx')

b.close()

运行结果

 

openpyxl库中也预设了一些常用的格式代码在字典BUILTIN_FORMATS中。使用前先导入numbers模块。

from openpyxl.styles import numbers

设置时引用对应的格式代码序号即可,如

cell.number_format = BUILTIN_FORMATS[22]  # 设置时间日期格式为 月/日/年  时:分。

预设格式包括了

BUILTIN_FORMATS = {
    0: 'General',
    1: '0',
    2: '0.00',
    3: '#,##0',
    4: '#,##0.00',
    5: '"$"#,##0_);("$"#,##0)',
    6: '"$"#,##0_);[Red]("$"#,##0)',
    7: '"$"#,##0.00_);("$"#,##0.00)',
    8: '"$"#,##0.00_);[Red]("$"#,##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)',

    41: r'_( #,##0_);_( (#,##0);_( "-"_);_(@_)',
    42: r'_("$" #,##0_);_("$" (#,##0);_("$" "-"_);_(@_)',
    43: r'_( #,##0.00_);_( (#,##0.00);_( "-"??_);_(@_)',

    44: r'_("$" #,##0.00_)_("$" (#,##0.00)_("$" "-"??_)_(@_)',
    45: 'mm:ss',
    46: '[h]:mm:ss',
    47: 'mmss.0',
    48: '##0.0E+0',
    49: '@', }