Source code

Download source archive ↓

src/openpyxl/styles/numbers.py

1# Copyright (c) 2010-2024 openpyxl
2
3import re
4
5from openpyxl.descriptors import (
6    String,
7    Sequence,
8    Integer,
9)
10from openpyxl.descriptors.serialisable import Serialisable
11
12
13BUILTIN_FORMATS = {
14    0: 'General',
15    1: '0',
16    2: '0.00',
17    3: '#,##0',
18    4: '#,##0.00',
19    5: '"$"#,##0_);("$"#,##0)',
20    6: '"$"#,##0_);[Red]("$"#,##0)',
21    7: '"$"#,##0.00_);("$"#,##0.00)',
22    8: '"$"#,##0.00_);[Red]("$"#,##0.00)',
23    9: '0%',
24    10: '0.00%',
25    11: '0.00E+00',
26    12: '# ?/?',
27    13: '# ??/??',
28    14: 'mm-dd-yy',
29    15: 'd-mmm-yy',
30    16: 'd-mmm',
31    17: 'mmm-yy',
32    18: 'h:mm AM/PM',
33    19: 'h:mm:ss AM/PM',
34    20: 'h:mm',
35    21: 'h:mm:ss',
36    22: 'm/d/yy h:mm',
37
38    37: '#,##0_);(#,##0)',
39    38: '#,##0_);[Red](#,##0)',
40    39: '#,##0.00_);(#,##0.00)',
41    40: '#,##0.00_);[Red](#,##0.00)',
42
43    41: r'_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)',
44    42: r'_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_)',
45    43: r'_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)',
46
47    44: r'_("$"* #,##0.00_)_("$"* \(#,##0.00\)_("$"* "-"??_)_(@_)',
48    45: 'mm:ss',
49    46: '[h]:mm:ss',
50    47: 'mmss.0',
51    48: '##0.0E+0',
52    49: '@', }
53
54BUILTIN_FORMATS_MAX_SIZE = 164
55BUILTIN_FORMATS_REVERSE = dict(
56        [(value, key) for key, value in BUILTIN_FORMATS.items()])
57
58FORMAT_GENERAL = BUILTIN_FORMATS[0]
59FORMAT_TEXT = BUILTIN_FORMATS[49]
60FORMAT_NUMBER = BUILTIN_FORMATS[1]
61FORMAT_NUMBER_00 = BUILTIN_FORMATS[2]
62FORMAT_NUMBER_COMMA_SEPARATED1 = BUILTIN_FORMATS[4]
63FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-'
64FORMAT_PERCENTAGE = BUILTIN_FORMATS[9]
65FORMAT_PERCENTAGE_00 = BUILTIN_FORMATS[10]
66FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'
67FORMAT_DATE_YYMMDD = 'yy-mm-dd'
68FORMAT_DATE_DDMMYY = 'dd/mm/yy'
69FORMAT_DATE_DMYSLASH = 'd/m/y'
70FORMAT_DATE_DMYMINUS = 'd-m-y'
71FORMAT_DATE_DMMINUS = 'd-m'
72FORMAT_DATE_MYMINUS = 'm-y'
73FORMAT_DATE_XLSX14 = BUILTIN_FORMATS[14]
74FORMAT_DATE_XLSX15 = BUILTIN_FORMATS[15]
75FORMAT_DATE_XLSX16 = BUILTIN_FORMATS[16]
76FORMAT_DATE_XLSX17 = BUILTIN_FORMATS[17]
77FORMAT_DATE_XLSX22 = BUILTIN_FORMATS[22]
78FORMAT_DATE_DATETIME = 'yyyy-mm-dd h:mm:ss'
79FORMAT_DATE_TIME1 = BUILTIN_FORMATS[18]
80FORMAT_DATE_TIME2 = BUILTIN_FORMATS[19]
81FORMAT_DATE_TIME3 = BUILTIN_FORMATS[20]
82FORMAT_DATE_TIME4 = BUILTIN_FORMATS[21]
83FORMAT_DATE_TIME5 = BUILTIN_FORMATS[45]
84FORMAT_DATE_TIME6 = BUILTIN_FORMATS[21]
85FORMAT_DATE_TIME7 = 'i:s.S'
86FORMAT_DATE_TIME8 = 'h:mm:ss@'
87FORMAT_DATE_TIMEDELTA = '[hh]:mm:ss'
88FORMAT_DATE_YYMMDDSLASH = 'yy/mm/dd@'
89FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-'
90FORMAT_CURRENCY_USD = '$#,##0_-'
91FORMAT_CURRENCY_EUR_SIMPLE = '[$EUR ]#,##0.00_-'
92
93
94COLORS = r"\[(BLACK|BLUE|CYAN|GREEN|MAGENTA|RED|WHITE|YELLOW)\]"
95LITERAL_GROUP = r'".*?"' # anything in quotes
96LOCALE_GROUP = r'\[(?!hh?\]|mm?\]|ss?\])[^\]]*\]' # anything in square brackets, except hours or minutes or seconds
97STRIP_RE = re.compile(f"{LITERAL_GROUP}|{LOCALE_GROUP}")
98TIMEDELTA_RE = re.compile(r'\[hh?\](:mm(:ss(\.0*)?)?)?|\[mm?\](:ss(\.0*)?)?|\[ss?\](\.0*)?', re.I)
99
100
101# Spec 18.8.31 numFmts
102# +ve;-ve;zero;text
103
104def is_date_format(fmt):
105    if fmt is None:
106        return False
107    fmt = fmt.split(";")[0] # only look at the first format
108    fmt = STRIP_RE.sub("", fmt) # ignore some formats
109    return re.search(r"(?<![_\\])[dmhysDMHYS]", fmt) is not None
110
111
112def is_timedelta_format(fmt):
113    if fmt is None:
114        return False
115    fmt = fmt.split(";")[0] # only look at the first format
116    return TIMEDELTA_RE.search(fmt) is not None
117
118
119def is_datetime(fmt):
120    """
121    Return date, time or datetime
122    """
123    if not is_date_format(fmt):
124        return
125
126    DATE = TIME = False
127
128    if any((x in fmt for x in 'dy')):
129        DATE = True
130    if any((x in fmt for x in 'hs')):
131        TIME = True
132
133    if DATE and TIME:
134        return "datetime"
135    if DATE:
136        return "date"
137    return "time"
138
139
140def is_builtin(fmt):
141    return fmt in BUILTIN_FORMATS.values()
142
143
144def builtin_format_code(index):
145    """Return one of the standard format codes by index."""
146    try:
147        fmt = BUILTIN_FORMATS[index]
148    except KeyError:
149        fmt = None
150    return fmt
151
152
153def builtin_format_id(fmt):
154    """Return the id of a standard style."""
155    return BUILTIN_FORMATS_REVERSE.get(fmt)
156
157
158class NumberFormatDescriptor(String):
159
160    def __set__(self, instance, value):
161        if value is None:
162            value = FORMAT_GENERAL
163        super().__set__(instance, value)
164
165
166class NumberFormat(Serialisable):
167
168    numFmtId = Integer()
169    formatCode = String()
170
171    def __init__(self,
172                 numFmtId=None,
173                 formatCode=None,
174                ):
175        self.numFmtId = numFmtId
176        self.formatCode = formatCode
177
178
179class NumberFormatList(Serialisable):
180
181    count = Integer(allow_none=True)
182    numFmt = Sequence(expected_type=NumberFormat)
183
184    __elements__ = ('numFmt',)
185    __attrs__ = ("count",)
186
187    def __init__(self,
188                 count=None,
189                 numFmt=(),
190                ):
191        self.numFmt = numFmt
192
193
194    @property
195    def count(self):
196        return len(self.numFmt)
197
198
199    def __getitem__(self, idx):
200        return self.numFmt[idx]