class ConditionalFormat(Serialisable):
tagname = "conditionalFormat"
scope = Set(values=(['selection', 'data', 'field']))
type = NoneSet(values=(['all', 'row', 'column']))
priority = Integer()
pivotAreas = NestedSequence(expected_type=PivotArea)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('pivotAreas', )
def __init__(
self,
scope=None,
type=None,
priority=None,
pivotAreas=(),
extLst=None,
):
self.scope = scope
self.type = type
self.priority = priority
self.pivotAreas = pivotAreas
self.extLst = extLst
class Complex(Serialisable):
tagname = "style"
fonts = NestedSequence(expected_type=Font, count=True)
def __init__(self, fonts=()):
self.fonts = fonts
class Consolidation(Serialisable):
tagname = "consolidation"
autoPage = Bool(allow_none=True)
pages = NestedSequence(expected_type=Page, count=True)
rangeSets = NestedSequence(expected_type=RangeSet, count=True)
__elements__ = ('pages', 'rangeSets')
def __init__(self,
autoPage=None,
pages=(),
rangeSets=(),
):
self.autoPage = autoPage
self.pages = pages
self.rangeSets = rangeSets
class PivotHierarchy(Serialisable):
tagname = "pivotHierarchy"
outline = Bool()
multipleItemSelectionAllowed = Bool()
subtotalTop = Bool()
showInFieldList = Bool()
dragToRow = Bool()
dragToCol = Bool()
dragToPage = Bool()
dragToData = Bool()
dragOff = Bool()
includeNewItemsInFilter = Bool()
caption = String(allow_none=True)
mps = NestedSequence(expected_type=MemberProperty, count=True)
members = Typed(expected_type=MemberList, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = (
'mps',
'members',
)
def __init__(
self,
outline=None,
multipleItemSelectionAllowed=None,
subtotalTop=None,
showInFieldList=None,
dragToRow=None,
dragToCol=None,
dragToPage=None,
dragToData=None,
dragOff=None,
includeNewItemsInFilter=None,
caption=None,
mps=(),
members=None,
extLst=None,
):
self.outline = outline
self.multipleItemSelectionAllowed = multipleItemSelectionAllowed
self.subtotalTop = subtotalTop
self.showInFieldList = showInFieldList
self.dragToRow = dragToRow
self.dragToCol = dragToCol
self.dragToPage = dragToPage
self.dragToData = dragToData
self.dragOff = dragOff
self.includeNewItemsInFilter = includeNewItemsInFilter
self.caption = caption
self.mps = mps
self.members = members
self.extLst = extLst
class PivotArea(Serialisable):
tagname = "pivotArea"
references = NestedSequence(expected_type=Reference, count=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
field = Integer(allow_none=True)
type = NoneSet(values=(
['normal', 'data', 'all', 'origin', 'button', 'topEnd', 'topRight']))
dataOnly = Bool(allow_none=True)
labelOnly = Bool(allow_none=True)
grandRow = Bool(allow_none=True)
grandCol = Bool(allow_none=True)
cacheIndex = Bool(allow_none=True)
outline = Bool(allow_none=True)
offset = String(allow_none=True)
collapsedLevelsAreSubtotals = Bool(allow_none=True)
axis = NoneSet(values=(['axisRow', 'axisCol', 'axisPage', 'axisValues']))
fieldPosition = Integer(allow_none=True)
__elements__ = ('references', )
def __init__(
self,
references=(),
extLst=None,
field=None,
type="normal",
dataOnly=True,
labelOnly=None,
grandRow=None,
grandCol=None,
cacheIndex=None,
outline=True,
offset=None,
collapsedLevelsAreSubtotals=None,
axis=None,
fieldPosition=None,
):
self.references = references
self.extLst = extLst
self.field = field
self.type = type
self.dataOnly = dataOnly
self.labelOnly = labelOnly
self.grandRow = grandRow
self.grandCol = grandCol
self.cacheIndex = cacheIndex
self.outline = outline
self.offset = offset
self.collapsedLevelsAreSubtotals = collapsedLevelsAreSubtotals
self.axis = axis
self.fieldPosition = fieldPosition
class MemberList(Serialisable):
tagname = "members"
level = Integer(allow_none=True)
member = NestedSequence(expected_type=String, attribute="name")
__elements__ = ('member', )
def __init__(
self,
count=None,
level=None,
member=(),
):
self.level = level
self.member = member
@property
def count(self):
return len(self.member)
class ExternalBook(Serialisable):
tagname = "externalBook"
sheetNames = Typed(expected_type=ExternalSheetNames, allow_none=True)
definedNames = NestedSequence(expected_type=ExternalDefinedName)
sheetDataSet = Typed(expected_type=ExternalSheetDataSet, allow_none=True)
id = Relation()
__elements__ = ('sheetNames', 'definedNames', 'sheetDataSet')
def __init__(
self,
sheetNames=None,
definedNames=(),
sheetDataSet=None,
id=None,
):
self.sheetNames = sheetNames
self.definedNames = definedNames
self.sheetDataSet = sheetDataSet
self.id = id
class Table(Serialisable):
_path = "/tables/table{0}.xml"
mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"
_rel_type = REL_NS + "/table"
_rel_id = None
tagname = "table"
id = Integer()
name = TableNameDescriptor(allow_none=True)
displayName = TableNameDescriptor()
comment = String(allow_none=True)
ref = CellRange()
tableType = NoneSet(values=(['worksheet', 'xml', 'queryTable']))
headerRowCount = Integer(allow_none=True)
insertRow = Bool(allow_none=True)
insertRowShift = Bool(allow_none=True)
totalsRowCount = Integer(allow_none=True)
totalsRowShown = Bool(allow_none=True)
published = Bool(allow_none=True)
headerRowDxfId = Integer(allow_none=True)
dataDxfId = Integer(allow_none=True)
totalsRowDxfId = Integer(allow_none=True)
headerRowBorderDxfId = Integer(allow_none=True)
tableBorderDxfId = Integer(allow_none=True)
totalsRowBorderDxfId = Integer(allow_none=True)
headerRowCellStyle = String(allow_none=True)
dataCellStyle = String(allow_none=True)
totalsRowCellStyle = String(allow_none=True)
connectionId = Integer(allow_none=True)
autoFilter = Typed(expected_type=AutoFilter, allow_none=True)
sortState = Typed(expected_type=SortState, allow_none=True)
tableColumns = NestedSequence(expected_type=TableColumn, count=True)
tableStyleInfo = Typed(expected_type=TableStyleInfo, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('autoFilter', 'sortState', 'tableColumns',
'tableStyleInfo')
def __init__(
self,
id=1,
displayName=None,
ref=None,
name=None,
comment=None,
tableType=None,
headerRowCount=1,
insertRow=None,
insertRowShift=None,
totalsRowCount=None,
totalsRowShown=None,
published=None,
headerRowDxfId=None,
dataDxfId=None,
totalsRowDxfId=None,
headerRowBorderDxfId=None,
tableBorderDxfId=None,
totalsRowBorderDxfId=None,
headerRowCellStyle=None,
dataCellStyle=None,
totalsRowCellStyle=None,
connectionId=None,
autoFilter=None,
sortState=None,
tableColumns=(),
tableStyleInfo=None,
extLst=None,
):
self.id = id
self.displayName = displayName
if name is None:
name = displayName
self.name = name
self.comment = comment
self.ref = ref
self.tableType = tableType
self.headerRowCount = headerRowCount
self.insertRow = insertRow
self.insertRowShift = insertRowShift
self.totalsRowCount = totalsRowCount
self.totalsRowShown = totalsRowShown
self.published = published
self.headerRowDxfId = headerRowDxfId
self.dataDxfId = dataDxfId
self.totalsRowDxfId = totalsRowDxfId
self.headerRowBorderDxfId = headerRowBorderDxfId
self.tableBorderDxfId = tableBorderDxfId
self.totalsRowBorderDxfId = totalsRowBorderDxfId
self.headerRowCellStyle = headerRowCellStyle
self.dataCellStyle = dataCellStyle
self.totalsRowCellStyle = totalsRowCellStyle
self.connectionId = connectionId
self.autoFilter = autoFilter
self.sortState = sortState
self.tableColumns = tableColumns
self.tableStyleInfo = tableStyleInfo
def to_tree(self):
tree = super(Table, self).to_tree()
tree.set("xmlns", SHEET_MAIN_NS)
return tree
@property
def path(self):
"""
Return path within the archive
"""
return "/xl" + self._path.format(self.id)
def _write(self, archive):
"""
Serialise to XML and write to archive
"""
xml = self.to_tree()
archive.writestr(self.path[1:], tostring(xml))
def _initialise_columns(self):
"""
Create a list of table columns from a cell range
Always set a ref if we have headers (the default)
Column headings must be strings and must match cells in the worksheet.
"""
min_col, min_row, max_col, max_row = range_boundaries(self.ref)
for idx in range(min_col, max_col + 1):
col = TableColumn(id=idx, name="Column{0}".format(idx))
self.tableColumns.append(col)
if self.headerRowCount:
self.autoFilter = AutoFilter(ref=self.ref)
class TableDefinition(Serialisable):
mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml"
rel_type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable"
_id = 1
_path = "/xl/pivotTables/pivotTable{0}.xml"
tagname = "pivotTableDefinition"
cache = None
name = String()
cacheId = Integer()
dataOnRows = Bool()
dataPosition = Integer(allow_none=True)
dataCaption = String()
grandTotalCaption = String(allow_none=True)
errorCaption = String(allow_none=True)
showError = Bool()
missingCaption = String(allow_none=True)
showMissing = Bool()
pageStyle = String(allow_none=True)
pivotTableStyle = String(allow_none=True)
vacatedStyle = String(allow_none=True)
tag = String(allow_none=True)
updatedVersion = Integer()
minRefreshableVersion = Integer()
asteriskTotals = Bool()
showItems = Bool()
editData = Bool()
disableFieldList = Bool()
showCalcMbrs = Bool()
visualTotals = Bool()
showMultipleLabel = Bool()
showDataDropDown = Bool()
showDrill = Bool()
printDrill = Bool()
showMemberPropertyTips = Bool()
showDataTips = Bool()
enableWizard = Bool()
enableDrill = Bool()
enableFieldProperties = Bool()
preserveFormatting = Bool()
useAutoFormatting = Bool()
pageWrap = Integer()
pageOverThenDown = Bool()
subtotalHiddenItems = Bool()
rowGrandTotals = Bool()
colGrandTotals = Bool()
fieldPrintTitles = Bool()
itemPrintTitles = Bool()
mergeItem = Bool()
showDropZones = Bool()
createdVersion = Integer()
indent = Integer()
showEmptyRow = Bool()
showEmptyCol = Bool()
showHeaders = Bool()
compact = Bool()
outline = Bool()
outlineData = Bool()
compactData = Bool()
published = Bool()
gridDropZones = Bool()
immersive = Bool()
multipleFieldFilters = Bool()
chartFormat = Integer()
rowHeaderCaption = String(allow_none=True)
colHeaderCaption = String(allow_none=True)
fieldListSortAscending = Bool()
mdxSubqueries = Bool()
customListSort = Bool(allow_none=True)
autoFormatId = Integer(allow_none=True)
applyNumberFormats = Bool()
applyBorderFormats = Bool()
applyFontFormats = Bool()
applyPatternFormats = Bool()
applyAlignmentFormats = Bool()
applyWidthHeightFormats = Bool()
location = Typed(expected_type=Location, )
pivotFields = NestedSequence(expected_type=PivotField, count=True)
rowFields = NestedSequence(expected_type=RowColField, count=True)
rowItems = NestedSequence(expected_type=RowColItem, count=True)
colFields = NestedSequence(expected_type=RowColField, count=True)
colItems = NestedSequence(expected_type=RowColItem, count=True)
pageFields = NestedSequence(expected_type=PageField, count=True)
dataFields = NestedSequence(expected_type=DataField, count=True)
formats = NestedSequence(expected_type=Format, count=True)
conditionalFormats = NestedSequence(expected_type=ConditionalFormat,
count=True)
chartFormats = NestedSequence(expected_type=ChartFormat, count=True)
pivotHierarchies = NestedSequence(expected_type=PivotHierarchy, count=True)
pivotTableStyleInfo = Typed(expected_type=PivotTableStyle, allow_none=True)
filters = NestedSequence(expected_type=PivotFilter, count=True)
rowHierarchiesUsage = Typed(expected_type=RowHierarchiesUsage,
allow_none=True)
colHierarchiesUsage = Typed(expected_type=ColHierarchiesUsage,
allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
id = Relation()
__elements__ = (
'location',
'pivotFields',
'rowFields',
'rowItems',
'colFields',
'colItems',
'pageFields',
'dataFields',
'formats',
'conditionalFormats',
'chartFormats',
'pivotHierarchies',
'pivotTableStyleInfo',
'filters',
'rowHierarchiesUsage',
'colHierarchiesUsage',
)
def __init__(
self,
name=None,
cacheId=None,
dataOnRows=False,
dataPosition=None,
dataCaption=None,
grandTotalCaption=None,
errorCaption=None,
showError=False,
missingCaption=None,
showMissing=True,
pageStyle=None,
pivotTableStyle=None,
vacatedStyle=None,
tag=None,
updatedVersion=0,
minRefreshableVersion=0,
asteriskTotals=False,
showItems=True,
editData=False,
disableFieldList=False,
showCalcMbrs=True,
visualTotals=True,
showMultipleLabel=True,
showDataDropDown=True,
showDrill=True,
printDrill=False,
showMemberPropertyTips=True,
showDataTips=True,
enableWizard=True,
enableDrill=True,
enableFieldProperties=True,
preserveFormatting=True,
useAutoFormatting=False,
pageWrap=0,
pageOverThenDown=False,
subtotalHiddenItems=False,
rowGrandTotals=True,
colGrandTotals=True,
fieldPrintTitles=False,
itemPrintTitles=False,
mergeItem=False,
showDropZones=True,
createdVersion=0,
indent=1,
showEmptyRow=False,
showEmptyCol=False,
showHeaders=True,
compact=True,
outline=False,
outlineData=False,
compactData=True,
published=False,
gridDropZones=False,
immersive=True,
multipleFieldFilters=None,
chartFormat=0,
rowHeaderCaption=None,
colHeaderCaption=None,
fieldListSortAscending=None,
mdxSubqueries=None,
customListSort=None,
autoFormatId=None,
applyNumberFormats=False,
applyBorderFormats=False,
applyFontFormats=False,
applyPatternFormats=False,
applyAlignmentFormats=False,
applyWidthHeightFormats=False,
location=None,
pivotFields=(),
rowFields=(),
rowItems=(),
colFields=(),
colItems=(),
pageFields=(),
dataFields=(),
formats=(),
conditionalFormats=(),
chartFormats=(),
pivotHierarchies=(),
pivotTableStyleInfo=None,
filters=(),
rowHierarchiesUsage=None,
colHierarchiesUsage=None,
extLst=None,
id=None,
):
self.name = name
self.cacheId = cacheId
self.dataOnRows = dataOnRows
self.dataPosition = dataPosition
self.dataCaption = dataCaption
self.grandTotalCaption = grandTotalCaption
self.errorCaption = errorCaption
self.showError = showError
self.missingCaption = missingCaption
self.showMissing = showMissing
self.pageStyle = pageStyle
self.pivotTableStyle = pivotTableStyle
self.vacatedStyle = vacatedStyle
self.tag = tag
self.updatedVersion = updatedVersion
self.minRefreshableVersion = minRefreshableVersion
self.asteriskTotals = asteriskTotals
self.showItems = showItems
self.editData = editData
self.disableFieldList = disableFieldList
self.showCalcMbrs = showCalcMbrs
self.visualTotals = visualTotals
self.showMultipleLabel = showMultipleLabel
self.showDataDropDown = showDataDropDown
self.showDrill = showDrill
self.printDrill = printDrill
self.showMemberPropertyTips = showMemberPropertyTips
self.showDataTips = showDataTips
self.enableWizard = enableWizard
self.enableDrill = enableDrill
self.enableFieldProperties = enableFieldProperties
self.preserveFormatting = preserveFormatting
self.useAutoFormatting = useAutoFormatting
self.pageWrap = pageWrap
self.pageOverThenDown = pageOverThenDown
self.subtotalHiddenItems = subtotalHiddenItems
self.rowGrandTotals = rowGrandTotals
self.colGrandTotals = colGrandTotals
self.fieldPrintTitles = fieldPrintTitles
self.itemPrintTitles = itemPrintTitles
self.mergeItem = mergeItem
self.showDropZones = showDropZones
self.createdVersion = createdVersion
self.indent = indent
self.showEmptyRow = showEmptyRow
self.showEmptyCol = showEmptyCol
self.showHeaders = showHeaders
self.compact = compact
self.outline = outline
self.outlineData = outlineData
self.compactData = compactData
self.published = published
self.gridDropZones = gridDropZones
self.immersive = immersive
self.multipleFieldFilters = multipleFieldFilters
self.chartFormat = chartFormat
self.rowHeaderCaption = rowHeaderCaption
self.colHeaderCaption = colHeaderCaption
self.fieldListSortAscending = fieldListSortAscending
self.mdxSubqueries = mdxSubqueries
self.customListSort = customListSort
self.autoFormatId = autoFormatId
self.applyNumberFormats = applyNumberFormats
self.applyBorderFormats = applyBorderFormats
self.applyFontFormats = applyFontFormats
self.applyPatternFormats = applyPatternFormats
self.applyAlignmentFormats = applyAlignmentFormats
self.applyWidthHeightFormats = applyWidthHeightFormats
self.location = location
self.pivotFields = pivotFields
self.rowFields = rowFields
self.rowItems = rowItems
self.colFields = colFields
self.colItems = colItems
self.pageFields = pageFields
self.dataFields = dataFields
self.formats = formats
self.conditionalFormats = conditionalFormats
self.chartFormats = chartFormats
self.pivotHierarchies = pivotHierarchies
self.pivotTableStyleInfo = pivotTableStyleInfo
self.filters = filters
self.rowHierarchiesUsage = rowHierarchiesUsage
self.colHierarchiesUsage = colHierarchiesUsage
self.extLst = extLst
self.id = id
def to_tree(self):
tree = super(TableDefinition, self).to_tree()
tree.set("xmlns", SHEET_MAIN_NS)
return tree
@property
def path(self):
return self._path.format(self._id)
def _write(self, archive, manifest):
"""
Add to zipfile and update manifest
"""
self._write_rels(archive, manifest)
xml = tostring(self.to_tree())
archive.writestr(self.path[1:], xml)
manifest.append(self)
def _write_rels(self, archive, manifest):
"""
Write the relevant child objects and add links
"""
if self.cache is None:
return
rels = RelationshipList()
r = Relationship(Type=self.cache.rel_type, Target=self.cache.path)
rels.append(r)
self.id = r.id
if self.cache.path[1:] not in archive.namelist():
self.cache._write(archive, manifest)
path = get_rels_path(self.path)
xml = tostring(rels.to_tree())
archive.writestr(path[1:], xml)
class PivotField(Serialisable):
tagname = "pivotField"
items = NestedSequence(expected_type=FieldItem, count=True)
autoSortScope = Typed(expected_type=AutoSortScope, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
name = String(allow_none=True)
axis = NoneSet(values=(['axisRow', 'axisCol', 'axisPage', 'axisValues']))
dataField = Bool(allow_none=True)
subtotalCaption = String(allow_none=True)
showDropDowns = Bool(allow_none=True)
hiddenLevel = Bool(allow_none=True)
uniqueMemberProperty = String(allow_none=True)
compact = Bool(allow_none=True)
allDrilled = Bool(allow_none=True)
numFmtId = Integer(allow_none=True)
outline = Bool(allow_none=True)
subtotalTop = Bool(allow_none=True)
dragToRow = Bool(allow_none=True)
dragToCol = Bool(allow_none=True)
multipleItemSelectionAllowed = Bool(allow_none=True)
dragToPage = Bool(allow_none=True)
dragToData = Bool(allow_none=True)
dragOff = Bool(allow_none=True)
showAll = Bool(allow_none=True)
insertBlankRow = Bool(allow_none=True)
serverField = Bool(allow_none=True)
insertPageBreak = Bool(allow_none=True)
autoShow = Bool(allow_none=True)
topAutoShow = Bool(allow_none=True)
hideNewItems = Bool(allow_none=True)
measureFilter = Bool(allow_none=True)
includeNewItemsInFilter = Bool(allow_none=True)
itemPageCount = Integer(allow_none=True)
sortType = Set(values=(['manual', 'ascending', 'descending']))
dataSourceSort = Bool(allow_none=True)
nonAutoSortDefault = Bool(allow_none=True)
rankBy = Integer(allow_none=True)
defaultSubtotal = Bool(allow_none=True)
sumSubtotal = Bool(allow_none=True)
countASubtotal = Bool(allow_none=True)
avgSubtotal = Bool(allow_none=True)
maxSubtotal = Bool(allow_none=True)
minSubtotal = Bool(allow_none=True)
productSubtotal = Bool(allow_none=True)
countSubtotal = Bool(allow_none=True)
stdDevSubtotal = Bool(allow_none=True)
stdDevPSubtotal = Bool(allow_none=True)
varSubtotal = Bool(allow_none=True)
varPSubtotal = Bool(allow_none=True)
showPropCell = Bool(allow_none=True)
showPropTip = Bool(allow_none=True)
showPropAsCaption = Bool(allow_none=True)
defaultAttributeDrillState = Bool(allow_none=True)
__elements__ = (
'items',
'autoSortScope',
)
def __init__(
self,
items=(),
autoSortScope=None,
name=None,
axis=None,
dataField=None,
subtotalCaption=None,
showDropDowns=True,
hiddenLevel=None,
uniqueMemberProperty=None,
compact=True,
allDrilled=None,
numFmtId=None,
outline=True,
subtotalTop=True,
dragToRow=True,
dragToCol=True,
multipleItemSelectionAllowed=None,
dragToPage=True,
dragToData=True,
dragOff=True,
showAll=True,
insertBlankRow=None,
serverField=None,
insertPageBreak=None,
autoShow=None,
topAutoShow=True,
hideNewItems=None,
measureFilter=None,
includeNewItemsInFilter=None,
itemPageCount=10,
sortType="manual",
dataSourceSort=None,
nonAutoSortDefault=None,
rankBy=None,
defaultSubtotal=True,
sumSubtotal=None,
countASubtotal=None,
avgSubtotal=None,
maxSubtotal=None,
minSubtotal=None,
productSubtotal=None,
countSubtotal=None,
stdDevSubtotal=None,
stdDevPSubtotal=None,
varSubtotal=None,
varPSubtotal=None,
showPropCell=None,
showPropTip=None,
showPropAsCaption=None,
defaultAttributeDrillState=None,
extLst=None,
):
self.items = items
self.autoSortScope = autoSortScope
self.name = name
self.axis = axis
self.dataField = dataField
self.subtotalCaption = subtotalCaption
self.showDropDowns = showDropDowns
self.hiddenLevel = hiddenLevel
self.uniqueMemberProperty = uniqueMemberProperty
self.compact = compact
self.allDrilled = allDrilled
self.numFmtId = numFmtId
self.outline = outline
self.subtotalTop = subtotalTop
self.dragToRow = dragToRow
self.dragToCol = dragToCol
self.multipleItemSelectionAllowed = multipleItemSelectionAllowed
self.dragToPage = dragToPage
self.dragToData = dragToData
self.dragOff = dragOff
self.showAll = showAll
self.insertBlankRow = insertBlankRow
self.serverField = serverField
self.insertPageBreak = insertPageBreak
self.autoShow = autoShow
self.topAutoShow = topAutoShow
self.hideNewItems = hideNewItems
self.measureFilter = measureFilter
self.includeNewItemsInFilter = includeNewItemsInFilter
self.itemPageCount = itemPageCount
self.sortType = sortType
self.dataSourceSort = dataSourceSort
self.nonAutoSortDefault = nonAutoSortDefault
self.rankBy = rankBy
self.defaultSubtotal = defaultSubtotal
self.sumSubtotal = sumSubtotal
self.countASubtotal = countASubtotal
self.avgSubtotal = avgSubtotal
self.maxSubtotal = maxSubtotal
self.minSubtotal = minSubtotal
self.productSubtotal = productSubtotal
self.countSubtotal = countSubtotal
self.stdDevSubtotal = stdDevSubtotal
self.stdDevPSubtotal = stdDevPSubtotal
self.varSubtotal = varSubtotal
self.varPSubtotal = varPSubtotal
self.showPropCell = showPropCell
self.showPropTip = showPropTip
self.showPropAsCaption = showPropAsCaption
self.defaultAttributeDrillState = defaultAttributeDrillState
class CommentSheet(Serialisable):
tagname = "comments"
authors = Typed(expected_type=AuthorList)
commentList = NestedSequence(expected_type=CommentRecord, count=0)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
_id = None
_path = "/xl/comments/comment{0}.xml"
mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"
_rel_type = "comments"
_rel_id = None
__elements__ = ('authors', 'commentList')
def __init__(self,
authors=None,
commentList=None,
extLst=None,
):
self.authors = authors
self.commentList = commentList
def to_tree(self):
tree = super(CommentSheet, self).to_tree()
tree.set("xmlns", SHEET_MAIN_NS)
return tree
@property
def comments(self):
"""
Return a dictionary of comments keyed by coord
"""
authors = self.authors.author
for c in self.commentList:
yield c.ref, Comment(c.content, authors[c.authorId])
@classmethod
def from_comments(cls, comments):
"""
Create a comment sheet from a list of comments for a particular worksheet
"""
authors = IndexedList()
# dedupe authors and get indexes
for comment in comments:
comment.authorId = authors.add(comment.author)
return cls(authors=AuthorList(authors), commentList=comments)
def write_shapes(self, vml=None):
"""
Create the VML for comments
"""
sw = ShapeWriter(self.comments)
return sw.write(vml)
@property
def path(self):
"""
Return path within the archive
"""
return self._path.format(self._id)
class Stylesheet(Serialisable):
tagname = "styleSheet"
numFmts = Typed(expected_type=NumberFormatList)
fonts = NestedSequence(expected_type=Font, count=True)
fills = NestedSequence(expected_type=Fill, count=True)
borders = NestedSequence(expected_type=Border, count=True)
cellStyleXfs = Typed(expected_type=CellStyleList)
cellXfs = Typed(expected_type=CellStyleList)
cellStyles = Typed(expected_type=_NamedCellStyleList)
dxfs = NestedSequence(expected_type=DifferentialStyle, count=True)
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',
'cellXfs', 'cellStyles', 'dxfs', 'tableStyles', 'colors')
def __init__(self,
numFmts=None,
fonts=(),
fills=(),
borders=(),
cellStyleXfs=None,
cellXfs=None,
cellStyles=None,
dxfs=(),
tableStyles=None,
colors=None,
extLst=None,
):
if numFmts is None:
numFmts = NumberFormatList()
self.numFmts = numFmts
self.number_formats = IndexedList()
self.fonts = fonts
self.fills = fills
self.borders = borders
if cellStyleXfs is None:
cellStyleXfs = CellStyleList()
self.cellStyleXfs = cellStyleXfs
if cellXfs is None:
cellXfs = CellStyleList()
self.cellXfs = cellXfs
if cellStyles is None:
cellStyles = _NamedCellStyleList()
self.cellStyles = cellStyles
self.dxfs = dxfs
self.tableStyles = tableStyles
self.colors = colors
self.cell_styles = self.cellXfs._to_array()
self.alignments = self.cellXfs.alignments
self.protections = self.cellXfs.prots
self._normalise_numbers()
self.named_styles = self._merge_named_styles()
@classmethod
def from_tree(cls, node):
# strip all attribs
attrs = dict(node.attrib)
for k in attrs:
del node.attrib[k]
return super(Stylesheet, cls).from_tree(node)
def _merge_named_styles(self):
"""
Merge named style names "cellStyles" with their associated styles
"cellStyleXfs"
"""
named_styles = self.cellStyles.names
for style in named_styles:
self._expand_named_style(style)
return named_styles
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]
if xf.numFmtId in self.custom_formats:
named_style.number_format = self.custom_formats[xf.numFmtId]
if xf.alignment:
named_style.alignment = xf.alignment
if xf.protection:
named_style.protection = xf.protection
def _split_named_styles(self, wb):
"""
Convert NamedStyle into separate CellStyle and Xf objects
"""
for style in wb._named_styles:
self.cellStyles.cellStyle.append(style.as_name())
self.cellStyleXfs.xf.append(style.as_xf())
if style.as_xf().fillId == 21:
style.name
@property
def custom_formats(self):
return dict([(n.numFmtId, n.formatCode) for n in self.numFmts.numFmt])
def _normalise_numbers(self):
"""
Rebase numFmtIds with a floor of 164
"""
custom = self.custom_formats
formats = self.number_formats
for style in self.cell_styles:
if style.numFmtId in custom:
fmt = custom[style.numFmtId]
if fmt in BUILTIN_FORMATS_REVERSE: # remove builtins
style.numFmtId = BUILTIN_FORMATS_REVERSE[fmt]
continue
style.numFmtId = formats.add(fmt) + 164
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
class WorkbookPackage(Serialisable):
"""
Represent the workbook file in the archive
"""
tagname = "workbook"
conformance = NoneSet(values=['strict', 'transitional'])
fileVersion = Typed(expected_type=FileVersion, allow_none=True)
fileSharing = Typed(expected_type=FileSharing, allow_none=True)
workbookPr = Typed(expected_type=WorkbookProperties, allow_none=True)
properties = Alias("workbookPr")
workbookProtection = Typed(expected_type=WorkbookProtection,
allow_none=True)
bookViews = NestedSequence(expected_type=BookView)
sheets = NestedSequence(expected_type=ChildSheet)
functionGroups = Typed(expected_type=FunctionGroupList, allow_none=True)
externalReferences = NestedSequence(expected_type=ExternalReference)
definedNames = Typed(expected_type=DefinedNameList, allow_none=True)
calcPr = Typed(expected_type=CalcProperties, allow_none=True)
oleSize = NestedString(allow_none=True, attribute="ref")
customWorkbookViews = NestedSequence(expected_type=CustomWorkbookView)
pivotCaches = NestedSequence(expected_type=PivotCache, allow_none=True)
smartTagPr = Typed(expected_type=SmartTagProperties, allow_none=True)
smartTagTypes = Typed(expected_type=SmartTagList, allow_none=True)
webPublishing = Typed(expected_type=WebPublishing, allow_none=True)
fileRecoveryPr = Typed(expected_type=FileRecoveryProperties,
allow_none=True)
webPublishObjects = Typed(expected_type=WebPublishObjectList,
allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
Ignorable = NestedString(
namespace="http://schemas.openxmlformats.org/markup-compatibility/2006",
allow_none=True)
__elements__ = ('fileVersion', 'fileSharing', 'workbookPr',
'workbookProtection', 'bookViews', 'sheets',
'functionGroups', 'externalReferences', 'definedNames',
'calcPr', 'oleSize', 'customWorkbookViews', 'pivotCaches',
'smartTagPr', 'smartTagTypes', 'webPublishing',
'fileRecoveryPr', 'webPublishObjects')
def __init__(
self,
conformance=None,
fileVersion=None,
fileSharing=None,
workbookPr=None,
workbookProtection=None,
bookViews=(),
sheets=(),
functionGroups=None,
externalReferences=(),
definedNames=None,
calcPr=None,
oleSize=None,
customWorkbookViews=(),
pivotCaches=(),
smartTagPr=None,
smartTagTypes=None,
webPublishing=None,
fileRecoveryPr=None,
webPublishObjects=None,
extLst=None,
Ignorable=None,
):
self.conformance = conformance
self.fileVersion = fileVersion
self.fileSharing = fileSharing
if workbookPr is None:
workbookPr = WorkbookProperties()
self.workbookPr = workbookPr
self.workbookProtection = workbookProtection
self.bookViews = bookViews
self.sheets = sheets
self.functionGroups = functionGroups
self.externalReferences = externalReferences
self.definedNames = definedNames
self.calcPr = calcPr
self.oleSize = oleSize
self.customWorkbookViews = customWorkbookViews
self.pivotCaches = pivotCaches
self.smartTagPr = smartTagPr
self.smartTagTypes = smartTagTypes
self.webPublishing = webPublishing
self.fileRecoveryPr = fileRecoveryPr
self.webPublishObjects = webPublishObjects
def to_tree(self):
tree = super(WorkbookPackage, self).to_tree()
tree.set("xmlns", SHEET_MAIN_NS)
return tree
@property
def active(self):
for view in self.bookViews:
if view.activeTab is not None:
return view.activeTab
return 0
class CacheDefinition(Serialisable):
mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml"
rel_type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition"
_id = 1
_path = "/xl/pivotCache/pivotCacheDefinition{0}.xml"
records = None
tagname = "pivotCacheDefinition"
invalid = Bool(allow_none=True)
saveData = Bool(allow_none=True)
refreshOnLoad = Bool(allow_none=True)
optimizeMemory = Bool(allow_none=True)
enableRefresh = Bool(allow_none=True)
refreshedBy = String(allow_none=True)
refreshedDate = Float(allow_none=True)
refreshedDateIso = DateTime(allow_none=True)
backgroundQuery = Bool(allow_none=True)
missingItemsLimit = Integer(allow_none=True)
createdVersion = Integer(allow_none=True)
refreshedVersion = Integer(allow_none=True)
minRefreshableVersion = Integer(allow_none=True)
recordCount = Integer(allow_none=True)
upgradeOnRefresh = Bool(allow_none=True)
tupleCache = Bool(allow_none=True)
supportSubquery = Bool(allow_none=True)
supportAdvancedDrill = Bool(allow_none=True)
cacheSource = Typed(expected_type=CacheSource)
cacheFields = NestedSequence(expected_type=CacheField, count=True)
cacheHierarchies = NestedSequence(expected_type=CacheHierarchy, allow_none=True)
kpis = NestedSequence(expected_type=PCDKPI, allow_none=True)
tupleCache = Typed(expected_type=TupleCache, allow_none=True)
calculatedItems = NestedSequence(expected_type=CalculatedItem, count=True)
calculatedMembers = NestedSequence(expected_type=CalculatedMember, count=True)
dimensions = NestedSequence(expected_type=PivotDimension, allow_none=True)
measureGroups = NestedSequence(expected_type=MeasureGroup, count=True)
maps = NestedSequence(expected_type=MeasureDimensionMap, count=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
id = Relation()
__elements__ = ('cacheSource', 'cacheFields', 'cacheHierarchies', 'kpis',
'tupleCache', 'calculatedItems', 'calculatedMembers', 'dimensions',
'measureGroups', 'maps',)
def __init__(self,
invalid=None,
saveData=None,
refreshOnLoad=None,
optimizeMemory=None,
enableRefresh=None,
refreshedBy=None,
refreshedDate=None,
refreshedDateIso=None,
backgroundQuery=None,
missingItemsLimit=None,
createdVersion=None,
refreshedVersion=None,
minRefreshableVersion=None,
recordCount=None,
upgradeOnRefresh=None,
tupleCache=None,
supportSubquery=None,
supportAdvancedDrill=None,
cacheSource=None,
cacheFields=(),
cacheHierarchies=(),
kpis=(),
calculatedItems=(),
calculatedMembers=(),
dimensions=(),
measureGroups=(),
maps=(),
extLst=None,
id = None,
):
self.invalid = invalid
self.saveData = saveData
self.refreshOnLoad = refreshOnLoad
self.optimizeMemory = optimizeMemory
self.enableRefresh = enableRefresh
self.refreshedBy = refreshedBy
self.refreshedDate = refreshedDate
self.refreshedDateIso = refreshedDateIso
self.backgroundQuery = backgroundQuery
self.missingItemsLimit = missingItemsLimit
self.createdVersion = createdVersion
self.refreshedVersion = refreshedVersion
self.minRefreshableVersion = minRefreshableVersion
self.recordCount = recordCount
self.upgradeOnRefresh = upgradeOnRefresh
self.tupleCache = tupleCache
self.supportSubquery = supportSubquery
self.supportAdvancedDrill = supportAdvancedDrill
self.cacheSource = cacheSource
self.cacheFields = cacheFields
self.cacheHierarchies = cacheHierarchies
self.kpis = kpis
self.tupleCache = tupleCache
self.calculatedItems = calculatedItems
self.calculatedMembers = calculatedMembers
self.dimensions = dimensions
self.measureGroups = measureGroups
self.maps = maps
self.id = id
def to_tree(self):
node = super(CacheDefinition, self).to_tree()
node.set("xmlns", SHEET_MAIN_NS)
return node
@property
def path(self):
return self._path.format(self._id)
def _write(self, archive, manifest):
"""
Add to zipfile and update manifest
"""
self._write_rels(archive, manifest)
xml = tostring(self.to_tree())
archive.writestr(self.path[1:], xml)
manifest.append(self)
def _write_rels(self, archive, manifest):
"""
Write the relevant child objects and add links
"""
if self.records is None:
return
rels = RelationshipList()
r = Relationship(Type=self.records.rel_type, Target=self.records.path)
rels.append(r)
self.id = r.id
self.records._id = self._id
self.records._write(archive, manifest)
path = get_rels_path(self.path)
xml = tostring(rels.to_tree())
archive.writestr(path[1:], xml)