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 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 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"
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 = 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"))
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
def to_tree(self, tagname=None):
attrs = dict(self)
el = Element(self.tagname, attrs)
for n in self.__nested__:
value = getattr(self, n)
if value:
SubElement(el, n).text = value
return el
@deprecated("Use DataValidation.add(). Will be removed in 2.4")
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 Will be removed in 2.4"
)
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 Will be removed in 2.4"
)
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 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 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 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 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=unicode)
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)
dxf = Typed(expected_type=DifferentialStyle, allow_none=True)
__elements__ = ('colorScale', 'dataBar', 'iconSet', 'formula')
__attrs__ = ('type', 'rank', 'priority', 'equalAverage', 'operator',
'aboveAverage', 'dxfId', 'stdDev', 'stopIfTrue', 'timePeriod', 'text',
'percent', 'bottom')
def __init__(self,
type,
dxfId=None,
priority=0,
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,
dxf=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.dxf = dxf
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 Font(HashableObject):
"""Font options used in styles."""
spec = """18.8.22, p.3930"""
UNDERLINE_DOUBLE = 'double'
UNDERLINE_DOUBLE_ACCOUNTING = 'doubleAccounting'
UNDERLINE_SINGLE = 'single'
UNDERLINE_SINGLE_ACCOUNTING = 'singleAccounting'
name = String()
charset = Integer(allow_none=True)
family = MinMax(min=0, max=14)
sz = Float()
size = Alias("sz")
b = Bool()
bold = Alias("b")
i = Bool()
italic = Alias("i")
strike = Bool()
strikethrough = Alias("strike")
outline = Bool()
shadow = Bool()
condense = Bool()
extend = Bool()
u = NoneSet(values=(UNDERLINE_DOUBLE, UNDERLINE_DOUBLE_ACCOUNTING,
UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACCOUNTING))
underline = Alias("u")
vertAlign = NoneSet(values=('superscript', 'subscript', 'baseline'))
color = ColorDescriptor()
scheme = NoneSet(values=("major", "minor"))
__fields__ = (
'name',
'sz',
'b',
'i',
'u',
'strike',
'color',
'vertAlign',
'charset',
'outline',
'shadow',
'condense',
'extend',
'family',
)
def __init__(self,
name='Calibri',
sz=11,
b=False,
i=False,
charset=None,
u=None,
strike=False,
color=BLACK,
scheme=None,
family=2,
size=None,
bold=None,
italic=None,
strikethrough=None,
underline=None,
vertAlign=None,
outline=False,
shadow=False,
condense=False,
extend=False):
self.name = name
self.family = family
if size is not None:
sz = size
self.sz = sz
if bold is not None:
b = bold
self.b = b
if italic is not None:
i = italic
self.i = i
if underline is not None:
u = underline
self.u = u
if strikethrough is not None:
strike = strikethrough
self.strike = strike
self.color = color
self.vertAlign = vertAlign
self.charset = charset
self.outline = outline
self.shadow = shadow
self.condense = condense
self.extend = extend
self.scheme = scheme
class LineProperties(Serialisable):
tagname = "ln"
namespace = DRAWING_NS
w = MinMax(min=0, max=20116800, allow_none=True) # EMU
width = Alias('w')
cap = NoneSet(values=(['rnd', 'sq', 'flat']))
cmpd = NoneSet(values=(['sng', 'dbl', 'thickThin', 'thinThick', 'tri']))
algn = NoneSet(values=(['ctr', 'in']))
noFill = EmptyTag()
solidFill = ColorChoiceDescriptor()
gradFill = Typed(expected_type=GradientFillProperties, allow_none=True)
pattFill = Typed(expected_type=PatternFillProperties, allow_none=True)
prstDash = NestedNoneSet(values=(['solid', 'dot', 'dash', 'lgDash', 'dashDot',
'lgDashDot', 'lgDashDotDot', 'sysDash', 'sysDot', 'sysDashDot',
'sysDashDotDot']), namespace=namespace)
dashStyle = Alias('prstDash')
custDash = Typed(expected_type=DashStop, allow_none=True)
round = EmptyTag()
bevel = EmptyTag()
miter = NestedInteger(allow_none=True, attribute="lim")
headEnd = Typed(expected_type=LineEndProperties, allow_none=True)
tailEnd = Typed(expected_type=LineEndProperties, allow_none=True)
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
__elements__ = ('noFill', 'solidFill', 'gradFill', 'pattFill',
'prstDash', 'custDash', 'round', 'bevel', 'miter', 'headEnd', 'tailEnd')
def __init__(self,
w=None,
cap=None,
cmpd=None,
algn=None,
noFill=None,
solidFill=None,
gradFill=None,
pattFill=None,
prstDash=None,
custDash=None,
round=None,
bevel=None,
miter=None,
headEnd=None,
tailEnd=None,
extLst=None,
):
self.w = w
self.cap = cap
self.cmpd = cmpd
self.algn = algn
self.noFill = noFill
self.solidFill = solidFill
self.gradFill = gradFill
self.pattFill = pattFill
if prstDash is None:
prstDash = "solid"
self.prstDash = prstDash
self.custDash = custDash
self.round = round
self.bevel = bevel
self.miter = miter
self.headEnd = headEnd
self.tailEnd = tailEnd
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 TableColumn(Serialisable):
tagname = "tableColumn"
id = Integer()
uniqueName = String(allow_none=True)
name = String()
totalsRowFunction = NoneSet(values=([
'sum', 'min', 'max', 'average', 'count', 'countNums', 'stdDev', 'var',
'custom'
]))
totalsRowLabel = String(allow_none=True)
queryTableFieldId = Integer(allow_none=True)
headerRowDxfId = Integer(allow_none=True)
dataDxfId = Integer(allow_none=True)
totalsRowDxfId = Integer(allow_none=True)
headerRowCellStyle = String(allow_none=True)
dataCellStyle = String(allow_none=True)
totalsRowCellStyle = String(allow_none=True)
calculatedColumnFormula = Typed(expected_type=TableFormula,
allow_none=True)
totalsRowFormula = Typed(expected_type=TableFormula, allow_none=True)
xmlColumnPr = Typed(expected_type=XMLColumnProps, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('calculatedColumnFormula', 'totalsRowFormula',
'xmlColumnPr', 'extLst')
def __init__(
self,
id=None,
uniqueName=None,
name=None,
totalsRowFunction=None,
totalsRowLabel=None,
queryTableFieldId=None,
headerRowDxfId=None,
dataDxfId=None,
totalsRowDxfId=None,
headerRowCellStyle=None,
dataCellStyle=None,
totalsRowCellStyle=None,
calculatedColumnFormula=None,
totalsRowFormula=None,
xmlColumnPr=None,
extLst=None,
):
self.id = id
self.uniqueName = uniqueName
self.name = name
self.totalsRowFunction = totalsRowFunction
self.totalsRowLabel = totalsRowLabel
self.queryTableFieldId = queryTableFieldId
self.headerRowDxfId = headerRowDxfId
self.dataDxfId = dataDxfId
self.totalsRowDxfId = totalsRowDxfId
self.headerRowCellStyle = headerRowCellStyle
self.dataCellStyle = dataCellStyle
self.totalsRowCellStyle = totalsRowCellStyle
self.calculatedColumnFormula = calculatedColumnFormula
self.totalsRowFormula = totalsRowFormula
self.xmlColumnPr = xmlColumnPr
self.extLst = extLst
def __iter__(self):
for k, v in super(TableColumn, self).__iter__():
if k == 'name':
v = escape(v)
yield k, v
@classmethod
def from_tree(cls, node):
self = super(TableColumn, cls).from_tree(node)
self.name = unescape(self.name)
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 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 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 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 = Typed(expected_type=PivotCacheList, 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=None,
smartTagPr=None,
smartTagTypes=None,
webPublishing=None,
fileRecoveryPr=None,
webPublishObjects=None,
extLst=None,
Ignorable=None,
):
self.conformance = conformance
self.fileVersion = fileVersion
self.fileSharing = fileSharing
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 CustomWorkbookView(Serialisable):
tagname = "customWorkbookView"
name = String()
guid = Guid()
autoUpdate = Bool(allow_none=True)
mergeInterval = Integer(allow_none=True)
changesSavedWin = Bool(allow_none=True)
onlySync = Bool(allow_none=True)
personalView = Bool(allow_none=True)
includePrintSettings = Bool(allow_none=True)
includeHiddenRowCol = Bool(allow_none=True)
maximized = Bool(allow_none=True)
minimized = Bool(allow_none=True)
showHorizontalScroll = Bool(allow_none=True)
showVerticalScroll = Bool(allow_none=True)
showSheetTabs = Bool(allow_none=True)
xWindow = Integer(allow_none=True)
yWindow = Integer(allow_none=True)
windowWidth = Integer()
windowHeight = Integer()
tabRatio = Integer(allow_none=True)
activeSheetId = Integer()
showFormulaBar = Bool(allow_none=True)
showStatusbar = Bool(allow_none=True)
showComments = NoneSet(values=(['commNone', 'commIndicator',
'commIndAndComment']))
showObjects = NoneSet(values=(['all', 'placeholders']))
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ()
def __init__(self,
name=None,
guid=None,
autoUpdate=None,
mergeInterval=None,
changesSavedWin=None,
onlySync=None,
personalView=None,
includePrintSettings=None,
includeHiddenRowCol=None,
maximized=None,
minimized=None,
showHorizontalScroll=None,
showVerticalScroll=None,
showSheetTabs=None,
xWindow=None,
yWindow=None,
windowWidth=None,
windowHeight=None,
tabRatio=None,
activeSheetId=None,
showFormulaBar=None,
showStatusbar=None,
showComments="commIndicator",
showObjects="all",
extLst=None,
):
self.name = name
self.guid = guid
self.autoUpdate = autoUpdate
self.mergeInterval = mergeInterval
self.changesSavedWin = changesSavedWin
self.onlySync = onlySync
self.personalView = personalView
self.includePrintSettings = includePrintSettings
self.includeHiddenRowCol = includeHiddenRowCol
self.maximized = maximized
self.minimized = minimized
self.showHorizontalScroll = showHorizontalScroll
self.showVerticalScroll = showVerticalScroll
self.showSheetTabs = showSheetTabs
self.xWindow = xWindow
self.yWindow = yWindow
self.windowWidth = windowWidth
self.windowHeight = windowHeight
self.tabRatio = tabRatio
self.activeSheetId = activeSheetId
self.showFormulaBar = showFormulaBar
self.showStatusbar = showStatusbar
self.showComments = showComments
self.showObjects = showObjects
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 Font(HashableObject):
"""Font options used in styles."""
spec = """18.8.22, p.3930"""
UNDERLINE_DOUBLE = 'double'
UNDERLINE_DOUBLE_ACCOUNTING = 'doubleAccounting'
UNDERLINE_SINGLE = 'single'
UNDERLINE_SINGLE_ACCOUNTING = 'singleAccounting'
name = String(nested=True)
charset = Integer(allow_none=True, nested=True)
family = MinMax(min=0, max=14, nested=True)
sz = Float(nested=True)
size = Alias("sz")
b = Bool(nested=True)
bold = Alias("b")
i = Bool(nested=True)
italic = Alias("i")
strike = Bool(nested=True)
strikethrough = Alias("strike")
outline = Bool(nested=True)
shadow = Bool(nested=True)
condense = Bool(nested=True)
extend = Bool(nested=True)
u = NoneSet(values=('single', 'double', 'singleAccounting',
'doubleAccounting'), nested=True
)
underline = Alias("u")
vertAlign = NoneSet(values=('superscript', 'subscript', 'baseline'), nested=True)
color = ColorDescriptor()
scheme = NoneSet(values=("major", "minor"), nested=True)
tagname = "font"
__nested__ = ('name', 'charset', 'family', 'b', 'i', 'strike', 'outline',
'shadow', 'condense', 'extend', 'sz', 'u', 'vertAlign',
'scheme')
__fields__ = ('name', 'charset', 'family', 'b', 'i', 'strike', 'outline',
'shadow', 'condense', 'extend', 'sz', 'u', 'vertAlign',
'scheme', 'color')
@classmethod
def _create_nested(cls, el, tag):
if tag == "u":
return el.get("val", "single")
return super(Font, cls)._create_nested(el, tag)
def to_tree(self, tagname=None):
el = Element(self.tagname)
attrs = list(self.__nested__)
attrs.insert(10, 'color')
for attr in attrs:
value = getattr(self, attr)
if value:
if attr == 'color':
color = value.to_tree()
el.append(color)
else:
SubElement(el, attr, val=safe_string(value))
return el
def __init__(self, name='Calibri', sz=11, b=False, i=False, charset=None,
u=None, strike=False, color=BLACK, scheme=None, family=2, size=None,
bold=None, italic=None, strikethrough=None, underline=None,
vertAlign=None, outline=False, shadow=False, condense=False,
extend=False):
self.name = name
self.family = family
if size is not None:
sz = size
self.sz = sz
if bold is not None:
b = bold
self.b = b
if italic is not None:
i = italic
self.i = i
if underline is not None:
u = underline
self.u = u
if strikethrough is not None:
strike = strikethrough
self.strike = strike
self.color = color
self.vertAlign = vertAlign
self.charset = charset
self.outline = outline
self.shadow = shadow
self.condense = condense
self.extend = extend
self.scheme = scheme
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',
'noFill',
'solidFill',
'gradFill',
'blipFill',
'pattFill',
'grpFill',
'effectLst',
'effectDag',
'highlight',
'uLnTx',
'uLn',
'uFillTx',
'uFill',
'latin',
'ea',
'cs',
'sym',
'hlinkClick',
'hlinkMouseOver',
'rtl',
)
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 PageSetup(Strict):
""" Worksheet page setup """
tag = "{%s}pageSetup" % SHEET_MAIN_NS
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,
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.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
@deprecated("this property has to be called via sheet_properties")
def fitToPage(self):
pass
def __iter__(self):
for attr in ("orientation", "paperSize", "scale", "fitToHeight",
"fitToWidth", "firstPageNumber", "useFirstPageNumber",
"paperHeight", "paperWidth", "pageOrder",
"usePrinterDefaults", "blackAndWhite", "draft",
"cellComments", "errors", "horizontalDpi", "verticalDpi",
"copies", "id"):
value = getattr(self, attr)
if value is not None:
yield attr, safe_string(value)
def write_xml_element(self):
attrs = dict(self)
if 'id' in attrs:
attrs['{%s}id' % REL_NS] = attrs['id']
del attrs['id']
return Element(self.tag, attrs)