Python functions.SubElement类代码示例

    def _write_fonts(self):
        """ add fonts part to root
            return {font.crc => index}
        """

        fonts = SubElement(self._root, 'fonts')

        # default
        font_node = SubElement(fonts, 'font')
        SubElement(font_node, 'sz', {'val':'11'})
        SubElement(font_node, 'color', {'theme':'1'})
        SubElement(font_node, 'name', {'val':'Calibri'})
        SubElement(font_node, 'family', {'val':'2'})
        SubElement(font_node, 'scheme', {'val':'minor'})

        # others
        table = {}
        index = 1
        for st in self.styles:
            if st.font != DEFAULTS.font and st.font not in table:
                font_node = SubElement(fonts, 'font')
                table[st.font] = index
                index += 1
                self._write_font(font_node, st.font)

        fonts.attrib["count"] = "%d" % index
        return table

    def _write_borders(self):
        borders = SubElement(self._root, 'borders')

        # default
        border = SubElement(borders, 'border')
        SubElement(border, 'left')
        SubElement(border, 'right')
        SubElement(border, 'top')
        SubElement(border, 'bottom')
        SubElement(border, 'diagonal')

        # others
        table = {}
        index = 1
        for st in self._style_list:
            if st.borders != DEFAULTS.borders and st.borders not in table:
                table[st.borders] = index
                border = SubElement(borders, 'border')
                # caution: respect this order
                for side in ('left', 'right', 'top', 'bottom', 'diagonal'):
                    obj = getattr(st.borders, side)
                    if obj.border_style is None or obj.border_style == 'none':
                        node = SubElement(border, side)
                    else:
                        node = SubElement(border, side, {'style':obj.border_style})
                        self._unpack_color(node, obj.color.index)

                index += 1

        borders.attrib["count"] = str(index)
        return table

def write_sheetviews(worksheet):
    views = Element('sheetViews')
    sheetviewAttrs = {'workbookViewId': '0'}
    if not worksheet.show_gridlines:
        sheetviewAttrs['showGridLines'] = '0'
    view = SubElement(views, 'sheetView', sheetviewAttrs)
    selectionAttrs = {}
    topLeftCell = worksheet.freeze_panes
    if topLeftCell:
        colName, row = coordinate_from_string(topLeftCell)
        column = column_index_from_string(colName)
        pane = 'topRight'
        paneAttrs = {}
        if column > 1:
            paneAttrs['xSplit'] = str(column - 1)
        if row > 1:
            paneAttrs['ySplit'] = str(row - 1)
            pane = 'bottomLeft'
            if column > 1:
                pane = 'bottomRight'
        paneAttrs.update(dict(topLeftCell=topLeftCell,
                              activePane=pane,
                              state='frozen'))
        view.append(Element('pane', paneAttrs))
        selectionAttrs['pane'] = pane
        if row > 1 and column > 1:
            SubElement(view, 'selection', {'pane': 'topRight'})
            SubElement(view, 'selection', {'pane': 'bottomLeft'})

    selectionAttrs.update({'activeCell': worksheet.active_cell,
                           'sqref': worksheet.selected_cell})

    SubElement(view, 'selection', selectionAttrs)
    return views

    def _write_fills(self):
        fills = SubElement(self._root, 'fills', {'count':'2'})
        fill = SubElement(fills, 'fill')
        SubElement(fill, 'patternFill', {'patternType':'none'})
        fill = SubElement(fills, 'fill')
        SubElement(fill, 'patternFill', {'patternType':'gray125'})

        table = {}
        index = 2
        for st in self._style_list:
            if st.fill != DEFAULTS.fill and st.fill not in table:
                table[st.fill] = index
                fill = SubElement(fills, 'fill')
                if st.fill.fill_type != DEFAULTS.fill.fill_type:
                    node = SubElement(fill, 'patternFill', {'patternType':st.fill.fill_type})
                    if st.fill.start_color != DEFAULTS.fill.start_color:
                        self._unpack_color(node, st.fill.start_color.index, 'fgColor')

                    if st.fill.end_color != DEFAULTS.fill.end_color:
                        self._unpack_color(node, st.fill.end_color.index, 'bgColor')

                index += 1

        fills.attrib["count"] = str(index)
        return table

def write_cell(worksheet, cell, styled=None):
    coordinate = cell.coordinate
    attributes = {'r': coordinate}
    if styled:
        attributes['s'] = '%d' % cell.style_id

    if cell.data_type != 'f':
        attributes['t'] = cell.data_type

    value = cell._value

    el = Element("c", attributes)
    if value is None or value == "":
        return el

    if cell.data_type == 'f':
        shared_formula = worksheet.formula_attributes.get(coordinate, {})
        if (shared_formula.get('t') == 'shared'
            and 'ref' not in shared_formula):
            value = None
        formula = SubElement(el, 'f', shared_formula)
        if value is not None:
            formula.text = value[1:]
            value = None

    if cell.data_type == 's':
        value = worksheet.parent.shared_strings.add(value)
    cell_content = SubElement(el, 'v')
    if value is not None:
        cell_content.text = safe_string(value)
    return el

def write_external_link(links):
    """Serialise links to ranges in a single external worbook"""
    root = Element("{%s}externalLink" % SHEET_MAIN_NS)
    book =  SubElement(root, "{%s}externalBook" % SHEET_MAIN_NS, {'{%s}id' % REL_NS:'rId1'})
    external_ranges = SubElement(book, "{%s}definedNames" % SHEET_MAIN_NS)
    for l in links:
        external_ranges.append(Element("{%s}definedName" % SHEET_MAIN_NS, dict(l)))
    return root

def write_workbook(workbook):
    """Write the core workbook xml."""

    root = Element('{%s}workbook' % SHEET_MAIN_NS)
    if LXML:
        _nsmap = {'r':REL_NS}
        root = Element('{%s}workbook' % SHEET_MAIN_NS, nsmap=_nsmap)

    wb_props = {}
    if workbook.code_name is not None:
        wb_props['codeName'] = workbook.code_name
    SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, wb_props)

    # book views
    book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
    SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
               {'activeTab': '%d' % workbook._active_sheet_index}
               )

    # worksheets
    sheets = SubElement(root, '{%s}sheets' % SHEET_MAIN_NS)
    for idx, sheet in enumerate(workbook.worksheets + workbook.chartsheets, 1):
        sheet_node = SubElement(
            sheets, '{%s}sheet' % SHEET_MAIN_NS,
            {'name': sheet.title, 'sheetId': '%d' % idx,
             '{%s}id' % REL_NS: 'rId%d' % idx})
        if not sheet.sheet_state == 'visible':
            if len(workbook._sheets) == 1:
                raise ValueError("The only worksheet of a workbook cannot be hidden")
            sheet_node.set('state', sheet.sheet_state)

    # external references
    if getattr(workbook, '_external_links', []):
        external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
        # need to match a counter with a workbook's relations
        counter = len(workbook.worksheets) + 3 # strings, styles, theme
        if workbook.vba_archive:
            counter += 1
        for idx, _ in enumerate(workbook._external_links, counter+1):
            ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
            external_references.append(ext)

    # Defined names
    defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
    _write_defined_names(workbook, defined_names)

    # Defined names -> autoFilter
    for i, sheet in enumerate(workbook.worksheets):
        auto_filter = sheet.auto_filter.ref
        if not auto_filter:
            continue
        name = SubElement(
            defined_names, '{%s}definedName' % SHEET_MAIN_NS,
            dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
        name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
                                 absolute_coordinate(auto_filter))

    SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
               {'calcId': '124519', 'fullCalcOnLoad': '1'})
    return tostring(root)

def write_workbook(workbook):
    """Write the core workbook xml."""

    root = Element("{%s}workbook" % SHEET_MAIN_NS)
    if LXML:
        _nsmap = {"r": REL_NS}
        root = Element("{%s}workbook" % SHEET_MAIN_NS, nsmap=_nsmap)

    wb_props = {}
    if workbook.code_name is not None:
        wb_props["codeName"] = workbook.code_name
    SubElement(root, "{%s}workbookPr" % SHEET_MAIN_NS, wb_props)

    # book views
    book_views = SubElement(root, "{%s}bookViews" % SHEET_MAIN_NS)
    SubElement(book_views, "{%s}workbookView" % SHEET_MAIN_NS, {"activeTab": "%d" % workbook._active_sheet_index})

    # worksheets
    sheets = SubElement(root, "{%s}sheets" % SHEET_MAIN_NS)
    for i, sheet in enumerate(workbook.worksheets, 1):
        sheet_node = SubElement(
            sheets,
            "{%s}sheet" % SHEET_MAIN_NS,
            {"name": sheet.title, "sheetId": "%d" % i, "{%s}id" % REL_NS: "rId%d" % i},
        )
        if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
            if len(workbook.worksheets) == 1:
                raise ValueError("The only worksheet of a workbook cannot be hidden")
            sheet_node.set("state", sheet.sheet_state)

    # external references
    if getattr(workbook, "_external_links", []):
        external_references = SubElement(root, "{%s}externalReferences" % SHEET_MAIN_NS)
        # need to match a counter with a workbook's relations
        counter = len(workbook.worksheets) + 3  # strings, styles, theme
        if workbook.vba_archive:
            counter += 1
        for idx, _ in enumerate(workbook._external_links, counter + 1):
            ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS: "rId%d" % idx})
            external_references.append(ext)

    # Defined names
    defined_names = SubElement(root, "{%s}definedNames" % SHEET_MAIN_NS)
    _write_defined_names(workbook, defined_names)

    # Defined names -> autoFilter
    for i, sheet in enumerate(workbook.worksheets):
        auto_filter = sheet.auto_filter.ref
        if not auto_filter:
            continue
        name = SubElement(
            defined_names,
            "{%s}definedName" % SHEET_MAIN_NS,
            dict(name="_xlnm._FilterDatabase", localSheetId=str(i), hidden="1"),
        )
        name.text = "'%s'!%s" % (sheet.title.replace("'", "''"), absolute_coordinate(auto_filter))

    SubElement(root, "{%s}calcPr" % SHEET_MAIN_NS, {"calcId": "124519", "fullCalcOnLoad": "1"})
    return tostring(root)

def write_workbook(workbook):
    """Write the core workbook xml."""
    root = Element('{%s}workbook' % SHEET_MAIN_NS)
    SubElement(root, '{%s}fileVersion' % SHEET_MAIN_NS,
               {'appName': 'xl', 'lastEdited': '4', 'lowestEdited': '4', 'rupBuild': '4505'})
    SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS,
               {'defaultThemeVersion': '124226', 'codeName': 'ThisWorkbook'})

    # book views
    book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
    SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
               {'activeTab': '%d' % workbook.get_index(workbook.get_active_sheet()),
                'autoFilterDateGrouping': '1', 'firstSheet': '0', 'minimized': '0',
                'showHorizontalScroll': '1', 'showSheetTabs': '1',
                'showVerticalScroll': '1', 'tabRatio': '600',
                'visibility': 'visible'})

    # worksheets
    sheets = SubElement(root, '{%s}sheets' % SHEET_MAIN_NS)
    for i, sheet in enumerate(workbook.worksheets, 1):
        sheet_node = SubElement(
            sheets, '{%s}sheet' % SHEET_MAIN_NS,
            {'name': sheet.title, 'sheetId': '%d' % i,
             '{%s}id' % REL_NS: 'rId%d' % i })
        if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
            if len(workbook.worksheets) == 1:
                raise ValueError("The only worksheet of a workbook cannot be hidden")
            sheet_node.set('state', sheet.sheet_state)

    # external references
    if getattr(workbook, '_external_links', []):
        external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
        # need to match a counter with a workbook's relations
        counter = len(workbook.worksheets) + 3 # strings, styles, theme
        if workbook.vba_archive:
            counter += 1
        for idx, _ in enumerate(workbook._external_links, counter+1):
            ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
            external_references.append(ext)

    # Defined names
    defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
    _write_defined_names(workbook, defined_names)

    # Defined names -> autoFilter
    for i, sheet in enumerate(workbook.worksheets):
        auto_filter = sheet.auto_filter.ref
        if not auto_filter:
            continue
        name = SubElement(
            defined_names, '{%s}definedName' % SHEET_MAIN_NS,
            dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
        name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
                                 absolute_coordinate(auto_filter))

    SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
               {'calcId': '124519', 'calcMode': 'auto', 'fullCalcOnLoad': '1'})
    return tostring(root)

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')
                text = SubElement(el, 't')
                text.text = key
                if key.strip() != key:
                    text.set(PRESERVE_SPACE, 'preserve')
                xf.write(el)

    return  out.getvalue()

def etree_write_cell(xf, 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

    if cell.data_type == "d":
        if cell.parent.parent.iso_dates:
            if isinstance(value, timedelta):
                value = days_to_time(value)
            value = value.isoformat()
        else:
            attributes['t'] = "n"
            value = to_excel(value, worksheet.parent.epoch)

    if cell._comment is not None:
        comment = CommentRecord.from_cell(cell)
        worksheet._comments.append(comment)

    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(coordinate, {})
        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)

    if cell.hyperlink:
        worksheet._hyperlinks.append(cell.hyperlink)

    xf.write(el)

    def _write_cell_styles(self):
        """ write styles combinations based on ids found in tables """
        # writing the cellXfs
        cell_xfs = SubElement(self._root, 'cellXfs',
                              count='%d' % len(self.wb._cell_styles))

        for style in self.wb._cell_styles:

            node = style.to_tree()
            cell_xfs.append(node)

            if style.applyAlignment:
                al = self.wb._alignments[style.alignmentId]
                el = al.to_tree()
                node.append(el)

            if style.applyProtection:
                prot = self.wb._protections[style.protectionId]
                el = prot.to_tree()
                node.append(el)

    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': self.author_to_id[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 get_document_content(root)

    def _write_borders(self):
        borders = SubElement(self._root, 'borders')

        # default
        border = SubElement(borders, 'border')
        SubElement(border, 'left')
        SubElement(border, 'right')
        SubElement(border, 'top')
        SubElement(border, 'bottom')
        SubElement(border, 'diagonal')

        # others
        table = {}
        index = 1
        for st in self.styles:
            if st.border != DEFAULTS.border and st.border not in table:
                table[st.border] = index
                self._write_border(borders, st.border)
                index += 1

        borders.attrib["count"] = str(index)
        return table

    def _write_fills(self):
        fills = SubElement(self._root, 'fills', {'count':'2'})
        fill = SubElement(fills, 'fill')
        SubElement(fill, 'patternFill', {'patternType':'none'})
        fill = SubElement(fills, 'fill')
        SubElement(fill, 'patternFill', {'patternType':'gray125'})

        table = {}
        index = 2
        for st in self.styles:
            if st.fill != DEFAULTS.fill and st.fill not in table:

                table[st.fill] = index
                node = SubElement(fills, 'fill')
                if isinstance(st.fill, PatternFill):
                    self._write_pattern_fill(node, st.fill)
                elif isinstance(st.fill, GradientFill):
                    self._write_gradient_fill(node, st.fill)
                index += 1

        fills.attrib["count"] = str(index)
        return table