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')
Exemple #6
0
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")
Exemple #10
0
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())
Exemple #11
0
 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()
Exemple #13
0
    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()
Exemple #18
0
 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()
Exemple #20
0
 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="******")
Exemple #25
0
    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
Exemple #27
0
 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()
Exemple #31
0
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
Exemple #33
0
    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),
        ]
Exemple #34
0
    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)
Exemple #35
0
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
Exemple #36
0
 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")
Exemple #37
0
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
Exemple #38
0
    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
Exemple #39
0
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"
    }
Exemple #42
0
 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,
     )
Exemple #43
0
 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']
Exemple #44
0
 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) == {}
Exemple #45
0
 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")
Exemple #46
0
 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
                                   )
Exemple #48
0
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
Exemple #49
0
 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")
Exemple #50
0
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
Exemple #53
0
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
Exemple #54
0
    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
Exemple #56
0
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
Exemple #57
0
    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)
Exemple #58
0
 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)
Exemple #59
0
    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"
Exemple #60
0
 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