def write_cols(worksheet):
"""Write worksheet columns to xml.
<cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
col_def = dict(dimension)
if col_def == {}:
continue
idx = column_index_from_string(label)
cols.append((idx, col_def))
if not cols:
return
el = Element('cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
cmin = col_def.get('min') or v
cmax = col_def.get('max') or v
col_def.update({'min': cmin, 'max': cmax})
el.append(Element('col', col_def))
return el
def write_comments_vml(self):
root = Element("xml")
shape_layout = SubElement(root, "{%s}shapelayout" % officens,
{"{%s}ext" % vmlns: "edit"})
SubElement(shape_layout,
"{%s}idmap" % officens,
{"{%s}ext" % vmlns: "edit", "data": "1"})
shape_type = SubElement(root,
"{%s}shapetype" % vmlns,
{"id": "_x0000_t202",
"coordsize": "21600,21600",
"{%s}spt" % officens: "202",
"path": "m,l,21600r21600,l21600,xe"})
SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
SubElement(shape_type,
"{%s}path" % vmlns,
{"gradientshapeok": "t",
"{%s}connecttype" % officens: "rect"})
for idx, comment in enumerate(self.comments, 1026):
shape = _shape_factory()
col, row = coordinate_from_string(comment.ref)
row -= 1
column = column_index_from_string(col) - 1
shape.set('id', "_x0000_s%04d" % idx)
client_data = shape.find("{%s}ClientData" % excelns)
client_data.find("{%s}Row" % excelns).text = str(row)
client_data.find("{%s}Column" % excelns).text = str(column)
root.append(shape)
return tostring(root)
def write(self, root):
# Remove any existing comment shapes
if not hasattr(root, "findall"):
root = Element("xml")
comments = root.findall("{%s}shape" % vmlns)
for c in comments:
if c.get("type") == '#_x0000_t202':
root.remove(c)
# check whether comments shape type already exists
shape_types = root.findall("{%s}shapetype" % vmlns)
comments_type = False
for s in shape_types:
if s.get("id") == '_x0000_t202':
comments_type = True
break
if not comments_type:
self.add_comment_shapetype(root)
for idx, (coord, comment) in enumerate(self.comments, 1026):
self.add_comment_shape(root, idx, coord)
return tostring(root)
def write_external_book_rel(book):
"""Serialise link to external file"""
root = Element("Relationships", xmlns=PKG_REL_NS)
rel = Relationship("", target=book.Target, targetMode=book.TargetMode, id="rId1")
rel.type = book.Type
root.append(rel.to_tree())
return root
def write_rels(worksheet, comments_id=None, vba_controls_id=None):
"""Write relationships for the worksheet to xml."""
root = Element('Relationships', xmlns=PKG_REL_NS)
rels = worksheet._rels
# VBA
if worksheet.vba_controls is not None:
rel = Relationship("vmlDrawing", id=worksheet.vba_controls,
target='/xl/drawings/vmlDrawing%s.vml' % vba_controls_id)
rels.append(rel)
# Comments
if worksheet._comment_count > 0:
rel = Relationship(type="comments", id="comments",
target='/xl/comments%s.xml' % comments_id)
rels.append(rel)
if worksheet.vba_controls is None:
rel = Relationship(type="vmlDrawing", id="commentsvml",
target='/xl/drawings/commentsDrawing%s.vml' % comments_id)
rels.append(rel)
for idx, rel in enumerate(rels, 1):
if rel.id is None:
rel.id = "rId{0}".format(idx)
root.append(rel.to_tree())
return root
def write_external_book_rel(book):
"""Serialise link to external file"""
root = Element("{%s}Relationships" % PKG_REL_NS)
attrs = {"Id":"rId1", "Target":book.Target, "TargetMode":book.TargetMode,
"Type":book.Type}
root.append(RelationElement(attrs))
return root
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = write_sheetPr(self.sheet_properties)
xf.write(pr)
views = Element("sheetViews")
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
af = write_autofilter(self)
if af is not None:
xf.write(af)
if self._comments:
comments = Element("legacyDrawing", {"{%s}id" % REL_NS: "commentsvml"})
xf.write(comments)
def to_tree(self, tagname):
"""
Return as XML node
"""
el = Element(tagname)
el.text = str(self)
return el
def to_tree(self, tagname=None, value=None, namespace=None):
namespace = getattr(self, "namespace", namespace)
if value is not None:
if namespace is not None:
tagname = "{%s}%s" % (namespace, tagname)
el = Element(tagname)
el.text = safe_string(value)
return el
def _serialise_nested(self, sequence):
"""
Colors need special handling
"""
for idx, color in enumerate(sequence):
stop = Element("stop", position=str(idx))
stop.append(color.to_tree())
yield stop
def to_tree(self):
tree = Element("Relationships", xmlns=PKG_REL_NS)
for idx, rel in enumerate(self.Relationship, 1):
if not rel.Id:
rel.Id = "rId{0}".format(idx)
tree.append(rel.to_tree())
return tree
def to_tree(self, tagname, obj, namespace=None):
tagname = namespaced(self, tagname, namespace)
container = Element(tagname)
if self.count:
container.set('count', str(len(obj)))
for v in obj:
container.append(v.to_tree())
return container
def to_tree(self, tagname=None, value=None, namespace=None):
namespace = getattr(self, "namespace", namespace)
if namespace is not None:
tagname = "{%s}%s" % (namespace, tagname)
el = Element(tagname)
if value is not None:
el.text = datetime_to_W3CDTF(value)
return el
def write_pagebreaks(worksheet):
breaks = worksheet.page_breaks
if breaks:
tag = Element('rowBreaks', {'count': str(len(breaks)),
'manualBreakCount': str(len(breaks))})
for b in breaks:
tag.append(Element('brk', id=str(b), man="true", max='16383',
min='0'))
return tag
def write_mergecells(worksheet):
"""Write merged cells to xml."""
cells = worksheet._merged_cells
if not cells:
return
merge = Element('mergeCells', count='%d' % len(cells))
for range_string in cells:
merge.append(Element('mergeCell', ref=range_string))
return merge
def write_root_rels(workbook):
"""Write the relationships xml."""
root = Element("Relationships", xmlns=PKG_REL_NS)
relation_tag = "{%s}Relationship" % PKG_REL_NS
rel = Relationship(type="officeDocument", target=ARC_WORKBOOK, id="rId1")
root.append(rel.to_tree())
rel = Relationship("", target=ARC_CORE, id="rId2")
rel.type = "%s/metadata/core-properties" % PKG_REL_NS
root.append(rel.to_tree())
rel = Relationship("extended-properties", target=ARC_APP, id="rId3")
root.append(rel.to_tree())
if workbook.vba_archive is not None:
# See if there was a customUI relation and reuse its id
arc = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
rels = arc.findall(relation_tag)
rId = None
for rel in rels:
if rel.get("Target") == ARC_CUSTOM_UI:
rId = rel.get("Id")
break
if rId is not None:
vba = Relationship("", target=ARC_CUSTOM_UI, id=rId)
vba.type = CUSTOMUI_NS
root.append(vba.to_tree())
return tostring(root)
def to_tree(self, tagname=None):
if tagname is None:
tagname = self.tagname
attrs = dict(self)
el = Element(tagname, attrs)
for n in self.__nested__:
value = getattr(self, n)
if isinstance(value, tuple):
if hasattr(el, 'extend'):
el.extend(self._serialise_nested(value))
else: # py26 nolxml
for _ in self._serialise_nested(value):
el.append(_)
elif value:
SubElement(el, n, val=safe_string(value))
for child in self.__elements__:
obj = getattr(self, child)
if isinstance(obj, tuple):
for v in obj:
if hasattr(v, 'to_tree'):
el.append(v.to_tree(tagname=child))
else:
SubElement(el, child).text = v
elif obj is not None:
el.append(obj.to_tree(tagname=child))
return el
def write_hyperlinks(worksheet):
"""Write worksheet hyperlinks to xml."""
tag = Element('hyperlinks')
for cell in worksheet.get_cell_collection():
if cell.hyperlink_rel_id is not None:
attrs = {'display': cell.hyperlink,
'ref': cell.coordinate,
'{%s}id' % REL_NS: cell.hyperlink_rel_id}
tag.append(Element('hyperlink', attrs))
if tag.getchildren():
return tag
def write_hyperlinks(worksheet):
"""Write worksheet hyperlinks to xml."""
if not worksheet._hyperlinks:
return
tag = Element('hyperlinks')
for link in worksheet._hyperlinks:
rel = Relationship(type="hyperlink", targetMode="External", target=link.target)
worksheet._rels.append(rel)
link.id = "rId{0}".format(len(worksheet._rels))
tag.append(link.to_tree())
return tag
def to_tree(self, tagname, obj, namespace=None):
"""
Convert the sequence represented by the descriptor to an XML element
"""
for idx, v in enumerate(obj, self.idx_base):
if hasattr(v, "to_tree"):
el = v.to_tree(tagname, idx)
else:
tagname = namespaced(obj, tagname, namespace)
el = Element(tagname)
el.text = safe_string(v)
yield el
def _write_defined_names(workbook, names):
"""
Append definedName elements to the definedNames node.
"""
for named_range in workbook.get_named_ranges():
attrs = dict(named_range)
if named_range.scope is not None:
attrs['localSheetId'] = safe_string(named_range.scope)
name = Element('{%s}definedName' % SHEET_MAIN_NS, attrs)
name.text = named_range.value
names.append(name)
def _write_defined_names(workbook, names):
"""
Append definedName elements to the definedNames node.
"""
for named_range in workbook.get_named_ranges():
attrs = dict(named_range)
if named_range.scope is not None:
attrs['localSheetId'] = safe_string(named_range.scope)
name = Element('{%s}definedName' % SHEET_MAIN_NS, attrs)
name.text = named_range.value
names.append(name)
def to_tree(self, tagname, obj, namespace=None):
"""
Convert the sequence represented by the descriptor to an XML element
"""
for idx, v in enumerate(obj, self.idx_base):
if hasattr(v, "to_tree"):
el = v.to_tree(tagname, idx)
else:
tagname = namespaced(obj, tagname, namespace)
el = Element(tagname)
el.text = safe_string(v)
yield el
def write_datavalidation(worksheet):
""" Write data validation(s) to xml."""
# Filter out "empty" data-validation objects (i.e. with 0 cells)
required_dvs = [x for x in worksheet._data_validations
if len(x.cells) or len(x.ranges)]
if not required_dvs:
return
dvs = Element("dataValidations", count=str(len(required_dvs)))
for dv in required_dvs:
dvs.append(dv.to_tree())
return dvs
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 writer(data_validation):
"""
Serialse a data validation
"""
attrs = dict(data_validation)
el = Element("{%s}dataValidation" % SHEET_MAIN_NS, attrs)
for attr in ("formula1", "formula2"):
value = getattr(data_validation, attr, None)
if value is not None:
f = Element("{%s}%s" % (SHEET_MAIN_NS, attr))
f.text = value
el.append(f)
return el
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 write_datavalidation(worksheet):
""" Write data validation(s) to xml."""
# Filter out "empty" data-validation objects (i.e. with 0 cells)
required_dvs = [x for x in worksheet._data_validations
if len(x.cells) or len(x.ranges)]
if not required_dvs:
return
dvs = Element("dataValidations", count=str(len(required_dvs)))
for dv in required_dvs:
dvs.append(dv.to_tree())
return dvs
def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
if (not isgenerator(row) and
not isinstance(row, (list, tuple, range))
):
self._invalid_row(row)
cell = WriteOnlyCell(self) # singleton
self._max_row += 1
row_idx = self._max_row
if self.writer is None:
self.writer = self._write_header()
next(self.writer)
el = Element("row", r='%d' % self._max_row)
col_idx = None
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
if cell.comment is not None:
comment = cell.comment
comment._parent = CommentParentCell(cell)
self._comments.append(comment)
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
tree = write_cell(self, cell, styled)
el.append(tree)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
if col_idx:
self._max_col = max(self._max_col, col_idx)
el.set('spans', '1:%d' % col_idx)
try:
self.writer.send(el)
except StopIteration:
self._already_saved()
def write_hyperlinks(worksheet):
"""Write worksheet hyperlinks to xml."""
if not worksheet._hyperlinks:
return
tag = Element('hyperlinks')
for link in worksheet._hyperlinks:
rel = Relationship(type="hyperlink",
targetMode="External",
target=link.target)
worksheet._rels.append(rel)
link.id = "rId{0}".format(len(worksheet._rels))
tag.append(link.to_tree())
return tag
def write_conditional_formatting(worksheet):
"""Write conditional formatting to xml."""
wb = worksheet.parent
for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules):
cf = Element('conditionalFormatting', {'sqref': range_string})
for rule in rules:
if rule.dxf is not None:
if rule.dxf != DifferentialStyle():
rule.dxfId = len(wb._differential_styles)
wb._differential_styles.append(rule.dxf)
cf.append(rule.to_tree())
yield cf
def write_conditional_formatting(worksheet):
"""Write conditional formatting to xml."""
wb = worksheet.parent
for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules):
cf = Element('conditionalFormatting', {'sqref': range_string})
for rule in rules:
if rule.dxf is not None:
if rule.dxf != DifferentialStyle():
rule.dxfId = len(wb._differential_styles)
wb._differential_styles.append(rule.dxf)
cf.append(rule.to_tree())
yield cf
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
views = Element('sheetViews')
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(worksheet.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="commentsvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
views = Element('sheetViews')
views.append(self.sheet_view.to_tree())
xf.write(views)
xf.write(write_format(self))
cols = write_cols(self)
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
try:
while True:
r = (yield)
xf.write(r)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(worksheet.protection.to_tree())
af = write_autofilter(self)
if af is not None:
xf.write(af)
dv = write_datavalidation(self)
if dv is not None:
xf.write(dv)
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def to_tree(self, tagname=None, idx=None, namespace=None):
if tagname is None:
tagname = self.tagname
# keywords have to be masked
if tagname.startswith("_"):
tagname = tagname[1:]
tagname = namespaced(self, tagname, namespace)
namespace = getattr(self, "namespace", namespace)
attrs = dict(self)
for key, ns in self.__namespaced__:
if key in attrs:
attrs[ns] = attrs[key]
del attrs[key]
el = Element(tagname, attrs)
for child_tag in self.__elements__:
desc = getattr(self.__class__, child_tag, None)
obj = getattr(self, child_tag)
if isinstance(obj, seq_types):
if isinstance(desc, NestedSequence):
# wrap sequence in container
if not obj:
continue
nodes = [desc.to_tree(child_tag, obj, namespace)]
elif isinstance(desc, Sequence):
# sequence
desc.idx_base = self.idx_base
nodes = (desc.to_tree(child_tag, obj, namespace))
else: # property
nodes = (v.to_tree(child_tag, namespace) for v in obj)
for node in nodes:
el.append(node)
else:
if child_tag in self.__nested__:
node = desc.to_tree(child_tag, obj, namespace)
elif obj is None:
continue
else:
node = obj.to_tree(child_tag)
if node is not None:
el.append(node)
return el
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)
def test_write_style(self):
root = Element("{%s}test" % CHART_DRAWING_NS)
self.sw._write_style(root)
xml = tostring(root)
expected = """<cdr:test xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing"><cdr:style><a:lnRef idx="2" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="accent1"><a:shade val="50000" /></a:schemeClr></a:lnRef><a:fillRef idx="1" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="accent1" /></a:fillRef><a:effectRef idx="0" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="accent1" /></a:effectRef><a:fontRef idx="minor" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:schemeClr val="lt1" /></a:fontRef></cdr:style></cdr:test>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write_string_table(string_table):
"""Write the string table xml."""
out = BytesIO()
with xmlfile(out) as xf:
with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)):
for key in string_table:
el = Element('si')
if key.strip() != key:
el.set(PRESERVE_SPACE, 'preserve')
text = SubElement(el, 't')
text.text = key
xf.write(el)
return out.getvalue()
def write_comments_vml(self):
root = Element("xml")
shape_layout = SubElement(root, "{%s}shapelayout" % officens,
{"{%s}ext" % vmlns: "edit"})
SubElement(shape_layout, "{%s}idmap" % officens, {
"{%s}ext" % vmlns: "edit",
"data": "1"
})
shape_type = SubElement(
root, "{%s}shapetype" % vmlns, {
"id": "_x0000_t202",
"coordsize": "21600,21600",
"{%s}spt" % officens: "202",
"path": "m,l,21600r21600,l21600,xe"
})
SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
SubElement(shape_type, "{%s}path" % vmlns, {
"gradientshapeok": "t",
"{%s}connecttype" % officens: "rect"
})
for i, comment in enumerate(self.comments):
self._write_comment_shape(root, comment, i)
return get_document_content(root)
def _explicit_none(tagname, value, namespace=None):
"""
Override serialisation because explicit none required
"""
if namespace is not None:
tagname = "{%s}%s" % (namespace, tagname)
return Element(tagname, val=safe_string(value))
def write_worksheet_rels(worksheet, drawing_id, comments_id):
"""Write relationships for the worksheet to xml."""
root = Element('{%s}Relationships' % PKG_REL_NS)
for rel in worksheet.relationships:
attrs = {'Id': rel.id, 'Type': rel.type, 'Target': rel.target}
if rel.target_mode:
attrs['TargetMode'] = rel.target_mode
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
if worksheet._charts or worksheet._images:
attrs = {
'Id': 'rId1',
'Type': '%s/drawing' % REL_NS,
'Target': '../drawings/drawing%s.xml' % drawing_id
}
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
if worksheet._comment_count > 0:
# there's only one comments sheet per worksheet,
# so there's no reason to call the Id rIdx
attrs = {
'Id': 'comments',
'Type': COMMENTS_NS,
'Target': '../comments%s.xml' % comments_id
}
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
attrs = {
'Id': 'commentsvml',
'Type': VML_NS,
'Target': '../drawings/commentsDrawing%s.vml' % comments_id
}
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
return get_document_content(root)
def test_write_text(self):
root = Element("{%s}test" % CHART_DRAWING_NS)
self.sw._write_text(root, self.shape)
xml = tostring(root)
expected = """<cdr:test xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing"><cdr:txBody><a:bodyPr vertOverflow="clip" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" /><a:lstStyle xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" /><a:p xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:r><a:rPr lang="en-US"><a:solidFill><a:srgbClr val="000000" /></a:solidFill></a:rPr><a:t>My first chart</a:t></a:r></a:p></cdr:txBody></cdr:test>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def _shape_factory(row, column):
style = ("position:absolute; margin-left:59.25pt;"
"margin-top:1.5pt;width:{width};height:{height};"
"z-index:1;visibility:hidden").format(height="59.25pt",
width="108pt")
attrs = {
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = Element("{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns, {"color2": "#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns, {
"color": "black",
"obscured": "t"
})
SubElement(shape, "{%s}path" % vmlns,
{"{%s}connecttype" % officens: "none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns,
{"style": "mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns,
{"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = str(row)
SubElement(client_data, "{%s}Column" % excelns).text = str(column)
return shape
def test_xfs_fonts(self):
st = Style(font=Font(size=12, bold=True))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fills = DummyElement()
fonts = Element("fonts")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
assert """applyFont="1" """ in xml
assert """fontId="1" """ in xml
expected = """
<fonts count="2">
<font>
<sz val="12.0" />
<color rgb="00000000"></color>
<name val="Calibri" />
<family val="2" />
<b></b>
</font>
</fonts>
"""
xml = get_xml(fonts)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_xfs_fills(self):
st = Style(fill=PatternFill(fill_type='solid',
start_color=Color(colors.DARKYELLOW)))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fonts = DummyElement()
fills = Element("fills")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyFill="1" borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
expected = """<fills count="3">
<fill>
<patternFill patternType="solid">
<fgColor rgb="00808000"></fgColor>
</patternFill>
</fill>
</fills>
"""
xml = get_xml(fills)
diff = compare_xml(xml, expected)
assert diff is None, diff
def etree_write_cell(xf, worksheet, cell, styled=None):
value, attributes = _set_attributes(cell, styled)
el = Element("c", attributes)
if value is None or value == "":
xf.write(el)
return
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(cell.coordinate, {})
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
inline_string = SubElement(el, 'is')
text = SubElement(inline_string, 't')
text.text = value
whitespace(text)
else:
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
xf.write(el)
def write_conditional_formatting(worksheet):
"""Write conditional formatting to xml."""
for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules):
if not len(rules):
# Skip if there are no rules. This is possible if a dataBar rule was read in and ignored.
continue
cf = Element('conditionalFormatting', {'sqref': range_string})
for rule in rules:
if rule['type'] == 'dataBar':
# Ignore - uses extLst tag which is currently unsupported.
continue
attr = {'type': rule['type']}
for rule_attr in ConditionalFormatting.rule_attributes:
if rule_attr in rule:
attr[rule_attr] = str(rule[rule_attr])
cfr = SubElement(cf, 'cfRule', attr)
if 'formula' in rule:
for f in rule['formula']:
SubElement(cfr, 'formula').text = f
if 'colorScale' in rule:
cs = SubElement(cfr, 'colorScale')
for cfvo in rule['colorScale']['cfvo']:
SubElement(cs, 'cfvo', cfvo)
for color in rule['colorScale']['color']:
SubElement(cs, 'color', dict(color))
if 'iconSet' in rule:
iconAttr = {}
for icon_attr in ConditionalFormatting.icon_attributes:
if icon_attr in rule['iconSet']:
iconAttr[icon_attr] = rule['iconSet'][icon_attr]
iconSet = SubElement(cfr, 'iconSet', iconAttr)
for cfvo in rule['iconSet']['cfvo']:
SubElement(iconSet, 'cfvo', cfvo)
yield cf
def write_properties_app(workbook):
"""Write the properties xml."""
worksheets_count = len(workbook.worksheets)
root = Element('{%s}Properties' % XPROPS_NS)
SubElement(root, '{%s}Application' % XPROPS_NS).text = 'Microsoft Excel'
SubElement(root, '{%s}DocSecurity' % XPROPS_NS).text = '0'
SubElement(root, '{%s}ScaleCrop' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}Company' % XPROPS_NS)
SubElement(root, '{%s}LinksUpToDate' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}SharedDoc' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}HyperlinksChanged' % XPROPS_NS).text = 'false'
SubElement(root, '{%s}AppVersion' % XPROPS_NS).text = '12.0000'
# heading pairs part
heading_pairs = SubElement(root, '{%s}HeadingPairs' % XPROPS_NS)
vector = SubElement(heading_pairs, '{%s}vector' % VTYPES_NS,
{'size': '2', 'baseType': 'variant'})
variant = SubElement(vector, '{%s}variant' % VTYPES_NS)
SubElement(variant, '{%s}lpstr' % VTYPES_NS).text = 'Worksheets'
variant = SubElement(vector, '{%s}variant' % VTYPES_NS)
SubElement(variant, '{%s}i4' % VTYPES_NS).text = '%d' % worksheets_count
# title of parts
title_of_parts = SubElement(root, '{%s}TitlesOfParts' % XPROPS_NS)
vector = SubElement(title_of_parts, '{%s}vector' % VTYPES_NS,
{'size': '%d' % worksheets_count, 'baseType': 'lpstr'})
for ws in workbook.worksheets:
SubElement(vector, '{%s}lpstr' % VTYPES_NS).text = '%s' % ws.title
return tostring(root)
def to_tree(self, tagname=None, value=None, namespace=None):
namespace = getattr(self, "namespace", namespace)
if value is not None:
if namespace is not None:
tagname = "{%s}%s" % (namespace, tagname)
value = safe_string(value)
return Element(tagname, {self.attribute: value})
def test_protection(self):
prot = Protection(locked=True, hidden=True)
self.worksheet.cell('A1').style = Style(protection=prot)
w = StyleWriter(self.workbook)
w._write_protection(w._root, prot)
xml = get_xml(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<protection hidden="1" locked="1"/>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
nft = fonts = borders = fills = Element('empty')
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<protection hidden="1" locked="1"/>
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyProtection="1" borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0">
<protection hidden="1" locked="1"/>
</xf>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_chart(self):
from openpyxl.drawing import Drawing
root = Element("{%s}wsDr" % SHEET_DRAWING_NS)
chart = DummyChart()
drawing = Drawing()
chart.drawing = drawing
self.dw._write_chart(root, chart, 1)
drawing_schema.assertValid(root)
xml = tostring(root)
expected = """<xdr:wsDr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
<xdr:absoluteAnchor>
<xdr:pos x="0" y="0"/>
<xdr:ext cx="200025" cy="1828800"/>
<xdr:graphicFrame macro="">
<xdr:nvGraphicFramePr>
<xdr:cNvPr id="2" name="Chart 1"/>
<xdr:cNvGraphicFramePr/>
</xdr:nvGraphicFramePr>
<xdr:xfrm>
<a:off x="0" y="0"/>
<a:ext cx="0" cy="0"/>
</xdr:xfrm>
<a:graphic>
<a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart">
<c:chart xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"/>
</a:graphicData>
</a:graphic>
</xdr:graphicFrame>
<xdr:clientData/>
</xdr:absoluteAnchor>
</xdr:wsDr>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write_comments(self):
# produce xml
root = Element("{%s}comments" % SHEET_MAIN_NS)
authorlist_tag = SubElement(root, "{%s}authors" % SHEET_MAIN_NS)
for author in self.authors:
leaf = SubElement(authorlist_tag, "{%s}author" % SHEET_MAIN_NS)
leaf.text = author
commentlist_tag = SubElement(root, "{%s}commentList" % SHEET_MAIN_NS)
for comment in self.comments:
attrs = {
'ref': comment._parent.coordinate,
'authorId': '%d' % self.authors.index(comment.author),
'shapeId': '0'
}
comment_tag = SubElement(commentlist_tag,
"{%s}comment" % SHEET_MAIN_NS, attrs)
text_tag = SubElement(comment_tag, "{%s}text" % SHEET_MAIN_NS)
run_tag = SubElement(text_tag, "{%s}r" % SHEET_MAIN_NS)
SubElement(run_tag, "{%s}rPr" % SHEET_MAIN_NS)
t_tag = SubElement(run_tag, "{%s}t" % SHEET_MAIN_NS)
t_tag.text = comment.text
return tostring(root)
def write_cell(worksheet, cell, styled=None):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
el = Element("c", attributes)
if value is None or value == "":
return el
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
if (shared_formula.get('t') == 'shared'
and 'ref' not in shared_formula):
value = None
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
return el
def to_tree(self, tagname=None):
parent = Element("fill")
el = Element(self.tagname, patternType=safe_string(self.patternType))
for c in self.__elements__:
value = getattr(self, c)
if value != Color():
el.append(value.to_tree(c))
parent.append(el)
return parent
def write_properties(worksheet, vba_attrs):
pr = Element('sheetPr', vba_attrs)
summary = Element('outlinePr',
summaryBelow='%d' % worksheet.show_summary_below,
summaryRight= '%d' % worksheet.show_summary_right)
pr.append(summary)
if worksheet.page_setup.fitToPage:
pr.append(Element('pageSetUpPr', fitToPage='1'))
return pr
def write(self, shape_id):
root = Element('{%s}userShapes' % CHART_NS)
for shape in self._shapes:
anchor = SubElement(root, '{%s}relSizeAnchor' % CHART_DRAWING_NS)
xstart, ystart, xend, yend = shape.coordinates
_from = SubElement(anchor, '{%s}from' % CHART_DRAWING_NS)
SubElement(_from, '{%s}x' % CHART_DRAWING_NS).text = str(xstart)
SubElement(_from, '{%s}y' % CHART_DRAWING_NS).text = str(ystart)
_to = SubElement(anchor, '{%s}to' % CHART_DRAWING_NS)
SubElement(_to, '{%s}x' % CHART_DRAWING_NS).text = str(xend)
SubElement(_to, '{%s}y' % CHART_DRAWING_NS).text = str(yend)
sp = SubElement(anchor, '{%s}sp' % CHART_DRAWING_NS, {
'macro': '',
'textlink': ''
})
nvspr = SubElement(sp, '{%s}nvSpPr' % CHART_DRAWING_NS)
SubElement(nvspr, '{%s}cNvPr' % CHART_DRAWING_NS, {
'id': str(shape_id),
'name': 'shape %s' % shape_id
})
SubElement(nvspr, '{%s}cNvSpPr' % CHART_DRAWING_NS)
sppr = SubElement(sp, '{%s}spPr' % CHART_DRAWING_NS)
frm = SubElement(
sppr,
'{%s}xfrm' % DRAWING_NS,
)
# no transformation
SubElement(frm, '{%s}off' % DRAWING_NS, {'x': '0', 'y': '0'})
SubElement(frm, '{%s}ext' % DRAWING_NS, {'cx': '0', 'cy': '0'})
prstgeom = SubElement(sppr, '{%s}prstGeom' % DRAWING_NS,
{'prst': str(shape.style)})
SubElement(prstgeom, '{%s}avLst' % DRAWING_NS)
fill = SubElement(
sppr,
'{%s}solidFill' % DRAWING_NS,
)
SubElement(fill, '{%s}srgbClr' % DRAWING_NS, {'val': shape.color})
border = SubElement(sppr, '{%s}ln' % DRAWING_NS,
{'w': str(shape._border_width)})
sf = SubElement(border, '{%s}solidFill' % DRAWING_NS)
SubElement(sf, '{%s}srgbClr' % DRAWING_NS,
{'val': shape.border_color})
self._write_style(sp)
self._write_text(sp, shape)
shape_id += 1
return tostring(root)
def test_write_anchor(self, ImageFile):
drawing = ImageFile.drawing
root = Element("test")
self.dw._write_anchor(root, drawing)
xml = tostring(root)
expected = """<test><xdr:absoluteAnchor xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"><xdr:pos x="0" y="0"/><xdr:ext cx="1123950" cy="1123950"/></xdr:absoluteAnchor></test>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def write_rels(self, drawing_id):
root = Element("{%s}Relationships" % PKG_REL_NS)
attrs = {'Id' : 'rId1',
'Type' : '%s/chartUserShapes' % REL_NS,
'Target' : '../drawings/drawing%s.xml' % drawing_id }
SubElement(root, '{%s}Relationship' % PKG_REL_NS, attrs)
return get_document_content(root)