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 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 write_cols(xf, worksheet, style_table=None):
"""Write worksheet columns to xml.
style_table is ignored but required
for compatibility with the dumped worksheet <cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
dimension.style = worksheet._styles.get(label)
col_def = dict(dimension)
if col_def == {}:
continue
idx = column_index_from_string(label)
cols.append((idx, col_def))
if not cols:
return
with xf.element('cols'):
for idx, col_def in sorted(cols):
v = "%d" % idx
cmin = col_def.get('min') or v
cmax = col_def.get('max') or v
col_def.update({'min': cmin, 'max': cmax})
c = Element('col', col_def)
xf.write(c)
def _write_print_settings(self):
settings = SubElement(self.root, '{%s}printSettings' % CHART_NS)
SubElement(settings, '{%s}headerFooter' % CHART_NS)
margins = dict([(k, safe_string(v)) for (k, v) in iteritems(self.chart.print_margins)])
SubElement(settings, '{%s}pageMargins' % CHART_NS, margins)
SubElement(settings, '{%s}pageSetup' % CHART_NS)
def write_cols(worksheet):
"""Write worksheet columns to xml.
<cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
col_def = dict(dimension)
if col_def == {}:
continue
idx = column_index_from_string(label)
cols.append((idx, col_def))
if not cols:
return
el = Element('cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
cmin = col_def.get('min') or v
cmax = col_def.get('max') or v
col_def.update({'min': cmin, 'max': cmax})
el.append(Element('col', col_def))
return el
def write_conditional_formatting(xf, 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
cf = Element('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])
cfr = SubElement(cf, 'cfRule', attr)
if 'formula' in rule:
for f in rule['formula']:
SubElement(cfr, 'formula').text = f
if 'colorScale' in rule:
cs = SubElement(cfr, 'colorScale')
for cfvo in rule['colorScale']['cfvo']:
SubElement(cs, 'cfvo', cfvo)
for color in rule['colorScale']['color']:
SubElement(cs, 'color', dict(color))
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]
iconSet = SubElement(cfr, 'iconSet', iconAttr)
for cfvo in rule['iconSet']['cfvo']:
SubElement(iconSet, 'cfvo', cfvo)
xf.write(cf)
def write_cols(worksheet):
"""Write worksheet columns to xml.
<cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
col_def = dict(dimension)
if col_def == {}:
continue
idx = column_index_from_string(label)
cols.append((idx, col_def))
if not cols:
return
el = Element('cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
cmin = col_def.get('min') or v
cmax = col_def.get('max') or v
col_def.update({'min': cmin, 'max': cmax})
el.append(Element('col', col_def))
return el
def write_conditional_formatting(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
cf = Element('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])
cfr = SubElement(cf, 'cfRule', attr)
if 'formula' in rule:
for f in rule['formula']:
SubElement(cfr, 'formula').text = f
if 'colorScale' in rule:
cs = SubElement(cfr, 'colorScale')
for cfvo in rule['colorScale']['cfvo']:
SubElement(cs, 'cfvo', cfvo)
for color in rule['colorScale']['color']:
SubElement(cs, 'color', dict(color))
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]
iconSet = SubElement(cfr, 'iconSet', iconAttr)
for cfvo in rule['iconSet']['cfvo']:
SubElement(iconSet, 'cfvo', cfvo)
yield cf
def extract_comments(self):
"""
extract list of comments and authors
"""
for _coord, cell in iteritems(self.sheet._cells):
if cell.comment is not None:
self.authors.add(cell.comment.author)
self.comments.append(cell.comment)
def extract_comments(self):
"""
extract list of comments and authors
"""
for _coord, cell in iteritems(self.sheet._cells):
if cell.comment is not None:
self.authors.add(cell.comment.author)
self.comments.append(cell.comment)
def _garbage_collect(self):
"""Delete cells that are not storing a value."""
delete_list = []
for coordinate, cell in iteritems(self._cells):
if cell.value in ("", None) and cell.comment is None and (cell.style_id == 0):
delete_list.append(coordinate)
for coordinate in delete_list:
del self._cells[coordinate]
def _garbage_collect(self):
"""Delete cells that are not storing a value."""
delete_list = []
for coordinate, cell in iteritems(self._cells):
if (cell.value in ('', None) and cell.comment is None
and (cell.style_id == 0)):
delete_list.append(coordinate)
for coordinate in delete_list:
del self._cells[coordinate]
def _garbage_collect(self):
"""Delete cells that are not storing a value."""
delete_list = []
for coordinate, cell in iteritems(self._cells):
if (cell.value in ('', None)
and cell.comment is None
and (coordinate not in self._styles or cell.style == DEFAULTS_STYLE)):
delete_list.append(coordinate)
for coordinate in delete_list:
del self._cells[coordinate]
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
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()
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()
def write_conditional_formatting(worksheet):
"""Write conditional formatting to xml."""
wb = worksheet.parent
for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules):
cf = Element('conditionalFormatting', {'sqref': range_string})
for rule in rules:
if rule.dxf is not None:
if rule.dxf != DifferentialStyle():
rule.dxfId = len(wb._differential_styles)
wb._differential_styles.append(rule.dxf)
cf.append(rule.to_tree())
yield cf
def append(self, iterable):
"""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 iterable: list, range or generator, or dict containing values to append
:type iterable: list/tuple/range/generator 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({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self._current_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
if isinstance(content, Cell):
# compatible with write-only mode
cell = content
if cell.parent and cell.parent.parent != self.parent:
raise ValueError(
"Cells cannot be copied from other workbooks")
cell.parent = self
cell.col_idx = col_idx
cell.row = row_idx
else:
cell = Cell(self,
row=row_idx,
col_idx=col_idx,
value=content)
self._cells[(row_idx, col_idx)] = cell
elif isinstance(iterable, dict):
for col_idx, content in iteritems(iterable):
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
self._cells[(row_idx, col_idx)] = cell
else:
self._invalid_row(iterable)
self._current_row = row_idx
def write_conditional_formatting(worksheet):
"""Write conditional formatting to xml."""
wb = worksheet.parent
for range_string, rules in iteritems(worksheet.conditional_formatting.cf_rules):
cf = Element('conditionalFormatting', {'sqref': range_string})
for rule in rules:
if rule.dxf is not None:
if rule.dxf != DifferentialStyle():
rule.dxfId = len(wb._differential_styles)
wb._differential_styles.append(rule.dxf)
cf.append(rule.to_tree())
yield cf
def __init__(self, sheet):
self.sheet = sheet
# get list of comments
self.comments = []
for coord, cell in iteritems(sheet._cells):
if cell.comment is not None:
self.comments.append(cell.comment)
# get list of authors
self.authors = []
self.author_to_id = {}
for comment in self.comments:
if comment.author not in self.author_to_id:
self.author_to_id[comment.author] = str(len(self.authors))
self.authors.append(comment.author)
def __init__(self, sheet):
self.sheet = sheet
# get list of comments
self.comments = []
for coord, cell in iteritems(sheet._cells):
if cell.comment is not None:
self.comments.append(cell.comment)
# get list of authors
self.authors = []
self.author_to_id = {}
for comment in self.comments:
if comment.author not in self.author_to_id:
self.author_to_id[comment.author] = str(len(self.authors))
self.authors.append(comment.author)
def append(self, iterable):
"""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 iterable: list, range or generator, or dict containing values to append
:type iterable: list/tuple/range/generator 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({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self._current_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
if isinstance(content, Cell):
# compatible with write-only mode
cell = content
if cell.parent and cell.parent != self:
raise ValueError("Cells cannot be copied from other worksheets")
cell.parent = self
cell.col_idx = col_idx
cell.row = row_idx
else:
cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
self._cells[(row_idx, col_idx)] = cell
elif isinstance(iterable, dict):
for col_idx, content in iteritems(iterable):
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
self._cells[(row_idx, col_idx)] = cell
else:
self._invalid_row(iterable)
self._current_row = row_idx
def append(self, iterable):
"""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 iterable: list, range or generator, or dict containing values to append
:type iterable: list/tuple/range/generator 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({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self.max_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
col = get_column_letter(col_idx)
if isinstance(content, Cell):
# compatible with write-only mode
cell = content
cell.parent = self
cell.column = col
cell.row = row_idx
cell.coordinate = "%s%s" % (col, row_idx)
self._add_cell(cell)
else:
cell = self._new_cell(col, row_idx, content)
elif isinstance(iterable, dict):
for col_idx, content in iteritems(iterable):
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
self.cell(row=row_idx, column=col_idx, value=content)
else:
self._invalid_row(iterable)
self.row_dimensions[row_idx] = RowDimension(worksheet=self,
index=row_idx)
def append(self, iterable):
"""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 iterable: list, range or generator, or dict containing values to append
:type iterable: list/tuple/range/generator 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({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self.max_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
col = get_column_letter(col_idx)
if isinstance(content, Cell):
# compatible with write-only mode
cell = content
cell.parent = self
cell.column = col
cell.row = row_idx
cell.coordinate = "%s%s" % (col, row_idx)
self._add_cell(cell)
else:
cell = self._new_cell(col, row_idx, content)
elif isinstance(iterable, dict):
for col_idx, content in iteritems(iterable):
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
self.cell(row=row_idx, column=col_idx, value=content)
else:
self._invalid_row(iterable)
self.row_dimensions[row_idx] = RowDimension(worksheet=self, index=row_idx)
def append(self, iterable):
"""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 iterable: list, range or generator, or dict containing values to append
:type iterable: list/tuple/range/generator 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({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self.max_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
col = get_column_letter(col_idx)
if col not in self.column_dimensions:
self.column_dimensions[col] = ColumnDimension(col)
cell = Cell(self, col, row_idx, content)
self._cells['%s%d' % (col, row_idx)] = cell
elif isinstance(iterable, dict):
for col_idx, content in iteritems(iterable):
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
self.cell(row=row_idx, column=col_idx).value = content
else:
self._invalid_row(iterable)
self.row_dimensions[row_idx] = RowDimension(row_idx)
def unpack_rules(cfRules):
for key, rules in iteritems(cfRules):
for idx, rule in enumerate(rules):
yield (key, idx, rule.priority)
def test_iteritems(dictionary):
from openpyxl.compat import iteritems
d = dictionary
assert set(iteritems(d)) == set([(3, 'd'), ('1', 1), ('a', 'b')])
def test_iteritems(dictionary):
from openpyxl.compat import iteritems
d = dictionary
assert set(iteritems(d)) == set([(3, 'd'), ('1', 1), ('a', 'b')])
def update(self, dictionary):
for k, v in iteritems(dictionary):
self[k] = v
def sheet_comments(self, sheet):
comments = []
for _coord, cell in iteritems(sheet._cells):
if cell.comment is not None:
comments.append(cell.comment)
return comments
def update(self, dictionary):
for k, v in iteritems(dictionary):
self[k] = v
def test_iteritems(dictionary):
from openpyxl.compat import iteritems
d = dictionary
assert set(iteritems(d)) == set([(3, "d"), ("1", 1), ("a", "b")])
def unpack_rules(cfRules):
for key, rules in iteritems(cfRules):
for idx,rule in enumerate(rules):
yield (key, idx, rule.priority)