Example #1
0
    def append(self, list_or_dict):
        """Appends a group of values at the bottom of the current sheet.

        * If it's a list: all values are added in order, starting from the first column
        * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)

        :param list_or_dict: list or dict containing values to append
        :type list_or_dict: list/tuple or dict

        Usage:

        * append(['This is A1', 'This is B1', 'This is C1'])
        * **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
        * **or** append({0 : 'This is A1', 2 : 'This is C1'})

        :raise: TypeError when list_or_dict is neither a list/tuple nor a dict

        """
        row_idx = len(self.row_dimensions)
        if isinstance(list_or_dict, (list, tuple)):
            for col_idx, content in enumerate(list_or_dict):
                self.cell(row=row_idx, column=col_idx).value = content

        elif isinstance(list_or_dict, dict):
            for col_idx, content in iteritems(list_or_dict):
                if isinstance(col_idx, basestring):
                    col_idx = column_index_from_string(col_idx) - 1
                self.cell(row=row_idx, column=col_idx).value = content

        else:
            raise TypeError('list_or_dict must be a list or a dict')
Example #2
0
def write_worksheet_cols(doc, worksheet, style_table):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, 'cols')
        for column_string, columndimension in \
                iteritems(worksheet.column_dimensions):
            col_index = column_index_from_string(column_string)
            col_def = {'min': str(col_index), 'max': str(col_index)}
            if columndimension.width != -1:
                col_def['customWidth'] = '1'
            if not columndimension.visible:
                col_def['hidden'] = 'true'
            if columndimension.outline_level > 0:
                col_def['outlineLevel'] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def['collapsed'] = 'true'
            if columndimension.auto_size:
                col_def['bestFit'] = 'true'
            if columndimension.style_index:
                col_def['style'] = str(style_table[hash(columndimension.style_index)])
            if columndimension.width > 0:
                col_def['width'] = str(columndimension.width)
            else:
                col_def['width'] = '9.10'
            tag(doc, 'col', col_def)
        end_tag(doc, 'cols')
Example #3
0
def write_worksheet_cols(doc, worksheet):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, 'cols')
        for column_string, columndimension in \
                iteritems(worksheet.column_dimensions):
            col_index = column_index_from_string(column_string)
            col_def = {}
            col_def['collapsed'] = str(columndimension.style_index)
            col_def['min'] = str(col_index)
            col_def['max'] = str(col_index)
            if columndimension.width != \
                    worksheet.default_column_dimension.width:
                col_def['customWidth'] = 'true'
            if not columndimension.visible:
                col_def['hidden'] = 'true'
            if columndimension.outline_level > 0:
                col_def['outlineLevel'] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def['collapsed'] = 'true'
            if columndimension.auto_size:
                col_def['bestFit'] = 'true'
            if columndimension.width > 0:
                col_def['width'] = str(columndimension.width)
            else:
                col_def['width'] = '9.10'
            tag(doc, 'col', col_def)
        end_tag(doc, 'cols')
Example #4
0
    def append(self, list_or_dict):
        """Appends a group of values at the bottom of the current sheet.

        * If it's a list: all values are added in order, starting from the first column
        * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)

        :param list_or_dict: list or dict containing values to append
        :type list_or_dict: list/tuple or dict

        Usage:

        * append(['This is A1', 'This is B1', 'This is C1'])
        * **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
        * **or** append({0 : 'This is A1', 2 : 'This is C1'})

        :raise: TypeError when list_or_dict is neither a list/tuple nor a dict

        """
        row_idx = len(self.row_dimensions)
        if isinstance(list_or_dict, (list, tuple)):
            for col_idx, content in enumerate(list_or_dict):
                self.cell(row=row_idx, column=col_idx).value = content

        elif isinstance(list_or_dict, dict):
            for col_idx, content in iteritems(list_or_dict):
                if isinstance(col_idx, basestring):
                    col_idx = column_index_from_string(col_idx) - 1
                self.cell(row=row_idx, column=col_idx).value = content

        else:
            raise TypeError('list_or_dict must be a list or a dict')
Example #5
0
def write_worksheet_cols(doc, worksheet, style_table):
    """Write worksheet columns to xml."""
    if worksheet.column_dimensions:
        start_tag(doc, 'cols')
        for column_string, columndimension in \
                sorted(iteritems(worksheet.column_dimensions)):
            col_index = column_index_from_string(column_string)
            col_def = {'min': str(col_index), 'max': str(col_index)}
            if columndimension.width != -1:
                col_def['customWidth'] = '1'
            if not columndimension.visible:
                col_def['hidden'] = 'true'
            if columndimension.outline_level > 0:
                col_def['outlineLevel'] = str(columndimension.outline_level)
            if columndimension.collapsed:
                col_def['collapsed'] = 'true'
            if columndimension.auto_size:
                col_def['bestFit'] = 'true'
            if columndimension.style_index and hash(
                    columndimension.style_index) in style_table:
                col_def['style'] = str(style_table[hash(
                    columndimension.style_index)])
            if columndimension.width > 0:
                col_def['width'] = str(columndimension.width)
            else:
                col_def['width'] = '9.10'
            tag(doc, 'col', col_def)
        end_tag(doc, 'cols')
Example #6
0
 def garbage_collect(self):
     """Delete cells that are not storing a value."""
     delete_list = [coordinate for coordinate, cell in \
         iteritems(self._cells) if (not cell.merged and cell.value in ('', None) and \
         (coordinate not in self._styles or
         hash(cell.style) == _DEFAULTS_STYLE_HASH))]
     for coordinate in delete_list:
         del self._cells[coordinate]
Example #7
0
 def garbage_collect(self):
     """Delete cells that are not storing a value."""
     delete_list = [coordinate for coordinate, cell in \
         iteritems(self._cells) if (not cell.merged and cell.value in ('', None) and \
         (coordinate not in self._styles or
         hash(cell.style) == _DEFAULTS_STYLE_HASH))]
     for coordinate in delete_list:
         del self._cells[coordinate]
Example #8
0
 def setRules(self, cfRules):
     """Set the conditional formatting rules from a dictionary.  Intended for use when loading a document.
     cfRules use the structure: {range_string: [rule1, rule2]}, eg:
     {'A1:A4': [{'type': 'colorScale', 'priority': '13', 'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}],
     'color': [Color('FFFF7128'), Color('FFFFEF9C')]}]}
     """
     self.cf_rules = {}
     self.max_priority = 0
     priorityMap = []
     for range_string, rules in iteritems(cfRules):
         self.cf_rules[range_string] = rules
         for rule in rules:
             priorityMap.append(int(rule['priority']))
     priorityMap.sort()
     for range_string, rules in iteritems(cfRules):
         self.cf_rules[range_string] = rules
         for rule in rules:
             priority = priorityMap.index(int(rule['priority'])) + 1
             rule['priority'] = str(priority)
             if 'priority' in rule and priority > self.max_priority:
                 self.max_priority = priority
Example #9
0
def write_worksheet_conditional_formatting(doc, 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
        start_tag(doc, '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])
            start_tag(doc, 'cfRule', attr)
            if 'formula' in rule:
                for f in rule['formula']:
                    tag(doc, 'formula', None, f)
            if 'colorScale' in rule:
                start_tag(doc, 'colorScale')
                for cfvo in rule['colorScale']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                for color in rule['colorScale']['color']:
                    if str(color.index).split(
                            ':'
                    )[0] == 'theme':  # strip prefix theme if marked as such
                        if str(color.index).split(':')[2]:
                            tag(
                                doc, 'color', {
                                    'theme': str(color.index).split(':')[1],
                                    'tint': str(color.index).split(':')[2]
                                })
                        else:
                            tag(doc, 'color',
                                {'theme': str(color.index).split(':')[1]})
                    else:
                        tag(doc, 'color', {'rgb': str(color.index)})
                end_tag(doc, 'colorScale')
            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]
                start_tag(doc, 'iconSet', iconAttr)
                for cfvo in rule['iconSet']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                end_tag(doc, 'iconSet')
            end_tag(doc, 'cfRule')
        end_tag(doc, 'conditionalFormatting')
Example #10
0
    def _write_print_settings(self, root):

        settings = SubElement(root, 'c:printSettings')
        SubElement(settings, 'c:headerFooter')
        try:
            # Python 2
            print_margins_items = iteritems(self.chart.print_margins)
        except AttributeError:
            # Python 3
            print_margins_items = self.chart.print_margins.items()

        margins = dict([(k, str(v)) for (k, v) in print_margins_items])
        SubElement(settings, 'c:pageMargins', margins)
        SubElement(settings, 'c:pageSetup')
Example #11
0
def write_worksheet_conditional_formatting(doc, 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
        start_tag(doc, '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])
            start_tag(doc, 'cfRule', attr)
            if 'formula' in rule:
                for f in rule['formula']:
                    tag(doc, 'formula', None, f)
            if 'colorScale' in rule:
                start_tag(doc, 'colorScale')
                for cfvo in rule['colorScale']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                for color in rule['colorScale']['color']:
                    if str(color.index).split(':')[0] == 'theme':  # strip prefix theme if marked as such
                        if str(color.index).split(':')[2]:
                            tag(doc, 'color', {'theme': str(color.index).split(':')[1],
                                               'tint': str(color.index).split(':')[2]})
                        else:
                            tag(doc, 'color', {'theme': str(color.index).split(':')[1]})
                    else:
                        tag(doc, 'color', {'rgb': str(color.index)})
                end_tag(doc, 'colorScale')
            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]
                start_tag(doc, 'iconSet', iconAttr)
                for cfvo in rule['iconSet']['cfvo']:
                    tag(doc, 'cfvo', cfvo)
                end_tag(doc, 'iconSet')
            end_tag(doc, 'cfRule')
        end_tag(doc, 'conditionalFormatting')