class ConditionalFormatting(object): """Conditional formatting rules.""" def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0 def add(self, range_string, cfRule): """Add a rule such as ColorScaleRule, FormulaRule or CellIsRule The priority will be added automatically. """ if not isinstance(cfRule, Rule): raise ValueError( "Only instances of openpyxl.formatting.rule.Rule may be added") rule = cfRule self.max_priority += 1 rule.priority = self.max_priority self.cf_rules.setdefault(range_string, []).append(rule) def _fix_priorities(self): rules = unpack_rules(self.cf_rules) rules = sorted(rules, key=lambda x: x[2]) for idx, (key, rule_no, prio) in enumerate(rules, 1): self.cf_rules[key][rule_no].priority = idx self.max_priority = len(rules) @deprecated("Always use Rule objects") def update(self, cfRules): pass @deprecated("Conditionl Formats are saved automatically") def setDxfStyles(self, wb): pass
class ConditionalFormattingList(object): """Conditional formatting rules.""" def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0 def add(self, range_string, cfRule): """Add a rule such as ColorScaleRule, FormulaRule or CellIsRule The priority will be added automatically. """ if not isinstance(cfRule, Rule): raise ValueError("Only instances of openpyxl.formatting.rule.Rule may be added") rule = cfRule self.max_priority += 1 if not rule.priority: rule.priority = self.max_priority self.cf_rules.setdefault(range_string, []).append(rule) def __bool__(self): return bool(self.cf_rules) __nonzero = __bool__ def __iter__(self): for cell_range, rules in self.cf_rules.items(): yield ConditionalFormatting(sqref=cell_range, cfRule=rules)
def names(self): """ Convert to NamedStyle objects and remove duplicates """ def sort_fn(v): return v.xfId styles = OrderedDict() for ns in sorted(self.cellStyle, key=sort_fn): style = NamedStyle(name=ns.name, hidden=ns.hidden) style.builtinId = ns.builtinId style.xfId = ns.xfId styles[ns.name] = style return NamedStyleList(styles.values())
def margins(self): margins = OrderedDict() for margin_name in self.valid_margins: margin_value = getattr(self, margin_name) if margin_value: margins[margin_name] = "%0.2f" % margin_value return margins
def options(self): optionsGroup = OrderedDict() for options_name in self.valid_options: options_value = getattr(self, options_name) if options_value is not None: optionsGroup[options_name] = '1' return optionsGroup
def names(self): """ Convert to NamedStyle objects and remove duplicates """ def sort_fn(v): return v.xfId styles = OrderedDict() for ns in sorted(self.cellStyle, key=sort_fn): style = NamedStyle( name=ns.name, hidden=ns.hidden ) style.builtinId = ns.builtinId style.xfId = ns.xfId styles[ns.name] = style return NamedStyleList(styles.values())
def rules(): return OrderedDict([('H1:H10', [{ 'priority': 23, }]), ('Q1:Q10', [{ 'priority': 14, }]), ('G1:G10', [{ 'priority': 24, }]), ('F1:F10', [{ 'priority': 25, }]), ('O1:O10', [{ 'priority': 16, }]), ('K1:K10', []), ('T1:T10', [{ 'priority': 11, }]), ('X1:X10', [{ 'priority': 7, }]), ('R1:R10', [{ 'priority': 13 }]), ('C1:C10', [{ 'priority': 28, }]), ('J1:J10', [{ 'priority': 21, }]), ('E1:E10', [{ 'priority': 26, }]), ('I1:I10', [{ 'priority': 22, }]), ('Z1:Z10', [{ 'priority': 5 }]), ('V1:V10', [{ 'priority': 9 }]), ('AC1:AC10', [{ 'priority': 2, }]), ('L1:L10', []), ('N1:N10', [{ 'priority': 17, }]), ('AA1:AA10', [{ 'priority': 4, }]), ('M1:M10', []), ('Y1:Y10', [{ 'priority': 6, }]), ('B1:B10', [{ 'priority': 29, }]), ('P1:P10', [{ 'priority': 15, }]), ('W1:W10', [{ 'priority': 8, }]), ('AB1:AB10', [{ 'priority': 3, }]), ('A1:A1048576', [{ 'priority': 29, }]), ('S1:S10', [{ 'priority': 12, }]), ('D1:D10', [{ 'priority': 27, }])])
def setup(self): setupGroup = OrderedDict() for setup_name in self.valid_setup: setup_value = getattr(self, setup_name) if setup_value is not None: if setup_name == 'orientation': setupGroup[setup_name] = '%s' % setup_value elif setup_name in ('paperSize', 'scale'): setupGroup[setup_name] = '%d' % int(setup_value) elif setup_name in ('fitToHeight', 'fitToWidth') and int(setup_value) >= 0: setupGroup[setup_name] = '%d' % int(setup_value) return setupGroup
class ConditionalFormatting(object): """Conditional formatting rules.""" def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0 def add(self, range_string, cfRule): """Add a rule such as ColorScaleRule, FormulaRule or CellIsRule The priority will be added automatically. """ if not isinstance(cfRule, Rule): raise ValueError("Only instances of openpyxl.formatting.rule.Rule may be added") rule = cfRule self.max_priority += 1 rule.priority = self.max_priority self.cf_rules.setdefault(range_string, []).append(rule) def _fix_priorities(self): rules = unpack_rules(self.cf_rules) rules = sorted(rules, key=lambda x: x[2]) for idx, (key, rule_no, prio) in enumerate(rules, 1): self.cf_rules[key][rule_no].priority = idx self.max_priority = len(rules) @deprecated("Always use Rule objects") def update(self, cfRules): pass @deprecated("Conditionl Formats are saved automatically") def setDxfStyles(self, wb): pass
def rules(): class DummyRule: def __init__(self, priority): self.priority = priority return OrderedDict( [('H1:H10', [DummyRule(23)]), ('Q1:Q10', [DummyRule(14)]), ('G1:G10', [DummyRule(24)]), ('F1:F10', [DummyRule(25)]), ('O1:O10', [DummyRule(16)]), ('K1:K10', []), ('T1:T10', [DummyRule(11)]), ('X1:X10', [DummyRule(7)]), ('R1:R10', [DummyRule(13)]), ('C1:C10', [DummyRule(28)]), ('J1:J10', [DummyRule(21)]), ('E1:E10', [DummyRule(26)]), ('I1:I10', [DummyRule(22)]), ('Z1:Z10', [DummyRule(5)]), ('V1:V10', [DummyRule(9)]), ('AC1:AC10', [DummyRule(2)]), ('L1:L10', []), ('N1:N10', [DummyRule(17)]), ('AA1:AA10', [DummyRule(4)]), ('M1:M10', []), ('Y1:Y10', [DummyRule(6)]), ('B1:B10', [DummyRule(29)]), ('P1:P10', [DummyRule(15)]), ('W1:W10', [DummyRule(8)]), ('AB1:AB10', [DummyRule(3)]), ('A1:A1048576', [DummyRule(29)]), ('S1:S10', [DummyRule(12)]), ('D1:D10', [DummyRule(27)])])
def __init__(self, parent_workbook, title='Sheet'): self._parent = parent_workbook self._title = '' if not title: self.title = 'Sheet%d' % (1 + len(self._parent.worksheets)) else: self.title = title self.row_dimensions = {} self.column_dimensions = OrderedDict([]) self.page_breaks = [] self._cells = {} self._styles = {} self._charts = [] self._images = [] self._comment_count = 0 self._merged_cells = [] self.relationships = [] self._data_validations = [] self.selected_cell = 'A1' self.active_cell = 'A1' self.sheet_state = self.SHEETSTATE_VISIBLE self.page_setup = PageSetup() self.page_margins = PageMargins() self.header_footer = HeaderFooter() self.sheet_view = SheetView() self.protection = SheetProtection() self.show_gridlines = True self.print_gridlines = False self.show_summary_below = True self.show_summary_right = True self.default_row_dimension = RowDimension(self) self.default_column_dimension = ColumnDimension(self) self._auto_filter = AutoFilter() self._freeze_panes = None self.paper_size = None self.formula_attributes = {} self.orientation = None self.xml_source = None self.conditional_formatting = ConditionalFormatting()
def collapse_cell_addresses(cells, input_ranges=()): """ Collapse a collection of cell co-ordinates down into an optimal range or collection of ranges. E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation object applied, attempt to collapse down to a single range, A1:B3. Currently only collapsing contiguous vertical ranges (i.e. above example results in A1:A3 B1:B3). More work to come. """ keyfunc = lambda x: x[0] # Get the raw coordinates for each cell given raw_coords = [coordinate_from_string(cell) for cell in cells] # Group up as {column: [list of rows]} grouped_coords = OrderedDict( (k, [c[1] for c in g]) for k, g in groupby(sorted(raw_coords, key=keyfunc), keyfunc)) ranges = list(input_ranges) # For each column, find contiguous ranges of rows for column in grouped_coords: rows = sorted(grouped_coords[column]) grouped_rows = [[ r[1] for r in list(g) ] for k, g in groupby(enumerate(rows), lambda x: x[0] - x[1])] for rows in grouped_rows: if len(rows) == 0: pass elif len(rows) == 1: ranges.append("%s%d" % (column, rows[0])) else: ranges.append("%s%d:%s%d" % (column, rows[0], column, rows[-1])) return " ".join(ranges)
class ConditionalFormatting(object): """Conditional formatting rules.""" rule_attributes = ('aboveAverage', 'bottom', 'dxfId', 'equalAverage', 'operator', 'percent', 'priority', 'rank', 'stdDev', 'stopIfTrue', 'text') icon_attributes = ('iconSet', 'showValue', 'reverse') def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0 self.parse_rules = {} def add(self, range_string, cfRule): """Add a rule. Rule is either: 1. A dictionary containing a key called type, and other keys, as in `ConditionalFormatting.rule_attributes`. 2. A rule object, such as ColorScaleRule, FormulaRule or CellIsRule The priority will be added automatically. """ if isinstance(cfRule, dict): rule = cfRule else: rule = cfRule.rule rule['priority'] = self.max_priority + 1 self.max_priority += 1 if range_string not in self.cf_rules: self.cf_rules[range_string] = [] self.cf_rules[range_string].append(rule) def update(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')]}]} """ for range_string, rules in iteritems(cfRules): if range_string not in self.cf_rules: self.cf_rules[range_string] = rules else: self.cf_rules[range_string] += rules # Fix any gap in the priority range. self.max_priority = 0 priorityMap = [] for range_string, rules in iteritems(self.cf_rules): for rule in rules: priorityMap.append(rule['priority']) priorityMap.sort() for range_string, rules in iteritems(self.cf_rules): for rule in rules: priority = priorityMap.index(rule['priority']) + 1 rule['priority'] = priority if 'priority' in rule and priority > self.max_priority: self.max_priority = priority def setDxfStyles(self, wb): """Formatting for non color scale conditional formatting uses the dxf style list in styles.xml. This scans the cf_rules for dxf styles which have not been added - and saves them to the workbook. When adding a conditional formatting rule that uses a font, border or fill, this must be called at least once before saving the workbook. :param wb: the workbook """ if not wb.style_properties: wb.style_properties = {'dxf_list': []} elif 'dxf_list' not in wb.style_properties: wb.style_properties['dxf_list'] = [] for rules in self.cf_rules.values(): for rule in rules: if 'dxf' in rule: dxf = {} if 'font' in rule['dxf'] and isinstance(rule['dxf']['font'], Font): # DXF font is limited to color, bold, italic, underline and strikethrough dxf['font'] = rule['dxf']['font'] if 'border' in rule['dxf'] and isinstance(rule['dxf']['border'], Borders): dxf['border'] = rule['dxf']['border'] if 'fill' in rule['dxf'] and isinstance(rule['dxf']['fill'], Fill): dxf['fill'] = rule['dxf']['fill'] wb.style_properties['dxf_list'].append(dxf) rule.pop('dxf') rule['dxfId'] = len(wb.style_properties['dxf_list']) - 1
def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0
class ConditionalFormatting(object): """Conditional formatting rules.""" rule_attributes = ('aboveAverage', 'bottom', 'dxfId', 'equalAverage', 'operator', 'percent', 'priority', 'rank', 'stdDev', 'stopIfTrue', 'text') icon_attributes = ('iconSet', 'showValue', 'reverse') def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0 self.parse_rules = {} def add(self, range_string, cfRule): """Add a rule. Rule is either: 1. A dictionary containing a key called type, and other keys, as in `ConditionalFormatting.rule_attributes`. 2. A rule object, such as ColorScaleRule, FormulaRule or CellIsRule The priority will be added automatically. """ if isinstance(cfRule, dict): rule = cfRule else: rule = cfRule.rule rule['priority'] = self.max_priority + 1 self.max_priority += 1 if range_string not in self.cf_rules: self.cf_rules[range_string] = [] self.cf_rules[range_string].append(rule) def update(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')]}]} """ for range_string, rules in iteritems(cfRules): if range_string not in self.cf_rules: self.cf_rules[range_string] = rules else: self.cf_rules[range_string] += rules # Fix any gap in the priority range. self.max_priority = 0 priorityMap = [] for range_string, rules in iteritems(self.cf_rules): for rule in rules: priorityMap.append(rule['priority']) priorityMap.sort() for range_string, rules in iteritems(self.cf_rules): for rule in rules: priority = priorityMap.index(rule['priority']) + 1 rule['priority'] = priority if 'priority' in rule and priority > self.max_priority: self.max_priority = priority def setDxfStyles(self, wb): """Formatting for non color scale conditional formatting uses the dxf style list in styles.xml. This scans the cf_rules for dxf styles which have not been added - and saves them to the workbook. When adding a conditional formatting rule that uses a font, border or fill, this must be called at least once before saving the workbook. :param wb: the workbook """ if not wb.style_properties: wb.style_properties = {'dxf_list': []} elif 'dxf_list' not in wb.style_properties: wb.style_properties['dxf_list'] = [] for rules in self.cf_rules.values(): for rule in rules: if 'dxf' in rule: dxf = {} if 'font' in rule['dxf'] and isinstance( rule['dxf']['font'], Font): # DXF font is limited to color, bold, italic, underline and strikethrough dxf['font'] = rule['dxf']['font'] if 'border' in rule['dxf'] and isinstance( rule['dxf']['border'], Borders): dxf['border'] = rule['dxf']['border'] if 'fill' in rule['dxf'] and isinstance( rule['dxf']['fill'], Fill): dxf['fill'] = rule['dxf']['fill'] wb.style_properties['dxf_list'].append(dxf) rule.pop('dxf') rule['dxfId'] = len(wb.style_properties['dxf_list']) - 1
class ConditionalFormatting(object): """Conditional formatting rules.""" rule_attributes = ('aboveAverage', 'bottom', 'dxfId', 'equalAverage', 'operator', 'percent', 'priority', 'rank', 'stdDev', 'stopIfTrue', 'text') icon_attributes = ('iconSet', 'showValue', 'reverse') def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0 self.parse_rules = {} def add(self, range_string, cfRule): """Add a rule. Rule is either: 1. A dictionary containing a key called type, and other keys, as in `ConditionalFormatting.rule_attributes`. 2. A rule object, such as ColorScaleRule, FormulaRule or CellIsRule The priority will be added automatically. """ if isinstance(cfRule, dict): rule = cfRule else: rule = cfRule.rule rule['priority'] = self.max_priority + 1 self.max_priority += 1 if range_string not in self.cf_rules: self.cf_rules[range_string] = [] self.cf_rules[range_string].append(rule) def _fix_priorities(self): rules = unpack_rules(self.cf_rules) rules = sorted(rules, key=lambda x: x[2]) for idx, (key, rule_no, prio) in enumerate(rules, 1): self.cf_rules[key][rule_no]['priority'] = idx self.max_priority = len(rules) def update(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')]}]} """ for range_string, rules in iteritems(cfRules): if range_string not in self.cf_rules: self.cf_rules[range_string] = rules else: self.cf_rules[range_string] += rules self._fix_priorities() @deprecated("Conditionl Formats are saved automatically") def setDxfStyles(self, wb): self._save_styles(wb) def _save_styles(self, wb): """Formatting for non color scale conditional formatting uses the dxf style list in styles.xml. This scans the cf_rules for dxf styles which have not been added - and saves them to the workbook. When adding a conditional formatting rule that uses a font, border or fill, this must be called at least once before saving the workbook. :param wb: the workbook """ for rules in self.cf_rules.values(): for rule in rules: if 'dxf' in rule: dxf = DifferentialStyle() if 'font' in rule['dxf'] and isinstance(rule['dxf']['font'], Font): # DXF font is limited to color, bold, italic, underline and strikethrough dxf.font = rule['dxf']['font'] if 'border' in rule['dxf'] and isinstance(rule['dxf']['border'], Border): dxf.border = rule['dxf']['border'] if 'fill' in rule['dxf'] and isinstance(rule['dxf']['fill'], PatternFill): dxf.fill = rule['dxf']['fill'] wb._differential_styles.append(dxf) rule.pop('dxf') rule['dxfId'] = len(wb._differential_styles) - 1
class ConditionalFormatting(object): """Conditional formatting rules.""" rule_attributes = ('aboveAverage', 'bottom', 'dxfId', 'equalAverage', 'operator', 'percent', 'priority', 'rank', 'stdDev', 'stopIfTrue', 'text') icon_attributes = ('iconSet', 'showValue', 'reverse') def __init__(self): self.cf_rules = OrderedDict() self.max_priority = 0 self.parse_rules = {} def add(self, range_string, cfRule): """Add a rule. Rule is either: 1. A dictionary containing a key called type, and other keys, as in `ConditionalFormatting.rule_attributes`. 2. A rule object, such as ColorScaleRule, FormulaRule or CellIsRule The priority will be added automatically. """ if isinstance(cfRule, dict): rule = cfRule else: rule = cfRule.rule rule['priority'] = self.max_priority + 1 self.max_priority += 1 if range_string not in self.cf_rules: self.cf_rules[range_string] = [] self.cf_rules[range_string].append(rule) def _fix_priorities(self): rules = unpack_rules(self.cf_rules) rules = sorted(rules, key=lambda x: x[2]) for idx, (key, rule_no, prio) in enumerate(rules, 1): self.cf_rules[key][rule_no]['priority'] = idx self.max_priority = len(rules) def update(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')]}]} """ for range_string, rules in iteritems(cfRules): if range_string not in self.cf_rules: self.cf_rules[range_string] = rules else: self.cf_rules[range_string] += rules self._fix_priorities() @deprecated("Conditionl Formats are saved automatically") def setDxfStyles(self, wb): self._save_styles(wb) def _save_styles(self, wb): """Formatting for non color scale conditional formatting uses the dxf style list in styles.xml. This scans the cf_rules for dxf styles which have not been added - and saves them to the workbook. When adding a conditional formatting rule that uses a font, border or fill, this must be called at least once before saving the workbook. :param wb: the workbook """ for rules in self.cf_rules.values(): for rule in rules: if 'dxf' in rule: dxf = ConditionaStyle() if 'font' in rule['dxf'] and isinstance(rule['dxf']['font'], Font): # DXF font is limited to color, bold, italic, underline and strikethrough dxf.font = rule['dxf']['font'] if 'border' in rule['dxf'] and isinstance(rule['dxf']['border'], Border): dxf.border = rule['dxf']['border'] if 'fill' in rule['dxf'] and isinstance(rule['dxf']['fill'], PatternFill): dxf.fill = rule['dxf']['fill'] wb.conditional_formats.append(dxf) rule.pop('dxf') rule['dxfId'] = len(wb.conditional_formats) - 1
def _descriptors_cache(self): try: return self._parent._local_data.cache except AttributeError: self._parent._local_data.cache = OrderedDict() return self._parent._local_data.cache