def parse_column_dimensions(self, element):
for col in safe_iterator(element, "{%s}col" % SHEET_MAIN_NS):
min = int(col.get("min")) if col.get("min") else 1
max = int(col.get("max")) if col.get("max") else 1
# Ignore ranges that go up to the max column 16384. Columns need to be extended to handle
# ranges without creating an entry for every single one.
if max != 16384:
for colId in range(min, max + 1):
column = get_column_letter(colId)
width = col.get("width")
auto_size = col.get("bestFit") == "1"
visible = col.get("hidden") != "1"
outline = col.get("outlineLevel") or 0
collapsed = col.get("collapsed") == "1"
style_index = self.style_table.get(int(col.get("style", 0)))
if column not in self.ws.column_dimensions:
new_dim = ColumnDimension(
index=column,
width=width,
auto_size=auto_size,
visible=visible,
outline_level=outline,
collapsed=collapsed,
style_index=style_index,
)
self.ws.column_dimensions[column] = new_dim
def parse_row_dimensions(self, element):
for row in safe_iterator(element, '{%s}row' % SHEET_MAIN_NS):
rowId = int(row.get('r'))
if rowId not in self.ws.row_dimensions:
self.ws.row_dimensions[rowId] = RowDimension(rowId)
ht = row.get('ht')
if ht is not None:
self.ws.row_dimensions[rowId].height = float(ht)
def parse_row_dimensions(self, element):
for row in safe_iterator(element, "{%s}row" % SHEET_MAIN_NS):
rowId = int(row.get("r"))
if rowId not in self.ws.row_dimensions:
self.ws.row_dimensions[rowId] = RowDimension(rowId)
ht = row.get("ht")
if ht is not None:
self.ws.row_dimensions[rowId].height = float(ht)
def test_write_chart(self, bar_chart_2):
"""check if some characteristic tags of LineChart are there"""
cw = BarChartWriter(bar_chart_2)
cw._write_chart()
tagnames = ['{%s}barChart' % CHART_NS,
'{%s}valAx' % CHART_NS,
'{%s}catAx' % CHART_NS]
root = safe_iterator(cw.root)
chart_tags = [e.tag for e in root]
for tag in tagnames:
assert tag in chart_tags
def test_write_chart(self, pie_chart):
"""check if some characteristic tags of PieChart are there"""
cw = PieChartWriter(pie_chart)
cw._write_chart()
tagnames = ['{%s}pieChart' % CHART_NS, '{%s}varyColors' % CHART_NS]
root = safe_iterator(cw.root)
chart_tags = [e.tag for e in root]
for tag in tagnames:
assert tag in chart_tags
assert 'c:catAx' not in chart_tags
def test_write_chart(self, bar_chart_2):
"""check if some characteristic tags of LineChart are there"""
cw = BarChartWriter(bar_chart_2)
cw._write_chart()
tagnames = [
'{%s}barChart' % CHART_NS,
'{%s}valAx' % CHART_NS,
'{%s}catAx' % CHART_NS
]
root = safe_iterator(cw.root)
chart_tags = [e.tag for e in root]
for tag in tagnames:
assert tag in chart_tags
def test_write_chart(self, pie_chart):
"""check if some characteristic tags of PieChart are there"""
cw = PieChartWriter(pie_chart)
cw._write_chart()
tagnames = ['{%s}pieChart' % CHART_NS,
'{%s}varyColors' % CHART_NS
]
root = safe_iterator(cw.root)
chart_tags = [e.tag for e in root]
for tag in tagnames:
assert tag in chart_tags
assert 'c:catAx' not in chart_tags
def parse_column_dimensions(self, element):
for col in safe_iterator(element, '{%s}col' % SHEET_MAIN_NS):
min = int(col.get('min')) if col.get('min') else 1
max = int(col.get('max')) if col.get('max') else 1
# Ignore ranges that go up to the max column 16384. Columns need to be extended to handle
# ranges without creating an entry for every single one.
if max != 16384:
for colId in range(min, max + 1):
column = get_column_letter(colId)
width = col.get("width")
auto_size = col.get('bestFit') == '1'
visible = col.get('hidden') != '1'
outline = col.get('outlineLevel') or 0
collapsed = col.get('collapsed') == '1'
style_index = self.style_table.get(int(col.get('style', 0)))
if column not in self.ws.column_dimensions:
new_dim = ColumnDimension(index=column,
width=width, auto_size=auto_size,
visible=visible, outline_level=outline,
collapsed=collapsed, style_index=style_index)
self.ws.column_dimensions[column] = new_dim
def parser_conditional_formatting(self, element):
rules = {}
for cf in safe_iterator(element, '{%s}conditionalFormatting' % SHEET_MAIN_NS):
if not cf.get('sqref'):
# Potentially flag - this attribute should always be present.
continue
range_string = cf.get('sqref')
cfRules = cf.findall('{%s}cfRule' % SHEET_MAIN_NS)
rules[range_string] = []
for cfRule in cfRules:
if not cfRule.get('type') or cfRule.get('type') == 'dataBar':
# dataBar conditional formatting isn't supported, as it relies on the complex <extLst> tag
continue
rule = {'type': cfRule.get('type')}
for attr in ConditionalFormatting.rule_attributes:
if cfRule.get(attr) is not None:
rule[attr] = cfRule.get(attr)
formula = cfRule.findall('{%s}formula' % SHEET_MAIN_NS)
for f in formula:
if 'formula' not in rule:
rule['formula'] = []
rule['formula'].append(f.text)
colorScale = cfRule.find('{%s}colorScale' % SHEET_MAIN_NS)
if colorScale is not None:
rule['colorScale'] = {'cfvo': [], 'color': []}
cfvoNodes = colorScale.findall('{%s}cfvo' % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get('type') is not None:
cfvo['type'] = node.get('type')
if node.get('val') is not None:
cfvo['val'] = node.get('val')
rule['colorScale']['cfvo'].append(cfvo)
colorNodes = colorScale.findall('{%s}color' % SHEET_MAIN_NS)
for color in colorNodes:
c = Color(Color.BLACK)
if self.color_index\
and color.get('indexed') is not None\
and 0 <= int(color.get('indexed')) < len(self.color_index):
c.index = self.color_index[int(color.get('indexed'))]
if color.get('theme') is not None:
if color.get('tint') is not None:
c.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
c.index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
c.index = color.get('rgb')
rule['colorScale']['color'].append(c)
iconSet = cfRule.find('{%s}iconSet' % SHEET_MAIN_NS)
if iconSet is not None:
rule['iconSet'] = {'cfvo': []}
for iconAttr in ConditionalFormatting.icon_attributes:
if iconSet.get(iconAttr) is not None:
rule['iconSet'][iconAttr] = iconSet.get(iconAttr)
cfvoNodes = iconSet.findall('{%s}cfvo' % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get('type') is not None:
cfvo['type'] = node.get('type')
if node.get('val') is not None:
cfvo['val'] = node.get('val')
rule['iconSet']['cfvo'].append(cfvo)
rules[range_string].append(rule)
if len(rules):
self.ws.conditional_formatting.setRules(rules)
def parse_merge(self, element):
for mergeCell in safe_iterator(element, ('{%s}mergeCell' % SHEET_MAIN_NS)):
self.ws.merge_cells(mergeCell.get('ref'))
def parser_conditional_formatting(self, element):
rules = {}
for cf in safe_iterator(element, "{%s}conditionalFormatting" % SHEET_MAIN_NS):
if not cf.get("sqref"):
# Potentially flag - this attribute should always be present.
continue
range_string = cf.get("sqref")
cfRules = cf.findall("{%s}cfRule" % SHEET_MAIN_NS)
rules[range_string] = []
for cfRule in cfRules:
if not cfRule.get("type") or cfRule.get("type") == "dataBar":
# dataBar conditional formatting isn't supported, as it relies on the complex <extLst> tag
continue
rule = {"type": cfRule.get("type")}
for attr in ConditionalFormatting.rule_attributes:
if cfRule.get(attr) is not None:
rule[attr] = cfRule.get(attr)
formula = cfRule.findall("{%s}formula" % SHEET_MAIN_NS)
for f in formula:
if "formula" not in rule:
rule["formula"] = []
rule["formula"].append(f.text)
colorScale = cfRule.find("{%s}colorScale" % SHEET_MAIN_NS)
if colorScale is not None:
rule["colorScale"] = {"cfvo": [], "color": []}
cfvoNodes = colorScale.findall("{%s}cfvo" % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get("type") is not None:
cfvo["type"] = node.get("type")
if node.get("val") is not None:
cfvo["val"] = node.get("val")
rule["colorScale"]["cfvo"].append(cfvo)
colorNodes = colorScale.findall("{%s}color" % SHEET_MAIN_NS)
for color in colorNodes:
c = Color(Color.BLACK)
if (
self.color_index
and color.get("indexed") is not None
and 0 <= int(color.get("indexed")) < len(self.color_index)
):
c.index = self.color_index[int(color.get("indexed"))]
if color.get("theme") is not None:
if color.get("tint") is not None:
c.index = "theme:%s:%s" % (color.get("theme"), color.get("tint"))
else:
c.index = "theme:%s:" % color.get("theme") # prefix color with theme
elif color.get("rgb"):
c.index = color.get("rgb")
rule["colorScale"]["color"].append(c)
iconSet = cfRule.find("{%s}iconSet" % SHEET_MAIN_NS)
if iconSet is not None:
rule["iconSet"] = {"cfvo": []}
for iconAttr in ConditionalFormatting.icon_attributes:
if iconSet.get(iconAttr) is not None:
rule["iconSet"][iconAttr] = iconSet.get(iconAttr)
cfvoNodes = iconSet.findall("{%s}cfvo" % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get("type") is not None:
cfvo["type"] = node.get("type")
if node.get("val") is not None:
cfvo["val"] = node.get("val")
rule["iconSet"]["cfvo"].append(cfvo)
rules[range_string].append(rule)
if len(rules):
self.ws.conditional_formatting.setRules(rules)
def parse_merge(self, element):
for mergeCell in safe_iterator(element, ("{%s}mergeCell" % SHEET_MAIN_NS)):
self.ws.merge_cells(mergeCell.get("ref"))
def parser_conditional_formatting(self, element):
rules = {}
for cf in safe_iterator(element, '{%s}conditionalFormatting' % SHEET_MAIN_NS):
if not cf.get('sqref'):
# Potentially flag - this attribute should always be present.
continue
range_string = cf.get('sqref')
cfRules = cf.findall('{%s}cfRule' % SHEET_MAIN_NS)
rules[range_string] = []
for cfRule in cfRules:
if not cfRule.get('type') or cfRule.get('type') == 'dataBar':
# dataBar conditional formatting isn't supported, as it relies on the complex <extLst> tag
continue
rule = {'type': cfRule.get('type')}
for attr in ConditionalFormatting.rule_attributes:
if cfRule.get(attr) is not None:
rule[attr] = cfRule.get(attr)
formula = cfRule.findall('{%s}formula' % SHEET_MAIN_NS)
for f in formula:
if 'formula' not in rule:
rule['formula'] = []
rule['formula'].append(f.text)
colorScale = cfRule.find('{%s}colorScale' % SHEET_MAIN_NS)
if colorScale is not None:
rule['colorScale'] = {'cfvo': [], 'color': []}
cfvoNodes = colorScale.findall('{%s}cfvo' % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get('type') is not None:
cfvo['type'] = node.get('type')
if node.get('val') is not None:
cfvo['val'] = node.get('val')
rule['colorScale']['cfvo'].append(cfvo)
colorNodes = colorScale.findall('{%s}color' % SHEET_MAIN_NS)
for color in colorNodes:
c = Color(Color.BLACK)
if self.color_index\
and color.get('indexed') is not None\
and 0 <= int(color.get('indexed')) < len(color_index):
c.index = color_index[int(color.get('indexed'))]
if color.get('theme') is not None:
if color.get('tint') is not None:
c.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
c.index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
c.index = color.get('rgb')
rule['colorScale']['color'].append(c)
iconSet = cfRule.find('{%s}iconSet' % SHEET_MAIN_NS)
if iconSet is not None:
rule['iconSet'] = {'cfvo': []}
for iconAttr in ConditionalFormatting.icon_attributes:
if iconSet.get(iconAttr) is not None:
rule['iconSet'][iconAttr] = iconSet.get(iconAttr)
cfvoNodes = iconSet.findall('{%s}cfvo' % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get('type') is not None:
cfvo['type'] = node.get('type')
if node.get('val') is not None:
cfvo['val'] = node.get('val')
rule['iconSet']['cfvo'].append(cfvo)
rules[range_string].append(rule)
if len(rules):
self.ws.conditional_formatting.setRules(rules)
