How to use the openpyxl.xml.functions.SubElement function in openpyxl

To help you get started, we’ve selected a few openpyxl examples, based on popular ways it is used in public projects.

Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.

github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
def _write_dxfs(self):
        if self._style_properties and 'dxf_list' in self._style_properties:
            dxfs = SubElement(self._root, 'dxfs', {'count': str(len(self._style_properties['dxf_list']))})
            for d in self._style_properties['dxf_list']:
                dxf = SubElement(dxfs, 'dxf')
                if 'font' in d and d['font'] is not None:
                    font_node = SubElement(dxf, 'font')
                    if d['font'].color is not None:
                        if str(d['font'].color.index).split(':')[0] == 'theme':  # strip prefix theme if marked
                            if str(d['font'].color.index).split(':')[2]:
                                SubElement(font_node, 'color', {'theme': str(d['font'].color.index).split(':')[1],
                                                                'tint': str(d['font'].color.index).split(':')[2]})
                            else:
                                SubElement(font_node, 'color', {'theme': str(d['font'].color.index).split(':')[1]})
                        else:
                            SubElement(font_node, 'color', {'rgb': str(d['font'].color.index)})
                    if d['font'].bold:
                        SubElement(font_node, 'b', {'val': '1'})
                    if d['font'].italic:
                        SubElement(font_node, 'i', {'val': '1'})
                    if d['font'].underline != 'none':
                        SubElement(font_node, 'u', {'val': d['font'].underline})
                    if d['font'].strikethrough:
                        SubElement(font_node, 'strike')
                if 'fill' in d:
                    f = d['fill']
                    fill = SubElement(dxf, 'fill')
                    if f.fill_type:
                        node = SubElement(fill, 'patternFill', {'patternType': f.fill_type})
                    else:
github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
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._style_list:
            if st.font != DEFAULTS.font and st.font not in table:
                table[hash(st.font)] = str(index)
                font_node = SubElement(fonts, 'font')
                SubElement(font_node, 'sz', {'val':str(st.font.size)})
                if str(st.font.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
                    if str(st.font.color.index).split(':')[2]:
                        SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1],
                                                        'tint':str(st.font.color.index).split(':')[2]})
                    else:
                        SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1]})
                else:
github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
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[hash(st.borders)] = str(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})
                        if str(obj.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
                            if str(obj.color.index).split(':')[2]:
                                SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1],
                                                                'tint':str(obj.color.index).split(':')[2]})
                            else:
                                SubElement(node, 'color', {'theme':str(obj.color.index).split(':')[1]})
                        else:
                            SubElement(node, 'color', {'rgb':str(obj.color.index)})
                index += 1

        borders.attrib["count"] = str(index)
        return table
github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
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._style_list:
            if st.font != DEFAULTS.font and st.font not in table:
                table[hash(st.font)] = str(index)
                font_node = SubElement(fonts, 'font')
                SubElement(font_node, 'sz', {'val':str(st.font.size)})
                if str(st.font.color.index).split(':')[0] == 'theme': # strip prefix theme if marked as such
                    if str(st.font.color.index).split(':')[2]:
                        SubElement(font_node, 'color', {'theme':str(st.font.color.index).split(':')[1],
github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
dxfs = SubElement(self._root, 'dxfs', {'count': str(len(self._style_properties['dxf_list']))})
            for d in self._style_properties['dxf_list']:
                dxf = SubElement(dxfs, 'dxf')
                if 'font' in d and d['font'] is not None:
                    font_node = SubElement(dxf, 'font')
                    if d['font'].color is not None:
                        if str(d['font'].color.index).split(':')[0] == 'theme':  # strip prefix theme if marked
                            if str(d['font'].color.index).split(':')[2]:
                                SubElement(font_node, 'color', {'theme': str(d['font'].color.index).split(':')[1],
                                                                'tint': str(d['font'].color.index).split(':')[2]})
                            else:
                                SubElement(font_node, 'color', {'theme': str(d['font'].color.index).split(':')[1]})
                        else:
                            SubElement(font_node, 'color', {'rgb': str(d['font'].color.index)})
                    if d['font'].bold:
                        SubElement(font_node, 'b', {'val': '1'})
                    if d['font'].italic:
                        SubElement(font_node, 'i', {'val': '1'})
                    if d['font'].underline != 'none':
                        SubElement(font_node, 'u', {'val': d['font'].underline})
                    if d['font'].strikethrough:
                        SubElement(font_node, 'strike')
                if 'fill' in d:
                    f = d['fill']
                    fill = SubElement(dxf, 'fill')
                    if f.fill_type:
                        node = SubElement(fill, 'patternFill', {'patternType': f.fill_type})
                    else:
                        node = SubElement(fill, 'patternFill')
                    if f.start_color != DEFAULTS.fill.start_color:
                        if str(f.start_color.index).split(':')[0] == 'theme':  # strip prefix theme if marked
                            if str(f.start_color.index).split(':')[2]:
github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
'tint': str(d['font'].color.index).split(':')[2]})
                            else:
                                SubElement(font_node, 'color', {'theme': str(d['font'].color.index).split(':')[1]})
                        else:
                            SubElement(font_node, 'color', {'rgb': str(d['font'].color.index)})
                    if d['font'].bold:
                        SubElement(font_node, 'b', {'val': '1'})
                    if d['font'].italic:
                        SubElement(font_node, 'i', {'val': '1'})
                    if d['font'].underline != 'none':
                        SubElement(font_node, 'u', {'val': d['font'].underline})
                    if d['font'].strikethrough:
                        SubElement(font_node, 'strike')
                if 'fill' in d:
                    f = d['fill']
                    fill = SubElement(dxf, 'fill')
                    if f.fill_type:
                        node = SubElement(fill, 'patternFill', {'patternType': f.fill_type})
                    else:
                        node = SubElement(fill, 'patternFill')
                    if f.start_color != DEFAULTS.fill.start_color:
                        if str(f.start_color.index).split(':')[0] == 'theme':  # strip prefix theme if marked
                            if str(f.start_color.index).split(':')[2]:
                                SubElement(node, 'fgColor', {'theme': str(f.start_color.index).split(':')[1],
                                                             'tint': str(f.start_color.index).split(':')[2]})
                            else:
                                SubElement(node, 'fgColor', {'theme': str(f.start_color.index).split(':')[1]})
                        else:
                            SubElement(node, 'fgColor', {'rgb': str(f.start_color.index)})
                    if f.end_color != DEFAULTS.fill.end_color:
                        if str(f.end_color.index).split(':')[0] == 'theme':  # strip prefix theme if marked
                            if str(f.end_color.index).split(':')[2]:
github ericgazoni / openpyxl / openpyxl / writer / workbook.py View on Github external
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):
        sheet_node = SubElement(
            sheets, '{%s}sheet' % SHEET_MAIN_NS,
            {'name': sheet.title, 'sheetId': '%d' % (i + 1),
             '{%s}id' % REL_NS: 'rId%d' % (i + 1)})
        if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE:
            sheet_node.set('state', sheet.sheet_state)
github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
def _write_cell_xfs(self, number_format_table, fonts_table, fills_table, borders_table):
        """ write styles combinations based on ids found in tables """

        # writing the cellXfs
        cell_xfs = SubElement(self._root, 'cellXfs',
            {'count':'%d' % (len(self._style_list) + 1)})

        # default
        def _get_default_vals():
            return dict(numFmtId='0', fontId='0', fillId='0',
                xfId='0', borderId='0')

        SubElement(cell_xfs, 'xf', _get_default_vals())

        for st in self._style_list:
            vals = _get_default_vals()

            if st.font != DEFAULTS.font:
                vals['fontId'] = fonts_table[hash(st.font)]
                vals['applyFont'] = '1'
github ericgazoni / openpyxl / openpyxl / writer / styles.py View on Github external
def _write_cell_style(self):
        cell_styles = SubElement(self._root, 'cellStyles', {'count':'1'})
        cell_style = SubElement(cell_styles, 'cellStyle',
            {'name':"Normal", 'xfId':"0", 'builtinId':"0"})
github ericgazoni / openpyxl / openpyxl / writer / workbook.py View on Github external
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)