class Dimension(Strict, StyleableObject):
"""Information about the display properties of a row or column."""
__fields__ = (
'hidden',
'outlineLevel',
'collapsed',
)
index = Integer()
hidden = Bool()
outlineLevel = Integer(allow_none=True)
outline_level = Alias('outlineLevel')
collapsed = Bool()
def __init__(self,
index,
hidden,
outlineLevel,
collapsed,
worksheet,
visible=True,
style=None):
super(Dimension, self).__init__(sheet=worksheet, style_array=style)
self.index = index
self.hidden = hidden
self.outlineLevel = outlineLevel
self.collapsed = collapsed
def __iter__(self):
for key in self.__fields__:
value = getattr(self, key)
if value:
yield key, safe_string(value)
@property
@deprecated("Use `hidden` instead")
def visible(self):
return not self.hidden
class DifferentialStyleList(Serialisable):
"""
Deduping container for differential styles.
"""
tagname = "dxfs"
dxf = Sequence(expected_type=DifferentialStyle)
styles = Alias("dxf")
def __init__(self, dxf=()):
self.dxf = dxf
def append(self, dxf):
"""
Check to see whether style already exists and append it if does not.
"""
if not isinstance(dxf, DifferentialStyle):
raise TypeError('expected ' + str(DifferentialStyle))
if dxf in self.styles:
return
self.styles.append(dxf)
def add(self, dxf):
"""
Add a differential style and return its index
"""
self.append(dxf)
return self.styles.index(dxf)
def __bool__(self):
return bool(self.styles)
__nonzero__ = __bool__
def __getitem__(self, idx):
return self.styles[idx]
class StockChart(ChartBase):
tagname = "stockChart"
ser = Sequence(expected_type=Series) #min 3, max4
dLbls = Typed(expected_type=DataLabelList, allow_none=True)
dataLabels = Alias('dLbls')
dropLines = Typed(expected_type=ChartLines, allow_none=True)
hiLowLines = Typed(expected_type=ChartLines, allow_none=True)
upDownBars = Typed(expected_type=UpDownBars, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
x_axis = Typed(expected_type=TextAxis)
y_axis = Typed(expected_type=NumericAxis)
_series_type = "line"
__elements__ = ('ser', 'dLbls', 'dropLines', 'hiLowLines', 'upDownBars',
'axId')
def __init__(self,
ser=(),
dLbls=None,
dropLines=None,
hiLowLines=None,
upDownBars=None,
extLst=None,
**kw
):
self.ser = ser
self.dLbls = dLbls
self.dropLines = dropLines
self.hiLowLines = hiLowLines
self.upDownBars = upDownBars
self.x_axis = TextAxis()
self.y_axis = NumericAxis()
super(StockChart, self).__init__(**kw)
class DataPoint(Serialisable):
tagname = "dPt"
idx = NestedInteger()
invertIfNegative = NestedBool(allow_none=True)
marker = Typed(expected_type=Marker, allow_none=True)
bubble3D = NestedBool(allow_none=True)
explosion = NestedInteger(allow_none=True)
spPr = Typed(expected_type=GraphicalProperties, allow_none=True)
graphicalProperties = Alias('spPr')
pictureOptions = Typed(expected_type=PictureOptions, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('idx', 'invertIfNegative', 'marker', 'bubble3D',
'explosion', 'spPr', 'pictureOptions')
def __init__(
self,
idx=None,
invertIfNegative=None,
marker=None,
bubble3D=None,
explosion=None,
spPr=None,
pictureOptions=None,
extLst=None,
):
self.idx = idx
self.invertIfNegative = invertIfNegative
self.marker = marker
self.bubble3D = bubble3D
self.explosion = explosion
if spPr is None:
spPr = GraphicalProperties()
self.spPr = spPr
self.pictureOptions = pictureOptions
class ConditionalFormatting(Serialisable):
tagname = "conditionalFormatting"
sqref = String(allow_none=True)
pivot = Bool(allow_none=True)
cfRule = Sequence(expected_type=Rule)
rules = Alias("cfRule")
def __init__(self, sqref=None, pivot=None, cfRule=(), extLst=None):
self.sqref = sqref
self.pivot = pivot
self.cfRule = cfRule
def __eq__(self, other):
if not isinstance(other, self.__class__):
return False
return self.sqref == other.sqref
def __hash__(self):
return hash(self.sqref)
def __repr__(self):
return self.sqref
class Chartsheet(_WorkbookChild, Serialisable):
tagname = "chartsheet"
_default_title = "Chart"
_rel_type = "chartsheet"
_path = "/xl/chartsheets/sheet{0}.xml"
mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.chartsheet+xml"
sheetPr = Typed(expected_type=ChartsheetProperties, allow_none=True)
sheetViews = Typed(expected_type=ChartsheetViewList)
sheetProtection = Typed(expected_type=ChartsheetProtection,
allow_none=True)
customSheetViews = Typed(expected_type=CustomChartsheetViews,
allow_none=True)
pageMargins = Typed(expected_type=PageMargins, allow_none=True)
pageSetup = Typed(expected_type=PrintPageSetup, allow_none=True)
drawing = Typed(expected_type=Drawing, allow_none=True)
drawingHF = Typed(expected_type=DrawingHF, allow_none=True)
picture = Typed(expected_type=SheetBackgroundPicture, allow_none=True)
webPublishItems = Typed(expected_type=WebPublishItems, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
sheet_state = Set(values=('visible', 'hidden', 'veryHidden'))
headerFooter = Typed(expected_type=HeaderFooter)
HeaderFooter = Alias('headerFooter')
__elements__ = ('sheetPr', 'sheetViews', 'sheetProtection',
'customSheetViews', 'pageMargins', 'pageSetup',
'headerFooter', 'drawing', 'drawingHF', 'picture',
'webPublishItems')
__attrs__ = ()
def __init__(
self,
sheetPr=None,
sheetViews=None,
sheetProtection=None,
customSheetViews=None,
pageMargins=None,
pageSetup=None,
headerFooter=None,
drawing=None,
drawingHF=None,
picture=None,
webPublishItems=None,
extLst=None,
parent=None,
title="",
sheet_state='visible',
):
super(Chartsheet, self).__init__(parent, title)
self._charts = []
self.sheetPr = sheetPr
if sheetViews is None:
sheetViews = ChartsheetViewList()
self.sheetViews = sheetViews
self.sheetProtection = sheetProtection
self.customSheetViews = customSheetViews
self.pageMargins = pageMargins
self.pageSetup = pageSetup
if headerFooter is not None:
self.headerFooter = headerFooter
self.drawing = Drawing("rId1")
self.drawingHF = drawingHF
self.picture = picture
self.webPublishItems = webPublishItems
self.sheet_state = sheet_state
def add_chart(self, chart):
chart.anchor = AbsoluteAnchor()
self._charts.append(chart)
def to_tree(self):
self._drawing = SpreadsheetDrawing()
self._drawing.charts = self._charts
tree = super(Chartsheet, self).to_tree()
if not self.headerFooter:
el = tree.find('headerFooter')
tree.remove(el)
tree.set("xmlns", SHEET_MAIN_NS)
return tree
class WorkbookPackage(Serialisable):
"""
Represent the workbook file in the archive
"""
tagname = "workbook"
conformance = NoneSet(values=['strict', 'transitional'])
fileVersion = Typed(expected_type=FileVersion, allow_none=True)
fileSharing = Typed(expected_type=FileSharing, allow_none=True)
workbookPr = Typed(expected_type=WorkbookProperties, allow_none=True)
properties = Alias("workbookPr")
workbookProtection = Typed(expected_type=WorkbookProtection,
allow_none=True)
bookViews = NestedSequence(expected_type=BookView)
sheets = NestedSequence(expected_type=ChildSheet)
functionGroups = Typed(expected_type=FunctionGroupList, allow_none=True)
externalReferences = NestedSequence(expected_type=ExternalReference)
definedNames = Typed(expected_type=DefinedNameList, allow_none=True)
calcPr = Typed(expected_type=CalcProperties, allow_none=True)
oleSize = NestedString(allow_none=True, attribute="ref")
customWorkbookViews = NestedSequence(expected_type=CustomWorkbookView)
pivotCaches = NestedSequence(expected_type=PivotCache, allow_none=True)
smartTagPr = Typed(expected_type=SmartTagProperties, allow_none=True)
smartTagTypes = Typed(expected_type=SmartTagList, allow_none=True)
webPublishing = Typed(expected_type=WebPublishing, allow_none=True)
fileRecoveryPr = Typed(expected_type=FileRecoveryProperties,
allow_none=True)
webPublishObjects = Typed(expected_type=WebPublishObjectList,
allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
Ignorable = NestedString(
namespace="http://schemas.openxmlformats.org/markup-compatibility/2006",
allow_none=True)
__elements__ = ('fileVersion', 'fileSharing', 'workbookPr',
'workbookProtection', 'bookViews', 'sheets',
'functionGroups', 'externalReferences', 'definedNames',
'calcPr', 'oleSize', 'customWorkbookViews', 'pivotCaches',
'smartTagPr', 'smartTagTypes', 'webPublishing',
'fileRecoveryPr', 'webPublishObjects')
def __init__(
self,
conformance=None,
fileVersion=None,
fileSharing=None,
workbookPr=None,
workbookProtection=None,
bookViews=(),
sheets=(),
functionGroups=None,
externalReferences=(),
definedNames=None,
calcPr=None,
oleSize=None,
customWorkbookViews=(),
pivotCaches=(),
smartTagPr=None,
smartTagTypes=None,
webPublishing=None,
fileRecoveryPr=None,
webPublishObjects=None,
extLst=None,
Ignorable=None,
):
self.conformance = conformance
self.fileVersion = fileVersion
self.fileSharing = fileSharing
if workbookPr is None:
workbookPr = WorkbookProperties()
self.workbookPr = workbookPr
self.workbookProtection = workbookProtection
self.bookViews = bookViews
self.sheets = sheets
self.functionGroups = functionGroups
self.externalReferences = externalReferences
self.definedNames = definedNames
self.calcPr = calcPr
self.oleSize = oleSize
self.customWorkbookViews = customWorkbookViews
self.pivotCaches = pivotCaches
self.smartTagPr = smartTagPr
self.smartTagTypes = smartTagTypes
self.webPublishing = webPublishing
self.fileRecoveryPr = fileRecoveryPr
self.webPublishObjects = webPublishObjects
def to_tree(self):
tree = super(WorkbookPackage, self).to_tree()
tree.set("xmlns", SHEET_MAIN_NS)
return tree
@property
def active(self):
for view in self.bookViews:
if view.activeTab is not None:
return view.activeTab
return 0
@property
def pivot_caches(self):
"""
Get PivotCache objects
"""
d = {}
for c in self.caches:
cache = get_rel(self.archive,
self.rels,
id=c.id,
cls=CacheDefinition)
if cache.deps:
records = get_rel(self.archive, cache.deps, cache.id,
RecordList)
else:
records = None
cache.records = records
d[c.cacheId] = cache
return d
class Font(Serialisable):
"""Font options used in styles."""
UNDERLINE_DOUBLE = 'double'
UNDERLINE_DOUBLE_ACCOUNTING = 'doubleAccounting'
UNDERLINE_SINGLE = 'single'
UNDERLINE_SINGLE_ACCOUNTING = 'singleAccounting'
name = NestedString(allow_none=True)
charset = NestedInteger(allow_none=True)
family = NestedMinMax(min=0, max=14, allow_none=True)
sz = NestedFloat(allow_none=True)
size = Alias("sz")
b = NestedBool(to_tree=_no_value)
bold = Alias("b")
i = NestedBool(to_tree=_no_value)
italic = Alias("i")
strike = NestedBool(allow_none=True)
strikethrough = Alias("strike")
outline = NestedBool(allow_none=True)
shadow = NestedBool(allow_none=True)
condense = NestedBool(allow_none=True)
extend = NestedBool(allow_none=True)
u = NestedNoneSet(values=('single', 'double', 'singleAccounting',
'doubleAccounting'))
underline = Alias("u")
vertAlign = NestedNoneSet(values=('superscript', 'subscript', 'baseline'))
color = ColorDescriptor(allow_none=True)
scheme = NestedNoneSet(values=("major", "minor"))
tagname = "font"
__elements__ = ('name', 'charset', 'family', 'b', 'i', 'strike', 'outline',
'shadow', 'condense', 'color', 'extend', 'sz', 'u',
'vertAlign', 'scheme')
def __init__(self,
name=None,
sz=None,
b=None,
i=None,
charset=None,
u=None,
strike=None,
color=None,
scheme=None,
family=None,
size=None,
bold=None,
italic=None,
strikethrough=None,
underline=None,
vertAlign=None,
outline=None,
shadow=None,
condense=None,
extend=None):
self.name = name
self.family = family
if size is not None:
sz = size
self.sz = sz
if bold is not None:
b = bold
self.b = b
if italic is not None:
i = italic
self.i = i
if underline is not None:
u = underline
self.u = u
if strikethrough is not None:
strike = strikethrough
self.strike = strike
self.color = color
self.vertAlign = vertAlign
self.charset = charset
self.outline = outline
self.shadow = shadow
self.condense = condense
self.extend = extend
self.scheme = scheme
class SheetProtection(Strict):
"""
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
"""
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 = String(allow_none=True)
spinCount = Integer(allow_none=True)
algorithmName = String(allow_none=True)
_password = None
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):
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.set_password(password)
self.algorithmName = algorithmName
self.saltValue = saltValue
self.spinCount = spinCount
def set_password(self, value='', already_hashed=False):
"""Set a password on this sheet."""
if not already_hashed:
value = hash_password(value)
self._password = value
self.enable()
@property
def password(self):
"""Return the password value, regardless of hash."""
return self._password
@password.setter
def password(self, value):
"""Set a password directly, forcing a hash step."""
self.set_password(value, already_hashed=False)
def enable(self):
self.sheet = True
def disable(self):
self.sheet = False
def __iter__(self):
for key in ('sheet', 'objects', 'scenarios', 'formatCells',
'formatRows', 'formatColumns', 'insertColumns',
'insertRows', 'insertHyperlinks', 'deleteColumns',
'deleteRows', 'selectLockedCells', 'selectUnlockedCells',
'sort', 'autoFilter', 'pivotTables', 'password',
'algorithmName', 'saltValue', 'spinCount'):
value = getattr(self, key)
if value is not None:
yield key, safe_string(value)
class PlotArea(Serialisable):
tagname = "plotArea"
layout = Typed(expected_type=Layout, allow_none=True)
dTable = Typed(expected_type=DataTable, allow_none=True)
spPr = Typed(expected_type=GraphicalProperties, allow_none=True)
graphicalProperties = Alias("spPr")
extLst = Typed(expected_type=ExtensionList, allow_none=True)
# at least one chart
areaChart = Typed(expected_type=AreaChart, allow_none=True)
area3DChart = Typed(expected_type=AreaChart3D, allow_none=True)
lineChart = Typed(expected_type=LineChart, allow_none=True)
line3DChart = Typed(expected_type=LineChart3D, allow_none=True)
stockChart = Typed(expected_type=StockChart, allow_none=True)
radarChart = Typed(expected_type=RadarChart, allow_none=True)
scatterChart = Typed(expected_type=ScatterChart, allow_none=True)
pieChart = Typed(expected_type=PieChart, allow_none=True)
pie3DChart = Typed(expected_type=PieChart3D, allow_none=True)
doughnutChart = Typed(expected_type=DoughnutChart, allow_none=True)
barChart = Typed(expected_type=BarChart, allow_none=True)
bar3DChart = Typed(expected_type=BarChart3D, allow_none=True)
ofPieChart = Typed(expected_type=ProjectedPieChart, allow_none=True)
surfaceChart = Typed(expected_type=SurfaceChart, allow_none=True)
surface3DChart = Typed(expected_type=SurfaceChart3D, allow_none=True)
bubbleChart = Typed(expected_type=BubbleChart, allow_none=True)
# maybe axes
valAx = Sequence(expected_type=NumericAxis, allow_none=True)
catAx = Sequence(expected_type=TextAxis, allow_none=True)
dateAx = Sequence(expected_type=DateAxis, allow_none=True)
serAx = Sequence(expected_type=SeriesAxis, allow_none=True)
__elements__ = ('layout', 'areaChart', 'area3DChart', 'lineChart',
'line3DChart', 'stockChart', 'radarChart', 'scatterChart', 'pieChart',
'pie3DChart', 'doughnutChart', 'barChart', 'bar3DChart', 'ofPieChart',
'surfaceChart', 'surface3DChart', 'bubbleChart', 'valAx', 'catAx', 'dateAx', 'serAx',
'dTable', 'spPr')
def __init__(self,
layout=None,
dTable=None,
spPr=None,
areaChart=None,
area3DChart=None,
lineChart=None,
line3DChart=None,
stockChart=None,
radarChart=None,
scatterChart=None,
pieChart=None,
pie3DChart=None,
doughnutChart=None,
barChart=None,
bar3DChart=None,
ofPieChart=None,
surfaceChart=None,
surface3DChart=None,
bubbleChart=None,
valAx=(),
catAx=(),
serAx=(),
dateAx=(),
extLst=None,
):
self.layout = layout
self.dTable = dTable
self.spPr = spPr
self.areaChart = areaChart
self.area3DChart = area3DChart
self.lineChart = lineChart
self.line3DChart = line3DChart
self.stockChart = stockChart
self.radarChart = radarChart
self.scatterChart = scatterChart
self.pieChart = pieChart
self.pie3DChart = pie3DChart
self.doughnutChart = doughnutChart
self.barChart = barChart
self.bar3DChart = bar3DChart
self.ofPieChart = ofPieChart
self.surfaceChart = surfaceChart
self.surface3DChart = surface3DChart
self.bubbleChart = bubbleChart
self.valAx = valAx
self.catAx = catAx
self.dateAx = dateAx
self.serAx = serAx
self._charts = []
def to_tree(self, tagname=None, idx=None):
if tagname is None:
tagname = self.tagname
el = Element(tagname)
if self.layout is not None:
el.append(self.layout.to_tree())
for chart in self._charts:
el.append(chart.to_tree())
for ax in ['valAx', 'catAx', 'dateAx', 'serAx',]:
seq = getattr(self, ax)
if seq:
for obj in seq:
el.append(obj.to_tree())
for attr in ['dTable', 'spPr']:
obj = getattr(self, attr)
if obj is not None:
el.append(obj.to_tree())
return el
class DocumentProperties(Strict):
"""High-level properties of the document.
Defined in ECMA-376 Par2 Annex D
"""
category = String(allow_none=True)
contentStatus = String(allow_none=True)
keywords = String(allow_none=True)
lastModifiedBy = String(allow_none=True)
lastPrinted = W3CDateTime(expected_type=datetime.datetime, allow_none=True)
revision = String(allow_none=True)
version = String(allow_none=True)
last_modified_by = Alias("lastModifiedBy")
# Dublin Core Properties
subject = String(allow_none=True)
title = String(allow_none=True)
creator = String(allow_none=True)
description = String(allow_none=True)
identifier = String(allow_none=True)
language = String(allow_none=True)
created = W3CDateTime(expected_type=datetime.datetime, allow_none=True)
modified = W3CDateTime(expected_type=datetime.datetime, allow_none=True)
__fields__ = ("category", "contentStatus", "lastModifiedBy", "keywords",
"lastPrinted", "revision", "version", "created", "creator", "description",
"identifier", "language", "modified", "subject", "title")
def __init__(self,
category=None,
contentStatus=None,
keywords=None,
lastModifiedBy=None,
lastPrinted=None,
revision=None,
version=None,
created=datetime.datetime.now(),
creator="openpyxl",
description=None,
identifier=None,
language=None,
modified=datetime.datetime.now(),
subject=None,
title=None,
):
self.contentStatus = contentStatus
self.lastPrinted = lastPrinted
self.revision = revision
self.version = version
self.creator = creator
self.lastModifiedBy = lastModifiedBy
self.modified = modified
self.created = created
self.title = title
self.subject = subject
self.description = description
self.identifier = identifier
self.language = language
self.keywords = keywords
self.category = category
def __iter__(self):
for attr in self.__fields__:
value = getattr(self, attr)
if value is not None:
yield attr, safe_string(value)
class LineProperties(Serialisable):
tagname = "ln"
namespace = DRAWING_NS
w = MinMax(min=0, max=20116800, allow_none=True) # EMU
width = Alias('w')
cap = NoneSet(values=(['rnd', 'sq', 'flat']))
cmpd = NoneSet(values=(['sng', 'dbl', 'thickThin', 'thinThick', 'tri']))
algn = NoneSet(values=(['ctr', 'in']))
noFill = EmptyTag()
solidFill = ColorChoiceDescriptor()
gradFill = Typed(expected_type=GradientFillProperties, allow_none=True)
pattFill = Typed(expected_type=PatternFillProperties, allow_none=True)
prstDash = NestedNoneSet(values=([
'solid', 'dot', 'dash', 'lgDash', 'dashDot', 'lgDashDot',
'lgDashDotDot', 'sysDash', 'sysDot', 'sysDashDot', 'sysDashDotDot'
]),
namespace=namespace)
dashStyle = Alias('prstDash')
custDash = Typed(expected_type=DashStop, allow_none=True)
round = EmptyTag()
bevel = EmptyTag()
miter = Typed(expected_type=LineJoinMiterProperties, allow_none=True)
headEnd = Typed(expected_type=LineEndProperties, allow_none=True)
tailEnd = Typed(expected_type=LineEndProperties, allow_none=True)
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
__elements__ = ('noFill', 'solidFill', 'gradFill', 'pattFill', 'prstDash',
'custDash', 'round', 'bevel', 'mitre', 'headEnd',
'tailEnd')
def __init__(
self,
w=None,
cap=None,
cmpd=None,
algn=None,
noFill=None,
solidFill=None,
gradFill=None,
pattFill=None,
prstDash=None,
custDash=None,
round=None,
bevel=None,
miter=None,
headEnd=None,
tailEnd=None,
extLst=None,
):
self.w = w
self.cap = cap
self.cmpd = cmpd
self.algn = algn
self.noFill = noFill
self.solidFill = solidFill
self.gradFill = gradFill
self.pattFill = pattFill
if prstDash is None:
prstDash = "solid"
self.prstDash = prstDash
self.custDash = custDash
self.round = round
self.bevel = bevel
self.mitre = bevel
self.headEnd = headEnd
self.tailEnd = tailEnd
class Alignment(Serialisable):
"""Alignment options for use in styles."""
tagname = "alignment"
__fields__ = (
'horizontal',
'vertical',
'textRotation',
'wrapText',
'shrinkToFit',
'indent',
'relativeIndent',
'justifyLastLine',
'readingOrder',
)
horizontal = NoneSet(values=horizontal_alignments)
vertical = NoneSet(values=vertical_aligments)
textRotation = NoneSet(values=range(181))
textRotation.values.add(255)
text_rotation = Alias('textRotation')
wrapText = Bool(allow_none=True)
wrap_text = Alias('wrapText')
shrinkToFit = Bool(allow_none=True)
shrink_to_fit = Alias('shrinkToFit')
indent = Min(min=0)
relativeIndent = Min(min=0)
justifyLastLine = Bool(allow_none=True)
readingOrder = Min(min=0)
def __init__(self,
horizontal=None,
vertical=None,
textRotation=0,
wrapText=None,
shrinkToFit=None,
indent=0,
relativeIndent=0,
justifyLastLine=None,
readingOrder=0,
text_rotation=None,
wrap_text=None,
shrink_to_fit=None,
mergeCell=None):
self.horizontal = horizontal
self.vertical = vertical
self.indent = indent
self.relativeIndent = relativeIndent
self.justifyLastLine = justifyLastLine
self.readingOrder = readingOrder
if text_rotation is not None:
textRotation = text_rotation
if textRotation is not None:
self.textRotation = int(textRotation)
if wrap_text is not None:
wrapText = wrap_text
self.wrapText = wrapText
if shrink_to_fit is not None:
shrinkToFit = shrink_to_fit
self.shrinkToFit = shrinkToFit
# mergeCell is vestigial
def __iter__(self):
for attr in self.__attrs__:
value = getattr(self, attr)
if value is not None and value != 0:
yield attr, safe_string(value)
class DataValidation(Serialisable):
tagname = "dataValidation"
showErrorMessage = Bool()
showDropDown = Bool(allow_none=True)
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 Font(HashableObject):
"""Font options used in styles."""
spec = """18.8.22, p.3930"""
UNDERLINE_NONE = 'none'
UNDERLINE_DOUBLE = 'double'
UNDERLINE_DOUBLE_ACCOUNTING = 'doubleAccounting'
UNDERLINE_SINGLE = 'single'
UNDERLINE_SINGLE_ACCOUNTING = 'singleAccounting'
name = String()
charset = Integer(allow_none=True)
family = MinMax(min=0, max=14)
sz = Float()
size = Alias("sz")
b = Bool()
bold = Alias("b")
i = Bool()
italic = Alias("i")
strike = Bool()
strikethrough = Alias("strike")
outline = Bool()
shadow = Bool()
condense = Bool()
extend = Bool()
u = Set(values=set([None, UNDERLINE_DOUBLE, UNDERLINE_NONE,
UNDERLINE_DOUBLE_ACCOUNTING, UNDERLINE_SINGLE,
UNDERLINE_SINGLE_ACCOUNTING]))
underline = Alias("u")
vertAlign = Set(values=set(['superscript', 'subscript', 'baseline', None]))
color = Typed(expected_type=Color)
scheme = Set(values=(None, "major", "minor"))
__fields__ = ('name',
'sz',
'b',
'i',
'u',
'strike',
'color',
'vertAlign',
'charset',
'outline',
'shadow',
'condense',
'extend',
'family',
)
def __init__(self, name='Calibri', sz=11, b=False, i=False, charset=None,
u=None, strike=False, color=Color(), scheme=None, family=2, size=None,
bold=None, italic=None, strikethrough=None, underline=UNDERLINE_NONE,
vertAlign=None, outline=False, shadow=False, condense=False, extend=False):
self.name = name
self.family = family
if size is not None:
sz = size
self.sz = sz
if bold is not None:
b = bold
self.b = b
if italic is not None:
i = italic
self.i = i
if underline is not None:
u = underline
self.u = u
if strikethrough is not None:
strike = strikethrough
self.strike = strike
self.color = color
self.vertAlign = vertAlign
self.charset = charset
self.outline = outline
self.shadow = shadow
self.condense = condense
self.extend = extend
self.scheme = scheme
class Font(HashableObject):
"""Font options used in styles."""
spec = """18.8.22, p.3930"""
UNDERLINE_DOUBLE = 'double'
UNDERLINE_DOUBLE_ACCOUNTING = 'doubleAccounting'
UNDERLINE_SINGLE = 'single'
UNDERLINE_SINGLE_ACCOUNTING = 'singleAccounting'
name = String(nested=True)
charset = Integer(allow_none=True, nested=True)
family = MinMax(min=0, max=14, nested=True)
sz = Float(nested=True)
size = Alias("sz")
b = Bool(nested=True)
bold = Alias("b")
i = Bool(nested=True)
italic = Alias("i")
strike = Bool(nested=True)
strikethrough = Alias("strike")
outline = Bool(nested=True)
shadow = Bool(nested=True)
condense = Bool(nested=True)
extend = Bool(nested=True)
u = NoneSet(values=('single', 'double', 'singleAccounting',
'doubleAccounting'), nested=True
)
underline = Alias("u")
vertAlign = NoneSet(values=('superscript', 'subscript', 'baseline'), nested=True)
color = ColorDescriptor()
scheme = NoneSet(values=("major", "minor"), nested=True)
tagname = "font"
__nested__ = ('name', 'charset', 'family', 'b', 'i', 'strike', 'outline',
'shadow', 'condense', 'extend', 'sz', 'u', 'vertAlign',
'scheme')
__fields__ = ('name', 'charset', 'family', 'b', 'i', 'strike', 'outline',
'shadow', 'condense', 'extend', 'sz', 'u', 'vertAlign',
'scheme', 'color')
@classmethod
def _create_nested(cls, el, tag):
if tag == "u":
return el.get("val", "single")
return super(Font, cls)._create_nested(el, tag)
def to_tree(self, tagname=None):
el = Element(self.tagname)
attrs = list(self.__nested__)
attrs.insert(10, 'color')
for attr in attrs:
value = getattr(self, attr)
if value:
if attr == 'color':
color = value.to_tree()
el.append(color)
else:
SubElement(el, attr, val=safe_string(value))
return el
def __init__(self, name='Calibri', sz=11, b=False, i=False, charset=None,
u=None, strike=False, color=BLACK, scheme=None, family=2, size=None,
bold=None, italic=None, strikethrough=None, underline=None,
vertAlign=None, outline=False, shadow=False, condense=False,
extend=False):
self.name = name
self.family = family
if size is not None:
sz = size
self.sz = sz
if bold is not None:
b = bold
self.b = b
if italic is not None:
i = italic
self.i = i
if underline is not None:
u = underline
self.u = u
if strikethrough is not None:
strike = strikethrough
self.strike = strike
self.color = color
self.vertAlign = vertAlign
self.charset = charset
self.outline = outline
self.shadow = shadow
self.condense = condense
self.extend = extend
self.scheme = scheme
class DocumentProperties(Serialisable):
"""High-level properties of the document.
Defined in ECMA-376 Par2 Annex D
"""
tagname = "coreProperties"
namespace = COREPROPS_NS
category = NestedText(expected_type=str, allow_none=True)
contentStatus = NestedText(expected_type=str, allow_none=True)
keywords = NestedText(expected_type=str, allow_none=True)
lastModifiedBy = NestedText(expected_type=str, allow_none=True)
lastPrinted = NestedDateTime(allow_none=True)
revision = NestedText(expected_type=str, allow_none=True)
version = NestedText(expected_type=str, allow_none=True)
last_modified_by = Alias("lastModifiedBy")
# Dublin Core Properties
subject = NestedText(expected_type=str,
allow_none=True,
namespace=DCORE_NS)
title = NestedText(expected_type=str, allow_none=True, namespace=DCORE_NS)
creator = NestedText(expected_type=str,
allow_none=True,
namespace=DCORE_NS)
description = NestedText(expected_type=str,
allow_none=True,
namespace=DCORE_NS)
identifier = NestedText(expected_type=str,
allow_none=True,
namespace=DCORE_NS)
language = NestedText(expected_type=str,
allow_none=True,
namespace=DCORE_NS)
# Dublin Core Terms
created = QualifiedDateTime(allow_none=True, namespace=DCTERMS_NS)
modified = QualifiedDateTime(allow_none=True, namespace=DCTERMS_NS)
__elements__ = (
"creator",
"title",
"description",
"subject",
"identifier",
"language",
"created",
"modified",
"lastModifiedBy",
"category",
"contentStatus",
"version",
"revision",
"keywords",
"lastPrinted",
)
def __init__(
self,
category=None,
contentStatus=None,
keywords=None,
lastModifiedBy=None,
lastPrinted=None,
revision=None,
version=None,
created=datetime.datetime.now(),
creator="openpyxl",
description=None,
identifier=None,
language=None,
modified=datetime.datetime.now(),
subject=None,
title=None,
):
self.contentStatus = contentStatus
self.lastPrinted = lastPrinted
self.revision = revision
self.version = version
self.creator = creator
self.lastModifiedBy = lastModifiedBy
self.modified = modified
self.created = created
self.title = title
self.subject = subject
self.description = description
self.identifier = identifier
self.language = language
self.keywords = keywords
self.category = category
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 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 HeaderFooterItem(Strict):
"""
Header or footer item
"""
left = Typed(expected_type=_HeaderFooterPart)
center = Typed(expected_type=_HeaderFooterPart)
centre = Alias("center")
right = Typed(expected_type=_HeaderFooterPart)
__keys = ('L', 'C', 'R')
def __init__(self, left=None, right=None, center=None):
if left is None:
left = _HeaderFooterPart()
self.left = left
if center is None:
center = _HeaderFooterPart()
self.center = center
if right is None:
right = _HeaderFooterPart()
self.right = right
def __str__(self):
"""
Pack parts into a single string
"""
TRANSFORM = {'&[Tab]': '&A', '&[Pages]': '&N', '&[Date]': '&D',
'&[Path]': '&Z', '&[Page]': '&P', '&[Time]': '&T', '&[File]': '&F',
'&[Picture]': '&G'}
# escape keys and create regex
SUBS_REGEX = re.compile("|".join(["({0})".format(re.escape(k))
for k in TRANSFORM]))
def replace(match):
"""
Callback for re.sub
Replace expanded control with mini-format equivalent
"""
sub = match.group(0)
return TRANSFORM[sub]
txt = []
for key, part in zip(
self.__keys, [self.left, self.center, self.right]):
if part.text is not None:
txt.append(u"&{0}{1}".format(key, str(part)))
txt = "".join(txt)
txt = SUBS_REGEX.sub(replace, txt)
return escape(txt)
def __bool__(self):
return any([self.left, self.center, self.right])
def to_tree(self, tagname):
"""
Return as XML node
"""
el = Element(tagname)
el.text = str(self)
return el
@classmethod
def from_tree(cls, node):
if node.text:
text = unescape(node.text)
parts = _split_string(text)
for k, v in parts.items():
if v is not None:
parts[k] = _HeaderFooterPart.from_str(v)
self = cls(**parts)
return self
class ShapeProperties(Serialisable):
"""
Somewhat vaguely 21.2.2.197 says this:
This element specifies the formatting for the parent chart element. The
custGeom, prstGeom, scene3d, and xfrm elements are not supported. The
bwMode attribute is not supported.
This doesn't leave much. And the element is used in different places.
"""
tagname = "spPr"
bwMode = NoneSet(values=([
'clr', 'auto', 'gray', 'ltGray', 'invGray', 'grayWhite', 'blackGray',
'blackWhite', 'black', 'white', 'hidden'
]))
xfrm = Typed(expected_type=Transform2D, allow_none=True)
transform = Alias('xfrm')
custGeom = Typed(expected_type=CustomGeometry2D,
allow_none=True) # either or
prstGeom = Typed(expected_type=PresetGeometry2D, allow_none=True)
# fills one of
noFill = EmptyTag(namespace=DRAWING_NS)
solidFill = ColorChoiceDescriptor()
gradFill = Typed(expected_type=GradientFillProperties, allow_none=True)
pattFill = Typed(expected_type=PatternFillProperties, allow_none=True)
#pattFill = Typed(expected_type=CT_PatternFillProperties)
#grpFill = Typed(expected_type=CT_GroupFillProperties)
ln = Typed(expected_type=LineProperties, allow_none=True)
line = Alias('ln')
scene3d = Typed(expected_type=Scene3D, allow_none=True)
sp3d = Typed(expected_type=Shape3D, allow_none=True)
shape3D = Alias('sp3d')
extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True)
__elements__ = ('xfrm', 'prstGeom', 'noFill', 'solidFill', 'gradFill',
'pattFill', 'ln', 'scene3d', 'sp3d')
def __init__(
self,
bwMode=None,
xfrm=None,
noFill=None,
solidFill=None,
gradFill=None,
pattFill=None,
ln=None,
scene3d=None,
custGeom=None,
prstGeom=None,
sp3d=None,
extLst=None,
):
self.bwMode = bwMode
self.xfrm = xfrm
self.noFill = noFill
self.solidFill = solidFill
self.gradFill = gradFill
self.pattFill = pattFill
if ln is None:
ln = LineProperties()
self.ln = ln
self.custGeom = custGeom
self.prstGeom = prstGeom
self.scene3d = scene3d
self.sp3d = sp3d
class _BaseAxis(Serialisable):
axId = NestedInteger(expected_type=int)
scaling = Typed(expected_type=Scaling)
delete = NestedBool(allow_none=True)
axPos = NestedSet(values=(['b', 'l', 'r', 't']))
majorGridlines = Typed(expected_type=ChartLines, allow_none=True)
minorGridlines = Typed(expected_type=ChartLines, allow_none=True)
title = TitleDescriptor()
numFmt = NumberFormatDescriptor()
number_format = Alias("numFmt")
majorTickMark = NestedNoneSet(values=(['cross', 'in', 'out']))
minorTickMark = NestedNoneSet(values=(['cross', 'in', 'out']))
tickLblPos = NestedNoneSet(values=(['high', 'low', 'nextTo']))
spPr = Typed(expected_type=GraphicalProperties, allow_none=True)
graphicalProperties = Alias('spPr')
txPr = Typed(expected_type=RichText, allow_none=True)
textProperties = Alias('txPr')
crossAx = NestedInteger(expected_type=int) # references other axis
crosses = NestedNoneSet(values=(['autoZero', 'max', 'min']))
crossesAt = NestedFloat(allow_none=True)
# crosses & crossesAt are mutually exclusive
__elements__ = ('axId', 'scaling', 'delete', 'axPos', 'majorGridlines',
'minorGridlines', 'numFmt', 'majorTickMark',
'minorTickMark', 'tickLblPos', 'spPr', 'title', 'txPr',
'crossAx', 'crosses', 'crossesAt')
def __init__(
self,
axId=None,
scaling=None,
delete=None,
axPos='l',
majorGridlines=None,
minorGridlines=None,
title=None,
numFmt=None,
majorTickMark=None,
minorTickMark=None,
tickLblPos=None,
spPr=None,
txPr=None,
crossAx=None,
crosses=None,
crossesAt=None,
):
self.axId = axId
if scaling is None:
scaling = Scaling()
self.scaling = Scaling()
self.delete = delete
self.axPos = axPos
self.majorGridlines = majorGridlines
self.minorGridlines = minorGridlines
self.title = title
self.numFmt = numFmt
self.majorTickMark = majorTickMark
self.minorTickMark = minorTickMark
self.tickLblPos = tickLblPos
self.spPr = spPr
self.txPr = txPr
self.crossAx = crossAx
self.crosses = crosses
self.crossesAt = None
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=str)
formula2 = NestedText(allow_none=True, expected_type=str)
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,
showErrorMessage=True,
showInputMessage=True,
showDropDown=None,
allowBlank=None,
sqref=(),
promptTitle=None,
errorStyle=None,
error=None,
prompt=None,
errorTitle=None,
imeMode=None,
operator=None,
allow_blank=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 PlotArea(Serialisable):
tagname = "plotArea"
layout = Typed(expected_type=Layout, allow_none=True)
dTable = Typed(expected_type=DataTable, allow_none=True)
spPr = Typed(expected_type=GraphicalProperties, allow_none=True)
graphicalProperties = Alias("spPr")
extLst = Typed(expected_type=ExtensionList, allow_none=True)
# at least one chart
_charts = MultiSequence()
areaChart = MultiSequencePart(expected_type=AreaChart, store="_charts")
area3DChart = MultiSequencePart(expected_type=AreaChart3D, store="_charts")
lineChart = MultiSequencePart(expected_type=LineChart, store="_charts")
line3DChart = MultiSequencePart(expected_type=LineChart3D, store="_charts")
stockChart = MultiSequencePart(expected_type=StockChart, store="_charts")
radarChart = MultiSequencePart(expected_type=RadarChart, store="_charts")
scatterChart = MultiSequencePart(expected_type=ScatterChart, store="_charts")
pieChart = MultiSequencePart(expected_type=PieChart, store="_charts")
pie3DChart = MultiSequencePart(expected_type=PieChart3D, store="_charts")
doughnutChart = MultiSequencePart(expected_type=DoughnutChart, store="_charts")
barChart = MultiSequencePart(expected_type=BarChart, store="_charts")
bar3DChart = MultiSequencePart(expected_type=BarChart3D, store="_charts")
ofPieChart = MultiSequencePart(expected_type=ProjectedPieChart, store="_charts")
surfaceChart = MultiSequencePart(expected_type=SurfaceChart, store="_charts")
surface3DChart = MultiSequencePart(expected_type=SurfaceChart3D, store="_charts")
bubbleChart = MultiSequencePart(expected_type=BubbleChart, store="_charts")
# axes
_axes = MultiSequence()
valAx = MultiSequencePart(expected_type=NumericAxis, store="_axes")
catAx = MultiSequencePart(expected_type=TextAxis, store="_axes")
dateAx = MultiSequencePart(expected_type=DateAxis, store="_axes")
serAx = MultiSequencePart(expected_type=SeriesAxis, store="_axes")
__elements__ = ('layout', '_charts', '_axes', 'dTable', 'spPr')
def __init__(self,
layout=None,
dTable=None,
spPr=None,
_charts=(),
_axes=(),
extLst=None,
):
self.layout = layout
self.dTable = dTable
self.spPr = spPr
self._charts = _charts
self._axes = _axes
def to_tree(self, tagname=None, idx=None, namespace=None):
axIds = set((ax.axId for ax in self._axes))
for chart in self._charts:
for id, axis in chart._axes.items():
if id not in axIds:
setattr(self, axis.tagname, axis)
axIds.add(id)
return super(PlotArea, self).to_tree(tagname)
@classmethod
def from_tree(cls, node):
self = super(PlotArea, cls).from_tree(node)
axes = dict((axis.axId, axis) for axis in self._axes)
for chart in self._charts:
if isinstance(chart, ScatterChart):
x, y = (axes[axId] for axId in chart.axId)
chart.x_axis = x
chart.y_axis = y
continue
for axId in chart.axId:
axis = axes.get(axId)
if axis is None and isinstance(chart, _3DBase):
# Series Axis can be optional
chart.z_axis = None
continue
if axis.tagname in ("catAx", "dateAx"):
chart.x_axis = axis
elif axis.tagname == "valAx":
chart.y_axis = axis
elif axis.tagname == "serAx":
chart.z_axis = axis
return self
class ChartBase(Serialisable):
"""
Base class for all charts
"""
legend = Typed(expected_type=Legend, allow_none=True)
layout = Typed(expected_type=Layout, allow_none=True)
_series_type = ""
ser = ()
series = Alias('ser')
title = TitleDescriptor()
anchor = "E15" # default anchor position
width = 15 # in cm, approx 5 rows
height = 7.5 # in cm, approx 14 rows
_id = 1
_path = "/xl/charts/chart{0}.xml"
style = Integer(allow_none=True)
mime_type = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml"
graphical_properties = Typed(expected_type=GraphicalProperties,
allow_none=True)
__elements__ = ()
def __init__(self, **kw):
self._charts = [self]
self.title = None
self.layout = None
self.legend = Legend()
self.graphical_properties = None
self.style = None
self.plot_area = PlotArea()
super(ChartBase, self).__init__(**kw)
def __hash__(self):
"""
Just need to check for identity
"""
return id(self)
def __iadd__(self, other):
"""
Combine the chart with another one
"""
if not isinstance(other, ChartBase):
raise TypeError("Only other charts can be added")
self._charts.append(other)
return self
def to_tree(self, tagname=None, idx=None):
if self.ser is not None:
for s in self.ser:
s.__elements__ = attribute_mapping[self._series_type]
return super(ChartBase, self).to_tree(tagname, idx)
def _write(self):
from .chartspace import ChartSpace, ChartContainer
self.plot_area = PlotArea()
self.plot_area.layout = self.layout
self.plot_area.graphical_properties = self.graphical_properties
idx_base = 0
for chart in self._charts:
chart.idx_base = idx_base
self.plot_area._charts.append(chart)
idx_base += len(chart.series)
axIds = []
for axId in ("x_axis", "y_axis", 'z_axis'):
for chart in self._charts:
axis = getattr(chart, axId, None)
if axis is None:
continue
if axis.axId not in axIds:
ax = getattr(self.plot_area, axis.tagname)
ax.append(axis)
axIds.append(axis.axId)
container = ChartContainer(plotArea=self.plot_area,
legend=self.legend,
title=self.title)
if isinstance(chart, _3DBase):
container.view3D = chart.view3D
container.floor = chart.floor
container.sideWall = chart.sideWall
container.backWall = chart.backWall
cs = ChartSpace(chart=container)
cs.style = self.style
tree = cs.to_tree()
tree.set("xmlns", CHART_NS)
return tree
@property
def axId(self):
x = getattr(self, "x_axis", None)
y = getattr(self, "y_axis", None)
z = getattr(self, "z_axis", None)
ids = [AxId(axis.axId) for axis in (x, y, z) if axis]
return ids
def set_categories(self, labels):
"""
Set the categories / x-axis values
"""
if not isinstance(labels, Reference):
labels = Reference(range_string=labels)
for s in self.ser:
s.cat = AxDataSource(numRef=NumRef(f=labels))
def add_data(self, data, from_rows=False, titles_from_data=False):
"""
Add a range of data in a single pass.
The default is to treat each column as a data series.
"""
if not isinstance(data, Reference):
data = Reference(range_string=data)
if from_rows:
values = data.rows
else:
values = data.cols
for v in values:
range_string = u"{0}!{1}:{2}".format(data.sheetname, v[0], v[-1])
series = SeriesFactory(range_string,
title_from_data=titles_from_data)
self.ser.append(series)
def append(self, value):
"""Append a data series to the chart"""
l = self.series[:]
l.append(value)
self.series = l
@property
def path(self):
return self._path.format(self._id)
class DefinedName(Serialisable):
tagname = "definedName"
name = String() # unique per workbook/worksheet
comment = String(allow_none=True)
customMenu = String(allow_none=True)
description = String(allow_none=True)
help = String(allow_none=True)
statusBar = String(allow_none=True)
localSheetId = Integer(allow_none=True)
hidden = Bool(allow_none=True)
function = Bool(allow_none=True)
vbProcedure = Bool(allow_none=True)
xlm = Bool(allow_none=True)
functionGroupId = Integer(allow_none=True)
shortcutKey = String(allow_none=True)
publishToServer = Bool(allow_none=True)
workbookParameter = Bool(allow_none=True)
attr_text = Descriptor()
value = Alias("attr_text")
def __init__(self,
name=None,
comment=None,
customMenu=None,
description=None,
help=None,
statusBar=None,
localSheetId=None,
hidden=None,
function=None,
vbProcedure=None,
xlm=None,
functionGroupId=None,
shortcutKey=None,
publishToServer=None,
workbookParameter=None,
attr_text=None):
self.name = name
self.comment = comment
self.customMenu = customMenu
self.description = description
self.help = help
self.statusBar = statusBar
self.localSheetId = localSheetId
self.hidden = hidden
self.function = function
self.vbProcedure = vbProcedure
self.xlm = xlm
self.functionGroupId = functionGroupId
self.shortcutKey = shortcutKey
self.publishToServer = publishToServer
self.workbookParameter = workbookParameter
self.attr_text = attr_text
@property
def type(self):
tok = Tokenizer("=" + self.value)
parsed = tok.items[0]
if parsed.type == "OPERAND":
return parsed.subtype
return parsed.type
@property
def destinations(self):
if self.type == "RANGE":
tok = Tokenizer("=" + self.value)
for part in tok.items:
if part.subtype == "RANGE":
m = SHEETRANGE_RE.match(part.value)
sheetname = m.group('notquoted') or m.group('quoted')
yield sheetname, m.group('cells')
@property
def is_reserved(self):
m = RESERVED_REGEX.match(self.name)
if m:
return m.group("name")
@property
def is_external(self):
return re.compile(r"^\[\d+\].*").match(self.value) is not None
def __iter__(self):
for key in self.__attrs__:
if key == "attr_text":
continue
v = getattr(self, key)
if v is not None:
if v in RESERVED:
v = "_xlnm." + v
yield key, safe_string(v)
class ChartBase(Serialisable):
"""
Base class for all charts
"""
legend = Typed(expected_type=Legend, allow_none=True)
layout = Typed(expected_type=Layout, allow_none=True)
roundedCorners = Bool(allow_none=True)
axId = ValueSequence(expected_type=int)
visible_cells_only = Bool(allow_none=True)
display_blanks = Set(values=['span', 'gap', 'zero'])
_series_type = ""
ser = ()
series = Alias('ser')
title = TitleDescriptor()
anchor = "E15" # default anchor position
width = 15 # in cm, approx 5 rows
height = 7.5 # in cm, approx 14 rows
_id = 1
_path = "/xl/charts/chart{0}.xml"
style = MinMax(allow_none=True, min=1, max=48)
mime_type = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml"
graphical_properties = Typed(expected_type=GraphicalProperties,
allow_none=True)
__elements__ = ()
def __init__(self, axId=(), **kw):
self._charts = [self]
self.title = None
self.layout = None
self.roundedCorners = None
self.legend = Legend()
self.graphical_properties = None
self.style = None
self.plot_area = PlotArea()
self.axId = axId
self.display_blanks = 'gap'
self.pivotSource = None
self.pivotFormats = ()
self.visible_cells_only = True
self.idx_base = 0
super(ChartBase, self).__init__()
def __hash__(self):
"""
Just need to check for identity
"""
return id(self)
def __iadd__(self, other):
"""
Combine the chart with another one
"""
if not isinstance(other, ChartBase):
raise TypeError("Only other charts can be added")
self._charts.append(other)
return self
def to_tree(self, namespace=None, tagname=None, idx=None):
self.axId = [id for id in self._axes]
if self.ser is not None:
for s in self.ser:
s.__elements__ = attribute_mapping[self._series_type]
return super(ChartBase, self).to_tree(tagname, idx)
def _reindex(self):
"""
Normalise and rebase series: sort by order and then rebase order
"""
# sort data series in order and rebase
ds = sorted(self.series, key=attrgetter("order"))
for idx, s in enumerate(ds):
s.order = idx
self.series = ds
def _write(self):
from .chartspace import ChartSpace, ChartContainer
self.plot_area.layout = self.layout
idx_base = self.idx_base
for chart in self._charts:
if chart not in self.plot_area._charts:
chart.idx_base = idx_base
idx_base += len(chart.series)
self.plot_area._charts = self._charts
container = ChartContainer(plotArea=self.plot_area,
legend=self.legend,
title=self.title)
if isinstance(chart, _3DBase):
container.view3D = chart.view3D
container.floor = chart.floor
container.sideWall = chart.sideWall
container.backWall = chart.backWall
container.plotVisOnly = self.visible_cells_only
container.dispBlanksAs = self.display_blanks
container.pivotFmts = self.pivotFormats
cs = ChartSpace(chart=container)
cs.style = self.style
cs.roundedCorners = self.roundedCorners
cs.pivotSource = self.pivotSource
return cs.to_tree()
@property
def _axes(self):
x = getattr(self, "x_axis", None)
y = getattr(self, "y_axis", None)
z = getattr(self, "z_axis", None)
return OrderedDict([(axis.axId, axis) for axis in (x, y, z) if axis])
def set_categories(self, labels):
"""
Set the categories / x-axis values
"""
if not isinstance(labels, Reference):
labels = Reference(range_string=labels)
for s in self.ser:
s.cat = AxDataSource(numRef=NumRef(f=labels))
def add_data(self, data, from_rows=False, titles_from_data=False):
"""
Add a range of data in a single pass.
The default is to treat each column as a data series.
"""
if not isinstance(data, Reference):
data = Reference(range_string=data)
if from_rows:
values = data.rows
else:
values = data.cols
for ref in values:
series = SeriesFactory(ref, title_from_data=titles_from_data)
self.series.append(series)
def append(self, value):
"""Append a data series to the chart"""
l = self.series[:]
l.append(value)
self.series = l
@property
def path(self):
return self._path.format(self._id)
class DrawingHF(Serialisable):
id = Relation()
lho = Integer(allow_none=True)
leftHeaderOddPages = Alias('lho')
lhe = Integer(allow_none=True)
leftHeaderEvenPages = Alias('lhe')
lhf = Integer(allow_none=True)
leftHeaderFirstPage = Alias('lhf')
cho = Integer(allow_none=True)
centerHeaderOddPages = Alias('cho')
che = Integer(allow_none=True)
centerHeaderEvenPages = Alias('che')
chf = Integer(allow_none=True)
centerHeaderFirstPage = Alias('chf')
rho = Integer(allow_none=True)
rightHeaderOddPages = Alias('rho')
rhe = Integer(allow_none=True)
rightHeaderEvenPages = Alias('rhe')
rhf = Integer(allow_none=True)
rightHeaderFirstPage = Alias('rhf')
lfo = Integer(allow_none=True)
leftFooterOddPages = Alias('lfo')
lfe = Integer(allow_none=True)
leftFooterEvenPages = Alias('lfe')
lff = Integer(allow_none=True)
leftFooterFirstPage = Alias('lff')
cfo = Integer(allow_none=True)
centerFooterOddPages = Alias('cfo')
cfe = Integer(allow_none=True)
centerFooterEvenPages = Alias('cfe')
cff = Integer(allow_none=True)
centerFooterFirstPage = Alias('cff')
rfo = Integer(allow_none=True)
rightFooterOddPages = Alias('rfo')
rfe = Integer(allow_none=True)
rightFooterEvenPages = Alias('rfe')
rff = Integer(allow_none=True)
rightFooterFirstPage = Alias('eff')
def __init__(
self,
id=None,
lho=None,
lhe=None,
lhf=None,
cho=None,
che=None,
chf=None,
rho=None,
rhe=None,
rhf=None,
lfo=None,
lfe=None,
lff=None,
cfo=None,
cfe=None,
cff=None,
rfo=None,
rfe=None,
rff=None,
):
self.id = id
self.lho = lho
self.lhe = lhe
self.lhf = lhf
self.cho = cho
self.che = che
self.chf = chf
self.rho = rho
self.rhe = rhe
self.rhf = rhf
self.lfo = lfo
self.lfe = lfe
self.lff = lff
self.cfo = cfo
self.cfe = cfe
self.cff = cff
self.rfo = rfo
self.rfe = rfe
self.rff = rff
class Series(Serialisable):
"""
Generic series object. Should not be instantiated directly.
User the chart.Series factory instead.
"""
tagname = "ser"
idx = NestedInteger()
order = NestedInteger()
tx = Typed(expected_type=SeriesLabel, allow_none=True)
title = Alias('tx')
spPr = Typed(expected_type=GraphicalProperties, allow_none=True)
graphicalProperties = Alias('spPr')
# area chart
pictureOptions = Typed(expected_type=PictureOptions, allow_none=True)
dPt = Sequence(expected_type=DataPoint, allow_none=True)
data_points = Alias("dPt")
dLbls = Typed(expected_type=DataLabelList, allow_none=True)
labels = Alias("dLbls")
trendline = Typed(expected_type=Trendline, allow_none=True)
errBars = Typed(expected_type=ErrorBars, allow_none=True)
cat = Typed(expected_type=AxDataSource, allow_none=True)
identifiers = Alias("cat")
val = Typed(expected_type=NumDataSource, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
#bar chart
invertIfNegative = NestedBool(allow_none=True)
shape = NestedNoneSet(values=(['cone', 'coneToMax', 'box', 'cylinder', 'pyramid', 'pyramidToMax']))
#bubble chart
xVal = Typed(expected_type=AxDataSource, allow_none=True)
yVal = Typed(expected_type=NumDataSource, allow_none=True)
bubbleSize = Typed(expected_type=NumDataSource, allow_none=True)
zVal = Alias("bubbleSize")
bubble3D = NestedBool(allow_none=True)
#line chart
marker = Typed(expected_type=Marker, allow_none=True)
smooth = NestedBool(allow_none=True)
#pie chart
explosion = NestedInteger(allow_none=True)
__elements__ = ()
def __init__(self,
idx=0,
order=0,
tx=None,
spPr=None,
pictureOptions=None,
dPt=(),
dLbls=None,
trendline=None,
errBars=None,
cat=None,
val=None,
invertIfNegative=None,
shape=None,
xVal=None,
yVal=None,
bubbleSize=None,
bubble3D=None,
marker=None,
smooth=None,
explosion=None,
extLst=None,
):
self.idx = idx
self.order = order
self.tx = tx
if spPr is None:
spPr = GraphicalProperties()
self.spPr = spPr
self.pictureOptions = pictureOptions
self.dPt = dPt
self.dLbls = dLbls
self.trendline = trendline
self.errBars = errBars
self.cat = cat
self.val = val
self.invertIfNegative = invertIfNegative
self.shape = shape
self.xVal = xVal
self.yVal = yVal
self.bubbleSize = bubbleSize
self.bubble3D = bubble3D
if marker is None:
marker = Marker()
self.marker = marker
self.smooth = smooth
self.explosion = explosion
def to_tree(self, tagname=None, idx=None):
"""The index can need rebasing"""
if idx is not None:
if self.order == self.idx:
self.order = idx # rebase the order if the index has been rebased
self.idx = idx
return super(Series, self).to_tree(tagname)
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
