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_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_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 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 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 _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 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 _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 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_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 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_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 write_workbook_rels(workbook): """Write the workbook relationships xml.""" root = Element('Relationships', xmlns=PKG_REL_NS) for i, _ in enumerate(workbook.worksheets, 1): rel = Relationship(type='worksheet', target='worksheets/sheet%s.xml' % i, id='rId%d' % i) root.append(rel.to_tree()) i += 1 strings = Relationship(type='sharedStrings', target='sharedStrings.xml', id='rId%d' % i) root.append(strings.to_tree()) i += 1 styles = Relationship(type='styles', target='styles.xml', id='rId%d' % i) root.append(styles.to_tree()) i += 1 styles = Relationship(type='theme', target='theme/theme1.xml', id='rId%d' % i) root.append(styles.to_tree()) if workbook.vba_archive: i += 1 vba = Relationship(type='vbaProject', target='vbaProject.bin', id='rId%d' % i) root.append(vba.to_tree()) external_links = workbook._external_links if external_links: for idx, link in enumerate(external_links, 1): ext = Relationship(type='externalLink', target='externalLinks/externalLink%d.xml' % idx, id='rId%d' % (i +idx)) root.append(ext.to_tree()) return tostring(root)
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_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 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 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_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 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 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_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 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_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_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 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, comment) return tostring(root)
def lxml_write_cell(xf, worksheet, cell, styled=False): value, attributes = _set_attributes(cell, styled) if value == '' or value is None: with xf.element("c", attributes): return with xf.element('c', attributes): if cell.data_type == 'f': shared_formula = worksheet.formula_attributes.get(cell.coordinate, {}) with xf.element('f', shared_formula): if value is not None: xf.write(value[1:]) value = None if cell.data_type == 's': with xf.element("is"): attrs = {} if value != value.strip(): attrs["{%s}space" % XML_NS] = "preserve" el = Element("t", attrs) # lxml can't handle xml-ns el.text = value xf.write(el) #with xf.element("t", attrs): #xf.write(value) else: with xf.element("v"): if value is not None: xf.write(safe_string(value))
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 to_tree(self, tagname): """ Return as XML node """ el = Element(tagname) el.text = unicode(self) return el
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