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