class HeaderFooter(Serialisable):
tagname = "headerFooter"
differentOddEven = Bool(allow_none=True)
differentFirst = Bool(allow_none=True)
scaleWithDoc = Bool(allow_none=True)
alignWithMargins = Bool(allow_none=True)
oddHeader = Typed(expected_type=HeaderFooterItem, allow_none=True)
oddFooter = Typed(expected_type=HeaderFooterItem, allow_none=True)
evenHeader = Typed(expected_type=HeaderFooterItem, allow_none=True)
evenFooter = Typed(expected_type=HeaderFooterItem, allow_none=True)
firstHeader = Typed(expected_type=HeaderFooterItem, allow_none=True)
firstFooter = Typed(expected_type=HeaderFooterItem, allow_none=True)
__elements__ = ("oddHeader", "oddFooter", "evenHeader", "evenFooter",
"firstHeader", "firstFooter")
def __init__(
self,
differentOddEven=None,
differentFirst=None,
scaleWithDoc=None,
alignWithMargins=None,
oddHeader=None,
oddFooter=None,
evenHeader=None,
evenFooter=None,
firstHeader=None,
firstFooter=None,
):
self.differentOddEven = differentOddEven
self.differentFirst = differentFirst
self.scaleWithDoc = scaleWithDoc
self.alignWithMargins = alignWithMargins
if oddHeader is None:
oddHeader = HeaderFooterItem()
self.oddHeader = oddHeader
if oddFooter is None:
oddFooter = HeaderFooterItem()
self.oddFooter = oddFooter
if evenHeader is None:
evenHeader = HeaderFooterItem()
self.evenHeader = evenHeader
if evenFooter is None:
evenFooter = HeaderFooterItem()
self.evenFooter = evenFooter
if firstHeader is None:
firstHeader = HeaderFooterItem()
self.firstHeader = firstHeader
if firstFooter is None:
firstFooter = HeaderFooterItem()
self.firstFooter = firstFooter
def __bool__(self):
parts = [
getattr(self, attr) for attr in self.__attrs__ + self.__elements__
]
return any(parts)
__nonzero__ = __bool__
class Table(Serialisable):
_path = "/tables/table{0}.xml"
mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"
_rel_type = "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 SheetView(Serialisable):
"""Information about the visible portions of this sheet."""
tagname = "sheetView"
windowProtection = Bool(allow_none=True)
showFormulas = Bool(allow_none=True)
showGridLines = Bool(allow_none=True)
showRowColHeaders = Bool(allow_none=True)
showZeros = Bool(allow_none=True)
rightToLeft = Bool(allow_none=True)
tabSelected = Bool(allow_none=True)
showRuler = Bool(allow_none=True)
showOutlineSymbols = Bool(allow_none=True)
defaultGridColor = Bool(allow_none=True)
showWhiteSpace = Bool(allow_none=True)
view = NoneSet(values=("normal", "pageBreakPreview", "pageLayout"))
topLeftCell = String(allow_none=True)
colorId = Integer(allow_none=True)
zoomScale = Integer(allow_none=True)
zoomScaleNormal = Integer(allow_none=True)
zoomScaleSheetLayoutView = Integer(allow_none=True)
zoomScalePageLayoutView = Integer(allow_none=True)
zoomToFit = Bool(allow_none=True) # Chart sheets only
workbookViewId = Integer()
selection = Sequence(expected_type=Selection)
pane = Typed(expected_type=Pane, allow_none=True)
def __init__(
self,
windowProtection=None,
showFormulas=None,
showGridLines=None,
showRowColHeaders=None,
showZeros=None,
rightToLeft=None,
tabSelected=None,
showRuler=None,
showOutlineSymbols=None,
defaultGridColor=None,
showWhiteSpace=None,
view=None,
topLeftCell=None,
colorId=None,
zoomScale=None,
zoomScaleNormal=None,
zoomScaleSheetLayoutView=None,
zoomScalePageLayoutView=None,
zoomToFit=None,
workbookViewId=0,
selection=None,
pane=None,
):
self.windowProtection = windowProtection
self.showFormulas = showFormulas
self.showGridLines = showGridLines
self.showRowColHeaders = showRowColHeaders
self.showZeros = showZeros
self.rightToLeft = rightToLeft
self.tabSelected = tabSelected
self.showRuler = showRuler
self.showOutlineSymbols = showOutlineSymbols
self.defaultGridColor = defaultGridColor
self.showWhiteSpace = showWhiteSpace
self.view = view
self.topLeftCell = topLeftCell
self.colorId = colorId
self.zoomScale = zoomScale
self.zoomScaleNormal = zoomScaleNormal
self.zoomScaleSheetLayoutView = zoomScaleSheetLayoutView
self.zoomScalePageLayoutView = zoomScalePageLayoutView
self.zoomToFit = zoomToFit
self.workbookViewId = workbookViewId
self.pane = pane
if selection is None:
selection = (Selection(), )
self.selection = selection
class ColumnDimension(Dimension):
"""Information about the display properties of a column."""
width = Float()
bestFit = Bool()
auto_size = Alias('bestFit')
index = String()
min = Integer(allow_none=True)
max = Integer(allow_none=True)
collapsed = Bool()
__fields__ = Dimension.__fields__ + ('width', 'bestFit', 'customWidth', 'style',
'min', 'max')
def __init__(self,
worksheet,
index='A',
width=0,
bestFit=False,
hidden=False,
outlineLevel=0,
outline_level=None,
collapsed=False,
style=None,
min=None,
max=None,
customWidth=False, # do not write
visible=None,
auto_size=None,):
self.width = width
self.min = min
self.max = max
if visible is not None:
hidden = not visible
if auto_size is not None:
bestFit = auto_size
self.bestFit = bestFit
if outline_level is not None:
outlineLevel = outline_level
self.collapsed = collapsed
super(ColumnDimension, self).__init__(index, hidden, outlineLevel,
collapsed, worksheet, style=style)
@property
def customWidth(self):
"""Always true if there is a width for the column"""
return bool(self.width)
def reindex(self):
"""
Set boundaries for column definition
"""
if not all([self.min, self.max]):
self.min = self.max = column_index_from_string(self.index)
def to_tree(self):
attrs = dict(self)
if set(attrs) != set(['min', 'max']):
return Element("col", **attrs)
class PictureLocking(Serialisable):
tagname = "picLocks"
namespace = DRAWING_NS
#Using attribute group AG_Locking
noCrop = Bool(allow_none=True)
noGrp = Bool(allow_none=True)
noSelect = Bool(allow_none=True)
noRot = Bool(allow_none=True)
noChangeAspect = Bool(allow_none=True)
noMove = Bool(allow_none=True)
noResize = Bool(allow_none=True)
noEditPoints = Bool(allow_none=True)
noAdjustHandles = Bool(allow_none=True)
noChangeArrowheads = Bool(allow_none=True)
noChangeShapeType = Bool(allow_none=True)
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
__elements__ = ()
def __init__(self,
noCrop=None,
noGrp=None,
noSelect=None,
noRot=None,
noChangeAspect=None,
noMove=None,
noResize=None,
noEditPoints=None,
noAdjustHandles=None,
noChangeArrowheads=None,
noChangeShapeType=None,
extLst=None,
):
self.noCrop = noCrop
self.noGrp = noGrp
self.noSelect = noSelect
self.noRot = noRot
self.noChangeAspect = noChangeAspect
self.noMove = noMove
self.noResize = noResize
self.noEditPoints = noEditPoints
self.noAdjustHandles = noAdjustHandles
self.noChangeArrowheads = noChangeArrowheads
self.noChangeShapeType = noChangeShapeType
class SharedItems(Serialisable):
tagname = "sharedItems"
_fields = MultiSequence()
m = MultiSequencePart(expected_type=Missing, store="_fields")
n = MultiSequencePart(expected_type=Number, store="_fields")
b = MultiSequencePart(expected_type=Boolean, store="_fields")
e = MultiSequencePart(expected_type=Error, store="_fields")
s = MultiSequencePart(expected_type=Text, store="_fields")
d = MultiSequencePart(expected_type=DateTimeField, store="_fields")
# attributes are optional and must be derived from associated cache records
containsSemiMixedTypes = Bool(allow_none=True)
containsNonDate = Bool(allow_none=True)
containsDate = Bool(allow_none=True)
containsString = Bool(allow_none=True)
containsBlank = Bool(allow_none=True)
containsMixedTypes = Bool(allow_none=True)
containsNumber = Bool(allow_none=True)
containsInteger = Bool(allow_none=True)
minValue = Float(allow_none=True)
maxValue = Float(allow_none=True)
minDate = DateTime(allow_none=True)
maxDate = DateTime(allow_none=True)
longText = Bool(allow_none=True)
__attrs__ = ('count', 'containsBlank', 'containsDate', 'containsInteger',
'containsMixedTypes', 'containsNonDate', 'containsNumber',
'containsSemiMixedTypes', 'containsString', 'minValue', 'maxValue',
'minDate', 'maxDate', 'longText')
def __init__(self,
_fields=(),
containsSemiMixedTypes=None,
containsNonDate=None,
containsDate=None,
containsString=None,
containsBlank=None,
containsMixedTypes=None,
containsNumber=None,
containsInteger=None,
minValue=None,
maxValue=None,
minDate=None,
maxDate=None,
count=None,
longText=None,
):
self._fields = _fields
self.containsBlank = containsBlank
self.containsDate = containsDate
self.containsNonDate = containsNonDate
self.containsString = containsString
self.containsMixedTypes = containsMixedTypes
self.containsSemiMixedTypes = containsSemiMixedTypes
self.containsNumber = containsNumber
self.containsInteger = containsInteger
self.minValue = minValue
self.maxValue = maxValue
self.minDate = minDate
self.maxDate = maxDate
self.longText = longText
@property
def count(self):
return len(self._fields)
class PrintPageSetup(Serialisable):
""" Worksheet print page setup """
tagname = "pageSetup"
orientation = NoneSet(values=("default", "portrait", "landscape"))
paperSize = Integer(allow_none=True)
scale = Integer(allow_none=True)
fitToHeight = Integer(allow_none=True)
fitToWidth = Integer(allow_none=True)
firstPageNumber = Integer(allow_none=True)
useFirstPageNumber = Bool(allow_none=True)
paperHeight = UniversalMeasure(allow_none=True)
paperWidth = UniversalMeasure(allow_none=True)
pageOrder = NoneSet(values=("downThenOver", "overThenDown"))
usePrinterDefaults = Bool(allow_none=True)
blackAndWhite = Bool(allow_none=True)
draft = Bool(allow_none=True)
cellComments = NoneSet(values=("asDisplayed", "atEnd"))
errors = NoneSet(values=("displayed", "blank", "dash", "NA"))
horizontalDpi = Integer(allow_none=True)
verticalDpi = Integer(allow_none=True)
copies = Integer(allow_none=True)
id = Relation()
def __init__(self,
worksheet=None,
orientation=None,
paperSize=None,
scale=None,
fitToHeight=None,
fitToWidth=None,
firstPageNumber=None,
useFirstPageNumber=None,
paperHeight=None,
paperWidth=None,
pageOrder=None,
usePrinterDefaults=None,
blackAndWhite=None,
draft=None,
cellComments=None,
errors=None,
horizontalDpi=None,
verticalDpi=None,
copies=None,
id=None):
self._parent = worksheet
self.orientation = orientation
self.paperSize = paperSize
self.scale = scale
self.fitToHeight = fitToHeight
self.fitToWidth = fitToWidth
self.firstPageNumber = firstPageNumber
self.useFirstPageNumber = useFirstPageNumber
self.paperHeight = paperHeight
self.paperWidth = paperWidth
self.pageOrder = pageOrder
self.usePrinterDefaults = usePrinterDefaults
self.blackAndWhite = blackAndWhite
self.draft = draft
self.cellComments = cellComments
self.errors = errors
self.horizontalDpi = horizontalDpi
self.verticalDpi = verticalDpi
self.copies = copies
self.id = id
@property
def sheet_properties(self):
"""
Proxy property
"""
return self._parent.sheet_properties.pageSetUpPr
@property
def fitToPage(self):
return self.sheet_properties.fitToPage
@fitToPage.setter
def fitToPage(self, value):
self.sheet_properties.fitToPage = value
@property
def autoPageBreaks(self):
return self.sheet_properties.autoPageBreaks
@autoPageBreaks.setter
def autoPageBreaks(self, value):
self.sheet_properties.autoPageBreaks = value
@classmethod
def from_tree(cls, node):
self = super(PrintPageSetup, cls).from_tree(node)
self.id = None # strip link to binary settings
return self
class WorkbookProperties(Serialisable):
tagname = "workbookPr"
date1904 = Bool(allow_none=True)
dateCompatibility = Bool(allow_none=True)
showObjects = NoneSet(values=(['all', 'placeholders']))
showBorderUnselectedTables = Bool(allow_none=True)
filterPrivacy = Bool(allow_none=True)
promptedSolutions = Bool(allow_none=True)
showInkAnnotation = Bool(allow_none=True)
backupFile = Bool(allow_none=True)
saveExternalLinkValues = Bool(allow_none=True)
updateLinks = NoneSet(values=(['userSet', 'never', 'always']))
codeName = String(allow_none=True)
hidePivotFieldList = Bool(allow_none=True)
showPivotChartFilter = Bool(allow_none=True)
allowRefreshQuery = Bool(allow_none=True)
publishItems = Bool(allow_none=True)
checkCompatibility = Bool(allow_none=True)
autoCompressPictures = Bool(allow_none=True)
refreshAllConnections = Bool(allow_none=True)
defaultThemeVersion = Integer(allow_none=True)
def __init__(
self,
date1904=None,
dateCompatibility=None,
showObjects=None,
showBorderUnselectedTables=None,
filterPrivacy=None,
promptedSolutions=None,
showInkAnnotation=None,
backupFile=None,
saveExternalLinkValues=None,
updateLinks=None,
codeName=None,
hidePivotFieldList=None,
showPivotChartFilter=None,
allowRefreshQuery=None,
publishItems=None,
checkCompatibility=None,
autoCompressPictures=None,
refreshAllConnections=None,
defaultThemeVersion=None,
):
self.date1904 = date1904
self.dateCompatibility = dateCompatibility
self.showObjects = showObjects
self.showBorderUnselectedTables = showBorderUnselectedTables
self.filterPrivacy = filterPrivacy
self.promptedSolutions = promptedSolutions
self.showInkAnnotation = showInkAnnotation
self.backupFile = backupFile
self.saveExternalLinkValues = saveExternalLinkValues
self.updateLinks = updateLinks
self.codeName = codeName
self.hidePivotFieldList = hidePivotFieldList
self.showPivotChartFilter = showPivotChartFilter
self.allowRefreshQuery = allowRefreshQuery
self.publishItems = publishItems
self.checkCompatibility = checkCompatibility
self.autoCompressPictures = autoCompressPictures
self.refreshAllConnections = refreshAllConnections
self.defaultThemeVersion = defaultThemeVersion
class ParagraphProperties(Serialisable):
tagname = "pPr"
namespace = DRAWING_NS
marL = Integer(allow_none=True)
marR = Integer(allow_none=True)
lvl = Integer(allow_none=True)
indent = Integer(allow_none=True)
algn = NoneSet(
values=(['l', 'ctr', 'r', 'just', 'justLow', 'dist', 'thaiDist']))
defTabSz = Integer(expected_type=Coordinate, allow_none=True)
rtl = Bool(allow_none=True)
eaLnBrk = Bool(allow_none=True)
fontAlgn = NoneSet(values=(['auto', 't', 'ctr', 'base', 'b']))
latinLnBrk = Bool(allow_none=True)
hangingPunct = Bool(allow_none=True)
# uses element group EG_TextBulletColor
# uses element group EG_TextBulletSize
# uses element group EG_TextBulletTypeface
# uses element group EG_TextBullet
lnSpc = Typed(expected_type=Spacing, allow_none=True)
spcBef = Typed(expected_type=Spacing, allow_none=True)
spcAft = Typed(expected_type=Spacing, allow_none=True)
tabLst = Typed(expected_type=TabStopList, allow_none=True)
defRPr = Typed(expected_type=CharacterProperties, allow_none=True)
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
buClrTx = EmptyTag()
buClr = Typed(expected_type=Color, allow_none=True)
buSzTx = EmptyTag()
buSzPct = NestedInteger(allow_none=True)
buSzPts = NestedInteger(allow_none=True)
buFontTx = EmptyTag()
buFont = Typed(expected_type=Font, allow_none=True)
buNone = EmptyTag()
buAutoNum = EmptyTag()
buChar = NestedValue(expected_type=unicode,
attribute="char",
allow_none=True)
buBlip = NestedValue(expected_type=Blip, attribute="blip", allow_none=True)
__elements__ = ('lnSpc', 'spcBef', 'spcAft', 'tabLst', 'defRPr', 'buClrTx',
'buClr', 'buSzTx', 'buSzPct', 'buSzPts', 'buFontTx',
'buFont', 'buNone', 'buAutoNum', 'buChar', 'buBlip')
def __init__(
self,
marL=None,
marR=None,
lvl=None,
indent=None,
algn=None,
defTabSz=None,
rtl=None,
eaLnBrk=None,
fontAlgn=None,
latinLnBrk=None,
hangingPunct=None,
lnSpc=None,
spcBef=None,
spcAft=None,
tabLst=None,
defRPr=None,
extLst=None,
buClrTx=None,
buClr=None,
buSzTx=None,
buSzPct=None,
buSzPts=None,
buFontTx=None,
buFont=None,
buNone=None,
buAutoNum=None,
buChar=None,
buBlip=None,
):
self.marL = marL
self.marR = marR
self.lvl = lvl
self.indent = indent
self.algn = algn
self.defTabSz = defTabSz
self.rtl = rtl
self.eaLnBrk = eaLnBrk
self.fontAlgn = fontAlgn
self.latinLnBrk = latinLnBrk
self.hangingPunct = hangingPunct
self.lnSpc = lnSpc
self.spcBef = spcBef
self.spcAft = spcAft
self.tabLst = tabLst
self.defRPr = defRPr
self.buClrTx = buClrTx
self.buClr = buClr
self.buSzTx = buSzTx
self.buSzPct = buSzPct
self.buSzPts = buSzPts
self.buFontTx = buFontTx
self.buFont = buFont
self.buNone = buNone
self.buAutoNum = buAutoNum
self.buChar = buChar
self.buBlip = buBlip
self.defRPr = defRPr
class Alignment(Serialisable):
"""Alignment options for use in styles."""
tagname = "alignment"
__fields__ = (
'horizontal',
'vertical',
'textRotation',
'wrapText',
'shrinkToFit',
'indent',
'relativeIndent',
'justifyLastLine',
'readingOrder',
)
horizontal = NoneSet(values=horizontal_alignments)
vertical = NoneSet(values=vertical_aligments)
textRotation = NoneSet(values=range(181))
textRotation.values.add(255)
text_rotation = Alias('textRotation')
wrapText = Bool(allow_none=True)
wrap_text = Alias('wrapText')
shrinkToFit = Bool(allow_none=True)
shrink_to_fit = Alias('shrinkToFit')
indent = Min(min=0)
relativeIndent = Min(min=0)
justifyLastLine = Bool(allow_none=True)
readingOrder = Min(min=0)
def __init__(self,
horizontal=None,
vertical=None,
textRotation=0,
wrapText=None,
shrinkToFit=None,
indent=0,
relativeIndent=0,
justifyLastLine=None,
readingOrder=0,
text_rotation=None,
wrap_text=None,
shrink_to_fit=None,
mergeCell=None):
self.horizontal = horizontal
self.vertical = vertical
self.indent = indent
self.relativeIndent = relativeIndent
self.justifyLastLine = justifyLastLine
self.readingOrder = readingOrder
if text_rotation is not None:
textRotation = text_rotation
if textRotation is not None:
self.textRotation = int(textRotation)
if wrap_text is not None:
wrapText = wrap_text
self.wrapText = wrapText
if shrink_to_fit is not None:
shrinkToFit = shrink_to_fit
self.shrinkToFit = shrinkToFit
# mergeCell is vestigial
def __iter__(self):
for attr in self.__attrs__:
value = getattr(self, attr)
if value is not None and value != 0:
yield attr, safe_string(value)
class DataValidation(Serialisable):
tagname = "dataValidation"
sqref = Convertible(expected_type=MultiCellRange)
cells = Alias("sqref")
ranges = Alias("sqref")
showErrorMessage = Bool()
showDropDown = Bool(allow_none=True)
hide_drop_down = Alias('showDropDown')
showInputMessage = Bool()
showErrorMessage = Bool()
allowBlank = Bool()
allow_blank = Alias('allowBlank')
errorTitle = String(allow_none=True)
error = String(allow_none=True)
promptTitle = String(allow_none=True)
prompt = String(allow_none=True)
formula1 = NestedText(allow_none=True, expected_type=unicode)
formula2 = NestedText(allow_none=True, expected_type=unicode)
type = NoneSet(values=("whole", "decimal", "list", "date", "time",
"textLength", "custom"))
errorStyle = NoneSet(values=("stop", "warning", "information"))
imeMode = NoneSet(values=("noControl", "off", "on", "disabled", "hiragana",
"fullKatakana", "halfKatakana", "fullAlpha",
"halfAlpha", "fullHangul", "halfHangul"))
operator = NoneSet(values=("between", "notBetween", "equal", "notEqual",
"lessThan", "lessThanOrEqual", "greaterThan",
"greaterThanOrEqual"))
validation_type = Alias('type')
def __init__(
self,
type=None,
formula1=None,
formula2=None,
allow_blank=False,
showErrorMessage=True,
showInputMessage=True,
showDropDown=None,
allowBlank=None,
sqref=(),
promptTitle=None,
errorStyle=None,
error=None,
prompt=None,
errorTitle=None,
imeMode=None,
operator=None,
):
self.sqref = sqref
self.showDropDown = showDropDown
self.imeMode = imeMode
self.operator = operator
self.formula1 = formula1
self.formula2 = formula2
if allow_blank is not None:
allowBlank = allow_blank
self.allowBlank = allowBlank
self.showErrorMessage = showErrorMessage
self.showInputMessage = showInputMessage
self.type = type
self.promptTitle = promptTitle
self.errorStyle = errorStyle
self.error = error
self.prompt = prompt
self.errorTitle = errorTitle
def add(self, cell):
"""Adds a cell or cell coordinate to this validator"""
if hasattr(cell, "coordinate"):
cell = cell.coordinate
self.sqref += cell
def __contains__(self, cell):
if hasattr(cell, "coordinate"):
cell = cell.coordinate
return cell in self.sqref
class Blip(Serialisable):
tagname = "blip"
namespace = DRAWING_NS
#Using attribute groupAG_Blob
cstate = NoneSet(values=(['email', 'screen', 'print', 'hqprint']))
embed = Relation() #rId
link = Relation() #hyperlink
noGrp = Bool(allow_none=True)
noSelect = Bool(allow_none=True)
noRot = Bool(allow_none=True)
noChangeAspect = Bool(allow_none=True)
noMove = Bool(allow_none=True)
noResize = Bool(allow_none=True)
noEditPoints = Bool(allow_none=True)
noAdjustHandles = Bool(allow_none=True)
noChangeArrowheads = Bool(allow_none=True)
noChangeShapeType = Bool(allow_none=True)
# some elements are choice
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
alphaBiLevel = Typed(expected_type=AlphaBiLevelEffect, allow_none=True)
alphaCeiling = Typed(expected_type=AlphaCeilingEffect, allow_none=True)
alphaFloor = Typed(expected_type=AlphaFloorEffect, allow_none=True)
alphaInv = Typed(expected_type=AlphaInverseEffect, allow_none=True)
alphaMod = Typed(expected_type=AlphaModulateEffect, allow_none=True)
alphaModFix = Typed(expected_type=AlphaModulateFixedEffect,
allow_none=True)
alphaRepl = Typed(expected_type=AlphaReplaceEffect, allow_none=True)
biLevel = Typed(expected_type=BiLevelEffect, allow_none=True)
blur = Typed(expected_type=BlurEffect, allow_none=True)
clrChange = Typed(expected_type=ColorChangeEffect, allow_none=True)
clrRepl = Typed(expected_type=ColorReplaceEffect, allow_none=True)
duotone = Typed(expected_type=DuotoneEffect, allow_none=True)
fillOverlay = Typed(expected_type=FillOverlayEffect, allow_none=True)
grayscl = Typed(expected_type=GrayscaleEffect, allow_none=True)
hsl = Typed(expected_type=HSLEffect, allow_none=True)
lum = Typed(expected_type=LuminanceEffect, allow_none=True)
tint = Typed(expected_type=TintEffect, allow_none=True)
__elements__ = ('alphaBiLevel', 'alphaCeiling', 'alphaFloor', 'alphaInv',
'alphaMod', 'alphaModFix', 'alphaRepl', 'biLevel', 'blur',
'clrChange', 'clrRepl', 'duotone', 'fillOverlay',
'grayscl', 'hsl', 'lum', 'tint')
def __init__(
self,
cstate=None,
embed=None,
link=None,
noGrp=None,
noSelect=None,
noRot=None,
noChangeAspect=None,
noMove=None,
noResize=None,
noEditPoints=None,
noAdjustHandles=None,
noChangeArrowheads=None,
noChangeShapeType=None,
extLst=None,
alphaBiLevel=None,
alphaCeiling=None,
alphaFloor=None,
alphaInv=None,
alphaMod=None,
alphaModFix=None,
alphaRepl=None,
biLevel=None,
blur=None,
clrChange=None,
clrRepl=None,
duotone=None,
fillOverlay=None,
grayscl=None,
hsl=None,
lum=None,
tint=None,
):
self.cstate = cstate
self.embed = embed
self.link = link
self.noGrp = noGrp
self.noSelect = noSelect
self.noRot = noRot
self.noChangeAspect = noChangeAspect
self.noMove = noMove
self.noResize = noResize
self.noEditPoints = noEditPoints
self.noAdjustHandles = noAdjustHandles
self.noChangeArrowheads = noChangeArrowheads
self.noChangeShapeType = noChangeShapeType
self.extLst = extLst
self.alphaBiLevel = alphaBiLevel
self.alphaCeiling = alphaCeiling
self.alphaFloor = alphaFloor
self.alphaInv = alphaInv
self.alphaMod = alphaMod
self.alphaModFix = alphaModFix
self.alphaRepl = alphaRepl
self.biLevel = biLevel
self.blur = blur
self.clrChange = clrChange
self.clrRepl = clrRepl
self.duotone = duotone
self.fillOverlay = fillOverlay
self.grayscl = grayscl
self.hsl = hsl
self.lum = lum
self.tint = tint
class PrintPageSetup(Serialisable):
""" Worksheet print page setup """
tagname = "pageSetup"
orientation = NoneSet(values=("default", "portrait", "landscape"))
paperSize = Integer(allow_none=True)
scale = Integer(allow_none=True)
fitToHeight = Integer(allow_none=True)
fitToWidth = Integer(allow_none=True)
firstPageNumber = Integer(allow_none=True)
useFirstPageNumber = Bool(allow_none=True)
paperHeight = UniversalMeasure(allow_none=True)
paperWidth = UniversalMeasure(allow_none=True)
pageOrder = NoneSet(values=("downThenOver", "overThenDown"))
usePrinterDefaults = Bool(allow_none=True)
blackAndWhite = Bool(allow_none=True)
draft = Bool(allow_none=True)
cellComments = NoneSet(values=("asDisplayed", "atEnd"))
errors = NoneSet(values=("displayed", "blank", "dash", "NA"))
horizontalDpi = Integer(allow_none=True)
verticalDpi = Integer(allow_none=True)
copies = Integer(allow_none=True)
id = String(allow_none=True)
def __init__(self,
worksheet=None,
orientation=None,
paperSize=None,
scale=None,
fitToHeight=None,
fitToWidth=None,
firstPageNumber=None,
useFirstPageNumber=None,
paperHeight=None,
paperWidth=None,
pageOrder=None,
usePrinterDefaults=None,
blackAndWhite=None,
draft=None,
cellComments=None,
errors=None,
horizontalDpi=None,
verticalDpi=None,
copies=None,
id=None):
self._parent = worksheet
self.orientation = orientation
self.paperSize = paperSize
self.scale = scale
self.fitToHeight = fitToHeight
self.fitToWidth = fitToWidth
self.firstPageNumber = firstPageNumber
self.useFirstPageNumber = useFirstPageNumber
self.paperHeight = paperHeight
self.paperWidth = paperWidth
self.pageOrder = pageOrder
self.usePrinterDefaults = usePrinterDefaults
self.blackAndWhite = blackAndWhite
self.draft = draft
self.cellComments = cellComments
self.errors = errors
self.horizontalDpi = horizontalDpi
self.verticalDpi = verticalDpi
self.copies = copies
self.id = id
@deprecated("this property does not exists anymore")
def setup(self):
pass
@deprecated("this property does not exists anymore")
def options(self):
pass
@deprecated("this property has to be called via print_options")
def horizontalCentered(self):
pass
@deprecated("this property has to be called via print_options")
def verticalCentered(self):
pass
@property
def sheet_properties(self):
"""
Proxy property
"""
return self._parent.sheet_properties.pageSetUpPr
@property
def fitToPage(self):
return self.sheet_properties.fitToPage
@fitToPage.setter
def fitToPage(self, value):
self.sheet_properties.fitToPage = value
@property
def autoPageBreaks(self):
return self.sheet_properties.autoPageBreaks
@autoPageBreaks.setter
def autoPageBreaks(self, value):
self.sheet_properties.autoPageBreaks = value
@classmethod
def from_tree(cls, node):
attrs = node.attrib
id_key = '{%s}id' % REL_NS
if id_key in attrs:
attrs.pop(id_key)
return cls(**attrs)
def to_tree(self):
attrs = dict(self)
if 'id' in attrs:
attrs['{%s}id' % REL_NS] = attrs['id']
del attrs['id']
return Element(self.tagname, attrs)
class ColumnDimension(Dimension):
"""Information about the display properties of a column."""
width = Float(allow_none=True)
bestFit = Bool()
auto_size = Alias('bestFit')
index = String()
min = Integer(allow_none=True)
max = Integer(allow_none=True)
collapsed = Bool()
__fields__ = Dimension.__fields__ + ('width', 'bestFit', 'customWidth',
'style', 'min', 'max')
def __init__(
self,
worksheet,
index='A',
width=None,
bestFit=False,
hidden=False,
outlineLevel=0,
outline_level=None,
collapsed=False,
style=None,
min=None,
max=None,
customWidth=False, # do not write
visible=None,
auto_size=None,
):
self.width = width
self.min = min
self.max = max
if visible is not None:
hidden = not visible
if auto_size is not None:
bestFit = auto_size
self.bestFit = bestFit
if outline_level is not None:
outlineLevel = outline_level
self.collapsed = collapsed
super(ColumnDimension, self).__init__(index,
hidden,
outlineLevel,
collapsed,
worksheet,
style=style)
@property
def customWidth(self):
"""Always true if there is a width for the column"""
return self.width is not None
def __iter__(self):
for key in self.__fields__:
if key == 'style':
value = self.style_id
else:
value = getattr(self, key)
if value:
yield key, safe_string(value)
class DataValidation(Strict):
showErrorMessage = Bool()
showDropDown = Bool(allow_none=True)
showInputMessage = Bool()
showErrorMessage = Bool()
allowBlank = Bool()
allow_blank = Bool()
errorTitle = String(allow_none=True)
error = String(allow_none=True)
promptTitle = String(allow_none=True)
prompt = String(allow_none=True)
sqref = String(allow_none=True)
formula1 = String(allow_none=True)
formula2 = String(allow_none=True)
type = NoneSet(values=("whole", "decimal", "list", "date", "time",
"textLength", "custom"))
errorStyle = NoneSet(values=("stop", "warning", "information"))
imeMode = NoneSet(values=("noControl", "off", "on", "disabled", "hiragana",
"fullKatakana", "halfKatakana", "fullAlpha",
"halfAlpha", "fullHangul", "halfHangul"))
operator = NoneSet(values=("between", "notBetween", "equal", "notEqual",
"lessThan", "lessThanOrEqual", "greaterThan",
"greaterThanOrEqual"))
def __init__(
self,
type=None,
formula1=None,
formula2=None,
allow_blank=False,
showErrorMessage=True,
showInputMessage=True,
showDropDown=None,
allowBlank=None,
sqref=None,
promptTitle=None,
errorStyle=None,
error=None,
prompt=None,
errorTitle=None,
imeMode=None,
operator=None,
validation_type=None, # remove in future
):
self.showDropDown = showDropDown
self.imeMode = imeMode
self.operator = operator
self.formula1 = formula1
self.formula2 = formula2
self.allowBlank = allow_blank
if allowBlank is not None:
self.allowBlank = allowBlank
self.showErrorMessage = showErrorMessage
self.showInputMessage = showInputMessage
if validation_type is not None:
warnings.warn(
"Use 'DataValidation(type={0})'".format(validation_type))
if type is not None:
self.type = validation_type
self.type = type
self.cells = set()
self.ranges = []
if sqref is not None:
self.sqref = sqref
self.promptTitle = promptTitle
self.errorStyle = errorStyle
self.error = error
self.prompt = prompt
self.errorTitle = errorTitle
@deprecated("Use DataValidation.add()")
def add_cell(self, cell):
"""Adds a openpyxl.cell to this validator"""
self.add(cell)
def add(self, cell):
"""Adds a openpyxl.cell to this validator"""
self.cells.add(cell.coordinate)
@deprecated("Set DataValidation.ErrorTitle and DataValidation.error")
def set_error_message(self, error, error_title="Validation Error"):
"""Creates a custom error message, displayed when a user changes a cell
to an invalid value"""
self.errorTitle = error_title
self.error = error
@deprecated("Set DataValidation.PromptTitle and DataValidation.prompt")
def set_prompt_message(self, prompt, prompt_title="Validation Prompt"):
"""Creates a custom prompt message"""
self.promptTitle = prompt_title
self.prompt = prompt
@property
def sqref(self):
return collapse_cell_addresses(self.cells, self.ranges)
@sqref.setter
def sqref(self, range_string):
self.cells = expand_cell_ranges(range_string)
def __iter__(self):
for attr in ('type', 'allowBlank', 'operator', 'sqref',
'showInputMessage', 'showErrorMessage', 'errorTitle',
'error', 'errorStyle', 'promptTitle', 'prompt'):
value = getattr(self, attr)
if value is not None:
yield attr, safe_string(value)
class RichTextProperties(Serialisable):
tagname = "bodyPr"
namespace = DRAWING_NS
rot = Integer(allow_none=True)
spcFirstLastPara = Bool(allow_none=True)
vertOverflow = NoneSet(values=(['overflow', 'ellipsis', 'clip']))
horzOverflow = NoneSet(values=(['overflow', 'clip']))
vert = NoneSet(values=([
'horz', 'vert', 'vert270', 'wordArtVert', 'eaVert', 'mongolianVert',
'wordArtVertRtl'
]))
wrap = NoneSet(values=(['none', 'square']))
lIns = Integer(allow_none=True)
tIns = Integer(allow_none=True)
rIns = Integer(allow_none=True)
bIns = Integer(allow_none=True)
numCol = Integer(allow_none=True)
spcCol = Integer(allow_none=True)
rtlCol = Bool(allow_none=True)
fromWordArt = Bool(allow_none=True)
anchor = NoneSet(values=(['t', 'ctr', 'b', 'just', 'dist']))
anchorCtr = Bool(allow_none=True)
forceAA = Bool(allow_none=True)
upright = Bool(allow_none=True)
compatLnSpc = Bool(allow_none=True)
prstTxWarp = Typed(expected_type=PresetTextShape, allow_none=True)
scene3d = Typed(expected_type=Scene3D, allow_none=True)
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
noAutofit = EmptyTag()
normAutofit = EmptyTag()
spAutoFit = EmptyTag()
flatTx = NestedInteger(attribute="z", allow_none=True)
__elements__ = ('prstTxWarp', 'scene3d', 'noAutofit', 'normAutofit',
'spAutoFit')
def __init__(
self,
rot=None,
spcFirstLastPara=None,
vertOverflow=None,
horzOverflow=None,
vert=None,
wrap=None,
lIns=None,
tIns=None,
rIns=None,
bIns=None,
numCol=None,
spcCol=None,
rtlCol=None,
fromWordArt=None,
anchor=None,
anchorCtr=None,
forceAA=None,
upright=None,
compatLnSpc=None,
prstTxWarp=None,
scene3d=None,
extLst=None,
noAutofit=None,
normAutofit=None,
spAutoFit=None,
flatTx=None,
):
self.rot = rot
self.spcFirstLastPara = spcFirstLastPara
self.vertOverflow = vertOverflow
self.horzOverflow = horzOverflow
self.vert = vert
self.wrap = wrap
self.lIns = lIns
self.tIns = tIns
self.rIns = rIns
self.bIns = bIns
self.numCol = numCol
self.spcCol = spcCol
self.rtlCol = rtlCol
self.fromWordArt = fromWordArt
self.anchor = anchor
self.anchorCtr = anchorCtr
self.forceAA = forceAA
self.upright = upright
self.compatLnSpc = compatLnSpc
self.prstTxWarp = prstTxWarp
self.scene3d = scene3d
self.noAutofit = noAutofit
self.normAutofit = normAutofit
self.spAutoFit = spAutoFit
self.flatTx = flatTx
class CacheHierarchy(Serialisable):
uniqueName = String()
caption = String(allow_none=True)
measure = Bool()
set = Bool()
parentSet = Integer(allow_none=True)
iconSet = Integer()
attribute = Bool()
time = Bool()
keyAttribute = Bool()
defaultMemberUniqueName = String()
allUniqueName = String()
allCaption = String()
dimensionUniqueName = String()
displayFolder = String()
measureGroup = String()
measures = Bool()
count = Integer()
oneField = Bool()
memberValueDatatype = Integer(allow_none=True)
unbalanced = Bool(allow_none=True)
unbalancedGroup = Bool(allow_none=True)
hidden = Bool()
fieldsUsage = Typed(expected_type=FieldsUsage, allow_none=True)
groupLevels = Typed(expected_type=GroupLevels, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('fieldsUsage', 'groupLevels', 'extLst')
def __init__(self,
uniqueName=None,
caption=None,
measure=None,
set=None,
parentSet=None,
iconSet=None,
attribute=None,
time=None,
keyAttribute=None,
defaultMemberUniqueName=None,
allUniqueName=None,
allCaption=None,
dimensionUniqueName=None,
displayFolder=None,
measureGroup=None,
measures=None,
count=None,
oneField=None,
memberValueDatatype=None,
unbalanced=None,
unbalancedGroup=None,
hidden=None,
fieldsUsage=None,
groupLevels=None,
extLst=None,
):
self.uniqueName = uniqueName
self.caption = caption
self.measure = measure
self.set = set
self.parentSet = parentSet
self.iconSet = iconSet
self.attribute = attribute
self.time = time
self.keyAttribute = keyAttribute
self.defaultMemberUniqueName = defaultMemberUniqueName
self.allUniqueName = allUniqueName
self.allCaption = allCaption
self.dimensionUniqueName = dimensionUniqueName
self.displayFolder = displayFolder
self.measureGroup = measureGroup
self.measures = measures
self.count = count
self.oneField = oneField
self.memberValueDatatype = memberValueDatatype
self.unbalanced = unbalanced
self.unbalancedGroup = unbalancedGroup
self.hidden = hidden
self.fieldsUsage = fieldsUsage
self.groupLevels = groupLevels
self.extLst = extLst
class CharacterProperties(Serialisable):
tagname = "defRPr"
namespace = DRAWING_NS
kumimoji = Bool(allow_none=True)
lang = String(allow_none=True)
altLang = String(allow_none=True)
sz = MinMax(allow_none=True, min=100, max=400000) # 100ths of a point
b = Bool(allow_none=True)
i = Bool(allow_none=True)
u = NoneSet(values=([
'words', 'sng', 'dbl', 'heavy', 'dotted', 'dottedHeavy', 'dash',
'dashHeavy', 'dashLong', 'dashLongHeavy', 'dotDash', 'dotDashHeavy',
'dotDotDash', 'dotDotDashHeavy', 'wavy', 'wavyHeavy', 'wavyDbl'
]))
strike = NoneSet(values=(['noStrike', 'sngStrike', 'dblStrike']))
kern = Integer(allow_none=True)
cap = NoneSet(values=(['small', 'all']))
spc = Integer(allow_none=True)
normalizeH = Bool(allow_none=True)
baseline = Integer(allow_none=True)
noProof = Bool(allow_none=True)
dirty = Bool(allow_none=True)
err = Bool(allow_none=True)
smtClean = Bool(allow_none=True)
smtId = Integer(allow_none=True)
bmk = String(allow_none=True)
ln = Typed(expected_type=LineProperties, allow_none=True)
highlight = Typed(expected_type=Color, allow_none=True)
latin = Typed(expected_type=Font, allow_none=True)
ea = Typed(expected_type=Font, allow_none=True)
cs = Typed(expected_type=Font, allow_none=True)
sym = Typed(expected_type=Font, allow_none=True)
hlinkClick = Typed(expected_type=Hyperlink, allow_none=True)
hlinkMouseOver = Typed(expected_type=Hyperlink, allow_none=True)
rtl = NestedBool(allow_none=True)
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
# uses element group EG_FillProperties
noFill = EmptyTag(namespace=DRAWING_NS)
solidFill = ColorChoiceDescriptor()
gradFill = Typed(expected_type=GradientFillProperties, allow_none=True)
blipFill = Typed(expected_type=BlipFillProperties, allow_none=True)
pattFill = Typed(expected_type=PatternFillProperties, allow_none=True)
grpFill = EmptyTag(namespace=DRAWING_NS)
# uses element group EG_EffectProperties
effectLst = Typed(expected_type=EffectList, allow_none=True)
effectDag = Typed(expected_type=EffectContainer, allow_none=True)
# uses element group EG_TextUnderlineLine
uLnTx = EmptyTag()
uLn = Typed(expected_type=LineProperties, allow_none=True)
# uses element group EG_TextUnderlineFill
uFillTx = EmptyTag()
uFill = EmptyTag()
__elements__ = ('ln', 'highlight', 'latin', 'ea', 'cs', 'sym',
'hlinkClick', 'hlinkMouseOver', 'rtl', 'noFill',
'solidFill', 'gradFill', 'blipFill', 'pattFill', 'grpFill',
'effectLst', 'effectDag', 'uLnTx', 'uLn', 'uFillTx',
'uFill')
def __init__(
self,
kumimoji=None,
lang=None,
altLang=None,
sz=None,
b=None,
i=None,
u=None,
strike=None,
kern=None,
cap=None,
spc=None,
normalizeH=None,
baseline=None,
noProof=None,
dirty=None,
err=None,
smtClean=None,
smtId=None,
bmk=None,
ln=None,
highlight=None,
latin=None,
ea=None,
cs=None,
sym=None,
hlinkClick=None,
hlinkMouseOver=None,
rtl=None,
extLst=None,
noFill=None,
solidFill=None,
gradFill=None,
blipFill=None,
pattFill=None,
grpFill=None,
effectLst=None,
effectDag=None,
uLnTx=None,
uLn=None,
uFillTx=None,
uFill=None,
):
self.kumimoji = kumimoji
self.lang = lang
self.altLang = altLang
self.sz = sz
self.b = b
self.i = i
self.u = u
self.strike = strike
self.kern = kern
self.cap = cap
self.spc = spc
self.normalizeH = normalizeH
self.baseline = baseline
self.noProof = noProof
self.dirty = dirty
self.err = err
self.smtClean = smtClean
self.smtId = smtId
self.bmk = bmk
self.ln = ln
self.highlight = highlight
self.latin = latin
self.ea = ea
self.cs = cs
self.sym = sym
self.hlinkClick = hlinkClick
self.hlinkMouseOver = hlinkMouseOver
self.rtl = rtl
self.noFill = noFill
self.solidFill = solidFill
self.gradFill = gradFill
self.blipFill = blipFill
self.pattFill = pattFill
self.grpFill = grpFill
self.effectLst = effectLst
self.effectDag = effectDag
self.uLnTx = uLnTx
self.uLn = uLn
self.uFillTx = uFillTx
self.uFill = uFill
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)
class NamedStyle(Serialisable):
"""
Named and editable styles
"""
font = Typed(expected_type=Font)
fill = Typed(expected_type=Fill)
border = Typed(expected_type=Border)
alignment = Typed(expected_type=Alignment)
number_format = NumberFormatDescriptor()
protection = Typed(expected_type=Protection)
builtinId = Integer(allow_none=True)
hidden = Bool(allow_none=True)
xfId = Integer(allow_none=True)
name = String()
def __init__(
self,
name="Normal",
font=Font(),
fill=PatternFill(),
border=Border(),
alignment=Alignment(),
number_format=None,
protection=Protection(),
builtinId=None,
hidden=False,
xfId=None,
):
self.name = name
self.font = font
self.fill = fill
self.border = border
self.alignment = alignment
self.number_format = number_format
self.protection = protection
self.builtinId = builtinId
self.hidden = hidden
self._wb = None
self._style = StyleArray()
def __setattr__(self, attr, value):
super(NamedStyle, self).__setattr__(attr, value)
if getattr(self, '_wb', None) and attr in (
'font',
'fill',
'border',
'alignment',
'number_format',
'protection',
):
self._recalculate()
def __iter__(self):
for key in ('name', 'builtinId', 'hidden', 'xfId'):
value = getattr(self, key, None)
if value is not None:
yield key, safe_string(value)
@property
def xfId(self):
"""
Index of the style in the list of named styles
"""
return self._style.xfId
def _set_index(self, idx):
"""
Allow the containing list to set the index
"""
self._style.xfId = idx
def bind(self, wb):
"""
Bind a named style to a workbook
"""
self._wb = wb
self._recalculate()
def _recalculate(self):
self._style.fontId = self._wb._fonts.add(self.font)
self._style.borderId = self._wb._borders.add(self.border)
self._style.fillId = self._wb._fills.add(self.fill)
self._style.protectionId = self._wb._protections.add(self.protection)
self._style.alignmentId = self._wb._alignments.add(self.alignment)
fmt = self.number_format
if fmt in BUILTIN_FORMATS_REVERSE:
fmt = BUILTIN_FORMATS_REVERSE[fmt]
else:
fmt = self._wb._number_formats.add(self.number_format) + 164
self._style.numFmtId = fmt
def as_tuple(self):
"""Return a style array representing the current style"""
return self._style
def as_xf(self):
"""
Return equivalent XfStyle
"""
xf = CellStyle.from_array(self._style)
xf.xfId = None
xf.pivotButton = None
xf.quotePrefix = None
return xf
def as_name(self):
"""
Return relevant named style
"""
named = _NamedCellStyle(name=self.name,
builtinId=self.builtinId,
hidden=self.hidden,
xfId=self.xfId)
return named
class DataValidation(Serialisable):
tagname = "dataValidation"
showErrorMessage = Bool()
showDropDown = Bool(allow_none=True)
hide_drop_down = Alias('showDropDown')
showInputMessage = Bool()
showErrorMessage = Bool()
allowBlank = Bool()
allow_blank = Alias('allowBlank')
errorTitle = String(allow_none=True)
error = String(allow_none=True)
promptTitle = String(allow_none=True)
prompt = String(allow_none=True)
formula1 = NestedText(allow_none=True, expected_type=unicode)
formula2 = NestedText(allow_none=True, expected_type=unicode)
type = NoneSet(values=("whole", "decimal", "list", "date", "time",
"textLength", "custom"))
errorStyle = NoneSet(values=("stop", "warning", "information"))
imeMode = NoneSet(values=("noControl", "off", "on", "disabled", "hiragana",
"fullKatakana", "halfKatakana", "fullAlpha",
"halfAlpha", "fullHangul", "halfHangul"))
operator = NoneSet(values=("between", "notBetween", "equal", "notEqual",
"lessThan", "lessThanOrEqual", "greaterThan",
"greaterThanOrEqual"))
validation_type = Alias('type')
def __init__(
self,
type=None,
formula1=None,
formula2=None,
allow_blank=False,
showErrorMessage=True,
showInputMessage=True,
showDropDown=None,
allowBlank=None,
sqref=None,
promptTitle=None,
errorStyle=None,
error=None,
prompt=None,
errorTitle=None,
imeMode=None,
operator=None,
):
self.showDropDown = showDropDown
self.imeMode = imeMode
self.operator = operator
self.formula1 = formula1
self.formula2 = formula2
if allow_blank is not None:
allowBlank = allow_blank
self.allowBlank = allowBlank
self.showErrorMessage = showErrorMessage
self.showInputMessage = showInputMessage
self.type = type
self.cells = set()
self.ranges = []
if sqref is not None:
self.sqref = sqref
self.promptTitle = promptTitle
self.errorStyle = errorStyle
self.error = error
self.prompt = prompt
self.errorTitle = errorTitle
self.__attrs__ = DataValidation.__attrs__ + ('sqref', )
def add(self, cell):
"""Adds a openpyxl.cell to this validator"""
self.cells.add(cell.coordinate)
@property
def sqref(self):
return collapse_cell_addresses(self.cells, self.ranges)
@sqref.setter
def sqref(self, range_string):
self.cells = expand_cell_ranges(range_string)
class ChartBase(Serialisable):
"""
Base class for all charts
"""
legend = Typed(expected_type=Legend, allow_none=True)
layout = Typed(expected_type=Layout, allow_none=True)
roundedCorners = Bool(allow_none=True)
axId = ValueSequence(expected_type=int)
visible_cells_only = Bool()
display_blanks = Set(values=['span', 'gap', 'zero'])
_series_type = ""
ser = ()
series = Alias('ser')
title = TitleDescriptor()
anchor = "E15" # default anchor position
width = 15 # in cm, approx 5 rows
height = 7.5 # in cm, approx 14 rows
_id = 1
_path = "/xl/charts/chart{0}.xml"
style = MinMax(allow_none=True, min=1, max=48)
mime_type = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml"
graphical_properties = Typed(expected_type=GraphicalProperties, allow_none=True)
__elements__ = ()
def __init__(self, axId=(), **kw):
self._charts = [self]
self.title = None
self.layout = None
self.roundedCorners = None
self.legend = Legend()
self.graphical_properties = None
self.style = None
self.plot_area = PlotArea()
self.axId = axId
self.display_blanks = 'gap'
def __hash__(self):
"""
Just need to check for identity
"""
return id(self)
def __iadd__(self, other):
"""
Combine the chart with another one
"""
if not isinstance(other, ChartBase):
raise TypeError("Only other charts can be added")
self._charts.append(other)
return self
def to_tree(self, namespace=None, tagname=None, idx=None):
self.axId = [id for id in self._axes]
if self.ser is not None:
for s in self.ser:
s.__elements__ = attribute_mapping[self._series_type]
return super(ChartBase, self).to_tree(tagname, idx)
def _write(self):
from .chartspace import ChartSpace, ChartContainer
self.plot_area.layout = self.layout
idx_base = 0
for chart in self._charts:
if chart not in self.plot_area._charts:
chart.idx_base = idx_base
idx_base += len(chart.series)
self.plot_area._charts = self._charts
container = ChartContainer(plotArea=self.plot_area, legend=self.legend, title=self.title)
if isinstance(chart, _3DBase):
container.view3D = chart.view3D
container.floor = chart.floor
container.sideWall = chart.sideWall
container.backWall = chart.backWall
container.plotVisOnly = self.visible_cells_only
container.dispBlanksAs = self.display_blanks
cs = ChartSpace(chart=container)
cs.style = self.style
cs.roundedCorners = self.roundedCorners
return cs.to_tree()
@property
def _axes(self):
x = getattr(self, "x_axis", None)
y = getattr(self, "y_axis", None)
z = getattr(self, "z_axis", None)
return OrderedDict([(axis.axId, axis) for axis in (x, y, z) if axis])
def set_categories(self, labels):
"""
Set the categories / x-axis values
"""
if not isinstance(labels, Reference):
labels = Reference(range_string=labels)
for s in self.ser:
s.cat = AxDataSource(numRef=NumRef(f=labels))
def add_data(self, data, from_rows=False, titles_from_data=False):
"""
Add a range of data in a single pass.
The default is to treat each column as a data series.
"""
if not isinstance(data, Reference):
data = Reference(range_string=data)
if from_rows:
values = data.rows
else:
values = data.cols
for v in values:
range_string = u"{0}!{1}:{2}".format(data.sheetname, v[0], v[-1])
series = SeriesFactory(range_string, title_from_data=titles_from_data)
self.ser.append(series)
def append(self, value):
"""Append a data series to the chart"""
l = self.series[:]
l.append(value)
self.series = l
@property
def path(self):
return self._path.format(self._id)
class Properties(Serialisable):
locked = Bool(allow_none=True)
defaultSize = Bool(allow_none=True)
_print = Bool(allow_none=True)
disabled = Bool(allow_none=True)
uiObject = Bool(allow_none=True)
autoFill = Bool(allow_none=True)
autoLine = Bool(allow_none=True)
altText = String(allow_none=True)
textHAlign = Set(
values=(['left', 'center', 'right', 'justify', 'distributed']))
textVAlign = Set(
values=(['top', 'center', 'bottom', 'justify', 'distributed']))
lockText = Bool(allow_none=True)
justLastX = Bool(allow_none=True)
autoScale = Bool(allow_none=True)
rowHidden = Bool(allow_none=True)
colHidden = Bool(allow_none=True)
#anchor = Typed(expected_type=ObjectAnchor, )
__elements__ = ('anchor', )
def __init__(
self,
locked=None,
defaultSize=None,
_print=None,
disabled=None,
uiObject=None,
autoFill=None,
autoLine=None,
altText=None,
textHAlign=None,
textVAlign=None,
lockText=None,
justLastX=None,
autoScale=None,
rowHidden=None,
colHidden=None,
anchor=None,
):
self.locked = locked
self.defaultSize = defaultSize
self._print = _print
self.disabled = disabled
self.uiObject = uiObject
self.autoFill = autoFill
self.autoLine = autoLine
self.altText = altText
self.textHAlign = textHAlign
self.textVAlign = textVAlign
self.lockText = lockText
self.justLastX = justLastX
self.autoScale = autoScale
self.rowHidden = rowHidden
self.colHidden = colHidden
self.anchor = anchor
class SharedItems(Serialisable):
containsSemiMixedTypes = Bool(allow_none=True)
containsNonDate = Bool(allow_none=True)
containsDate = Bool(allow_none=True)
containsString = Bool(allow_none=True)
containsBlank = Bool(allow_none=True)
containsMixedTypes = Bool(allow_none=True)
containsNumber = Bool(allow_none=True)
containsInteger = Bool(allow_none=True)
minValue = Float(allow_none=True)
maxValue = Float(allow_none=True)
minDate = Typed(expected_type=PivotDateTime, allow_none=True)
maxDate = Typed(expected_type=PivotDateTime, allow_none=True)
count = Integer(allow_none=True)
longText = Bool(allow_none=True)
# some elements are choice
m = Typed(expected_type=Missing, )
n = Typed(expected_type=Number, )
b = Bool(nested=True, )
e = Typed(expected_type=Error, )
s = Typed(expected_type=String, )
d = Typed(expected_type=DateTime, )
__elements__ = ('m', 'n', 'b', 'e', 's', 'd')
def __init__(self,
containsSemiMixedTypes=None,
containsNonDate=None,
containsDate=None,
containsString=None,
containsBlank=None,
containsMixedTypes=None,
containsNumber=None,
containsInteger=None,
minValue=None,
maxValue=None,
minDate=None,
maxDate=None,
count=None,
longText=None,
m=None,
n=None,
b=None,
e=None,
s=None,
d=None,
):
self.containsSemiMixedTypes = containsSemiMixedTypes
self.containsNonDate = containsNonDate
self.containsDate = containsDate
self.containsString = containsString
self.containsBlank = containsBlank
self.containsMixedTypes = containsMixedTypes
self.containsNumber = containsNumber
self.containsInteger = containsInteger
self.minValue = minValue
self.maxValue = maxValue
self.minDate = minDate
self.maxDate = maxDate
self.count = count
self.longText = longText
self.m = m
self.n = n
self.b = b
self.e = e
self.s = s
self.d = d
class DefinedName(Serialisable):
tagname = "definedName"
name = String() # unique per workbook/worksheet
comment = String(allow_none=True)
customMenu = String(allow_none=True)
description = String(allow_none=True)
help = String(allow_none=True)
statusBar = String(allow_none=True)
localSheetId = Integer(allow_none=True)
hidden = Bool(allow_none=True)
function = Bool(allow_none=True)
vbProcedure = Bool(allow_none=True)
xlm = Bool(allow_none=True)
functionGroupId = Integer(allow_none=True)
shortcutKey = String(allow_none=True)
publishToServer = Bool(allow_none=True)
workbookParameter = Bool(allow_none=True)
attr_text = Descriptor()
value = Alias("attr_text")
def __init__(self,
name=None,
comment=None,
customMenu=None,
description=None,
help=None,
statusBar=None,
localSheetId=None,
hidden=None,
function=None,
vbProcedure=None,
xlm=None,
functionGroupId=None,
shortcutKey=None,
publishToServer=None,
workbookParameter=None,
attr_text=None
):
self.name = name
self.comment = comment
self.customMenu = customMenu
self.description = description
self.help = help
self.statusBar = statusBar
self.localSheetId = localSheetId
self.hidden = hidden
self.function = function
self.vbProcedure = vbProcedure
self.xlm = xlm
self.functionGroupId = functionGroupId
self.shortcutKey = shortcutKey
self.publishToServer = publishToServer
self.workbookParameter = workbookParameter
self.attr_text = attr_text
@property
def type(self):
tok = Tokenizer("=" + self.value)
parsed = tok.items[0]
if parsed.type == "OPERAND":
return parsed.subtype
return parsed.type
@property
def destinations(self):
if self.type == "RANGE":
tok = Tokenizer("=" + self.value)
for part in tok.items:
if part.subtype == "RANGE":
m = SHEETRANGE_RE.match(part.value)
sheetname = m.group('notquoted') or m.group('quoted')
yield sheetname, m.group('cells')
@property
def is_reserved(self):
m = RESERVED_REGEX.match(self.name)
if m:
return m.group("name")
@property
def is_external(self):
return re.compile(r"^\[\d+\].*").match(self.value) is not None
def __iter__(self):
for key in self.__attrs__:
if key == "attr_text":
continue
v = getattr(self, key)
if v is not None:
if v in RESERVED:
v = "_xlnm." + v
yield key, safe_string(v)
class PivotCacheDefinition(Serialisable):
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()
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 = Typed(expected_type=CacheFields, )
cacheHierarchies = Typed(expected_type=CacheHierarchies, allow_none=True)
kpis = Typed(expected_type=PCDKPIs, allow_none=True)
tupleCache = Typed(expected_type=TupleCache, allow_none=True)
calculatedItems = Typed(expected_type=CalculatedItems, allow_none=True)
calculatedMembers = Typed(expected_type=CalculatedMembers, allow_none=True)
dimensions = Typed(expected_type=Dimensions, allow_none=True)
measureGroups = Typed(expected_type=MeasureGroups, allow_none=True)
maps = Typed(expected_type=MeasureDimensionMaps, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('cacheSource', 'cacheFields', 'cacheHierarchies', 'kpis', 'tupleCache', 'calculatedItems', 'calculatedMembers', 'dimensions', 'measureGroups', 'maps', 'extLst')
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=None,
cacheHierarchies=None,
kpis=None,
calculatedItems=None,
calculatedMembers=None,
dimensions=None,
measureGroups=None,
maps=None,
extLst=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.extLst = extLst
class SheetProtection(Serialisable, _Protected):
"""
Information about protection of various aspects of a sheet. True values
mean that protection for the object or action is active This is the
**default** when protection is active, ie. users cannot do something
"""
tagname = "sheetProtection"
sheet = Bool()
enabled = Alias('sheet')
objects = Bool()
scenarios = Bool()
formatCells = Bool()
formatColumns = Bool()
formatRows = Bool()
insertColumns = Bool()
insertRows = Bool()
insertHyperlinks = Bool()
deleteColumns = Bool()
deleteRows = Bool()
selectLockedCells = Bool()
selectUnlockedCells = Bool()
sort = Bool()
autoFilter = Bool()
pivotTables = Bool()
saltValue = Base64Binary(allow_none=True)
spinCount = Integer(allow_none=True)
algorithmName = String(allow_none=True)
hashValue = Base64Binary(allow_none=True)
__attrs__ = ('selectLockedCells', 'selectUnlockedCells', 'algorithmName',
'sheet', 'objects', 'insertRows', 'insertHyperlinks',
'autoFilter', 'scenarios', 'formatColumns', 'deleteColumns',
'insertColumns', 'pivotTables', 'deleteRows', 'formatCells',
'saltValue', 'formatRows', 'sort', 'spinCount', 'password',
'hashValue')
def __init__(self,
sheet=False,
objects=False,
scenarios=False,
formatCells=True,
formatRows=True,
formatColumns=True,
insertColumns=True,
insertRows=True,
insertHyperlinks=True,
deleteColumns=True,
deleteRows=True,
selectLockedCells=False,
selectUnlockedCells=False,
sort=True,
autoFilter=True,
pivotTables=True,
password=None,
algorithmName=None,
saltValue=None,
spinCount=None,
hashValue=None):
self.sheet = sheet
self.objects = objects
self.scenarios = scenarios
self.formatCells = formatCells
self.formatColumns = formatColumns
self.formatRows = formatRows
self.insertColumns = insertColumns
self.insertRows = insertRows
self.insertHyperlinks = insertHyperlinks
self.deleteColumns = deleteColumns
self.deleteRows = deleteRows
self.selectLockedCells = selectLockedCells
self.selectUnlockedCells = selectUnlockedCells
self.sort = sort
self.autoFilter = autoFilter
self.pivotTables = pivotTables
if password is not None:
self.password = password
self.algorithmName = algorithmName
self.saltValue = saltValue
self.spinCount = spinCount
self.hashValue = hashValue
def set_password(self, value='', already_hashed=False):
super(SheetProtection, self).set_password(value, already_hashed)
self.enable()
def enable(self):
self.sheet = True
def disable(self):
self.sheet = False
def __bool__(self):
return self.sheet
class PivotAreaReference(Serialisable):
field = Integer(allow_none=True)
count = Integer()
selected = Bool()
byPosition = Bool()
relative = Bool()
defaultSubtotal = Bool()
sumSubtotal = Bool()
countASubtotal = Bool()
avgSubtotal = Bool()
maxSubtotal = Bool()
minSubtotal = Bool()
productSubtotal = Bool()
countSubtotal = Bool()
stdDevSubtotal = Bool()
stdDevPSubtotal = Bool()
varSubtotal = Bool()
varPSubtotal = Bool()
x = Typed(expected_type=Index, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('x', 'extLst')
def __init__(self,
field=None,
count=None,
selected=None,
byPosition=None,
relative=None,
defaultSubtotal=None,
sumSubtotal=None,
countASubtotal=None,
avgSubtotal=None,
maxSubtotal=None,
minSubtotal=None,
productSubtotal=None,
countSubtotal=None,
stdDevSubtotal=None,
stdDevPSubtotal=None,
varSubtotal=None,
varPSubtotal=None,
x=None,
extLst=None,
):
self.field = field
self.count = count
self.selected = selected
self.byPosition = byPosition
self.relative = relative
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.x = x
self.extLst = extLst
class Rule(Serialisable):
tagname = "cfRule"
type = Set(values=([
'expression', 'cellIs', 'colorScale', 'dataBar', 'iconSet', 'top10',
'uniqueValues', 'duplicateValues', 'containsText', 'notContainsText',
'beginsWith', 'endsWith', 'containsBlanks', 'notContainsBlanks',
'containsErrors', 'notContainsErrors', 'timePeriod', 'aboveAverage'
]))
dxfId = Integer(allow_none=True)
priority = Integer()
stopIfTrue = Bool(allow_none=True)
aboveAverage = Bool(allow_none=True)
percent = Bool(allow_none=True)
bottom = Bool(allow_none=True)
operator = NoneSet(values=([
'lessThan', 'lessThanOrEqual', 'equal', 'notEqual',
'greaterThanOrEqual', 'greaterThan', 'between', 'notBetween',
'containsText', 'notContains', 'beginsWith', 'endsWith'
]))
text = String(allow_none=True)
timePeriod = NoneSet(values=([
'today', 'yesterday', 'tomorrow', 'last7Days', 'thisMonth',
'lastMonth', 'nextMonth', 'thisWeek', 'lastWeek', 'nextWeek'
]))
rank = Integer(allow_none=True)
stdDev = Integer(allow_none=True)
equalAverage = Bool(allow_none=True)
formula = Sequence(expected_type=str)
colorScale = Typed(expected_type=ColorScale, allow_none=True)
dataBar = Typed(expected_type=DataBar, allow_none=True)
iconSet = Typed(expected_type=IconSet, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
style = Typed(expected_type=DifferentialStyle, allow_none=True)
__elements__ = ('colorScale', 'dataBar', 'extLst', 'iconSet', 'formula')
def __init__(
self,
type,
dxfId=None,
priority=None,
stopIfTrue=None,
aboveAverage=None,
percent=None,
bottom=None,
operator=None,
text=None,
timePeriod=None,
rank=None,
stdDev=None,
equalAverage=None,
formula=[],
colorScale=None,
dataBar=None,
iconSet=None,
extLst=None,
style=None,
):
self.type = type
self.dxfId = dxfId
self.priority = priority
self.stopIfTrue = stopIfTrue
self.aboveAverage = aboveAverage
self.percent = percent
self.bottom = bottom
self.operator = operator
self.text = text
self.timePeriod = timePeriod
self.rank = rank
self.stdDev = stdDev
self.equalAverage = equalAverage
self.formula = formula
self.colorScale = colorScale
self.dataBar = dataBar
self.iconSet = iconSet
self.extLst = extLst
self.style = style
class Color(Serialisable):
"""Named colors for use in styles."""
tagname = "color"
rgb = RGB()
indexed = Integer()
auto = Bool()
theme = Integer()
tint = MinMax(min=-1, max=1, expected_type=float)
type = String()
def __init__(self,
rgb=BLACK,
indexed=None,
auto=None,
theme=None,
tint=0.0,
index=None,
type='rgb'):
if index is not None:
indexed = index
if indexed is not None:
self.type = 'indexed'
self.indexed = indexed
elif theme is not None:
self.type = 'theme'
self.theme = theme
elif auto is not None:
self.type = 'auto'
self.auto = auto
else:
self.rgb = rgb
self.type = 'rgb'
self.tint = tint
@property
def value(self):
return getattr(self, self.type)
@value.setter
def value(self, value):
setattr(self, self.type, value)
def __iter__(self):
attrs = [(self.type, self.value)]
if self.tint != 0:
attrs.append(('tint', self.tint))
for k, v in attrs:
yield k, safe_string(v)
@property
def index(self):
# legacy
return self.value
def __add__(self, other):
"""
Adding colours is undefined behaviour best do nothing
"""
if not isinstance(other, Color):
return super(Color, self).__add__(other)
return self
