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):
dimension.style = worksheet._styles.get(label)
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_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_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_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_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, 1026):
shape = self._write_comment_shape(comment, i)
root.append(shape)
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 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':
if hasattr(value, 'rich') and value.rich:
sub = value.rich.to_tree(tagname='is')
el.append(sub)
else:
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_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 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 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):
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 _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, 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):
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 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_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_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_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))
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_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_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 to_tree(self, tagname=None):
parent = Element("fill")
el = Element(self.tagname)
if self.patternType is not None:
el.set('patternType', 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_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 to_tree(self, tagname=None):
parent = Element("fill")
el = Element(self.tagname)
if self.patternType is not None:
el.set('patternType', 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_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=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)
if "attr_text" in self.__attrs__:
el.text = safe_string(getattr(self, "attr_text"))
for child_tag in self.__elements__:
desc = getattr(self.__class__, child_tag, None)
obj = getattr(self, child_tag)
if hasattr(desc, "namespace") and hasattr(obj, 'namespace'):
obj.namespace = desc.namespace
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_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 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 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 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 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 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)
if "attr_text" in self.__attrs__:
el.text = safe_string(getattr(self, "attr_text"))
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_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_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_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 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_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 _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 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
column = get_column_letter(col_idx)
if isinstance(value, Cell):
cell = value
else:
cell.value = value
cell.coordinate = "%s%d" % (column, row_idx)
if cell.comment is not None:
comment = cell.comment
comment._parent = CommentParentCell(cell)
self._comments.append(comment)
tree = write_cell(self, cell)
el.append(tree)
if cell.has_style: # 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 test_to_tree(self, Dummy):
dummy = Dummy([1, '2', 3])
root = Element("root")
for node in Dummy.value.to_tree("el", dummy.value, ):
root.append(node)
xml = tostring(root)
expected = """
<root>
<el>1</el>
<el>2</el>
<el>3</el>
</root>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_to_tree(self, Dummy):
dummy = Dummy([1, '2', 3])
root = Element("root")
for node in Dummy.value.to_tree("el", dummy.value, ):
root.append(node)
xml = tostring(root)
expected = """
<root>
<el>1</el>
<el>2</el>
<el>3</el>
</root>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def to_tree(self):
def sorter(value):
value.reindex()
return value.min
el = Element('cols')
outlines = set()
for col in sorted(self.values(), key=sorter):
obj = col.to_tree()
if obj is not None:
outlines.add(col.outlineLevel)
el.append(obj)
if outlines:
self.max_outline = max(outlines)
if len(el):
return el # must have at least one child
def to_tree(self):
def sorter(value):
value.reindex()
return value.min
el = Element('cols')
obj = None
outlines = set()
for col in sorted(self.values(), key=sorter):
obj = col.to_tree()
outlines.add(col.outlineLevel)
if obj is not None:
el.append(obj)
if outlines:
self.max_outline = max(outlines)
if obj is not None:
return el
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
if self.protection.sheet:
prot = Element('sheetProtection', dict(self.protection))
xf.write(prot)
af = write_autofilter(self)
if af is not None:
xf.write(af)
dv = write_datavalidation(self)
if dv is not None:
xf.write(dv)
if self._comments:
comments = Element('legacyDrawing',
{'{%s}id' % REL_NS: 'commentsvml'})
xf.write(comments)
def test_to_tree(self, Stooge):
dummy = Stooge()
dummy._stooges = [Larry(1), Curly(2), Larry(3), Mo(4)]
root = Element("root")
for node in Stooge._stooges.to_tree("el", dummy._stooges):
root.append(node)
tree = dummy.to_tree("root")
xml = tostring(root)
expected = """
<root>
<l value="1"></l>
<c hair="2"></c>
<l value="3"></l>
<m cap="4"></m>
</root>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def to_tree(self):
def sorter(value):
value.reindex()
return value.min
el = Element('cols')
obj = None
outlines = set()
for col in sorted(self.values(), key=sorter):
obj = col.to_tree()
outlines.add(col.outlineLevel)
if obj is not None:
el.append(obj)
if outlines:
self.max_outline = max(outlines)
if obj is not None:
return el
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_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
if self.protection.sheet:
prot = Element('sheetProtection', dict(self.protection))
xf.write(prot)
af = write_autofilter(self)
if af is not None:
xf.write(af)
dv = write_datavalidation(self)
if dv is not None:
xf.write(dv)
if self._comments:
comments = Element('legacyDrawing', {'{%s}id' % REL_NS: 'commentsvml'})
xf.write(comments)
def test_to_tree(self, Sequence):
class Dummy:
vals = Sequence(expected_type=SomeType, name="vals")
dummy = Dummy()
dummy.vals = [SomeType(1), SomeType(2), SomeType(3)]
root = Element("root")
for node in Dummy.vals.to_tree("el", dummy.vals):
root.append(node)
xml = tostring(root)
expected = """
<root>
<el value="1"></el>
<el value="2"></el>
<el value="3"></el>
</root>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
