扩展 openpyxl 对 Excel 中自定义单元格格式的处理

Posted on Views:

背景

在用 openpyxl 读取 Excel 中的数据时，发现某些单元格读取到的是数字 44712，但打开 Excel 文件却显示的是时间：2022年6月2日

查看单元格格式会发现这个单元格属于自定义格式中的: yyyy”年”m”月”d”日”。这个可以理解，但为啥 openpyxl 读到的不是时间呢？

原因分析

通过查看 openpyxl 的源码，发现其在 openpyxl.styles.numbers.py 中定义了一组格式,如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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: '@', }

其中并没有我们想要的 yyyy”年”m”月”d”日”。

然后通过查阅 Excel 的官方文档中关于 NumberingFormat Class 的解释发现 openpyxl 中定义的这些格式属于通用格式，是不区分语种的。

而我们想要找的这种格式，属于汉语中的特殊格式。对于这种特殊格式， Excel 中仅保存一个格式 ID ，但不保存具体格式的定义，格式的定义会随着所在国家发生变化。

解决方案

解决方案很简单，从文档中找到中文对应的格式 ID 和格式字符串的对应关系，然后采用 hook 的方式将其注入 openpyxl 模块中即可。

代码如下：（注意这些代码需要在导入 openpyxl 模块之前执行）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 扩展openpyxl的数字格式
# 此处扩展的是中文格式
extra_formats = {
    27: 'yyyy"年"m"月"',
    28: 'm"月"d"日"',
    29: 'm"月"d"日"',
    30: "m-d-yy",
    31: 'yyyy"年"m"月"d"日"',
    32: 'h"时"mm"分"',
    33: 'h"时"mm"分"ss"秒"',
    34: '上午/下午h"时"mm"分"',
    35: '上午/下午h"时"mm"分"ss"秒"',
    36: 'yyyy"年"m"月"',
    #
    50: 'yyyy"年"m"月"',
    51: 'm"月"d"日"',
    52: 'yyyy"年"m"月"',
    53: 'm"月"d"日"',
    54: 'm"月"d"日"',
    55: '上午/下午h"时"mm"分"',
    56: '上午/下午h"时"mm"分"ss"秒"',
    57: 'yyyy"年"m"月"',
    58: 'm"月"d"日"',
}
from openpyxl.styles.numbers import BUILTIN_FORMATS

BUILTIN_FORMATS.update(extra_formats)

参考文档：

https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat?view=openxml-2.8.1