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 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
__nonzero__ = __bool__
class Reference(Strict):
"""
Normalise cell range references
"""
min_row = MinMax(min=1, max=1000000, expected_type=int)
max_row = MinMax(min=1, max=1000000, expected_type=int)
min_col = MinMax(min=1, max=16384, expected_type=int)
max_col = MinMax(min=1, max=16384, expected_type=int)
range_string = String(allow_none=True)
def __init__(self,
worksheet=None,
min_col=None,
min_row=None,
max_col=None,
max_row=None,
range_string=None):
if range_string is not None:
sheetname, boundaries = range_to_tuple(range_string)
min_col, min_row, max_col, max_row = boundaries
worksheet = DummyWorksheet(sheetname)
self.worksheet = worksheet
self.min_col = min_col
self.min_row = min_row
if max_col is None:
max_col = min_col
self.max_col = max_col
if max_row is None:
max_row = min_row
self.max_row = max_row
def __repr__(self):
return str(self)
def __str__(self):
fmt = u"{0}!${1}${2}:${3}${4}"
if (self.min_col == self.max_col and self.min_row == self.max_row):
fmt = u"{0}!${1}${2}"
return fmt.format(self.sheetname,
get_column_letter(self.min_col), self.min_row,
get_column_letter(self.max_col), self.max_row)
__str__ = __str__
def __len__(self):
if self.min_row == self.max_row:
return 1 + self.max_col - self.min_col
return 1 + self.max_row - self.min_row
def __eq__(self, other):
return str(self) == str(other)
@property
def rows(self):
"""
Return all rows in the range
"""
for row in range(self.min_row, self.max_row + 1):
yield Reference(self.worksheet, self.min_col, row, self.max_col,
row)
@property
def cols(self):
"""
Return all columns in the range
"""
for col in range(self.min_col, self.max_col + 1):
yield Reference(self.worksheet, col, self.min_row, col,
self.max_row)
def pop(self):
"""
Return and remove the first cell
"""
cell = "{0}{1}".format(get_column_letter(self.min_col), self.min_row)
if self.min_row == self.max_row:
self.min_col += 1
else:
self.min_row += 1
return cell
@property
def sheetname(self):
return quote_sheetname(self.worksheet.title)
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,
):
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 PivotFilter(Serialisable):
fld = Integer()
mpFld = Integer(allow_none=True)
type = Set(values=([
'unknown', 'count', 'percent', 'sum', 'captionEqual',
'captionNotEqual', 'captionBeginsWith', 'captionNotBeginsWith',
'captionEndsWith', 'captionNotEndsWith', 'captionContains',
'captionNotContains', 'captionGreaterThan',
'captionGreaterThanOrEqual', 'captionLessThan',
'captionLessThanOrEqual', 'captionBetween', 'captionNotBetween',
'valueEqual', 'valueNotEqual', 'valueGreaterThan',
'valueGreaterThanOrEqual', 'valueLessThan', 'valueLessThanOrEqual',
'valueBetween', 'valueNotBetween', 'dateEqual', 'dateNotEqual',
'dateOlderThan', 'dateOlderThanOrEqual', 'dateNewerThan',
'dateNewerThanOrEqual', 'dateBetween', 'dateNotBetween', 'tomorrow',
'today', 'yesterday', 'nextWeek', 'thisWeek', 'lastWeek', 'nextMonth',
'thisMonth', 'lastMonth', 'nextQuarter', 'thisQuarter', 'lastQuarter',
'nextYear', 'thisYear', 'lastYear', 'yearToDate', 'Q1', 'Q2', 'Q3',
'Q4', 'M1', 'M2', 'M3', 'M4', 'M5', 'M6', 'M7', 'M8', 'M9', 'M10',
'M11', 'M12'
]))
evalOrder = Integer(allow_none=True)
id = Integer()
iMeasureHier = Integer(allow_none=True)
iMeasureFld = Integer(allow_none=True)
name = String(allow_none=True)
description = String()
stringValue1 = String()
stringValue2 = String()
autoFilter = Typed(expected_type=AutoFilter, )
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('autoFilter', )
def __init__(
self,
fld=None,
mpFld=None,
type=None,
evalOrder=None,
id=None,
iMeasureHier=None,
iMeasureFld=None,
name=None,
description=None,
stringValue1=None,
stringValue2=None,
autoFilter=None,
extLst=None,
):
self.fld = fld
self.mpFld = mpFld
self.type = type
self.evalOrder = evalOrder
self.id = id
self.iMeasureHier = iMeasureHier
self.iMeasureFld = iMeasureFld
self.name = name
self.description = description
self.stringValue1 = stringValue1
self.stringValue2 = stringValue2
self.autoFilter = autoFilter
self.extLst = extLst
class WorkbookProtection(Serialisable):
_workbook_password, _revisions_password = None, None
tagname = "workbookPr"
workbook_password = Alias("workbookPassword")
workbookPasswordCharacterSet = String(allow_none=True)
revision_password = Alias("revisionsPassword")
revisionsPasswordCharacterSet = String(allow_none=True)
lockStructure = Bool(allow_none=True)
lock_structure = Alias("lockStructure")
lockWindows = Bool(allow_none=True)
lock_windows = Alias("lockWindows")
lockRevision = Bool(allow_none=True)
lock_revision = Alias("lockRevision")
revisionsAlgorithmName = String(allow_none=True)
revisionsHashValue = Base64Binary(allow_none=True)
revisionsSaltValue = Base64Binary(allow_none=True)
revisionsSpinCount = Integer(allow_none=True)
workbookAlgorithmName = String(allow_none=True)
workbookHashValue = Base64Binary(allow_none=True)
workbookSaltValue = Base64Binary(allow_none=True)
workbookSpinCount = Integer(allow_none=True)
__attrs__ = ('workbookPassword', 'workbookPasswordCharacterSet', 'revisionsPassword',
'revisionsPasswordCharacterSet', 'lockStructure', 'lockWindows', 'lockRevision',
'revisionsAlgorithmName', 'revisionsHashValue', 'revisionsSaltValue',
'revisionsSpinCount', 'workbookAlgorithmName', 'workbookHashValue',
'workbookSaltValue', 'workbookSpinCount')
def __init__(self,
workbookPassword=None,
workbookPasswordCharacterSet=None,
revisionsPassword=None,
revisionsPasswordCharacterSet=None,
lockStructure=None,
lockWindows=None,
lockRevision=None,
revisionsAlgorithmName=None,
revisionsHashValue=None,
revisionsSaltValue=None,
revisionsSpinCount=None,
workbookAlgorithmName=None,
workbookHashValue=None,
workbookSaltValue=None,
workbookSpinCount=None,
):
if workbookPassword is not None:
self.workbookPassword = workbookPassword
self.workbookPasswordCharacterSet = workbookPasswordCharacterSet
if revisionsPassword is not None:
self.revisionsPassword = revisionsPassword
self.revisionsPasswordCharacterSet = revisionsPasswordCharacterSet
self.lockStructure = lockStructure
self.lockWindows = lockWindows
self.lockRevision = lockRevision
self.revisionsAlgorithmName = revisionsAlgorithmName
self.revisionsHashValue = revisionsHashValue
self.revisionsSaltValue = revisionsSaltValue
self.revisionsSpinCount = revisionsSpinCount
self.workbookAlgorithmName = workbookAlgorithmName
self.workbookHashValue = workbookHashValue
self.workbookSaltValue = workbookSaltValue
self.workbookSpinCount = workbookSpinCount
def set_workbook_password(self, value='', already_hashed=False):
"""Set a password on this workbook."""
if not already_hashed:
value = hash_password(value)
self._workbook_password = value
@property
def workbookPassword(self):
"""Return the workbook password value, regardless of hash."""
return self._workbook_password
@workbookPassword.setter
def workbookPassword(self, value):
"""Set a workbook password directly, forcing a hash step."""
self.set_workbook_password(value)
def set_revisions_password(self, value='', already_hashed=False):
"""Set a revision password on this workbook."""
if not already_hashed:
value = hash_password(value)
self._revisions_password = value
@property
def revisionsPassword(self):
"""Return the revisions password value, regardless of hash."""
return self._revisions_password
@revisionsPassword.setter
def revisionsPassword(self, value):
"""Set a revisions password directly, forcing a hash step."""
self.set_revisions_password(value)
@classmethod
def from_tree(cls, node):
"""Don't hash passwords when deserialising from XML"""
self = super(WorkbookProtection, cls).from_tree(node)
if self.workbookPassword:
self.set_workbook_password(node.get('workbookPassword'), already_hashed=True)
if self.revisionsPassword:
self.set_revisions_password(node.get('revisionsPassword'), already_hashed=True)
return self
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=DEFAULT_COLUMN_WIDTH,
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 attrs.keys() != {'min', 'max'}:
return Element("col", **attrs)
class _HeaderFooterPart(Strict):
"""
Individual left/center/right header/footer part
Do not use directly.
Header & Footer ampersand codes:
* &A Inserts the worksheet name
* &B Toggles bold
* &D or &[Date] Inserts the current date
* &E Toggles double-underline
* &F or &[File] Inserts the workbook name
* &I Toggles italic
* &N or &[Pages] Inserts the total page count
* &S Toggles strikethrough
* &T Inserts the current time
* &[Tab] Inserts the worksheet name
* &U Toggles underline
* &X Toggles superscript
* &Y Toggles subscript
* &P or &[Page] Inserts the current page number
* &P+n Inserts the page number incremented by n
* &P-n Inserts the page number decremented by n
* &[Path] Inserts the workbook path
* && Escapes the ampersand character
* &"fontname" Selects the named font
* &nn Selects the specified 2-digit font point size
Colours are in RGB Hex
"""
text = String(allow_none=True)
font = String(allow_none=True)
size = Integer(allow_none=True)
RGB = ("^[A-Fa-f0-9]{6}$")
color = MatchPattern(allow_none=True, pattern=RGB)
def __init__(self, text=None, font=None, size=None, color=None):
self.text = text
self.font = font
self.size = size
self.color = color
def __str__(self):
"""
Convert to Excel HeaderFooter miniformat minus position
"""
fmt = []
if self.font:
fmt.append(u'&"{0}"'.format(self.font))
if self.size:
fmt.append("&{0} ".format(self.size))
if self.color:
fmt.append("&K{0}".format(self.color))
return u"".join(fmt + [self.text])
def __bool__(self):
return bool(self.text)
__nonzero__ = __bool__
@classmethod
def from_str(cls, text):
"""
Convert from miniformat to object
"""
keys = ('font', 'color', 'size')
kw = dict((k, v) for match in FORMAT_REGEX.findall(text)
for k, v in zip(keys, match) if v)
kw['text'] = FORMAT_REGEX.sub('', text)
return cls(**kw)
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 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=basestring)
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 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 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 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 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 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 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 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 = String(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 PCDKPI(Serialisable):
tagname = "pCDKPI"
uniqueName = String()
caption = String(allow_none=True)
displayFolder = String()
measureGroup = String()
parent = String()
value = String()
goal = String()
status = String()
trend = String()
weight = String()
time = String()
def __init__(
self,
uniqueName=None,
caption=None,
displayFolder=None,
measureGroup=None,
parent=None,
value=None,
goal=None,
status=None,
trend=None,
weight=None,
time=None,
):
self.uniqueName = uniqueName
self.caption = caption
self.displayFolder = displayFolder
self.measureGroup = measureGroup
self.parent = parent
self.value = value
self.goal = goal
self.status = status
self.trend = trend
self.weight = weight
self.time = time
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__[1:]:
if key == 'style':
value = getattr(self, '_style')
else:
value = getattr(self, key)
if value:
yield key, safe_string(value)
class CacheHierarchy(Serialisable):
tagname = "cacheHierarchy"
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')
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 Reference(Strict):
"""
Normalise cell range references
"""
min_row = MinMax(min=1, max=1000000, expected_type=int)
max_row = MinMax(min=1, max=1000000, expected_type=int)
min_col = MinMax(min=1, max=16384, expected_type=int)
max_col = MinMax(min=1, max=16384, expected_type=int)
range_string = String(allow_none=True)
def __init__(self,
worksheet=None,
min_col=None,
min_row=None,
max_col=None,
max_row=None,
range_string=None):
if range_string is not None:
sheetname, boundaries = range_to_tuple(range_string)
min_col, min_row, max_col, max_row = boundaries
worksheet = DummyWorksheet(sheetname)
self.worksheet = worksheet
self.min_col = min_col
self.min_row = min_row
if max_col is None:
max_col = min_col
self.max_col = max_col
if max_row is None:
max_row = min_row
self.max_row = max_row
def __repr__(self):
return unicode(self)
def __str__(self):
fmt = u"{0}!${1}${2}:${3}${4}"
if (self.min_col == self.max_col and self.min_row == self.max_row):
fmt = u"{0}!${1}${2}"
return fmt.format(self.sheetname,
get_column_letter(self.min_col), self.min_row,
get_column_letter(self.max_col), self.max_row)
__unicode__ = __str__
def __len__(self):
if self.min_row == self.max_row:
return 1 + self.max_col - self.min_col
return 1 + self.max_row - self.min_row
@property
def rows(self):
"""
Return all cells in range by column
"""
for row in range(self.min_row, self.max_row + 1):
yield tuple('%s%d' % (get_column_letter(col), row)
for col in range(self.min_col, self.max_col + 1))
@property
def cols(self):
"""
Return all cells in range by row
"""
for col in range(self.min_col, self.max_col + 1):
yield tuple('%s%d' % (get_column_letter(col), row)
for row in range(self.min_row, self.max_row + 1))
@property
def cells(self):
"""
Return a flattened list of all cells (by column)
"""
return chain.from_iterable(self.cols)
def pop(self):
"""
Return and remove the first cell
"""
cell = next(self.cells)
if self.min_row == self.max_row:
self.min_col += 1
else:
self.min_row += 1
return cell
@property
def sheetname(self):
return quote_sheetname(self.worksheet.title)
class CacheField(Serialisable):
tagname = "cacheField"
sharedItems = Typed(expected_type=SharedItems, allow_none=True)
fieldGroup = Typed(expected_type=FieldGroup, allow_none=True)
mpMap = NestedInteger(allow_none=True, attribute="v")
extLst = Typed(expected_type=ExtensionList, allow_none=True)
name = String()
caption = String(allow_none=True)
propertyName = String(allow_none=True)
serverField = Bool(allow_none=True)
uniqueList = Bool(allow_none=True)
numFmtId = Integer(allow_none=True)
formula = String(allow_none=True)
sqlType = Integer(allow_none=True)
hierarchy = Integer(allow_none=True)
level = Integer(allow_none=True)
databaseField = Bool(allow_none=True)
mappingCount = Integer(allow_none=True)
memberPropertyField = Bool(allow_none=True)
__elements__ = ('sharedItems', 'fieldGroup', 'mpMap')
def __init__(
self,
sharedItems=None,
fieldGroup=None,
mpMap=None,
extLst=None,
name=None,
caption=None,
propertyName=None,
serverField=None,
uniqueList=True,
numFmtId=None,
formula=None,
sqlType=0,
hierarchy=0,
level=0,
databaseField=True,
mappingCount=None,
memberPropertyField=None,
):
self.sharedItems = sharedItems
self.fieldGroup = fieldGroup
self.mpMap = mpMap
self.extLst = extLst
self.name = name
self.caption = caption
self.propertyName = propertyName
self.serverField = serverField
self.uniqueList = uniqueList
self.numFmtId = numFmtId
self.formula = formula
self.sqlType = sqlType
self.hierarchy = hierarchy
self.level = level
self.databaseField = databaseField
self.mappingCount = mappingCount
self.memberPropertyField = memberPropertyField
class TableDefinition(Serialisable):
mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml"
rel_type = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable"
_id = 1
_path = "/xl/pivotTables/pivotTable{0}.xml"
tagname = "pivotTableDefinition"
cache = None
name = String()
cacheId = Integer()
dataOnRows = Bool()
dataPosition = Integer(allow_none=True)
dataCaption = String()
grandTotalCaption = String(allow_none=True)
errorCaption = String(allow_none=True)
showError = Bool()
missingCaption = String(allow_none=True)
showMissing = Bool()
pageStyle = String(allow_none=True)
pivotTableStyle = String(allow_none=True)
vacatedStyle = String(allow_none=True)
tag = String(allow_none=True)
updatedVersion = Integer()
minRefreshableVersion = Integer()
asteriskTotals = Bool()
showItems = Bool()
editData = Bool()
disableFieldList = Bool()
showCalcMbrs = Bool()
visualTotals = Bool()
showMultipleLabel = Bool()
showDataDropDown = Bool()
showDrill = Bool()
printDrill = Bool()
showMemberPropertyTips = Bool()
showDataTips = Bool()
enableWizard = Bool()
enableDrill = Bool()
enableFieldProperties = Bool()
preserveFormatting = Bool()
useAutoFormatting = Bool()
pageWrap = Integer()
pageOverThenDown = Bool()
subtotalHiddenItems = Bool()
rowGrandTotals = Bool()
colGrandTotals = Bool()
fieldPrintTitles = Bool()
itemPrintTitles = Bool()
mergeItem = Bool()
showDropZones = Bool()
createdVersion = Integer()
indent = Integer()
showEmptyRow = Bool()
showEmptyCol = Bool()
showHeaders = Bool()
compact = Bool()
outline = Bool()
outlineData = Bool()
compactData = Bool()
published = Bool()
gridDropZones = Bool()
immersive = Bool()
multipleFieldFilters = Bool()
chartFormat = Integer()
rowHeaderCaption = String(allow_none=True)
colHeaderCaption = String(allow_none=True)
fieldListSortAscending = Bool()
mdxSubqueries = Bool()
customListSort = Bool(allow_none=True)
autoFormatId = Integer(allow_none=True)
applyNumberFormats = Bool()
applyBorderFormats = Bool()
applyFontFormats = Bool()
applyPatternFormats = Bool()
applyAlignmentFormats = Bool()
applyWidthHeightFormats = Bool()
location = Typed(expected_type=Location, )
pivotFields = NestedSequence(expected_type=PivotField, count=True)
rowFields = NestedSequence(expected_type=RowColField, count=True)
rowItems = NestedSequence(expected_type=RowColItem, count=True)
colFields = NestedSequence(expected_type=RowColField, count=True)
colItems = NestedSequence(expected_type=RowColItem, count=True)
pageFields = NestedSequence(expected_type=PageField, count=True)
dataFields = NestedSequence(expected_type=DataField, count=True)
formats = NestedSequence(expected_type=Format, count=True)
conditionalFormats = NestedSequence(expected_type=ConditionalFormat,
count=True)
chartFormats = NestedSequence(expected_type=ChartFormat, count=True)
pivotHierarchies = NestedSequence(expected_type=PivotHierarchy, count=True)
pivotTableStyleInfo = Typed(expected_type=PivotTableStyle, allow_none=True)
filters = NestedSequence(expected_type=PivotFilter, count=True)
rowHierarchiesUsage = Typed(expected_type=RowHierarchiesUsage,
allow_none=True)
colHierarchiesUsage = Typed(expected_type=ColHierarchiesUsage,
allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
id = Relation()
__elements__ = (
'location',
'pivotFields',
'rowFields',
'rowItems',
'colFields',
'colItems',
'pageFields',
'dataFields',
'formats',
'conditionalFormats',
'chartFormats',
'pivotHierarchies',
'pivotTableStyleInfo',
'filters',
'rowHierarchiesUsage',
'colHierarchiesUsage',
)
def __init__(
self,
name=None,
cacheId=None,
dataOnRows=False,
dataPosition=None,
dataCaption=None,
grandTotalCaption=None,
errorCaption=None,
showError=False,
missingCaption=None,
showMissing=True,
pageStyle=None,
pivotTableStyle=None,
vacatedStyle=None,
tag=None,
updatedVersion=0,
minRefreshableVersion=0,
asteriskTotals=False,
showItems=True,
editData=False,
disableFieldList=False,
showCalcMbrs=True,
visualTotals=True,
showMultipleLabel=True,
showDataDropDown=True,
showDrill=True,
printDrill=False,
showMemberPropertyTips=True,
showDataTips=True,
enableWizard=True,
enableDrill=True,
enableFieldProperties=True,
preserveFormatting=True,
useAutoFormatting=False,
pageWrap=0,
pageOverThenDown=False,
subtotalHiddenItems=False,
rowGrandTotals=True,
colGrandTotals=True,
fieldPrintTitles=False,
itemPrintTitles=False,
mergeItem=False,
showDropZones=True,
createdVersion=0,
indent=1,
showEmptyRow=False,
showEmptyCol=False,
showHeaders=True,
compact=True,
outline=False,
outlineData=False,
compactData=True,
published=False,
gridDropZones=False,
immersive=True,
multipleFieldFilters=None,
chartFormat=0,
rowHeaderCaption=None,
colHeaderCaption=None,
fieldListSortAscending=None,
mdxSubqueries=None,
customListSort=None,
autoFormatId=None,
applyNumberFormats=False,
applyBorderFormats=False,
applyFontFormats=False,
applyPatternFormats=False,
applyAlignmentFormats=False,
applyWidthHeightFormats=False,
location=None,
pivotFields=(),
rowFields=(),
rowItems=(),
colFields=(),
colItems=(),
pageFields=(),
dataFields=(),
formats=(),
conditionalFormats=(),
chartFormats=(),
pivotHierarchies=(),
pivotTableStyleInfo=None,
filters=(),
rowHierarchiesUsage=None,
colHierarchiesUsage=None,
extLst=None,
id=None,
):
self.name = name
self.cacheId = cacheId
self.dataOnRows = dataOnRows
self.dataPosition = dataPosition
self.dataCaption = dataCaption
self.grandTotalCaption = grandTotalCaption
self.errorCaption = errorCaption
self.showError = showError
self.missingCaption = missingCaption
self.showMissing = showMissing
self.pageStyle = pageStyle
self.pivotTableStyle = pivotTableStyle
self.vacatedStyle = vacatedStyle
self.tag = tag
self.updatedVersion = updatedVersion
self.minRefreshableVersion = minRefreshableVersion
self.asteriskTotals = asteriskTotals
self.showItems = showItems
self.editData = editData
self.disableFieldList = disableFieldList
self.showCalcMbrs = showCalcMbrs
self.visualTotals = visualTotals
self.showMultipleLabel = showMultipleLabel
self.showDataDropDown = showDataDropDown
self.showDrill = showDrill
self.printDrill = printDrill
self.showMemberPropertyTips = showMemberPropertyTips
self.showDataTips = showDataTips
self.enableWizard = enableWizard
self.enableDrill = enableDrill
self.enableFieldProperties = enableFieldProperties
self.preserveFormatting = preserveFormatting
self.useAutoFormatting = useAutoFormatting
self.pageWrap = pageWrap
self.pageOverThenDown = pageOverThenDown
self.subtotalHiddenItems = subtotalHiddenItems
self.rowGrandTotals = rowGrandTotals
self.colGrandTotals = colGrandTotals
self.fieldPrintTitles = fieldPrintTitles
self.itemPrintTitles = itemPrintTitles
self.mergeItem = mergeItem
self.showDropZones = showDropZones
self.createdVersion = createdVersion
self.indent = indent
self.showEmptyRow = showEmptyRow
self.showEmptyCol = showEmptyCol
self.showHeaders = showHeaders
self.compact = compact
self.outline = outline
self.outlineData = outlineData
self.compactData = compactData
self.published = published
self.gridDropZones = gridDropZones
self.immersive = immersive
self.multipleFieldFilters = multipleFieldFilters
self.chartFormat = chartFormat
self.rowHeaderCaption = rowHeaderCaption
self.colHeaderCaption = colHeaderCaption
self.fieldListSortAscending = fieldListSortAscending
self.mdxSubqueries = mdxSubqueries
self.customListSort = customListSort
self.autoFormatId = autoFormatId
self.applyNumberFormats = applyNumberFormats
self.applyBorderFormats = applyBorderFormats
self.applyFontFormats = applyFontFormats
self.applyPatternFormats = applyPatternFormats
self.applyAlignmentFormats = applyAlignmentFormats
self.applyWidthHeightFormats = applyWidthHeightFormats
self.location = location
self.pivotFields = pivotFields
self.rowFields = rowFields
self.rowItems = rowItems
self.colFields = colFields
self.colItems = colItems
self.pageFields = pageFields
self.dataFields = dataFields
self.formats = formats
self.conditionalFormats = conditionalFormats
self.chartFormats = chartFormats
self.pivotHierarchies = pivotHierarchies
self.pivotTableStyleInfo = pivotTableStyleInfo
self.filters = filters
self.rowHierarchiesUsage = rowHierarchiesUsage
self.colHierarchiesUsage = colHierarchiesUsage
self.extLst = extLst
self.id = id
def to_tree(self):
tree = super(TableDefinition, self).to_tree()
tree.set("xmlns", SHEET_MAIN_NS)
return tree
@property
def path(self):
return self._path.format(self._id)
def _write(self, archive, manifest):
"""
Add to zipfile and update manifest
"""
self._write_rels(archive, manifest)
xml = tostring(self.to_tree())
archive.writestr(self.path[1:], xml)
manifest.append(self)
def _write_rels(self, archive, manifest):
"""
Write the relevant child objects and add links
"""
if self.cache is None:
return
rels = RelationshipList()
r = Relationship(Type=self.cache.rel_type, Target=self.cache.path)
rels.append(r)
self.id = r.id
if self.cache.path[1:] not in archive.namelist():
self.cache._write(archive, manifest)
path = get_rels_path(self.path)
xml = tostring(rels.to_tree())
archive.writestr(path[1:], xml)
class 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)
yield m.group('notquoted'), 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 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()
_wb = None
_style = StyleArray()
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 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 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)
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 CharacterProperties(Serialisable):
tagname = "defRPr"
namespace = DRAWING_NS
kumimoji = Bool(allow_none=True)
lang = String(allow_none=True)
altLang = String(allow_none=True)
sz = Integer(allow_none=True)
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 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
