Source code

Download source archive ↓

src/openpyxl/worksheet/table.py

1# Copyright (c) 2010-2024 openpyxl
2
3from openpyxl.descriptors.serialisable import Serialisable
4from openpyxl.descriptors import (
5    Descriptor,
6    Alias,
7    Typed,
8    Bool,
9    Integer,
10    NoneSet,
11    String,
12    Sequence,
13)
14from openpyxl.descriptors.excel import ExtensionList, CellRange
15from openpyxl.descriptors.sequence import NestedSequence
16from openpyxl.xml.constants import SHEET_MAIN_NS, REL_NS
17from openpyxl.xml.functions import tostring
18from openpyxl.utils import range_boundaries
19from openpyxl.utils.escape import escape, unescape
20
21from .related import Related
22
23from .filters import (
24    AutoFilter,
25    SortState,
26)
27
28TABLESTYLES = tuple(
29    ["TableStyleMedium{0}".format(i) for i in range(1, 29)]
30    + ["TableStyleLight{0}".format(i) for i in range(1, 22)]
31    + ["TableStyleDark{0}".format(i) for i in range(1, 12)]
32)
33
34PIVOTSTYLES = tuple(
35    ["PivotStyleMedium{0}".format(i) for i in range(1, 29)]
36    + ["PivotStyleLight{0}".format(i) for i in range(1, 29)]
37    + ["PivotStyleDark{0}".format(i) for i in range(1, 29)]
38)
39
40
41class TableStyleInfo(Serialisable):
42
43    tagname = "tableStyleInfo"
44
45    name = String(allow_none=True)
46    showFirstColumn = Bool(allow_none=True)
47    showLastColumn = Bool(allow_none=True)
48    showRowStripes = Bool(allow_none=True)
49    showColumnStripes = Bool(allow_none=True)
50
51    def __init__(self,
52                 name=None,
53                 showFirstColumn=None,
54                 showLastColumn=None,
55                 showRowStripes=None,
56                 showColumnStripes=None,
57                ):
58        self.name = name
59        self.showFirstColumn = showFirstColumn
60        self.showLastColumn = showLastColumn
61        self.showRowStripes = showRowStripes
62        self.showColumnStripes = showColumnStripes
63
64
65class XMLColumnProps(Serialisable):
66
67    tagname = "xmlColumnPr"
68
69    mapId = Integer()
70    xpath = String()
71    denormalized = Bool(allow_none=True)
72    xmlDataType = String()
73    extLst = Typed(expected_type=ExtensionList, allow_none=True)
74
75    __elements__ = ()
76
77    def __init__(self,
78                 mapId=None,
79                 xpath=None,
80                 denormalized=None,
81                 xmlDataType=None,
82                 extLst=None,
83                ):
84        self.mapId = mapId
85        self.xpath = xpath
86        self.denormalized = denormalized
87        self.xmlDataType = xmlDataType
88
89
90class TableFormula(Serialisable):
91
92    tagname = "tableFormula"
93
94    ## Note formula is stored as the text value
95
96    array = Bool(allow_none=True)
97    attr_text = Descriptor()
98    text = Alias('attr_text')
99
100
101    def __init__(self,
102                 array=None,
103                 attr_text=None,
104                ):
105        self.array = array
106        self.attr_text = attr_text
107
108
109class TableColumn(Serialisable):
110
111    tagname = "tableColumn"
112
113    id = Integer()
114    uniqueName = String(allow_none=True)
115    name = String()
116    totalsRowFunction = NoneSet(values=(['sum', 'min', 'max', 'average',
117                                         'count', 'countNums', 'stdDev', 'var', 'custom']))
118    totalsRowLabel = String(allow_none=True)
119    queryTableFieldId = Integer(allow_none=True)
120    headerRowDxfId = Integer(allow_none=True)
121    dataDxfId = Integer(allow_none=True)
122    totalsRowDxfId = Integer(allow_none=True)
123    headerRowCellStyle = String(allow_none=True)
124    dataCellStyle = String(allow_none=True)
125    totalsRowCellStyle = String(allow_none=True)
126    calculatedColumnFormula = Typed(expected_type=TableFormula, allow_none=True)
127    totalsRowFormula = Typed(expected_type=TableFormula, allow_none=True)
128    xmlColumnPr = Typed(expected_type=XMLColumnProps, allow_none=True)
129    extLst = Typed(expected_type=ExtensionList, allow_none=True)
130
131    __elements__ = ('calculatedColumnFormula', 'totalsRowFormula',
132                    'xmlColumnPr', 'extLst')
133
134    def __init__(self,
135                 id=None,
136                 uniqueName=None,
137                 name=None,
138                 totalsRowFunction=None,
139                 totalsRowLabel=None,
140                 queryTableFieldId=None,
141                 headerRowDxfId=None,
142                 dataDxfId=None,
143                 totalsRowDxfId=None,
144                 headerRowCellStyle=None,
145                 dataCellStyle=None,
146                 totalsRowCellStyle=None,
147                 calculatedColumnFormula=None,
148                 totalsRowFormula=None,
149                 xmlColumnPr=None,
150                 extLst=None,
151                ):
152        self.id = id
153        self.uniqueName = uniqueName
154        self.name = name
155        self.totalsRowFunction = totalsRowFunction
156        self.totalsRowLabel = totalsRowLabel
157        self.queryTableFieldId = queryTableFieldId
158        self.headerRowDxfId = headerRowDxfId
159        self.dataDxfId = dataDxfId
160        self.totalsRowDxfId = totalsRowDxfId
161        self.headerRowCellStyle = headerRowCellStyle
162        self.dataCellStyle = dataCellStyle
163        self.totalsRowCellStyle = totalsRowCellStyle
164        self.calculatedColumnFormula = calculatedColumnFormula
165        self.totalsRowFormula = totalsRowFormula
166        self.xmlColumnPr = xmlColumnPr
167        self.extLst = extLst
168
169
170    def __iter__(self):
171        for k, v in super().__iter__():
172            if k == 'name':
173                v = escape(v)
174            yield k, v
175
176
177    @classmethod
178    def from_tree(cls, node):
179        self = super().from_tree(node)
180        self.name = unescape(self.name)
181        return self
182
183
184class TableNameDescriptor(String):
185
186    """
187    Table names cannot have spaces in them
188    """
189
190    def __set__(self, instance, value):
191        if value is not None and " " in value:
192            raise ValueError("Table names cannot have spaces")
193        super().__set__(instance, value)
194
195
196class Table(Serialisable):
197
198    _path = "/tables/table{0}.xml"
199    mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"
200    _rel_type = REL_NS + "/table"
201    _rel_id = None
202
203    tagname = "table"
204
205    id = Integer()
206    name = String(allow_none=True)
207    displayName = TableNameDescriptor()
208    comment = String(allow_none=True)
209    ref = CellRange()
210    tableType = NoneSet(values=(['worksheet', 'xml', 'queryTable']))
211    headerRowCount = Integer(allow_none=True)
212    insertRow = Bool(allow_none=True)
213    insertRowShift = Bool(allow_none=True)
214    totalsRowCount = Integer(allow_none=True)
215    totalsRowShown = Bool(allow_none=True)
216    published = Bool(allow_none=True)
217    headerRowDxfId = Integer(allow_none=True)
218    dataDxfId = Integer(allow_none=True)
219    totalsRowDxfId = Integer(allow_none=True)
220    headerRowBorderDxfId = Integer(allow_none=True)
221    tableBorderDxfId = Integer(allow_none=True)
222    totalsRowBorderDxfId = Integer(allow_none=True)
223    headerRowCellStyle = String(allow_none=True)
224    dataCellStyle = String(allow_none=True)
225    totalsRowCellStyle = String(allow_none=True)
226    connectionId = Integer(allow_none=True)
227    autoFilter = Typed(expected_type=AutoFilter, allow_none=True)
228    sortState = Typed(expected_type=SortState, allow_none=True)
229    tableColumns = NestedSequence(expected_type=TableColumn, count=True)
230    tableStyleInfo = Typed(expected_type=TableStyleInfo, allow_none=True)
231    extLst = Typed(expected_type=ExtensionList, allow_none=True)
232
233    __elements__ = ('autoFilter', 'sortState', 'tableColumns',
234                    'tableStyleInfo')
235
236    def __init__(self,
237                 id=1,
238                 displayName=None,
239                 ref=None,
240                 name=None,
241                 comment=None,
242                 tableType=None,
243                 headerRowCount=1,
244                 insertRow=None,
245                 insertRowShift=None,
246                 totalsRowCount=None,
247                 totalsRowShown=None,
248                 published=None,
249                 headerRowDxfId=None,
250                 dataDxfId=None,
251                 totalsRowDxfId=None,
252                 headerRowBorderDxfId=None,
253                 tableBorderDxfId=None,
254                 totalsRowBorderDxfId=None,
255                 headerRowCellStyle=None,
256                 dataCellStyle=None,
257                 totalsRowCellStyle=None,
258                 connectionId=None,
259                 autoFilter=None,
260                 sortState=None,
261                 tableColumns=(),
262                 tableStyleInfo=None,
263                 extLst=None,
264                ):
265        self.id = id
266        self.displayName = displayName
267        if name is None:
268            name = displayName
269        self.name = name
270        self.comment = comment
271        self.ref = ref
272        self.tableType = tableType
273        self.headerRowCount = headerRowCount
274        self.insertRow = insertRow
275        self.insertRowShift = insertRowShift
276        self.totalsRowCount = totalsRowCount
277        self.totalsRowShown = totalsRowShown
278        self.published = published
279        self.headerRowDxfId = headerRowDxfId
280        self.dataDxfId = dataDxfId
281        self.totalsRowDxfId = totalsRowDxfId
282        self.headerRowBorderDxfId = headerRowBorderDxfId
283        self.tableBorderDxfId = tableBorderDxfId
284        self.totalsRowBorderDxfId = totalsRowBorderDxfId
285        self.headerRowCellStyle = headerRowCellStyle
286        self.dataCellStyle = dataCellStyle
287        self.totalsRowCellStyle = totalsRowCellStyle
288        self.connectionId = connectionId
289        self.autoFilter = autoFilter
290        self.sortState = sortState
291        self.tableColumns = tableColumns
292        self.tableStyleInfo = tableStyleInfo
293
294
295    def to_tree(self):
296        tree = super().to_tree()
297        tree.set("xmlns", SHEET_MAIN_NS)
298        return tree
299
300
301    @property
302    def path(self):
303        """
304        Return path within the archive
305        """
306        return "/xl" + self._path.format(self.id)
307
308
309    def _write(self, archive):
310        """
311        Serialise to XML and write to archive
312        """
313        xml = self.to_tree()
314        archive.writestr(self.path[1:], tostring(xml))
315
316
317    def _initialise_columns(self):
318        """
319        Create a list of table columns from a cell range
320        Always set a ref if we have headers (the default)
321        Column headings must be strings and must match cells in the worksheet.
322        """
323
324        min_col, min_row, max_col, max_row = range_boundaries(self.ref)
325        for idx in range(min_col, max_col+1):
326            col = TableColumn(id=idx, name="Column{0}".format(idx))
327            self.tableColumns.append(col)
328        if self.headerRowCount and not self.autoFilter:
329            self.autoFilter = AutoFilter(ref=self.ref)
330
331
332    @property
333    def column_names(self):
334        return [column.name for column in self.tableColumns]
335
336
337class TablePartList(Serialisable):
338
339    tagname = "tableParts"
340
341    count = Integer(allow_none=True)
342    tablePart = Sequence(expected_type=Related)
343
344    __elements__ = ('tablePart',)
345    __attrs__ = ('count',)
346
347    def __init__(self,
348                 count=None,
349                 tablePart=(),
350                ):
351        self.tablePart = tablePart
352
353
354    def append(self, part):
355        self.tablePart.append(part)
356
357
358    @property
359    def count(self):
360        return len(self.tablePart)
361
362
363    def __bool__(self):
364        return bool(self.tablePart)
365
366
367class TableList(dict):
368
369
370    def add(self, table):
371        if not isinstance(table, Table):
372            raise TypeError("You can only add tables")
373        self[table.name] = table
374
375
376    def get(self, name=None, table_range=None):
377        if name is not None:
378            return super().get(name)
379        for table in self.values():
380            if table_range == table.ref:
381                return table
382
383
384    def items(self):
385        return [(name, table.ref) for name, table in super().items()]