通过日期时间和数字格式,您可以控制数据在工作表中的显示方式。 Google 表格提供了一些常用格式供您选择,但您也可以定义自己的格式。
在表格界面中,您可以使用格式 > 数字菜单将数字和日期格式应用于单元格。在 Sheets API 中,您可以使用 spreadsheets.batchUpdate
方法调用发送 UpdateCellsRequest
或 RepeatCellRequest
来设置这些格式。
本页介绍了如何定义可包含在 API 请求中的新日期和数字格式模式。为范围设置自定义日期时间或小数格式示例演示了如何使用 API 设置格式模式。请注意,格式的实际呈现方式取决于电子表格的 locale
。本指南假定 locale
为 en_US
。您可以通过使用 spreadsheets.get
请求读取 SpreadsheetProperties
来确定电子表格的 locale
。
关于日期和时间值
与大多数其他电子表格应用一样,Google 表格将日期和时间值视为小数值。这样,您就可以在公式中对它们进行算术运算,以增加天数或周数、加上或减去两个日期和时间,以及执行其他类似运算。
Google 表格使用电子表格中常用的纪元日期的形式。值的整数部分(小数点左侧)表示自 1899 年 12 月 30 日以来的天数。小数部分(小数点右侧)表示时间在一天中所占的比例。例如,1900 年 1 月 1 日中午是 2.5
,2
是 1899 年 12 月 30 日以后 2 天,0.5
是因为中午是半天。1900 年 2 月 1 日下午 3 点为 33.625
。
Google 表格正确地将 1900 年视为平年,而不是闰年。
日期和时间格式模式
日期时间格式模式是一串令牌子字符串,解析后会替换为相应的日期时间元素(例如月份或小时)。
日期和时间格式令牌
下表定义了可在日期时间格式模式中使用的令牌子字符串。+
字符表示前一个字符可以出现一次或多次,并且仍然与模式匹配。下表中未列出的字符被视为字面量,并且会按原样输出。
令牌 | 说明 |
---|---|
h |
一天中的时段。在 12 小时制和 24 小时制之间切换,具体取决于字符串中是否存在“凌晨/上午”或“下午/晚上”指示符。 |
hh+ |
与上一个相同,但对于 1-9,则带有前导 0。 |
m |
如果前面的非字面量令牌为小时或后面的为秒,则表示小时中的分钟(不带前导 0)。否则,将以数字形式表示一年中的月份(不带前导 0)。 |
M |
一年中的月份,不带前导 0。使用此令牌在您的模式中明确表明是一个月,而不是一分钟。 |
mm |
与 m 相同,但在两种情况下均以 0 开头。 |
MM |
一年中的月份,带有前导 0。使用此令牌在您的模式中明确表明是一个月,而不是一分钟。 |
mmm |
三个字母的月份缩写(例如“Feb”)。 |
mmmm |
月份的全名。mmmmmm+ 也与此匹配。 |
mmmmm |
月份的第一个字母(例如,“J”代表 6 月)。 |
s |
一分钟中的秒数,不带前导 0。 |
ss |
一分钟中的秒,以 0 开头。 |
[h+] |
一段时间内经过的小时数。字母数表示最小位数(添加前导 0)。 |
[m+] |
一段时间内经过的分钟数。字母数表示最小位数(添加前导 0)。 |
[s+] |
持续时间中经过的秒数。字母数表示最小位数(添加前导 0)。 |
d |
一个月中的某一天,小于 10 的数字不带前导 0。 |
dd |
一个月中的某一天,小于 10 的数字带有前导 0。 |
ddd |
周几,由三个字母组成的缩写(例如“Mon”)。 |
dddd+ |
周几,全名。 |
y |
以 2 位数表示的年份。 |
yy |
|
yyy |
年(4 位数)。 |
yyyy+ |
|
a/p |
AM 显示“a”,PM 显示“p”。同时将营业时间更改为 12 小时制。如果词元字母为大写,则输出也为大写。 |
am/pm |
同上,但改为显示“AM”或“PM”,并且始终采用首字母大写形式。 |
0 |
十分之一秒。您可以使用 00 将精度提高到两位数,也可以使用 000 提高三位数(毫秒)。 |
\ |
将下一个字符视为字面量值,而不是它可能具有的任何特殊含义。 |
"text" |
将引号内的任何文本显示为字面量。 |
日期和时间格式示例
下表显示了一些示例模式及其对应的日期时间呈现方式(给定日期和时间为 Tuesday, April 5, 2016, 4:08:53.528 PM
)。表的第二部分显示了经过时间的 3 hours, 13 minutes, 41.255 seconds
的经过时间格式示例:
日期时间模式 | Tuesday, April 5, 2016, 4:08:53.528 PM |
---|---|
h:mm:ss.00 a/p |
4:08:53.53 下午 |
hh:mm A/P".M." |
下午 4:08 |
yyyy-mm-dd |
2016-04-05 |
mmmm d \[dddd\] |
4 月 5 日 [星期二] |
h PM, ddd mmm dd |
4 月 5 日星期二下午 4 点 |
dddd, m/d/yy at h:mm |
2016 年 4 月 5 日星期二,16:08 |
经过时间模式 | 3 hours, 13 minutes, 41.255 seconds |
[hh]:[mm]:[ss].000 |
03:13:41.255 |
[mmmm]:[ss].000 |
0193:41.255 |
数字格式模式
数字格式模式是一串词元子字符串,解析后会替换为相应的数字表示形式。数字格式模式最多可以由四个部分组成,部分用英文分号分隔,这些部分定义了用于正数、负数、零和文本(按此顺序)的不同格式:
[POSITIVE FORMAT];[NEGATIVE FORMAT];[ZERO FORMAT];[TEXT FORMAT]
您无需在格式中包含全部四个部分。如果仅包含一个部分,则该格式将用于所有值。使用两个部分会将第一种格式应用于 0 和正数,将第二种格式应用于负数。使用三个部分可以为正数、负数和零定义不同的格式。例如:
[NUMBER FORMAT] [POSITIVE/ZERO FORMAT];[NEGATIVE FORMAT] [POSITIVE FORMAT];[NEGATIVE FORMAT];[ZERO FORMAT]
但是,如果有两个或更多部分,而最后一个部分是文本格式,则这部分将被视为文本格式,其他部分表现为少一个部分。因此,通过包含最终文本格式,可以定义如下格式:
[POSITIVE/ZERO FORMAT];[NEGATIVE FORMAT];[TEXT FORMAT] [NUMBER FORMAT];[TEXT FORMAT]
先将格式解析为多个部分,然后再进行其他解析,因此可以在一个部分中包含日期或时间格式(但效用有限)。
数字格式令牌
下表定义了令牌子字符串,您可以在格式部分中使用它们来定义如何表示该部分的值。
令牌 | 说明 |
---|---|
0 |
表示数字中的数字。如果该数位是没有意义的 0,则呈现为 0。例如,数字格式 00.0 会将数字 3 呈现为“03.0 ”。 |
# |
表示数字中的数字。如果该数位是没有意义的 0,则不会呈现。例如,数字格式 ##0 会将数字 12 呈现为“12 ”。 |
? |
表示数字中的数字。如果数位是没有意义的 0,将呈现为空格。这通常用于在使用等宽字体时对齐列中的小数点。例如,数字格式 ???.??? 会将数字 12.4 呈现为:“12.4 ”。 |
. |
第一个句点表示数字中的小数点。后面的句点会呈现为字面量。如果格式中包含小数点,系统会始终呈现小数点,即使整数也是如此。例如,#0.# 会将数字 3 呈现为“3. ”。 |
% |
显示为字面量,但也会将现有数字乘以 100 在呈现之前,使百分比更易于阅读。例如,数字格式 #% 会将数字 0.25 呈现为“25% ”。 |
, |
如果它出现在两位数字符(0 、# 或 ? )之间,则使用分组分隔符(千位分组)呈现整个数字。如果它跟在数字字符之后,则会将每个逗号按一千位数字进行缩放(例如,#0.0,, 格式会将数字 12,200,000 呈现为 12.2 )。 |
E- |
以科学格式呈现数字,E 左侧的格式用于非指数部分,E 右侧的格式用于指数部分。E+ 为正指数显示 + 符号。E- 仅针对负指数显示符号。如果使用小写,则输出 e 也采用小写。例如,数字格式 ##0.00#E+## 会将数字 0.0000001 呈现为“100.00E-9 ”。 |
E+ |
|
e- |
|
e+ |
|
/ |
如果它出现在两位数字符(0 、# 或 ? )之间,则会将这些数字组视为分数格式。例如,数字格式 0 #/# 会将数字 23.25 呈现为 23 1/4 。分母也可以是字面量整数,在这种情况下,它会将该整数强制作为分母。数字格式 0 #/8 会将数字 23.25 显示为 23 2/8 。如果分子变为 0,则分数部分不会呈现。数字格式 0 #/3 的数字 23.1 会呈现为 23 (因为 0.1 会四舍五入为 0/3)。/ 与科学格式或包含小数点的格式不兼容。 |
* |
包含此文件是为了兼容 Microsoft Excel 数字格式。目前已被忽略。 |
_ |
下划线标记会跳过下一个字符并呈现空格。当负值被括号括起来时,此参数用于对数字格式进行排列。 |
\ |
将下一个字符视为字面量值,而不是它可能具有的任何特殊含义。例如,数字格式 \#0 会将数字 10 呈现为“#10 ”。 |
"text" |
将引号内的任何文本显示为字面量。 |
@ |
如果单元格包含文本输入,则会插入单元格的原始文本。与任何其他特殊字符都不兼容,并且不会显示为数值(显示为常规格式)。 |
$ - + ( ) : space |
Google 表格会将这些字符视为字面量,并按原样显示它们。 |
元指令
此外,每个格式部分都可以包含可选的元指令(包含在 []
字符中),这些指令位于格式前面并提供其他说明。元指令有两种类型,给定部分可以同时使用这两种类型:
指令 | 说明 |
---|---|
[condition] |
将该部分的默认正数、负数和零比较关系替换为另一个条件表达式。例如,[<100]”Low”;[>1000]”High”;000 会在值低于 100 时呈现“低”字样,在值大于 1000 时呈现“高”,中间则呈现三位数(带有前导 0)。条件只能应用于前两种子格式,并且如果某个数字与多个格式匹配,则使用匹配的第一种子格式。如果存在第三种格式,则将其用于“其他”;如果一个数字与任何一种格式都不匹配,则将其呈现为以“#”填充单元格宽度。如果存在,则第四种格式始终用于文本。 |
[Color] or [Color#] |
使此子格式呈现的任何值都以给定的文本颜色显示。Color 的有效值包括 Black、Blue、Cyan、Green、Magenta、Red、White 或 Yellow。 Color# 中“#”的有效值为 0 到 56(此调色板显示了与每个数字对应的颜色列表)。数字格式颜色会替换用户在单元格中输入的任何颜色,但不会替换通过条件格式设置的颜色。 |
数字格式示例
下表显示了一些示例模式及其对应的格式化数字呈现方式:
数字 | 模式 | 统一格式的值 |
---|---|---|
12345.125 | ####.# |
12345.1 |
12.34 | 000.0000 |
012.3400 |
12 | #.0# |
12.0 |
5.125 | # ???/??? |
5 月 8 日 |
12000 | #,### |
12000 |
1230000 | 0.0,,"M" |
120 万 |
1234500000 | 0.00e+00 |
1.23e+09 |
123114.15115 MyText |
###0.000;"TEXT: "_(@_) |
123114.151 文本:MyText |
1234 -1234 0 MyText |
[Blue]#,##0;[Red]#,##0;[Green]0.0;[Magenta]_(@_) |
1234 1234 0.0 MyText |
1005 32 527 |
[>1000]"HIGH";[Color43][<=200]"LOW";0000 |
高 低 0527 |