stylesheet.py 8.16 KB
Newer Older
CharlieC's avatar
CharlieC committed
1
# Copyright (c) 2010-2021 openpyxl
panotwo's avatar
panotwo committed
2

3
4
from warnings import warn

5
from openpyxl.descriptors.serialisable import Serialisable
6
7
8
from openpyxl.descriptors import (
    Typed,
)
CharlieC's avatar
CharlieC committed
9
from openpyxl.descriptors.sequence import NestedSequence
10
from openpyxl.descriptors.excel import ExtensionList
11
from openpyxl.utils.indexed_list import IndexedList
CharlieC's avatar
CharlieC committed
12
from openpyxl.xml.constants import ARC_STYLE, SHEET_MAIN_NS
CharlieC's avatar
CharlieC committed
13
from openpyxl.xml.functions import fromstring
14

15
from .builtins import styles
16
from .colors import ColorList, COLOR_INDEX
CharlieC's avatar
CharlieC committed
17
from .differential import DifferentialStyle
18
from .table import TableStyleList
CharlieC's avatar
CharlieC committed
19
20
21
from .borders import Border
from .fills import Fill
from .fonts import Font
22
23
from .numbers import (
    NumberFormatList,
24
    BUILTIN_FORMATS,
25
    BUILTIN_FORMATS_MAX_SIZE,
26
27
    BUILTIN_FORMATS_REVERSE,
    is_date_format,
28
    is_timedelta_format,
29
    builtin_format_code
30
31
)
from .named_styles import (
32
    _NamedCellStyleList
33
)
CharlieC's avatar
CharlieC committed
34
from .cell_style import CellStyle, CellStyleList
35
36


37
38
class Stylesheet(Serialisable):

CharlieC's avatar
CharlieC committed
39
    tagname = "styleSheet"
40

CharlieC's avatar
CharlieC committed
41
    numFmts = Typed(expected_type=NumberFormatList)
42
43
44
    fonts = NestedSequence(expected_type=Font, count=True)
    fills = NestedSequence(expected_type=Fill, count=True)
    borders = NestedSequence(expected_type=Border, count=True)
CharlieC's avatar
CharlieC committed
45
46
    cellStyleXfs = Typed(expected_type=CellStyleList)
    cellXfs = Typed(expected_type=CellStyleList)
47
    cellStyles = Typed(expected_type=_NamedCellStyleList)
48
    dxfs = NestedSequence(expected_type=DifferentialStyle, count=True)
49
50
51
52
53
    tableStyles = Typed(expected_type=TableStyleList, allow_none=True)
    colors = Typed(expected_type=ColorList, allow_none=True)
    extLst = Typed(expected_type=ExtensionList, allow_none=True)

    __elements__ = ('numFmts', 'fonts', 'fills', 'borders', 'cellStyleXfs',
54
                    'cellXfs', 'cellStyles', 'dxfs', 'tableStyles', 'colors')
55
56
57

    def __init__(self,
                 numFmts=None,
CharlieC's avatar
CharlieC committed
58
59
60
                 fonts=(),
                 fills=(),
                 borders=(),
61
62
63
                 cellStyleXfs=None,
                 cellXfs=None,
                 cellStyles=None,
CharlieC's avatar
CharlieC committed
64
                 dxfs=(),
65
66
67
68
                 tableStyles=None,
                 colors=None,
                 extLst=None,
                ):
69
70
        if numFmts is None:
            numFmts = NumberFormatList()
71
        self.numFmts = numFmts
72
        self.number_formats = IndexedList()
73
74
75
        self.fonts = fonts
        self.fills = fills
        self.borders = borders
CharlieC's avatar
CharlieC committed
76
77
        if cellStyleXfs is None:
            cellStyleXfs = CellStyleList()
78
        self.cellStyleXfs = cellStyleXfs
79
80
        if cellXfs is None:
            cellXfs = CellStyleList()
81
        self.cellXfs = cellXfs
82
        if cellStyles is None:
83
            cellStyles = _NamedCellStyleList()
84
        self.cellStyles = cellStyles
CharlieC's avatar
CharlieC committed
85

86
87
88
        self.dxfs = dxfs
        self.tableStyles = tableStyles
        self.colors = colors
89

90
91
92
        self.cell_styles = self.cellXfs._to_array()
        self.alignments = self.cellXfs.alignments
        self.protections = self.cellXfs.prots
CharlieC's avatar
CharlieC committed
93
        self._normalise_numbers()
94
        self.named_styles = self._merge_named_styles()
95

96
97
98
99

    @classmethod
    def from_tree(cls, node):
        # strip all attribs
100
101
        attrs = dict(node.attrib)
        for k in attrs:
102
            del node.attrib[k]
103
        return super(Stylesheet, cls).from_tree(node)
104
105
106
107


    def _merge_named_styles(self):
        """
108
109
        Merge named style names "cellStyles" with their associated styles
        "cellStyleXfs"
110
        """
111
        named_styles = self.cellStyles.names
112

113
        for style in named_styles:
114
115
            self._expand_named_style(style)

116
117
118
        return named_styles


119
120
121
122
123
124
125
126
127
    def _expand_named_style(self, named_style):
        """
        Bind format definitions for a named style from the associated style
        record
        """
        xf = self.cellStyleXfs[named_style.xfId]
        named_style.font = self.fonts[xf.fontId]
        named_style.fill = self.fills[xf.fillId]
        named_style.border = self.borders[xf.borderId]
128
129
130
131
132
133
        if xf.numFmtId < BUILTIN_FORMATS_MAX_SIZE:
            formats = BUILTIN_FORMATS
        else:
            formats = self.custom_formats
        if xf.numFmtId in formats:
            named_style.number_format = formats[xf.numFmtId]
134
135
136
137
138
139
        if xf.alignment:
            named_style.alignment = xf.alignment
        if xf.protection:
            named_style.protection = xf.protection


140
141
142
143
    def _split_named_styles(self, wb):
        """
        Convert NamedStyle into separate CellStyle and Xf objects
        """
144
145
146
        for style in wb._named_styles:
            self.cellStyles.cellStyle.append(style.as_name())
            self.cellStyleXfs.xf.append(style.as_xf())
147
148


CharlieC's avatar
CharlieC committed
149
150
151
152
153
154
155
    @property
    def custom_formats(self):
        return dict([(n.numFmtId, n.formatCode) for n in self.numFmts.numFmt])


    def _normalise_numbers(self):
        """
156
157
        Rebase custom numFmtIds with a floor of 164 when reading stylesheet
        And index datetime formats
CharlieC's avatar
CharlieC committed
158
        """
159
        date_formats = set()
160
        timedelta_formats = set()
CharlieC's avatar
CharlieC committed
161
162
        custom = self.custom_formats
        formats = self.number_formats
163
        for idx, style in enumerate(self.cell_styles):
CharlieC's avatar
CharlieC committed
164
165
            if style.numFmtId in custom:
                fmt = custom[style.numFmtId]
166
167
                if fmt in BUILTIN_FORMATS_REVERSE: # remove builtins
                    style.numFmtId = BUILTIN_FORMATS_REVERSE[fmt]
168
                else:
169
                    style.numFmtId = formats.add(fmt) + BUILTIN_FORMATS_MAX_SIZE
170
            else:
171
                fmt = builtin_format_code(style.numFmtId)
172
173
174
            if is_date_format(fmt):
                # Create an index of which styles refer to datetimes
                date_formats.add(idx)
175
176
177
            if is_timedelta_format(fmt):
                # Create an index of which styles refer to timedeltas
                timedelta_formats.add(idx)
178
        self.date_formats = date_formats
179
        self.timedelta_formats = timedelta_formats
CharlieC's avatar
CharlieC committed
180
181


182
183
184
185
186
187
    def to_tree(self, tagname=None, idx=None, namespace=None):
        tree = super(Stylesheet, self).to_tree(tagname, idx, namespace)
        tree.set("xmlns", SHEET_MAIN_NS)
        return tree


188
189
190
191
def apply_stylesheet(archive, wb):
    """
    Add styles to workbook if present
    """
192
193
194
    try:
        src = archive.read(ARC_STYLE)
    except KeyError:
195
        return wb
196

197
    node = fromstring(src)
198
199
    stylesheet = Stylesheet.from_tree(node)

CharlieC's avatar
CharlieC committed
200
201
202
    wb._borders = IndexedList(stylesheet.borders)
    wb._fonts = IndexedList(stylesheet.fonts)
    wb._fills = IndexedList(stylesheet.fills)
203
    wb._differential_styles.styles = stylesheet.dxfs
204
205
206
    wb._number_formats = stylesheet.number_formats
    wb._protections = stylesheet.protections
    wb._alignments = stylesheet.alignments
CharlieC's avatar
CharlieC committed
207
    wb._table_styles = stylesheet.tableStyles
208
209
210
211

    # need to overwrite openpyxl defaults in case workbook has different ones
    wb._cell_styles = stylesheet.cell_styles
    wb._named_styles = stylesheet.named_styles
212
    wb._date_formats = stylesheet.date_formats
213
    wb._timedelta_formats = stylesheet.timedelta_formats
214
215
216
217

    for ns in wb._named_styles:
        ns.bind(wb)

218
219
220
221
222
    if not wb._named_styles:
        normal = styles['Normal']
        wb.add_named_style(normal)
        warn("Workbook contains no default style, apply openpyxl's default")

223
224
    if stylesheet.colors is not None:
        wb._colors = stylesheet.colors.index
225
226
227
228


def write_stylesheet(wb):
    stylesheet = Stylesheet()
CharlieC's avatar
CharlieC committed
229
230
231
    stylesheet.fonts = wb._fonts
    stylesheet.fills = wb._fills
    stylesheet.borders = wb._borders
232
    stylesheet.dxfs = wb._differential_styles.styles
233
    stylesheet.colors = ColorList(indexedColors=wb._colors)
234

CharlieC's avatar
CharlieC committed
235
236
    from .numbers import NumberFormat
    fmts = []
237
    for idx, code in enumerate(wb._number_formats, BUILTIN_FORMATS_MAX_SIZE):
CharlieC's avatar
CharlieC committed
238
239
240
241
242
        fmt = NumberFormat(idx, code)
        fmts.append(fmt)

    stylesheet.numFmts.numFmt = fmts

243
    xfs = []
244
    for style in wb._cell_styles:
245
        xf = CellStyle.from_array(style)
246

CharlieC's avatar
CharlieC committed
247
        if style.alignmentId:
248
249
            xf.alignment = wb._alignments[style.alignmentId]

CharlieC's avatar
CharlieC committed
250
        if style.protectionId:
CharlieC's avatar
CharlieC committed
251
            xf.protection = wb._protections[style.protectionId]
252
253
254
255
        xfs.append(xf)
    stylesheet.cellXfs = CellStyleList(xf=xfs)

    stylesheet._split_named_styles(wb)
CharlieC's avatar
CharlieC committed
256
    stylesheet.tableStyles = wb._table_styles
257

258
    return stylesheet.to_tree()