class NumVal(Serialisable):
idx = Integer()
formatCode = NestedText(allow_none=True, expected_type=unicode)
v = NestedText(allow_none=True, expected_type=float)
def __init__(self,
idx=None,
formatCode=None,
v=None,
):
self.idx = idx
self.formatCode = formatCode
self.v = v
class PivotSource(Serialisable):
tagname = "pivotSource"
name = NestedText(expected_type=unicode)
fmtId = NestedText(expected_type=int)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('name', 'fmtId')
def __init__(self,
name=None,
fmtId=None,
extLst=None,
):
self.name = name
self.fmtId = fmtId
class AnchorMarker(Serialisable):
tagname = "marker"
col = NestedText(expected_type=int)
colOff = NestedText(expected_type=int)
row = NestedText(expected_type=int)
rowOff = NestedText(expected_type=int)
def __init__(self,
col=0,
colOff=0,
row=0,
rowOff=0,
):
self.col = col
self.colOff = colOff
self.row = row
self.rowOff = rowOff
class SeriesLabel(Serialisable):
tagname = "tx"
strRef = Typed(expected_type=StrRef, allow_none=True)
v = NestedText(expected_type=str, allow_none=True)
value = Alias('v')
__elements__ = ('strRef', 'v')
def __init__(self, strRef=None, v=None):
self.strRef = strRef
self.v = v
class StrVal(Serialisable):
tagname = "strVal"
idx = Integer()
v = NestedText(expected_type=unicode)
def __init__(self,
idx=0,
v=None,
):
self.idx = idx
self.v = v
class NumVal(Serialisable):
idx = Integer()
formatCode = NestedText(allow_none=True, expected_type=str)
v = NumberValueDescriptor()
def __init__(
self,
idx=None,
formatCode=None,
v=None,
):
self.idx = idx
self.formatCode = formatCode
self.v = v
class NumRef(Serialisable):
f = NestedText(expected_type=unicode)
ref = Alias('f')
numCache = Typed(expected_type=NumData, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('f', 'numCache')
def __init__(self,
f=None,
numCache=None,
extLst=None,
):
self.f = f
self.numCache = numCache
class StrRef(Serialisable):
tagname = "strRef"
f = NestedText(expected_type=unicode, allow_none=True)
strCache = Typed(expected_type=StrData, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('f', 'strCache')
def __init__(self,
f=None,
strCache=None,
extLst=None,
):
self.f = f
self.strCache = strCache
class ExternalCell(Serialisable):
r = String()
t = NoneSet(values=(['b', 'd', 'n', 'e', 's', 'str', 'inlineStr']))
vm = Integer(allow_none=True)
v = NestedText(allow_none=True, expected_type=unicode)
def __init__(self,
r=None,
t=None,
vm=None,
v=None,
):
self.r = r
self.t = t
self.vm = vm
self.v = v
class RichText(Serialisable):
tagname = "RElt"
rPr = Typed(expected_type=InlineFont, allow_none=True)
font = Alias("rPr")
t = NestedText(expected_type=str, allow_none=True)
text = Alias("t")
__elements__ = ('rPr', 't')
def __init__(self,
rPr=None,
t=None,
):
self.rPr = rPr
self.t = t
class PhoneticText(Serialisable):
tagname = "rPh"
sb = Integer()
eb = Integer()
t = NestedText(expected_type=str)
text = Alias('t')
def __init__(self,
sb=None,
eb=None,
t=None,
):
self.sb = sb
self.eb = eb
self.t = t
class NumData(Serialisable):
formatCode = NestedText(expected_type=unicode, allow_none=True)
ptCount = NestedInteger(allow_none=True)
pt = Sequence(expected_type=NumVal)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('formatCode', 'ptCount', 'pt')
def __init__(self,
formatCode=None,
ptCount=None,
pt=(),
extLst=None,
):
self.formatCode = formatCode
self.ptCount = ptCount
self.pt = pt
class RegularTextRun(Serialisable):
tagname = "r"
namespace = DRAWING_NS
rPr = Typed(expected_type=CharacterProperties, allow_none=True)
properties = Alias("rPr")
t = NestedText(expected_type=unicode)
value = Alias("t")
__elements__ = ('rPr', 't')
def __init__(self,
rPr=None,
t="",
):
self.rPr = rPr
self.t = t
class MultiLevelStrRef(Serialisable):
tagname = "multiLvlStrRef"
f = NestedText(expected_type=unicode)
multiLvlStrCache = Typed(expected_type=MultiLevelStrData, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)
__elements__ = ('multiLvlStrCache', 'f')
def __init__(
self,
f=None,
multiLvlStrCache=None,
extLst=None,
):
self.f = f
self.multiLvlStrCache = multiLvlStrCache
class Text(Serialisable):
tagname = "text"
t = NestedText(allow_none=True, expected_type=unicode)
plain = Alias("t")
r = Sequence(expected_type=RichText, allow_none=True)
formatted = Alias("r")
rPh = Sequence(expected_type=PhoneticText, allow_none=True)
phonetic = Alias("rPh")
phoneticPr = Typed(expected_type=PhoneticProperties, allow_none=True)
PhoneticProperties = Alias("phoneticPr")
__elements__ = ('t', 'r', 'rPh', 'phoneticPr')
def __init__(
self,
t=None,
r=(),
rPh=(),
phoneticPr=None,
):
self.t = t
self.r = r
self.rPh = rPh
self.phoneticPr = phoneticPr
@property
def content(self):
"""
Text stripped of all formatting
"""
snippets = []
if self.plain is not None:
snippets.append(self.plain)
isrich = False
for block in self.formatted:
if block.t is not None:
isrich = True
snippets.append(block.t)
rv = u"".join(snippets)
if isrich:
return IAMRich(rv, self)
return rv
class ExtendedProperties(Serialisable):
"""
See 22.2
Most of this is irrelevant
"""
tagname = "Properties"
Template = NestedText(expected_type=unicode, allow_none=True)
Manager = NestedText(expected_type=unicode, allow_none=True)
Company = NestedText(expected_type=unicode, allow_none=True)
Pages = NestedText(expected_type=int, allow_none=True)
Words = NestedText(expected_type=int, allow_none=True)
Characters = NestedText(expected_type=int, allow_none=True)
PresentationFormat = NestedText(expected_type=unicode, allow_none=True)
Lines = NestedText(expected_type=int, allow_none=True)
Paragraphs = NestedText(expected_type=int, allow_none=True)
Slides = NestedText(expected_type=int, allow_none=True)
Notes = NestedText(expected_type=int, allow_none=True)
TotalTime = NestedText(expected_type=int, allow_none=True)
HiddenSlides = NestedText(expected_type=int, allow_none=True)
MMClips = NestedText(expected_type=int, allow_none=True)
ScaleCrop = NestedText(expected_type=bool, allow_none=True)
HeadingPairs = Typed(expected_type=VectorVariant, allow_none=True)
TitlesOfParts = Typed(expected_type=VectorLpstr, allow_none=True)
LinksUpToDate = NestedText(expected_type=bool, allow_none=True)
CharactersWithSpaces = NestedText(expected_type=int, allow_none=True)
SharedDoc = NestedText(expected_type=bool, allow_none=True)
HyperlinkBase = NestedText(expected_type=unicode, allow_none=True)
HLinks = Typed(expected_type=VectorVariant, allow_none=True)
HyperlinksChanged = NestedText(expected_type=bool, allow_none=True)
DigSig = Typed(expected_type=DigSigBlob, allow_none=True)
Application = NestedText(expected_type=unicode, allow_none=True)
AppVersion = NestedText(expected_type=unicode, allow_none=True)
DocSecurity = NestedText(expected_type=int, allow_none=True)
__elements__ = ('Application', 'AppVersion', 'DocSecurity', 'ScaleCrop',
'LinksUpToDate', 'SharedDoc', 'HyperlinksChanged')
def __init__(
self,
Template=None,
Manager=None,
Company=None,
Pages=None,
Words=None,
Characters=None,
PresentationFormat=None,
Lines=None,
Paragraphs=None,
Slides=None,
Notes=None,
TotalTime=None,
HiddenSlides=None,
MMClips=None,
ScaleCrop=None,
HeadingPairs=None,
TitlesOfParts=None,
LinksUpToDate=None,
CharactersWithSpaces=None,
SharedDoc=None,
HyperlinkBase=None,
HLinks=None,
HyperlinksChanged=None,
DigSig=None,
Application="Microsoft Excel",
AppVersion=VERSION,
DocSecurity=None,
):
self.Template = Template
self.Manager = Manager
self.Company = Company
self.Pages = Pages
self.Words = Words
self.Characters = Characters
self.PresentationFormat = PresentationFormat
self.Lines = Lines
self.Paragraphs = Paragraphs
self.Slides = Slides
self.Notes = Notes
self.TotalTime = TotalTime
self.HiddenSlides = HiddenSlides
self.MMClips = MMClips
self.ScaleCrop = ScaleCrop
self.HeadingPairs = None
self.TitlesOfParts = None
self.LinksUpToDate = LinksUpToDate
self.CharactersWithSpaces = CharactersWithSpaces
self.SharedDoc = SharedDoc
self.HyperlinkBase = HyperlinkBase
self.HLinks = None
self.HyperlinksChanged = HyperlinksChanged
self.DigSig = None
self.Application = Application
self.AppVersion = AppVersion
self.DocSecurity = DocSecurity
def to_tree(self):
tree = super(ExtendedProperties, self).to_tree()
tree.set("xmlns", XPROPS_NS)
return tree
class CustomDocumentProperty(Serialisable):
"""
to read/write a single Workbook.CustomDocumentProperty saved in 'docProps/custom.xml'
"""
tagname = "property"
name = String(allow_none=True)
lpwstr = NestedText(expected_type=str, allow_none=True, namespace=VTYPES_NS)
i4 = NestedText(expected_type=int, allow_none=True, namespace=VTYPES_NS)
r8 = NestedText(expected_type=float, allow_none=True, namespace=VTYPES_NS)
filetime = NestedDateTime(allow_none=True, namespace=VTYPES_NS)
bool = NestedText(expected_type=bool, allow_none=True, namespace=VTYPES_NS)
linkTarget = String(expected_type=str, allow_none=True)
fmtid = String()
pid = Integer()
def __init__(self,
name=None,
value=None,
typ=None,
lpwstr=None,
i4=None,
r8=None,
filetime=None,
bool=None,
linkTarget=None,
pid=0,
fmtid=CPROPS_FMTID):
self.fmtid = fmtid
self.pid = pid
self.name = name
self.lpwstr = lpwstr
self.i4 = i4
self.r8 = r8
self.filetime = filetime
self.bool = bool
self.linkTarget = linkTarget
if linkTarget is not None:
self.lpwstr = ""
if value is not None:
t = type(value)
prop = KNOWN_TYPES.get(t)
if prop is not None:
setattr(self, prop, value)
elif typ is not None and typ in XML_TYPES:
setattr(self, typ, value)
else:
raise ValueError(f"Unknown type {t}")
@property
def value(self):
"""Return the value from the active property"""
for a in self.__elements__:
v = getattr(self, a)
if v is not None:
return v
@property
def type(self):
for a in self.__elements__:
if getattr(self, a) is not None:
return a
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 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 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 DataValidation(Serialisable):
tagname = "dataValidation"
showErrorMessage = Bool()
showDropDown = Bool(allow_none=True)
showInputMessage = Bool()
showErrorMessage = Bool()
allowBlank = Bool()
allow_blank = Bool()
errorTitle = String(allow_none=True)
error = String(allow_none=True)
promptTitle = String(allow_none=True)
prompt = String(allow_none=True)
sqref = String(allow_none=True)
formula1 = NestedText(allow_none=True, expected_type=unicode)
formula2 = NestedText(allow_none=True, expected_type=unicode)
type = NoneSet(values=("whole", "decimal", "list", "date", "time",
"textLength", "custom"))
errorStyle = NoneSet(values=("stop", "warning", "information"))
imeMode = NoneSet(values=("noControl", "off", "on", "disabled", "hiragana",
"fullKatakana", "halfKatakana", "fullAlpha",
"halfAlpha", "fullHangul", "halfHangul"))
operator = NoneSet(values=("between", "notBetween", "equal", "notEqual",
"lessThan", "lessThanOrEqual", "greaterThan",
"greaterThanOrEqual"))
def __init__(
self,
type=None,
formula1=None,
formula2=None,
allow_blank=False,
showErrorMessage=True,
showInputMessage=True,
showDropDown=None,
allowBlank=None,
sqref=None,
promptTitle=None,
errorStyle=None,
error=None,
prompt=None,
errorTitle=None,
imeMode=None,
operator=None,
validation_type=None, # remove in future
):
self.showDropDown = showDropDown
self.imeMode = imeMode
self.operator = operator
self.formula1 = formula1
self.formula2 = formula2
self.allowBlank = allow_blank
if allowBlank is not None:
self.allowBlank = allowBlank
self.showErrorMessage = showErrorMessage
self.showInputMessage = showInputMessage
if validation_type is not None:
warnings.warn(
"Use 'DataValidation(type={0})'".format(validation_type))
if type is not None:
self.type = validation_type
self.type = type
self.cells = set()
self.ranges = []
if sqref is not None:
self.sqref = sqref
self.promptTitle = promptTitle
self.errorStyle = errorStyle
self.error = error
self.prompt = prompt
self.errorTitle = errorTitle
def to_tree(self, tagname=None):
attrs = dict(self)
el = Element(self.tagname, attrs)
for n in self.__nested__:
value = getattr(self, n)
if value:
SubElement(el, n).text = value
return el
@deprecated("Use DataValidation.add(). Will be removed in 2.4")
def add_cell(self, cell):
"""Adds a openpyxl.cell to this validator"""
self.add(cell)
def add(self, cell):
"""Adds a openpyxl.cell to this validator"""
self.cells.add(cell.coordinate)
@deprecated(
"Set DataValidation.ErrorTitle and DataValidation.error Will be removed in 2.4"
)
def set_error_message(self, error, error_title="Validation Error"):
"""Creates a custom error message, displayed when a user changes a cell
to an invalid value"""
self.errorTitle = error_title
self.error = error
@deprecated(
"Set DataValidation.PromptTitle and DataValidation.prompt Will be removed in 2.4"
)
def set_prompt_message(self, prompt, prompt_title="Validation Prompt"):
"""Creates a custom prompt message"""
self.promptTitle = prompt_title
self.prompt = prompt
@property
def sqref(self):
return collapse_cell_addresses(self.cells, self.ranges)
@sqref.setter
def sqref(self, range_string):
self.cells = expand_cell_ranges(range_string)
def __iter__(self):
for attr in ('type', 'allowBlank', 'operator', 'sqref',
'showInputMessage', 'showErrorMessage', 'errorTitle',
'error', 'errorStyle', 'promptTitle', 'prompt'):
value = getattr(self, attr)
if value is not None:
yield attr, safe_string(value)
