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