def test_from_xml(self, FunctionGroup): src = """ <functionGroup name="Database" /> """ node = fromstring(src) function_group = FunctionGroup.from_tree(node) assert function_group == FunctionGroup(name="Database")
def test_from_xml(self, FunctionGroupList): src = """ <functionGroups /> """ node = fromstring(src) function_group = FunctionGroupList.from_tree(node) assert function_group == FunctionGroupList()
def test_from_xml(self, SurfaceChart3D): src = """ <surface3DChart> <wireframe val="0"/> <ser> <idx val="0"/> <order val="0"/> <val> <numRef> <f>Blatt1!$A$1:$A$12</f> </numRef> </val> </ser> <ser> <idx val="1"/> <order val="1"/> <val> <numRef> <f>Blatt1!$B$1:$B$12</f> </numRef> </val> </ser> <bandFmts/> <axId val="2082935272"/> <axId val="2082938248"/> <axId val="2082941288"/> </surface3DChart> """ node = fromstring(src) chart = SurfaceChart3D.from_tree(node) assert len(chart.ser) == 2 assert [a.val for a in chart.axId] == [10, 100, 1000]
def from_xml(self, TextAxis): src = """ <catAx> <axId val="2065276984"/> <scaling> <orientation val="minMax"/> </scaling> <delete val="0"/> <axPos val="b"/> <majorTickMark val="out"/> <minorTickMark val="none"/> <tickLblPos val="nextTo"/> <crossAx val="2056619928"/> <crosses val="autoZero"/> <auto val="1"/> <lblAlgn val="ctr"/> <lblOffset val="100"/> <noMultiLvlLbl val="0"/> </catAx> """ node = fromstring(src) axis = CatAx.from_tree(node) assert axis.scaling.orientation == "minMax" assert axis.auto is True assert axis.majorTickMark == "out" assert axis.minorTickMark is None
def read_content_types(archive): """Read content types.""" xml_source = archive.read(ARC_CONTENT_TYPES) root = fromstring(xml_source) contents_root = root.findall('{%s}Override' % CONTYPES_NS) for type in contents_root: yield type.get('ContentType'), type.get('PartName')
def read_named_ranges(xml_source, workbook): """Read named ranges, excluding poorly defined ranges.""" sheetnames = set(sheet.title for sheet in workbook.worksheets) root = fromstring(xml_source) for name_node in safe_iterator(root, '{%s}definedName' %SHEET_MAIN_NS): range_name = name_node.get('name') if DISCARDED_RANGES.match(range_name): warnings.warn("Discarded range with reserved name") continue node_text = name_node.text if external_range(node_text): # treat names referring to external workbooks as values named_range = NamedValue(range_name, node_text) elif refers_to_range(node_text): destinations = split_named_range(node_text) # it can happen that a valid named range references # a missing worksheet, when Excel didn't properly maintain # the named range list destinations = [(workbook[sheet], cells) for sheet, cells in destinations if sheet in sheetnames] if not destinations: continue named_range = NamedRange(range_name, destinations) else: named_range = NamedValue(range_name, node_text) named_range.scope = name_node.get("localSheetId") yield named_range
def test_from_xml(self, ChartLines): src = """ <chartLines /> """ node = fromstring(src) axis = ChartLines.from_tree(node) assert axis == ChartLines()
def test_from_xml(self, GradientFillProperties): src = """ <gradFill></gradFill> """ node = fromstring(src) fill = GradientFillProperties.from_tree(node) assert fill == GradientFillProperties()
def test_from_xml(self, NonVisualDrawingProps): src = """ <cNvPr id="3" name="Chart 2"></cNvPr> """ node = fromstring(src) graphic = NonVisualDrawingProps.from_tree(node) assert graphic == NonVisualDrawingProps(id=3, name="Chart 2")
def write_root_rels(workbook): """Write the relationships xml.""" rels = RelationshipList() rel = Relationship(type="officeDocument", target=ARC_WORKBOOK, id="rId1") rels.append(rel) rel = Relationship("", target=ARC_CORE, id='rId2',) rel.type = "%s/metadata/core-properties" % PKG_REL_NS rels.append(rel) rel = Relationship("extended-properties", target=ARC_APP, id='rId3') rels.append(rel) if workbook.vba_archive is not None: relation_tag = '{%s}Relationship' % PKG_REL_NS # See if there was a customUI relation and reuse its id arc = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS)) rel_tags = arc.findall(relation_tag) rId = None for rel in rel_tags: 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 rels.append(vba) return tostring(rels.to_tree())
def test_from_xml(self, DisplayUnitsLabelList): src = """ <dispUnits /> """ node = fromstring(src) axis = DisplayUnitsLabelList.from_tree(node) assert axis == DisplayUnitsLabelList()
def test_from_xml(self, BandFormatList): src = """ <bandFmts /> """ node = fromstring(src) fmt = BandFormatList.from_tree(node) assert fmt == BandFormatList()
def test_from_xml(self, DateAxis): from openpyxl.chart.data_source import NumFmt src = """ <dateAx> <axId val="20"/> <scaling> <orientation val="minMax"/> </scaling> <delete val="0"/> <axPos val="b"/> <numFmt formatCode="d-mmm" sourceLinked="1"/> <majorTickMark val="out"/> <minorTickMark val="none"/> <tickLblPos val="nextTo"/> <crossAx val="10"/> <crosses val="autoZero"/> <auto val="1"/> <lblOffset val="100"/> <baseTimeUnit val="months"/> </dateAx> """ node = fromstring(src) axis = DateAxis.from_tree(node) assert axis == DateAxis(axId=20, crossAx=10, axPos="b", scaling="minMax", delete=False, numFmt=NumFmt("d-mmm", True), majorTickMark="out", crosses="autoZero", tickLblPos="nextTo", auto=True, lblOffset=100, baseTimeUnit="months")
def test_read_row(datadir, DummyWorkbook, ReadOnlyWorksheet): datadir.join("reader").chdir() src = b""" <sheetData xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" > <row r="1" spans="4:27"> <c r="D1"> <v>1</v> </c> <c r="K1"> <v>0.01</v> </c> <c r="AA1"> <v>100</v> </c> </row> </sheetData> """ ws = ReadOnlyWorksheet(DummyWorkbook, "Sheet", "", "bug393-worksheet.xml", []) xml = fromstring(src) row = tuple(ws._get_row(xml, 11, 11)) values = [c.value for c in row] assert values == [0.01] row = tuple(ws._get_row(xml, 1, 11)) values = [c.value for c in row] assert values == [None, None, None, 1, None, None, None, None, None, None, 0.01]
def read_excel_base_date(archive): src = archive.read(ARC_WORKBOOK) root = fromstring(src) wbPr = root.find('{%s}workbookPr' % SHEET_MAIN_NS) if wbPr is not None and wbPr.get('date1904') in ('1', 'true'): return CALENDAR_MAC_1904 return CALENDAR_WINDOWS_1900
def test_from_xml(self, DefinedNameList): src = """ <definedNames /> """ node = fromstring(src) names = DefinedNameList.from_tree(node) assert names == DefinedNameList()
def test_from_xml(self, Transform2D): src = """ <root /> """ node = fromstring(src) shapes = Transform2D.from_tree(node) assert shapes == Transform2D()
def test_from_xml(self, WebPublishObject): src = """ <webPublishingObject destinationFile="www" divId="main" id="1" /> """ node = fromstring(src) obj = WebPublishObject.from_tree(node) assert obj == WebPublishObject(id=1, divId="main", destinationFile="www")
def test_from_xml(self, SmartTagProperties): src = """ <smartTagPr /> """ node = fromstring(src) smart_tags = SmartTagProperties.from_tree(node) assert smart_tags == SmartTagProperties()
def test_from_xml(self, WebPublishObjectList): src = """ <webPublishingObjects /> """ node = fromstring(src) objs = WebPublishObjectList.from_tree(node) assert objs == WebPublishObjectList()
def test_read(self, ChartsheetView): src = """ <sheetView tabSelected="1" zoomScale="80" workbookViewId="0" zoomToFit="1"/> """ xml = fromstring(src) chart = ChartsheetView.from_tree(xml) assert chart.tabSelected == True
def test_from_xml(self, Title): src = """ <title /> """ node = fromstring(src) title = Title.from_tree(node) assert title == Title()
def test_from_xml(self, WorkbookProtection): src = """ <workbookPr /> """ node = fromstring(src) prot = WorkbookProtection.from_tree(node) assert prot == WorkbookProtection()
def test_from_xml(self, FileSharing): src = """ <fileSharing userName="******" /> """ node = fromstring(src) share = FileSharing.from_tree(node) assert share == FileSharing(userName="******")
def parse(self): src = self.archive.read(self.workbook_part_name) node = fromstring(src) package = WorkbookPackage.from_tree(node) if package.properties.date1904: self.wb.excel_base_date = CALENDAR_MAC_1904 self.wb.code_name = package.properties.codeName self.wb.active = package.active self.wb.views = package.bookViews self.sheets = package.sheets self.wb.calculation = package.calcPr self.caches = package.pivotCaches #external links contain cached worksheets and can be very big if not self.wb.keep_links: package.externalReferences = [] for ext_ref in package.externalReferences: rel = self.rels[ext_ref.id] self.wb._external_links.append( read_external_link(self.archive, rel.Target) ) if package.definedNames: package.definedNames._cleanup() self.wb.defined_names = package.definedNames self.wb.security = package.workbookProtection
def test_from_tree(self): from ..series import Series, attribute_mapping src = """ <ser> <idx val="0"/> <order val="0"/> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Blatt1!$A$1:$A$12</f> </numRef> </val> </ser> """ node = fromstring(src) ser = Series.from_tree(node) assert ser.idx == 0 assert ser.order == 0 assert ser.val.numRef.ref == 'Blatt1!$A$1:$A$12' ser.__elements__ = attribute_mapping['area'] xml = tostring(ser.to_tree()) diff = compare_xml(xml, src) assert diff is None, diff
def test_from_xml(self, WebPublishing): src = """ <webPublishing /> """ node = fromstring(src) web = WebPublishing.from_tree(node) assert web == WebPublishing()
def test_from_xml(self, DefinedName): src = """ <definedName name="Northwind"/> """ node = fromstring(src) defined_name = DefinedName.from_tree(node) assert defined_name == DefinedName(name="Northwind")
def test_from_xml(self, SmartTagList): src = """ <smartTagTypes /> """ node = fromstring(src) smart_tags = SmartTagList.from_tree(node) assert smart_tags == SmartTagList()
def test_from_xml(self, PictureFrame): src = """ <pic /> """ node = fromstring(src) graphic = PictureFrame.from_tree(node) assert graphic == PictureFrame()
def get_dependents(archive, filename): """ Normalise dependency file paths to absolute ones Relative paths are relative to parent object """ src = archive.read(filename) node = fromstring(src) rels = RelationshipList.from_tree(node) folder = posixpath.dirname(filename) parent = posixpath.split(folder)[0] for r in rels.Relationship: if r.target.startswith("/"): r.target = r.target[1:] continue pth = posixpath.join(parent, r.target) r.target = posixpath.normpath(pth) return rels
def test_templates(self, has_vba, as_template, content_type, Manifest, Override): from openpyxl import Workbook from ..manifest import write_content_types wb = Workbook() if has_vba: archive = ZipFile(BytesIO(), "w") parts = [Override("/xl/workbook.xml", "")] m = Manifest(Override=parts) archive.writestr(ARC_CONTENT_TYPES, tostring(m.to_tree())) wb.vba_archive = archive manifest = write_content_types(wb, as_template=as_template) xml = tostring(manifest.to_tree()) root = fromstring(xml) node = root.find('{%s}Override[@PartName="/xl/workbook.xml"]' % CONTYPES_NS) assert node.get("ContentType") == content_type
def test_alignment(self, datadir, Stylesheet): datadir.chdir() with open("alignment_styles.xml") as src: xml = src.read() node = fromstring(xml) stylesheet = Stylesheet.from_tree(node) styles = stylesheet.cell_styles assert len(styles) == 3 assert styles[2] == StyleArray([0, 0, 0, 0, 0, 2, 0, 0, 0]) from ..alignment import Alignment assert stylesheet.alignments == [ Alignment(), Alignment(textRotation=180), Alignment(vertical='top', textRotation=255), ]
def test_broken_sheet_ref(self, datadir, recwarn, WorkbookParser): from openpyxl.workbook.parser import WorkbookPackage datadir.chdir() with open("workbook_missing_id.xml", "rb") as src: xml = src.read() node = fromstring(xml) wb = WorkbookPackage.from_tree(node) archive = ZipFile(BytesIO(), "a") archive.write("workbook_links.xml", ARC_WORKBOOK) archive.writestr(ARC_WORKBOOK_RELS, b"<root />") parser = WorkbookParser(archive, ARC_WORKBOOK) parser.sheets = wb.sheets sheets = parser.find_sheets() list(sheets) w = recwarn.pop() assert issubclass(w.category, UserWarning)
def find_images(archive, path): src = archive.read(path) tree = fromstring(src) drawing = SpreadsheetDrawing.from_tree(tree) rels_path = get_rels_path(path) deps = [] if rels_path in archive.namelist(): deps = get_dependents(archive, rels_path) images = [] for rel in drawing._image_rels: id = rel.embed path = deps[id].target image = Image(BytesIO(archive.read(path))) image.anchor = rel.anchor images.append(image) return images
def test_create(self, Font): src = """ <font xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <charset val="204"></charset> <family val="2"></family> <name val="Calibri"></name> <sz val="11"></sz> <u val="single"/> <vertAlign val="superscript"></vertAlign> <color rgb="FF3300FF"></color> </font>""" xml = fromstring(src) ft = Font.from_tree(xml) assert ft == Font(name='Calibri', charset=204, vertAlign='superscript', underline='single', color="FF3300FF")
def test_bool_value(): from ..nested import NestedBool class Simple(Serialisable): bold = NestedBool() def __init__(self, bold): self.bold = bold xml = """ <font> <bold val="true"/> </font> """ node = fromstring(xml) simple = Simple.from_tree(node) assert simple.bold is True
def test_from_tree(self): from ..series import Series, attribute_mapping src = """ <ser xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <idx val="0"/> <order val="0"/> <spPr> <a:ln > <a:prstDash val="solid" /> </a:ln> </spPr> <marker> <symbol val="none"/> <spPr> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> </marker> <xVal> <numRef> <f>Blatt1!$A$1:$A$12</f> </numRef> </xVal> <yVal> <numRef> <f>Blatt1!$B$1:$B$12</f> </numRef> </yVal> <smooth val="0"/> </ser> """ node = fromstring(src) ser = Series.from_tree(node) assert ser.idx == 0 assert ser.order == 0 assert ser.xVal.numRef.ref == 'Blatt1!$A$1:$A$12' assert ser.yVal.numRef.ref == 'Blatt1!$B$1:$B$12' ser.__elements__ = attribute_mapping['scatter'] xml = tostring(ser.to_tree()) diff = compare_xml(xml, src) assert diff is None, diff
def test_noneset_value(): from ..nested import NestedNoneSet class Simple(Serialisable): underline = NestedNoneSet(values=('1', '2', '3')) def __init__(self, underline): self.underline = underline xml = """ <font> <underline val="1" /> </font> """ node = fromstring(xml) simple = Simple.from_tree(node) assert simple.underline == '1'
def test_from_xml(self, Sequence): src = """ <root> <vals value="1"></vals> <vals value="2"></vals> <vals value="3"></vals> </root> """ node = fromstring(src) class Dummy(Serialisable): vals = Sequence(expected_type=SomeType) def __init__(self, vals): self.vals = vals dummy = Dummy.from_tree(node) assert dummy.vals == [SomeType(1), SomeType(2), SomeType(3)]
def test_parser(DataValidation): xml = """ <dataValidation xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" type="list" errorStyle="warning" allowBlank="1" showInputMessage="1" showErrorMessage="1" error="Value must be between 1 and 3!" errorTitle="An Error Message" promptTitle="Multiplier" prompt="for monthly or quartely reports" sqref="H6"> </dataValidation> """ xml = fromstring(xml) dv = DataValidation.from_tree(xml) assert dict(dv) == { "error": "Value must be between 1 and 3!", "errorStyle": "warning", "errorTitle": "An Error Message", "prompt": "for monthly or quartely reports", "promptTitle": "Multiplier", "type": "list", "allowBlank": "1", "sqref": "H6", "showErrorMessage": "1", "showInputMessage": "1" }
def test_from_xml(self, CustomWorkbookView): src = """ <customWorkbookView activeSheetId="1" guid="{00000000-5BD2-4BC8-9F70-7020E1357FB2}" name="custom view" showComments="commIndicator" showObjects="all" windowHeight="600" windowWidth="800" /> """ node = fromstring(src) view = CustomWorkbookView.from_tree(node) assert view == CustomWorkbookView( name="custom view", guid="{00000000-5BD2-4BC8-9F70-7020E1357FB2}", windowWidth=800, windowHeight=600, activeSheetId=1, )
def test_styles(self, _NamedCellStyleList): src = """ <cellStyles count="11"> <cellStyle name="Followed Hyperlink" xfId="2" builtinId="9" hidden="1"/> <cellStyle name="Followed Hyperlink" xfId="4" builtinId="9" hidden="1"/> <cellStyle name="Followed Hyperlink" xfId="6" builtinId="9" hidden="1"/> <cellStyle name="Followed Hyperlink" xfId="8" builtinId="9" hidden="1"/> <cellStyle name="Followed Hyperlink" xfId="10" builtinId="9" hidden="1"/> <cellStyle name="Hyperlink" xfId="1" builtinId="8" hidden="1"/> <cellStyle name="Hyperlink" xfId="3" builtinId="8" hidden="1"/> <cellStyle name="Hyperlink" xfId="5" builtinId="8" hidden="1"/> <cellStyle name="Hyperlink" xfId="7" builtinId="8" hidden="1"/> <cellStyle name="Hyperlink" xfId="9" builtinId="8" hidden="1"/> <cellStyle name="Normal" xfId="0" builtinId="0"/> </cellStyles> """ node = fromstring(src) styles = _NamedCellStyleList.from_tree(node) assert [s.name for s in styles.names] == ['Normal', 'Hyperlink', 'Followed Hyperlink']
def test_from_xml(self, GraphicalProperties): src = """ <spPr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:pattFill prst="ltDnDiag"> <a:fgClr> <a:schemeClr val="accent2"/> </a:fgClr> <a:bgClr> <a:prstClr val="white"/> </a:bgClr> </a:pattFill> <a:ln w="38100" cmpd="sng"> <a:prstDash val="sysDot"/> </a:ln> </spPr> """ node = fromstring(src) shapes = GraphicalProperties.from_tree(node) assert dict(shapes) == {}
def test_from_xml_with_password(self, FileSharing): src = """ <fileSharing userName="******" algorithmName="SHA-512" hashValue="wDZaZrfM8uKpKghbfws7rY7pmVoOwHjy5qg5d2ABHdSMtH1y0IIkgwJT5Hl2lacSw1sNusImGBUQs/sHcql3hw==" saltValue="ah1OevWahpb3tQiJO3qrnQ==" spinCount="100000" /> """ node = fromstring(src) share = FileSharing.from_tree(node) assert share == FileSharing( userName="******", algorithmName="SHA-512", hashValue= "wDZaZrfM8uKpKghbfws7rY7pmVoOwHjy5qg5d2ABHdSMtH1y0IIkgwJT5Hl2lacSw1sNusImGBUQs/sHcql3hw==", saltValue="ah1OevWahpb3tQiJO3qrnQ==", spinCount="100000")
def test_create(self, GradientFill, Stop): src = """ <fill> <gradientFill xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" degree="90"> <stop position="0"> <color theme="0"/> </stop> <stop position="1"> <color theme="4"/> </stop> </gradientFill> </fill> """ xml = fromstring(src) fill = GradientFill.from_tree(xml) assert fill.stop == [ Stop(Color(theme=0), position=0), Stop(Color(theme=4), position=1) ]
def test_from_xml(self, ManualLayout): src = """ <manualLayout> <layoutTarget val="inner"></layoutTarget> <xMode val="edge"></xMode> <yMode val="factor"></yMode> <wMode val="factor"></wMode> <hMode val="edge"></hMode> <x val="10"></x> <y val="50"></y> <w val="4"></w> <h val="100"></h> </manualLayout> """ node = fromstring(src) layout = ManualLayout.from_tree(node) assert layout == ManualLayout(layoutTarget="inner", xMode="edge", yMode="factor", wMode="factor", hMode="edge", x=10, y=50, w=4, h=100 )
def test_parse(SheetView): src = """ <sheetView xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" tabSelected="1" zoomScale="200" zoomScaleNormal="200" zoomScalePageLayoutView="200" workbookViewId="0"> <pane xSplit="5" ySplit="19" topLeftCell="F20" activePane="bottomRight" state="frozenSplit"/> <selection pane="topRight" activeCell="F1" sqref="F1"/> <selection pane="bottomLeft" activeCell="A20" sqref="A20"/> <selection pane="bottomRight" activeCell="E22" sqref="E22"/> </sheetView> """ xml = fromstring(src) view = SheetView.from_tree(xml) assert dict(view) == { 'tabSelected': '1', 'zoomScale': '200', 'workbookViewId': "0", 'zoomScaleNormal': '200', 'zoomScalePageLayoutView': '200' } assert len(view.selection) == 3
def test_from_xml(self, WorkbookProtection): src = """ <workbookProtection workbookAlgorithmName="SHA-512" workbookHashValue="wDZaZrfM8uKpKghbfws7rY7pmVoOwHjy5qg5d2ABHdSMtH1y0IIkgwJT5Hl2lacSw1sNusImGBUQs/sHcql3hw==" workbookSaltValue="ah1OevWahpb3tQiJO3qrnQ==" workbookSpinCount="100000" lockStructure="1" /> """ node = fromstring(src) prot = WorkbookProtection.from_tree(node) assert prot == WorkbookProtection( workbookAlgorithmName="SHA-512", workbookHashValue= "wDZaZrfM8uKpKghbfws7rY7pmVoOwHjy5qg5d2ABHdSMtH1y0IIkgwJT5Hl2lacSw1sNusImGBUQs/sHcql3hw==", workbookSaltValue="ah1OevWahpb3tQiJO3qrnQ==", workbookSpinCount=100000, lockStructure="1")
def test_min_max_value(): from ..nested import NestedMinMax class Simple(Serialisable): size = NestedMinMax(min=5, max=10) def __init__(self, size): self.size = size xml = """ <font> <size val="6"/> </font> """ node = fromstring(xml) simple = Simple.from_tree(node) assert simple.size == 6
def test_assign_number_formats(self, Stylesheet): node = fromstring(r""" <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <numFmts count="1"> <numFmt numFmtId="43" formatCode='_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ ' /> </numFmts> <cellXfs count="0"> <xf numFmtId="43" fontId="2" fillId="0" borderId="0" applyFont="0" applyFill="0" applyBorder="0" applyAlignment="0" applyProtection="0"> <alignment vertical="center"/> </xf> </cellXfs> </styleSheet> """) stylesheet = Stylesheet.from_tree(node) styles = stylesheet.cell_styles assert styles[0] == StyleArray([2, 0, 0, 164, 0, 1, 0, 0, 0])
def apply_stylesheet(archive, wb): """ Add styles to workbook if present """ try: src = archive.read(ARC_STYLE) except KeyError: return wb node = fromstring(src) stylesheet = Stylesheet.from_tree(node) if stylesheet.cell_styles: wb._borders = IndexedList(stylesheet.borders) wb._fonts = IndexedList(stylesheet.fonts) wb._fills = IndexedList(stylesheet.fills) wb._differential_styles.styles = stylesheet.dxfs wb._number_formats = stylesheet.number_formats wb._protections = stylesheet.protections wb._alignments = stylesheet.alignments wb._table_styles = stylesheet.tableStyles # need to overwrite openpyxl defaults in case workbook has different ones wb._cell_styles = stylesheet.cell_styles wb._named_styles = stylesheet.named_styles wb._date_formats = stylesheet.date_formats wb._timedelta_formats = stylesheet.timedelta_formats for ns in wb._named_styles: ns.bind(wb) else: warn("Workbook contains no stylesheet, using openpyxl's defaults") if not wb._named_styles: normal = styles['Normal'] wb.add_named_style(normal) warn("Workbook contains no default style, apply openpyxl's default") if stylesheet.colors is not None: wb._colors = stylesheet.colors.index
def find_images(archive, path): """ Given the path to a drawing file extract charts and images Ingore errors due to unsupported parts of DrawingML """ src = archive.read(path) tree = fromstring(src) try: drawing = SpreadsheetDrawing.from_tree(tree) except TypeError: warn( "DrawingML support is incomplete and limited to charts and images only. Shapes and drawings will be lost." ) return [], [] rels_path = get_rels_path(path) deps = [] if rels_path in archive.namelist(): deps = get_dependents(archive, rels_path) charts = [] for rel in drawing._chart_rels: cs = get_rel(archive, deps, rel.id, ChartSpace) chart = read_chart(cs) chart.anchor = rel.anchor charts.append(chart) images = [] for rel in drawing._blip_rels: dep = deps[rel.embed] if dep.Type == IMAGE_NS: image = Image(BytesIO(archive.read(dep.target))) if image.format.upper() == "WMF": # cannot save msg = "{0} image format is not supported so the image is being dropped".format( image.format) warn(msg) continue image.anchor = rel.anchor images.append(image) return charts, images
def test_from_xml(self, DataLabelList): src = """ <dLbls> <showLegendKey val="0"/> <showVal val="0"/> <showCatName val="0"/> <showSerName val="0"/> <showPercent val="0"/> <showBubbleSize val="0"/> </dLbls> """ node = fromstring(src) dl = DataLabelList.from_tree(node) assert dl.showLegendKey is False assert dl.showVal is False assert dl.showCatName is False assert dl.showSerName is False assert dl.showPercent is False assert dl.showBubbleSize is False
def theme_colours(self): """Lazily-loaded theme colour info for a .xlsx file.""" if not hasattr(self, '_theme_colours'): from openpyxl.xml.functions import fromstring, QName root = fromstring(self.book.loaded_theme) ns = 'http://schemas.openxmlformats.org/drawingml/2006/main' theme_elt = root.find(QName(ns, 'themeElements').text) colour_schemes = theme_elt.findall(QName(ns, 'clrScheme').text) colours = self._theme_colours = [] for cname in [ 'lt1', 'dk1', 'lt2', 'dk2', 'accent1', 'accent2', 'accent3', 'accent4', 'accent5', 'accent6' ]: c = colour_schemes[0].find(QName(ns, cname).text) colour_def = c.getchildren()[0].attrib if 'window' in colour_def['val']: colours.append(colour_def['lastClr']) else: colours.append(colour_def['val']) return self._theme_colours
def test_read(datadir): datadir.chdir() from ..reader import read_chart with open("chart1.xml") as src: xml = src.read() tree = fromstring(xml) cs = ChartSpace.from_tree(tree) chart = read_chart(cs) assert isinstance(chart, LineChart) assert chart.title.tx.rich.p[0].r[0].t == "Website Performance" assert isinstance(chart.y_axis, NumericAxis) assert chart.y_axis.title.tx.rich.p[0].r[0].t == "Time in seconds" assert isinstance(chart.x_axis, DateAxis) assert chart.x_axis.title is None assert len(chart.series) == 10
def read_chartsheet(self, sheet, rel): sheet_path = rel.target rels_path = get_rels_path(sheet_path) rels = [] if rels_path in self.valid_files: rels = get_dependents(self.archive, rels_path) with self.archive.open(sheet_path, "r") as src: xml = src.read() node = fromstring(xml) cs = Chartsheet.from_tree(node) cs._parent = self.wb cs.title = sheet.name self.wb._add_sheet(cs) drawings = rels.find(SpreadsheetDrawing._rel_type) for rel in drawings: charts, images = find_images(self.archive, rel.target) for c in charts: cs.add_chart(c)
def test_from_xml(self, PivotFilter, Autofilter): src = """ <filter fld="0" type="dateBetween" evalOrder="-1" id="6"> <autoFilter ref="A1"> <filterColumn colId="0"> <customFilters and="1"> <customFilter operator="greaterThanOrEqual" val="1"/> <customFilter operator="lessThanOrEqual" val="2"/> </customFilters> </filterColumn> </autoFilter> </filter> """ node = fromstring(src) flt = PivotFilter.from_tree(node) assert flt == PivotFilter(fld=0, id=6, evalOrder=-1, type="dateBetween", autoFilter=Autofilter)
def test_from_tree_degree_sign(self, NumRef): src = b""" <numRef> <f>Hoja1!$A$2:$B$2</f> <numCache> <formatCode>0\xc2\xb0</formatCode> <ptCount val="2" /> <pt idx="0"> <v>3</v> </pt> <pt idx="1"> <v>14</v> </pt> </numCache> </numRef> """ node = fromstring(src) numRef = NumRef.from_tree(node) assert numRef.numCache.formatCode == u"0\xb0"
def _get_scheme_colors_from_excel(wb): xlmns = 'http://schemas.openxmlformats.org/drawingml/2006/main' if wb.loaded_theme is None: return [] root = fromstring(wb.loaded_theme) theme_element = root.find(QName(xlmns, 'themeElements').text) color_schemes = theme_element.findall( QName(xlmns, 'clrScheme').text) colors = [] for colorScheme in color_schemes: for tag in [ 'lt1', 'dk1', 'lt2', 'dk2', 'accent1', 'accent2', 'accent3', 'accent4', 'accent5', 'accent6' ]: accent = list(colorScheme.find(QName(xlmns, tag).text))[0] if 'window' in accent.attrib['val']: colors.append(accent.attrib['lastClr']) else: colors.append(accent.attrib['val']) return colors