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_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): 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 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_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 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 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 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_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_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 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, 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 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