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)
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
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 _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
