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 column_string in worksheet._styles:
col_def['style'] = str(style_table[hash(worksheet.get_style(column_string))])
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')
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")
def write_worksheet_cols(doc, 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 cols == []:
return
start_tag(doc, '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})
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
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 column_string in worksheet._styles:
col_def['style'] = str(style_table[hash(
worksheet.get_style(column_string))])
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')
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 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):
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
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 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')
def write_worksheet_cols(doc, 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):
col_def = dict(dimension)
style = worksheet._styles.get(label)
if col_def == {} and style is None:
continue
elif style is not None:
col_def['style'] = '%d' % style
idx = column_index_from_string(label)
cols.append((idx, col_def))
if cols == []:
return
start_tag(doc, 'cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
col_def.update({'min':v, 'max':v})
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
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 \
cell.comment is None and (coordinate not in self._styles or
hash(cell.style) == _DEFAULTS_STYLE_HASH))]
for coordinate in delete_list:
del self._cells[coordinate]
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 \
cell.comment is None and (coordinate not in self._styles or
hash(cell.style) == _DEFAULTS_STYLE_HASH))]
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 (not cell.merged and 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 write_worksheet_format(doc, worksheet):
attrs = {'defaultRowHeight': '15',
'baseColWidth': '10'}
dimensions_outline = [dim.outline_level
for _, dim in iteritems(worksheet.column_dimensions)]
if dimensions_outline:
outline_level = max(dimensions_outline)
if outline_level:
attrs['outlineLevelCol'] = str(outline_level)
tag(doc, 'sheetFormatPr', attrs)
def garbage_collect(self):
"""Delete cells that are not storing a value."""
delete_list = []
for coordinate, cell in iteritems(self._cells):
if (not cell.merged and 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 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')
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')
