Exemplo n.º 1
0
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
Exemplo n.º 2
0
    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)
Exemplo n.º 3
0
    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)
Exemplo n.º 4
0
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
Exemplo n.º 5
0
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)
Exemplo n.º 8
0
 def to_tree(self, tagname):
     """
     Return as XML node
     """
     el = Element(tagname)
     el.text = str(self)
     return el
Exemplo n.º 9
0
 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
Exemplo n.º 10
0
 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
Exemplo n.º 11
0
    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
Exemplo n.º 12
0
 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
Exemplo n.º 13
0
 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
Exemplo n.º 14
0
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
Exemplo n.º 15
0
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
Exemplo n.º 16
0
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)
Exemplo n.º 17
0
 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
Exemplo n.º 18
0
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
Exemplo n.º 19
0
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
Exemplo n.º 20
0
 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
Exemplo n.º 21
0
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)
Exemplo n.º 22
0
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)
Exemplo n.º 23
0
 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
Exemplo n.º 24
0
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
Exemplo n.º 25
0
 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
Exemplo n.º 26
0
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
Exemplo n.º 28
0
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
Exemplo n.º 29
0
    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()
Exemplo n.º 30
0
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
Exemplo n.º 31
0
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
Exemplo n.º 32
0
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
Exemplo n.º 33
0
    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)
Exemplo n.º 34
0
    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)
Exemplo n.º 35
0
    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
Exemplo n.º 36
0
    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)
Exemplo n.º 37
0
 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
Exemplo n.º 38
0
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()
Exemplo n.º 39
0
    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)
Exemplo n.º 40
0
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))
Exemplo n.º 41
0
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)
Exemplo n.º 42
0
 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
Exemplo n.º 43
0
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
Exemplo n.º 44
0
    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
Exemplo n.º 45
0
    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
Exemplo n.º 46
0
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
Exemplo n.º 48
0
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)
Exemplo n.º 49
0
 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})
Exemplo n.º 50
0
    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
Exemplo n.º 51
0
    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
Exemplo n.º 52
0
    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)
Exemplo n.º 53
0
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
Exemplo n.º 54
0
 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
Exemplo n.º 56
0
    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)
Exemplo n.º 57
0
 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
Exemplo n.º 58
0
    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)