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 = [] 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 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')