Source code

Download source archive ↓

src/openpyxl/pivot/table.py

1# Copyright (c) 2010-2024 openpyxl
2
3
4from collections import defaultdict
5from openpyxl.descriptors.serialisable import Serialisable
6from openpyxl.descriptors import (
7    Typed,
8    Integer,
9    NoneSet,
10    Set,
11    Bool,
12    String,
13    Bool,
14    Sequence,
15)
16
17from openpyxl.descriptors.excel import ExtensionList, Relation
18from openpyxl.descriptors.sequence import NestedSequence
19from openpyxl.xml.constants import SHEET_MAIN_NS
20from openpyxl.xml.functions import tostring
21from openpyxl.packaging.relationship import (
22    RelationshipList,
23    Relationship,
24    get_rels_path
25)
26from .fields import Index
27
28from openpyxl.worksheet.filters import (
29    AutoFilter,
30)
31
32
33class HierarchyUsage(Serialisable):
34
35    tagname = "hierarchyUsage"
36
37    hierarchyUsage = Integer()
38
39    def __init__(self,
40                 hierarchyUsage=None,
41                ):
42        self.hierarchyUsage = hierarchyUsage
43
44
45class ColHierarchiesUsage(Serialisable):
46
47    tagname = "colHierarchiesUsage"
48
49    colHierarchyUsage = Sequence(expected_type=HierarchyUsage, )
50
51    __elements__ = ('colHierarchyUsage',)
52    __attrs__ = ('count', )
53
54    def __init__(self,
55                 count=None,
56                 colHierarchyUsage=(),
57                ):
58        self.colHierarchyUsage = colHierarchyUsage
59
60
61    @property
62    def count(self):
63        return len(self.colHierarchyUsage)
64
65
66class RowHierarchiesUsage(Serialisable):
67
68    tagname = "rowHierarchiesUsage"
69
70    rowHierarchyUsage = Sequence(expected_type=HierarchyUsage, )
71
72    __elements__ = ('rowHierarchyUsage',)
73    __attrs__ = ('count', )
74
75    def __init__(self,
76                 count=None,
77                 rowHierarchyUsage=(),
78                ):
79        self.rowHierarchyUsage = rowHierarchyUsage
80
81    @property
82    def count(self):
83        return len(self.rowHierarchyUsage)
84
85
86class PivotFilter(Serialisable):
87
88    tagname = "filter"
89
90    fld = Integer()
91    mpFld = Integer(allow_none=True)
92    type = Set(values=(['unknown', 'count', 'percent', 'sum', 'captionEqual',
93                        'captionNotEqual', 'captionBeginsWith', 'captionNotBeginsWith',
94                        'captionEndsWith', 'captionNotEndsWith', 'captionContains',
95                        'captionNotContains', 'captionGreaterThan', 'captionGreaterThanOrEqual',
96                        'captionLessThan', 'captionLessThanOrEqual', 'captionBetween',
97                        'captionNotBetween', 'valueEqual', 'valueNotEqual', 'valueGreaterThan',
98                        'valueGreaterThanOrEqual', 'valueLessThan', 'valueLessThanOrEqual',
99                        'valueBetween', 'valueNotBetween', 'dateEqual', 'dateNotEqual',
100                        'dateOlderThan', 'dateOlderThanOrEqual', 'dateNewerThan',
101                        'dateNewerThanOrEqual', 'dateBetween', 'dateNotBetween', 'tomorrow',
102                        'today', 'yesterday', 'nextWeek', 'thisWeek', 'lastWeek', 'nextMonth',
103                        'thisMonth', 'lastMonth', 'nextQuarter', 'thisQuarter', 'lastQuarter',
104                        'nextYear', 'thisYear', 'lastYear', 'yearToDate', 'Q1', 'Q2', 'Q3', 'Q4',
105                        'M1', 'M2', 'M3', 'M4', 'M5', 'M6', 'M7', 'M8', 'M9', 'M10', 'M11',
106                        'M12']))
107    evalOrder = Integer(allow_none=True)
108    id = Integer()
109    iMeasureHier = Integer(allow_none=True)
110    iMeasureFld = Integer(allow_none=True)
111    name = String(allow_none=True)
112    description = String(allow_none=True)
113    stringValue1 = String(allow_none=True)
114    stringValue2 = String(allow_none=True)
115    autoFilter = Typed(expected_type=AutoFilter, )
116    extLst = Typed(expected_type=ExtensionList, allow_none=True)
117
118    __elements__ = ('autoFilter',)
119
120    def __init__(self,
121                 fld=None,
122                 mpFld=None,
123                 type=None,
124                 evalOrder=None,
125                 id=None,
126                 iMeasureHier=None,
127                 iMeasureFld=None,
128                 name=None,
129                 description=None,
130                 stringValue1=None,
131                 stringValue2=None,
132                 autoFilter=None,
133                 extLst=None,
134                ):
135        self.fld = fld
136        self.mpFld = mpFld
137        self.type = type
138        self.evalOrder = evalOrder
139        self.id = id
140        self.iMeasureHier = iMeasureHier
141        self.iMeasureFld = iMeasureFld
142        self.name = name
143        self.description = description
144        self.stringValue1 = stringValue1
145        self.stringValue2 = stringValue2
146        self.autoFilter = autoFilter
147
148
149class PivotFilters(Serialisable):
150
151    count = Integer()
152    filter = Typed(expected_type=PivotFilter, allow_none=True)
153
154    __elements__ = ('filter',)
155
156    def __init__(self,
157                 count=None,
158                 filter=None,
159                ):
160        self.filter = filter
161
162
163class PivotTableStyle(Serialisable):
164
165    tagname = "pivotTableStyleInfo"
166
167    name = String(allow_none=True)
168    showRowHeaders = Bool()
169    showColHeaders = Bool()
170    showRowStripes = Bool()
171    showColStripes = Bool()
172    showLastColumn = Bool()
173
174    def __init__(self,
175                 name=None,
176                 showRowHeaders=None,
177                 showColHeaders=None,
178                 showRowStripes=None,
179                 showColStripes=None,
180                 showLastColumn=None,
181                ):
182        self.name = name
183        self.showRowHeaders = showRowHeaders
184        self.showColHeaders = showColHeaders
185        self.showRowStripes = showRowStripes
186        self.showColStripes = showColStripes
187        self.showLastColumn = showLastColumn
188
189
190class MemberList(Serialisable):
191
192    tagname = "members"
193
194    level = Integer(allow_none=True)
195    member = NestedSequence(expected_type=String, attribute="name")
196
197    __elements__ = ('member',)
198
199    def __init__(self,
200                 count=None,
201                 level=None,
202                 member=(),
203                ):
204        self.level = level
205        self.member = member
206
207    @property
208    def count(self):
209        return len(self.member)
210
211
212class MemberProperty(Serialisable):
213
214    tagname = "mps"
215
216    name = String(allow_none=True)
217    showCell = Bool(allow_none=True)
218    showTip = Bool(allow_none=True)
219    showAsCaption = Bool(allow_none=True)
220    nameLen = Integer(allow_none=True)
221    pPos = Integer(allow_none=True)
222    pLen = Integer(allow_none=True)
223    level = Integer(allow_none=True)
224    field = Integer()
225
226    def __init__(self,
227                 name=None,
228                 showCell=None,
229                 showTip=None,
230                 showAsCaption=None,
231                 nameLen=None,
232                 pPos=None,
233                 pLen=None,
234                 level=None,
235                 field=None,
236                ):
237        self.name = name
238        self.showCell = showCell
239        self.showTip = showTip
240        self.showAsCaption = showAsCaption
241        self.nameLen = nameLen
242        self.pPos = pPos
243        self.pLen = pLen
244        self.level = level
245        self.field = field
246
247
248class PivotHierarchy(Serialisable):
249
250    tagname = "pivotHierarchy"
251
252    outline = Bool()
253    multipleItemSelectionAllowed = Bool()
254    subtotalTop = Bool()
255    showInFieldList = Bool()
256    dragToRow = Bool()
257    dragToCol = Bool()
258    dragToPage = Bool()
259    dragToData = Bool()
260    dragOff = Bool()
261    includeNewItemsInFilter = Bool()
262    caption = String(allow_none=True)
263    mps = NestedSequence(expected_type=MemberProperty, count=True)
264    members = Typed(expected_type=MemberList, allow_none=True)
265    extLst = Typed(expected_type=ExtensionList, allow_none=True)
266
267    __elements__ = ('mps', 'members',)
268
269    def __init__(self,
270                 outline=None,
271                 multipleItemSelectionAllowed=None,
272                 subtotalTop=None,
273                 showInFieldList=None,
274                 dragToRow=None,
275                 dragToCol=None,
276                 dragToPage=None,
277                 dragToData=None,
278                 dragOff=None,
279                 includeNewItemsInFilter=None,
280                 caption=None,
281                 mps=(),
282                 members=None,
283                 extLst=None,
284                ):
285        self.outline = outline
286        self.multipleItemSelectionAllowed = multipleItemSelectionAllowed
287        self.subtotalTop = subtotalTop
288        self.showInFieldList = showInFieldList
289        self.dragToRow = dragToRow
290        self.dragToCol = dragToCol
291        self.dragToPage = dragToPage
292        self.dragToData = dragToData
293        self.dragOff = dragOff
294        self.includeNewItemsInFilter = includeNewItemsInFilter
295        self.caption = caption
296        self.mps = mps
297        self.members = members
298        self.extLst = extLst
299
300
301class Reference(Serialisable):
302
303    tagname = "reference"
304
305    field = Integer(allow_none=True)
306    selected = Bool(allow_none=True)
307    byPosition = Bool(allow_none=True)
308    relative = Bool(allow_none=True)
309    defaultSubtotal = Bool(allow_none=True)
310    sumSubtotal = Bool(allow_none=True)
311    countASubtotal = Bool(allow_none=True)
312    avgSubtotal = Bool(allow_none=True)
313    maxSubtotal = Bool(allow_none=True)
314    minSubtotal = Bool(allow_none=True)
315    productSubtotal = Bool(allow_none=True)
316    countSubtotal = Bool(allow_none=True)
317    stdDevSubtotal = Bool(allow_none=True)
318    stdDevPSubtotal = Bool(allow_none=True)
319    varSubtotal = Bool(allow_none=True)
320    varPSubtotal = Bool(allow_none=True)
321    x = Sequence(expected_type=Index)
322    extLst = Typed(expected_type=ExtensionList, allow_none=True)
323
324    __elements__ = ('x',)
325
326    def __init__(self,
327                 field=None,
328                 count=None,
329                 selected=None,
330                 byPosition=None,
331                 relative=None,
332                 defaultSubtotal=None,
333                 sumSubtotal=None,
334                 countASubtotal=None,
335                 avgSubtotal=None,
336                 maxSubtotal=None,
337                 minSubtotal=None,
338                 productSubtotal=None,
339                 countSubtotal=None,
340                 stdDevSubtotal=None,
341                 stdDevPSubtotal=None,
342                 varSubtotal=None,
343                 varPSubtotal=None,
344                 x=(),
345                 extLst=None,
346                ):
347        self.field = field
348        self.selected = selected
349        self.byPosition = byPosition
350        self.relative = relative
351        self.defaultSubtotal = defaultSubtotal
352        self.sumSubtotal = sumSubtotal
353        self.countASubtotal = countASubtotal
354        self.avgSubtotal = avgSubtotal
355        self.maxSubtotal = maxSubtotal
356        self.minSubtotal = minSubtotal
357        self.productSubtotal = productSubtotal
358        self.countSubtotal = countSubtotal
359        self.stdDevSubtotal = stdDevSubtotal
360        self.stdDevPSubtotal = stdDevPSubtotal
361        self.varSubtotal = varSubtotal
362        self.varPSubtotal = varPSubtotal
363        self.x = x
364
365
366    @property
367    def count(self):
368        return len(self.field)
369
370
371class PivotArea(Serialisable):
372
373    tagname = "pivotArea"
374
375    references = NestedSequence(expected_type=Reference, count=True)
376    extLst = Typed(expected_type=ExtensionList, allow_none=True)
377    field = Integer(allow_none=True)
378    type = NoneSet(values=(['normal', 'data', 'all', 'origin', 'button',
379                            'topEnd', 'topRight']))
380    dataOnly = Bool(allow_none=True)
381    labelOnly = Bool(allow_none=True)
382    grandRow = Bool(allow_none=True)
383    grandCol = Bool(allow_none=True)
384    cacheIndex = Bool(allow_none=True)
385    outline = Bool(allow_none=True)
386    offset = String(allow_none=True)
387    collapsedLevelsAreSubtotals = Bool(allow_none=True)
388    axis = NoneSet(values=(['axisRow', 'axisCol', 'axisPage', 'axisValues']))
389    fieldPosition = Integer(allow_none=True)
390
391    __elements__ = ('references',)
392
393    def __init__(self,
394                 references=(),
395                 extLst=None,
396                 field=None,
397                 type="normal",
398                 dataOnly=True,
399                 labelOnly=None,
400                 grandRow=None,
401                 grandCol=None,
402                 cacheIndex=None,
403                 outline=True,
404                 offset=None,
405                 collapsedLevelsAreSubtotals=None,
406                 axis=None,
407                 fieldPosition=None,
408                ):
409        self.references = references
410        self.extLst = extLst
411        self.field = field
412        self.type = type
413        self.dataOnly = dataOnly
414        self.labelOnly = labelOnly
415        self.grandRow = grandRow
416        self.grandCol = grandCol
417        self.cacheIndex = cacheIndex
418        self.outline = outline
419        self.offset = offset
420        self.collapsedLevelsAreSubtotals = collapsedLevelsAreSubtotals
421        self.axis = axis
422        self.fieldPosition = fieldPosition
423
424
425class ChartFormat(Serialisable):
426
427    tagname = "chartFormat"
428
429    chart = Integer()
430    format = Integer()
431    series = Bool()
432    pivotArea = Typed(expected_type=PivotArea, )
433
434    __elements__ = ('pivotArea',)
435
436    def __init__(self,
437                 chart=None,
438                 format=None,
439                 series=None,
440                 pivotArea=None,
441                ):
442        self.chart = chart
443        self.format = format
444        self.series = series
445        self.pivotArea = pivotArea
446
447
448class ConditionalFormat(Serialisable):
449
450    tagname = "conditionalFormat"
451
452    scope = Set(values=(['selection', 'data', 'field']))
453    type = NoneSet(values=(['all', 'row', 'column']))
454    priority = Integer()
455    pivotAreas = NestedSequence(expected_type=PivotArea)
456    extLst = Typed(expected_type=ExtensionList, allow_none=True)
457
458    __elements__ = ('pivotAreas',)
459
460    def __init__(self,
461                 scope="selection",
462                 type=None,
463                 priority=None,
464                 pivotAreas=(),
465                 extLst=None,
466                ):
467        self.scope = scope
468        self.type = type
469        self.priority = priority
470        self.pivotAreas = pivotAreas
471        self.extLst = extLst
472
473
474class ConditionalFormatList(Serialisable):
475
476    tagname = "conditionalFormats"
477
478    conditionalFormat = Sequence(expected_type=ConditionalFormat)
479
480    __attrs__ = ("count",)
481
482    def __init__(self, conditionalFormat=(), count=None):
483        self.conditionalFormat = conditionalFormat
484
485
486    def by_priority(self):
487        """
488        Return a dictionary of format objects keyed by (field id and format property).
489        This can be used to map the formats to field but also to dedupe to match
490        worksheet definitions which are grouped by cell range
491        """
492
493        fmts = {}
494        for fmt in self.conditionalFormat:
495            for area in fmt.pivotAreas:
496                for ref in area.references:
497                    for field in ref.x:
498                        key = (field.v, fmt.priority)
499                        fmts[key] = fmt
500
501        return fmts
502
503
504    def _dedupe(self):
505        """
506        Group formats by field index and priority.
507        Sorted to match sorting and grouping for corresponding worksheet formats
508
509        The implemtenters notes contain significant deviance from the OOXML
510        specification, in particular how conditional formats in tables relate to
511        those defined in corresponding worksheets and how to determine which
512        format applies to which fields.
513
514        There are some magical interdependencies:
515
516        * Every pivot table fmt must have a worksheet cxf with the same priority.
517
518        * In the reference part the field 4294967294 refers to a data field, the
519        spec says -2
520
521        * Data fields are referenced by the 0-index reference.x.v value
522
523        Things are made more complicated by the fact that field items behave
524        diffently if the parent is a reference or shared item: "In Office if the
525        parent is the reference element, then restrictions of this value are
526        defined by reference@field. If the parent is the tables element, then
527        this value specifies the index into the table tag position in @url."
528        Yeah, right!
529        """
530        fmts = self.by_priority()
531        # sort by priority in order, keeping the highest numerical priority, least when
532        # actually applied
533        # this is not documented but it's what Excel is happy with
534        fmts = {field:fmt for (field, priority), fmt in sorted(fmts.items(), reverse=True)}
535        #fmts = {field:fmt for (field, priority), fmt in fmts.items()}
536        if fmts:
537            self.conditionalFormat = list(fmts.values())
538
539
540    @property
541    def count(self):
542        return len(self.conditionalFormat)
543
544
545    def to_tree(self, tagname=None):
546        self._dedupe()
547        return super().to_tree(tagname)
548
549
550class Format(Serialisable):
551
552    tagname = "format"
553
554    action = NoneSet(values=(['blank', 'formatting', 'drill', 'formula']))
555    dxfId = Integer(allow_none=True)
556    pivotArea = Typed(expected_type=PivotArea, )
557    extLst = Typed(expected_type=ExtensionList, allow_none=True)
558
559    __elements__ = ('pivotArea',)
560
561    def __init__(self,
562                 action="formatting",
563                 dxfId=None,
564                 pivotArea=None,
565                 extLst=None,
566                ):
567        self.action = action
568        self.dxfId = dxfId
569        self.pivotArea = pivotArea
570        self.extLst = extLst
571
572
573class DataField(Serialisable):
574
575    tagname = "dataField"
576
577    name = String(allow_none=True)
578    fld = Integer()
579    subtotal = Set(values=(['average', 'count', 'countNums', 'max', 'min',
580                            'product', 'stdDev', 'stdDevp', 'sum', 'var', 'varp']))
581    showDataAs = Set(values=(['normal', 'difference', 'percent',
582                              'percentDiff', 'runTotal', 'percentOfRow', 'percentOfCol',
583                              'percentOfTotal', 'index']))
584    baseField = Integer()
585    baseItem = Integer()
586    numFmtId = Integer(allow_none=True)
587    extLst = Typed(expected_type=ExtensionList, allow_none=True)
588
589    __elements__ = ()
590
591
592    def __init__(self,
593                 name=None,
594                 fld=None,
595                 subtotal="sum",
596                 showDataAs="normal",
597                 baseField=-1,
598                 baseItem=1048832,
599                 numFmtId=None,
600                 extLst=None,
601                ):
602        self.name = name
603        self.fld = fld
604        self.subtotal = subtotal
605        self.showDataAs = showDataAs
606        self.baseField = baseField
607        self.baseItem = baseItem
608        self.numFmtId = numFmtId
609        self.extLst = extLst
610
611
612class PageField(Serialisable):
613
614    tagname = "pageField"
615
616    fld = Integer()
617    item = Integer(allow_none=True)
618    hier = Integer(allow_none=True)
619    name = String(allow_none=True)
620    cap = String(allow_none=True)
621    extLst = Typed(expected_type=ExtensionList, allow_none=True)
622
623    __elements__ = ()
624
625    def __init__(self,
626                 fld=None,
627                 item=None,
628                 hier=None,
629                 name=None,
630                 cap=None,
631                 extLst=None,
632                ):
633        self.fld = fld
634        self.item = item
635        self.hier = hier
636        self.name = name
637        self.cap = cap
638        self.extLst = extLst
639
640
641class RowColItem(Serialisable):
642
643    tagname = "i"
644
645    t = Set(values=(['data', 'default', 'sum', 'countA', 'avg', 'max', 'min',
646                     'product', 'count', 'stdDev', 'stdDevP', 'var', 'varP', 'grand',
647                     'blank']))
648    r = Integer()
649    i = Integer()
650    x = Sequence(expected_type=Index, attribute="v")
651
652    __elements__ = ('x',)
653
654    def __init__(self,
655                 t="data",
656                 r=0,
657                 i=0,
658                 x=(),
659                ):
660        self.t = t
661        self.r = r
662        self.i = i
663        self.x = x
664
665
666class RowColField(Serialisable):
667
668    tagname = "field"
669
670    x = Integer()
671
672    def __init__(self,
673                 x=None,
674                ):
675        self.x = x
676
677
678class AutoSortScope(Serialisable):
679
680    pivotArea = Typed(expected_type=PivotArea, )
681
682    __elements__ = ('pivotArea',)
683
684    def __init__(self,
685                 pivotArea=None,
686                ):
687        self.pivotArea = pivotArea
688
689
690class FieldItem(Serialisable):
691
692    tagname = "item"
693
694    n = String(allow_none=True)
695    t = Set(values=(['data', 'default', 'sum', 'countA', 'avg', 'max', 'min',
696                     'product', 'count', 'stdDev', 'stdDevP', 'var', 'varP', 'grand',
697                     'blank']))
698    h = Bool(allow_none=True)
699    s = Bool(allow_none=True)
700    sd = Bool(allow_none=True)
701    f = Bool(allow_none=True)
702    m = Bool(allow_none=True)
703    c = Bool(allow_none=True)
704    x = Integer(allow_none=True)
705    d = Bool(allow_none=True)
706    e = Bool(allow_none=True)
707
708    def __init__(self,
709                 n=None,
710                 t="data",
711                 h=None,
712                 s=None,
713                 sd=True,
714                 f=None,
715                 m=None,
716                 c=None,
717                 x=None,
718                 d=None,
719                 e=None,
720                ):
721        self.n = n
722        self.t = t
723        self.h = h
724        self.s = s
725        self.sd = sd
726        self.f = f
727        self.m = m
728        self.c = c
729        self.x = x
730        self.d = d
731        self.e = e
732
733
734class PivotField(Serialisable):
735
736    tagname = "pivotField"
737
738    items = NestedSequence(expected_type=FieldItem, count=True)
739    autoSortScope = Typed(expected_type=AutoSortScope, allow_none=True)
740    extLst = Typed(expected_type=ExtensionList, allow_none=True)
741    name = String(allow_none=True)
742    axis = NoneSet(values=(['axisRow', 'axisCol', 'axisPage', 'axisValues']))
743    dataField = Bool(allow_none=True)
744    subtotalCaption = String(allow_none=True)
745    showDropDowns = Bool(allow_none=True)
746    hiddenLevel = Bool(allow_none=True)
747    uniqueMemberProperty = String(allow_none=True)
748    compact = Bool(allow_none=True)
749    allDrilled = Bool(allow_none=True)
750    numFmtId = Integer(allow_none=True)
751    outline = Bool(allow_none=True)
752    subtotalTop = Bool(allow_none=True)
753    dragToRow = Bool(allow_none=True)
754    dragToCol = Bool(allow_none=True)
755    multipleItemSelectionAllowed = Bool(allow_none=True)
756    dragToPage = Bool(allow_none=True)
757    dragToData = Bool(allow_none=True)
758    dragOff = Bool(allow_none=True)
759    showAll = Bool(allow_none=True)
760    insertBlankRow = Bool(allow_none=True)
761    serverField = Bool(allow_none=True)
762    insertPageBreak = Bool(allow_none=True)
763    autoShow = Bool(allow_none=True)
764    topAutoShow = Bool(allow_none=True)
765    hideNewItems = Bool(allow_none=True)
766    measureFilter = Bool(allow_none=True)
767    includeNewItemsInFilter = Bool(allow_none=True)
768    itemPageCount = Integer(allow_none=True)
769    sortType = Set(values=(['manual', 'ascending', 'descending']))
770    dataSourceSort = Bool(allow_none=True)
771    nonAutoSortDefault = Bool(allow_none=True)
772    rankBy = Integer(allow_none=True)
773    defaultSubtotal = Bool(allow_none=True)
774    sumSubtotal = Bool(allow_none=True)
775    countASubtotal = Bool(allow_none=True)
776    avgSubtotal = Bool(allow_none=True)
777    maxSubtotal = Bool(allow_none=True)
778    minSubtotal = Bool(allow_none=True)
779    productSubtotal = Bool(allow_none=True)
780    countSubtotal = Bool(allow_none=True)
781    stdDevSubtotal = Bool(allow_none=True)
782    stdDevPSubtotal = Bool(allow_none=True)
783    varSubtotal = Bool(allow_none=True)
784    varPSubtotal = Bool(allow_none=True)
785    showPropCell = Bool(allow_none=True)
786    showPropTip = Bool(allow_none=True)
787    showPropAsCaption = Bool(allow_none=True)
788    defaultAttributeDrillState = Bool(allow_none=True)
789
790    __elements__ = ('items', 'autoSortScope',)
791
792    def __init__(self,
793                 items=(),
794                 autoSortScope=None,
795                 name=None,
796                 axis=None,
797                 dataField=None,
798                 subtotalCaption=None,
799                 showDropDowns=True,
800                 hiddenLevel=None,
801                 uniqueMemberProperty=None,
802                 compact=True,
803                 allDrilled=None,
804                 numFmtId=None,
805                 outline=True,
806                 subtotalTop=True,
807                 dragToRow=True,
808                 dragToCol=True,
809                 multipleItemSelectionAllowed=None,
810                 dragToPage=True,
811                 dragToData=True,
812                 dragOff=True,
813                 showAll=True,
814                 insertBlankRow=None,
815                 serverField=None,
816                 insertPageBreak=None,
817                 autoShow=None,
818                 topAutoShow=True,
819                 hideNewItems=None,
820                 measureFilter=None,
821                 includeNewItemsInFilter=None,
822                 itemPageCount=10,
823                 sortType="manual",
824                 dataSourceSort=None,
825                 nonAutoSortDefault=None,
826                 rankBy=None,
827                 defaultSubtotal=True,
828                 sumSubtotal=None,
829                 countASubtotal=None,
830                 avgSubtotal=None,
831                 maxSubtotal=None,
832                 minSubtotal=None,
833                 productSubtotal=None,
834                 countSubtotal=None,
835                 stdDevSubtotal=None,
836                 stdDevPSubtotal=None,
837                 varSubtotal=None,
838                 varPSubtotal=None,
839                 showPropCell=None,
840                 showPropTip=None,
841                 showPropAsCaption=None,
842                 defaultAttributeDrillState=None,
843                 extLst=None,
844                ):
845        self.items = items
846        self.autoSortScope = autoSortScope
847        self.name = name
848        self.axis = axis
849        self.dataField = dataField
850        self.subtotalCaption = subtotalCaption
851        self.showDropDowns = showDropDowns
852        self.hiddenLevel = hiddenLevel
853        self.uniqueMemberProperty = uniqueMemberProperty
854        self.compact = compact
855        self.allDrilled = allDrilled
856        self.numFmtId = numFmtId
857        self.outline = outline
858        self.subtotalTop = subtotalTop
859        self.dragToRow = dragToRow
860        self.dragToCol = dragToCol
861        self.multipleItemSelectionAllowed = multipleItemSelectionAllowed
862        self.dragToPage = dragToPage
863        self.dragToData = dragToData
864        self.dragOff = dragOff
865        self.showAll = showAll
866        self.insertBlankRow = insertBlankRow
867        self.serverField = serverField
868        self.insertPageBreak = insertPageBreak
869        self.autoShow = autoShow
870        self.topAutoShow = topAutoShow
871        self.hideNewItems = hideNewItems
872        self.measureFilter = measureFilter
873        self.includeNewItemsInFilter = includeNewItemsInFilter
874        self.itemPageCount = itemPageCount
875        self.sortType = sortType
876        self.dataSourceSort = dataSourceSort
877        self.nonAutoSortDefault = nonAutoSortDefault
878        self.rankBy = rankBy
879        self.defaultSubtotal = defaultSubtotal
880        self.sumSubtotal = sumSubtotal
881        self.countASubtotal = countASubtotal
882        self.avgSubtotal = avgSubtotal
883        self.maxSubtotal = maxSubtotal
884        self.minSubtotal = minSubtotal
885        self.productSubtotal = productSubtotal
886        self.countSubtotal = countSubtotal
887        self.stdDevSubtotal = stdDevSubtotal
888        self.stdDevPSubtotal = stdDevPSubtotal
889        self.varSubtotal = varSubtotal
890        self.varPSubtotal = varPSubtotal
891        self.showPropCell = showPropCell
892        self.showPropTip = showPropTip
893        self.showPropAsCaption = showPropAsCaption
894        self.defaultAttributeDrillState = defaultAttributeDrillState
895
896
897class Location(Serialisable):
898
899    tagname = "location"
900
901    ref = String()
902    firstHeaderRow = Integer()
903    firstDataRow = Integer()
904    firstDataCol = Integer()
905    rowPageCount = Integer(allow_none=True)
906    colPageCount = Integer(allow_none=True)
907
908    def __init__(self,
909                 ref=None,
910                 firstHeaderRow=None,
911                 firstDataRow=None,
912                 firstDataCol=None,
913                 rowPageCount=None,
914                 colPageCount=None,
915                ):
916        self.ref = ref
917        self.firstHeaderRow = firstHeaderRow
918        self.firstDataRow = firstDataRow
919        self.firstDataCol = firstDataCol
920        self.rowPageCount = rowPageCount
921        self.colPageCount = colPageCount
922
923
924class TableDefinition(Serialisable):
925
926    mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml"
927    rel_type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable"
928    _id = 1
929    _path = "/xl/pivotTables/pivotTable{0}.xml"
930
931    tagname = "pivotTableDefinition"
932    cache = None
933
934    name = String()
935    cacheId = Integer()
936    dataOnRows = Bool()
937    dataPosition = Integer(allow_none=True)
938    dataCaption = String()
939    grandTotalCaption = String(allow_none=True)
940    errorCaption = String(allow_none=True)
941    showError = Bool()
942    missingCaption = String(allow_none=True)
943    showMissing = Bool()
944    pageStyle = String(allow_none=True)
945    pivotTableStyle = String(allow_none=True)
946    vacatedStyle = String(allow_none=True)
947    tag = String(allow_none=True)
948    updatedVersion = Integer()
949    minRefreshableVersion = Integer()
950    asteriskTotals = Bool()
951    showItems = Bool()
952    editData = Bool()
953    disableFieldList = Bool()
954    showCalcMbrs = Bool()
955    visualTotals = Bool()
956    showMultipleLabel = Bool()
957    showDataDropDown = Bool()
958    showDrill = Bool()
959    printDrill = Bool()
960    showMemberPropertyTips = Bool()
961    showDataTips = Bool()
962    enableWizard = Bool()
963    enableDrill = Bool()
964    enableFieldProperties = Bool()
965    preserveFormatting = Bool()
966    useAutoFormatting = Bool()
967    pageWrap = Integer()
968    pageOverThenDown = Bool()
969    subtotalHiddenItems = Bool()
970    rowGrandTotals = Bool()
971    colGrandTotals = Bool()
972    fieldPrintTitles = Bool()
973    itemPrintTitles = Bool()
974    mergeItem = Bool()
975    showDropZones = Bool()
976    createdVersion = Integer()
977    indent = Integer()
978    showEmptyRow = Bool()
979    showEmptyCol = Bool()
980    showHeaders = Bool()
981    compact = Bool()
982    outline = Bool()
983    outlineData = Bool()
984    compactData = Bool()
985    published = Bool()
986    gridDropZones = Bool()
987    immersive = Bool()
988    multipleFieldFilters = Bool()
989    chartFormat = Integer()
990    rowHeaderCaption = String(allow_none=True)
991    colHeaderCaption = String(allow_none=True)
992    fieldListSortAscending = Bool()
993    mdxSubqueries = Bool()
994    customListSort = Bool(allow_none=True)
995    autoFormatId = Integer(allow_none=True)
996    applyNumberFormats = Bool()
997    applyBorderFormats = Bool()
998    applyFontFormats = Bool()
999    applyPatternFormats = Bool()
1000    applyAlignmentFormats = Bool()
1001    applyWidthHeightFormats = Bool()
1002    location = Typed(expected_type=Location, )
1003    pivotFields = NestedSequence(expected_type=PivotField, count=True)
1004    rowFields = NestedSequence(expected_type=RowColField, count=True)
1005    rowItems = NestedSequence(expected_type=RowColItem, count=True)
1006    colFields = NestedSequence(expected_type=RowColField, count=True)
1007    colItems = NestedSequence(expected_type=RowColItem, count=True)
1008    pageFields = NestedSequence(expected_type=PageField, count=True)
1009    dataFields = NestedSequence(expected_type=DataField, count=True)
1010    formats = NestedSequence(expected_type=Format, count=True)
1011    conditionalFormats = Typed(expected_type=ConditionalFormatList, allow_none=True)
1012    chartFormats = NestedSequence(expected_type=ChartFormat, count=True)
1013    pivotHierarchies = NestedSequence(expected_type=PivotHierarchy, count=True)
1014    pivotTableStyleInfo = Typed(expected_type=PivotTableStyle, allow_none=True)
1015    filters = NestedSequence(expected_type=PivotFilter, count=True)
1016    rowHierarchiesUsage = Typed(expected_type=RowHierarchiesUsage, allow_none=True)
1017    colHierarchiesUsage = Typed(expected_type=ColHierarchiesUsage, allow_none=True)
1018    extLst = Typed(expected_type=ExtensionList, allow_none=True)
1019    id = Relation()
1020
1021    __elements__ = ('location', 'pivotFields', 'rowFields', 'rowItems',
1022                    'colFields', 'colItems', 'pageFields', 'dataFields', 'formats',
1023                    'conditionalFormats', 'chartFormats', 'pivotHierarchies',
1024                    'pivotTableStyleInfo', 'filters', 'rowHierarchiesUsage',
1025                    'colHierarchiesUsage',)
1026
1027    def __init__(self,
1028                 name=None,
1029                 cacheId=None,
1030                 dataOnRows=False,
1031                 dataPosition=None,
1032                 dataCaption=None,
1033                 grandTotalCaption=None,
1034                 errorCaption=None,
1035                 showError=False,
1036                 missingCaption=None,
1037                 showMissing=True,
1038                 pageStyle=None,
1039                 pivotTableStyle=None,
1040                 vacatedStyle=None,
1041                 tag=None,
1042                 updatedVersion=0,
1043                 minRefreshableVersion=0,
1044                 asteriskTotals=False,
1045                 showItems=True,
1046                 editData=False,
1047                 disableFieldList=False,
1048                 showCalcMbrs=True,
1049                 visualTotals=True,
1050                 showMultipleLabel=True,
1051                 showDataDropDown=True,
1052                 showDrill=True,
1053                 printDrill=False,
1054                 showMemberPropertyTips=True,
1055                 showDataTips=True,
1056                 enableWizard=True,
1057                 enableDrill=True,
1058                 enableFieldProperties=True,
1059                 preserveFormatting=True,
1060                 useAutoFormatting=False,
1061                 pageWrap=0,
1062                 pageOverThenDown=False,
1063                 subtotalHiddenItems=False,
1064                 rowGrandTotals=True,
1065                 colGrandTotals=True,
1066                 fieldPrintTitles=False,
1067                 itemPrintTitles=False,
1068                 mergeItem=False,
1069                 showDropZones=True,
1070                 createdVersion=0,
1071                 indent=1,
1072                 showEmptyRow=False,
1073                 showEmptyCol=False,
1074                 showHeaders=True,
1075                 compact=True,
1076                 outline=False,
1077                 outlineData=False,
1078                 compactData=True,
1079                 published=False,
1080                 gridDropZones=False,
1081                 immersive=True,
1082                 multipleFieldFilters=None,
1083                 chartFormat=0,
1084                 rowHeaderCaption=None,
1085                 colHeaderCaption=None,
1086                 fieldListSortAscending=None,
1087                 mdxSubqueries=None,
1088                 customListSort=None,
1089                 autoFormatId=None,
1090                 applyNumberFormats=False,
1091                 applyBorderFormats=False,
1092                 applyFontFormats=False,
1093                 applyPatternFormats=False,
1094                 applyAlignmentFormats=False,
1095                 applyWidthHeightFormats=False,
1096                 location=None,
1097                 pivotFields=(),
1098                 rowFields=(),
1099                 rowItems=(),
1100                 colFields=(),
1101                 colItems=(),
1102                 pageFields=(),
1103                 dataFields=(),
1104                 formats=(),
1105                 conditionalFormats=None,
1106                 chartFormats=(),
1107                 pivotHierarchies=(),
1108                 pivotTableStyleInfo=None,
1109                 filters=(),
1110                 rowHierarchiesUsage=None,
1111                 colHierarchiesUsage=None,
1112                 extLst=None,
1113                 id=None,
1114                ):
1115        self.name = name
1116        self.cacheId = cacheId
1117        self.dataOnRows = dataOnRows
1118        self.dataPosition = dataPosition
1119        self.dataCaption = dataCaption
1120        self.grandTotalCaption = grandTotalCaption
1121        self.errorCaption = errorCaption
1122        self.showError = showError
1123        self.missingCaption = missingCaption
1124        self.showMissing = showMissing
1125        self.pageStyle = pageStyle
1126        self.pivotTableStyle = pivotTableStyle
1127        self.vacatedStyle = vacatedStyle
1128        self.tag = tag
1129        self.updatedVersion = updatedVersion
1130        self.minRefreshableVersion = minRefreshableVersion
1131        self.asteriskTotals = asteriskTotals
1132        self.showItems = showItems
1133        self.editData = editData
1134        self.disableFieldList = disableFieldList
1135        self.showCalcMbrs = showCalcMbrs
1136        self.visualTotals = visualTotals
1137        self.showMultipleLabel = showMultipleLabel
1138        self.showDataDropDown = showDataDropDown
1139        self.showDrill = showDrill
1140        self.printDrill = printDrill
1141        self.showMemberPropertyTips = showMemberPropertyTips
1142        self.showDataTips = showDataTips
1143        self.enableWizard = enableWizard
1144        self.enableDrill = enableDrill
1145        self.enableFieldProperties = enableFieldProperties
1146        self.preserveFormatting = preserveFormatting
1147        self.useAutoFormatting = useAutoFormatting
1148        self.pageWrap = pageWrap
1149        self.pageOverThenDown = pageOverThenDown
1150        self.subtotalHiddenItems = subtotalHiddenItems
1151        self.rowGrandTotals = rowGrandTotals
1152        self.colGrandTotals = colGrandTotals
1153        self.fieldPrintTitles = fieldPrintTitles
1154        self.itemPrintTitles = itemPrintTitles
1155        self.mergeItem = mergeItem
1156        self.showDropZones = showDropZones
1157        self.createdVersion = createdVersion
1158        self.indent = indent
1159        self.showEmptyRow = showEmptyRow
1160        self.showEmptyCol = showEmptyCol
1161        self.showHeaders = showHeaders
1162        self.compact = compact
1163        self.outline = outline
1164        self.outlineData = outlineData
1165        self.compactData = compactData
1166        self.published = published
1167        self.gridDropZones = gridDropZones
1168        self.immersive = immersive
1169        self.multipleFieldFilters = multipleFieldFilters
1170        self.chartFormat = chartFormat
1171        self.rowHeaderCaption = rowHeaderCaption
1172        self.colHeaderCaption = colHeaderCaption
1173        self.fieldListSortAscending = fieldListSortAscending
1174        self.mdxSubqueries = mdxSubqueries
1175        self.customListSort = customListSort
1176        self.autoFormatId = autoFormatId
1177        self.applyNumberFormats = applyNumberFormats
1178        self.applyBorderFormats = applyBorderFormats
1179        self.applyFontFormats = applyFontFormats
1180        self.applyPatternFormats = applyPatternFormats
1181        self.applyAlignmentFormats = applyAlignmentFormats
1182        self.applyWidthHeightFormats = applyWidthHeightFormats
1183        self.location = location
1184        self.pivotFields = pivotFields
1185        self.rowFields = rowFields
1186        self.rowItems = rowItems
1187        self.colFields = colFields
1188        self.colItems = colItems
1189        self.pageFields = pageFields
1190        self.dataFields = dataFields
1191        self.formats = formats
1192        self.conditionalFormats = conditionalFormats
1193        self.conditionalFormats = None
1194        self.chartFormats = chartFormats
1195        self.pivotHierarchies = pivotHierarchies
1196        self.pivotTableStyleInfo = pivotTableStyleInfo
1197        self.filters = filters
1198        self.rowHierarchiesUsage = rowHierarchiesUsage
1199        self.colHierarchiesUsage = colHierarchiesUsage
1200        self.extLst = extLst
1201        self.id = id
1202
1203
1204    def to_tree(self):
1205        tree = super().to_tree()
1206        tree.set("xmlns", SHEET_MAIN_NS)
1207        return tree
1208
1209
1210    @property
1211    def path(self):
1212        return self._path.format(self._id)
1213
1214
1215    def _write(self, archive, manifest):
1216        """
1217        Add to zipfile and update manifest
1218        """
1219        self._write_rels(archive, manifest)
1220        xml = tostring(self.to_tree())
1221        archive.writestr(self.path[1:], xml)
1222        manifest.append(self)
1223
1224
1225    def _write_rels(self, archive, manifest):
1226        """
1227        Write the relevant child objects and add links
1228        """
1229        if self.cache is None:
1230            return
1231
1232        rels = RelationshipList()
1233        r = Relationship(Type=self.cache.rel_type, Target=self.cache.path)
1234        rels.append(r)
1235        self.id = r.id
1236        if self.cache.path[1:] not in archive.namelist():
1237            self.cache._write(archive, manifest)
1238
1239        path = get_rels_path(self.path)
1240        xml = tostring(rels.to_tree())
1241        archive.writestr(path[1:], xml)
1242
1243
1244    def formatted_fields(self):
1245        """Map fields to associated conditional formats by priority"""
1246        if not self.conditionalFormats:
1247            return {}
1248        fields = defaultdict(list)
1249        for idx, prio in self.conditionalFormats.by_priority():
1250            name = self.dataFields[idx].name
1251            fields[name].append(prio)
1252        return fields
1253
1254
1255    @property
1256    def summary(self):
1257        """
1258        Provide a simplified summary of the table
1259        """
1260
1261        return f"{self.name} {dict(self.location)}"