def parse_dxfs(root, color_index):
"""Read in the dxfs effects - used by conditional formatting."""
dxf_list = []
dxfs = root.find('{%s}dxfs' % SHEET_MAIN_NS)
if dxfs is not None:
nodes = dxfs.findall('{%s}dxf' % SHEET_MAIN_NS)
for dxf in nodes:
dxf_item = {}
font_node = dxf.find('{%s}font' % SHEET_MAIN_NS)
if font_node is not None:
dxf_item['font'] = {}
dxf_item['font']['bold'] = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False
dxf_item['font']['italic'] = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False
if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)):
underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val')
dxf_item['font']['underline'] = underline if underline else 'single'
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
dxf_item['font']['color'] = Color(Color.BLACK)
if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index):
dxf_item['font']['color'].index = color_index[int(color.get('indexed'))]
elif color.get('theme') is not None:
if color.get('tint') is not None:
dxf_item['font']['color'] .index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
dxf_item['font']['color'] .index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
dxf_item['font']['color'] .index = color.get('rgb')
fill_node = dxf.find('{%s}fill' % SHEET_MAIN_NS)
if fill_node is not None:
dxf_item['fill'] = parse_fills(dxf, color_index, True)
dxf_item['border'] = parse_borders(dxf, color_index, True)
dxf_list.append(dxf_item)
return dxf_list
def parse_dxfs(root, color_index):
"""Read in the dxfs effects - used by conditional formatting."""
dxf_list = []
dxfs = root.find('{%s}dxfs' % SHEET_MAIN_NS)
if dxfs is not None:
nodes = dxfs.findall('{%s}dxf' % SHEET_MAIN_NS)
for dxf in nodes:
dxf_item = {}
font_node = dxf.find('{%s}font' % SHEET_MAIN_NS)
if font_node is not None:
dxf_item['font'] = {}
dxf_item['font']['bold'] = True if len(
font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False
dxf_item['font']['italic'] = True if len(
font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False
if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)):
underline = font_node.find('{%s}u' %
SHEET_MAIN_NS).get('val')
dxf_item['font'][
'underline'] = underline if underline else 'single'
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
dxf_item['font']['color'] = Color(Color.BLACK)
if color.get('indexed') is not None and 0 <= int(
color.get('indexed')) < len(color_index):
dxf_item['font']['color'].index = color_index[int(
color.get('indexed'))]
elif color.get('theme') is not None:
if color.get('tint') is not None:
dxf_item['font']['color'].index = 'theme:%s:%s' % (
color.get('theme'), color.get('tint'))
else:
dxf_item['font'][
'color'].index = 'theme:%s:' % color.get(
'theme') # prefix color with theme
elif color.get('rgb'):
dxf_item['font']['color'].index = color.get('rgb')
fill_node = dxf.find('{%s}fill' % SHEET_MAIN_NS)
if fill_node is not None:
dxf_item['fill'] = parse_fills(dxf, color_index, True)
dxf_item['border'] = parse_borders(dxf, color_index, True)
dxf_list.append(dxf_item)
return dxf_list
def parse_dxfs(root, color_index):
"""Read in the dxfs effects - used by conditional formatting."""
dxf_list = []
dxfs = root.find("{%s}dxfs" % SHEET_MAIN_NS)
if dxfs is not None:
nodes = dxfs.findall("{%s}dxf" % SHEET_MAIN_NS)
for dxf in nodes:
dxf_item = {}
font_node = dxf.find("{%s}font" % SHEET_MAIN_NS)
if font_node is not None:
dxf_item["font"] = {}
dxf_item["font"]["bold"] = True if len(font_node.findall("{%s}b" % SHEET_MAIN_NS)) else False
dxf_item["font"]["italic"] = True if len(font_node.findall("{%s}i" % SHEET_MAIN_NS)) else False
if len(font_node.findall("{%s}u" % SHEET_MAIN_NS)):
underline = font_node.find("{%s}u" % SHEET_MAIN_NS).get("val")
dxf_item["font"]["underline"] = underline if underline else "single"
color = font_node.find("{%s}color" % SHEET_MAIN_NS)
if color is not None:
dxf_item["font"]["color"] = Color(Color.BLACK)
if color.get("indexed") is not None and 0 <= int(color.get("indexed")) < len(color_index):
dxf_item["font"]["color"].index = color_index[int(color.get("indexed"))]
elif color.get("theme") is not None:
if color.get("tint") is not None:
dxf_item["font"]["color"].index = "theme:%s:%s" % (color.get("theme"), color.get("tint"))
else:
dxf_item["font"]["color"].index = "theme:%s:" % color.get(
"theme"
) # prefix color with theme
elif color.get("rgb"):
dxf_item["font"]["color"].index = color.get("rgb")
fill_node = dxf.find("{%s}fill" % SHEET_MAIN_NS)
if fill_node is not None:
dxf_item["fill"] = parse_fills(dxf, color_index, True)
dxf_item["border"] = parse_borders(dxf, color_index, True)
dxf_list.append(dxf_item)
return dxf_list
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 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 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)
def fast_parse(ws, xml_source, string_table, style_table, color_index=None):
root = fromstring(xml_source)
guess_types = ws.parent._guess_types
mergeCells = root.find('{%s}mergeCells' % SHEET_MAIN_NS)
if mergeCells is not None:
for mergeCell in mergeCells.findall('{%s}mergeCell' % SHEET_MAIN_NS):
ws.merge_cells(mergeCell.get('ref'))
source = _get_xml_iter(xml_source)
it = iterparse(source)
for event, element in filter(filter_cells, it):
value = element.findtext('{%s}v' % SHEET_MAIN_NS)
formula = element.find('{%s}f' % SHEET_MAIN_NS)
coordinate = element.get('r')
style_id = element.get('s')
if style_id is not None:
ws._styles[coordinate] = style_table.get(int(style_id))
if value is not None:
data_type = element.get('t', 'n')
if data_type == Cell.TYPE_STRING:
value = string_table.get(int(value))
if formula is not None:
if formula.text:
value = "=" + str(formula.text)
else:
value = "="
formula_type = formula.get('t')
if formula_type:
ws.formula_attributes[coordinate] = {'t': formula_type}
if formula.get('si'): # Shared group index for shared formulas
ws.formula_attributes[coordinate]['si'] = formula.get('si')
if formula.get('ref'): # Range for shared formulas
ws.formula_attributes[coordinate]['ref'] = formula.get('ref')
if not guess_types and formula is None:
ws.cell(coordinate).set_explicit_value(value=value, data_type=data_type)
else:
ws.cell(coordinate).value = value
# to avoid memory exhaustion, clear the item after use
element.clear()
cols = root.find('{%s}cols' % SHEET_MAIN_NS)
if cols is not None:
colNodes = cols.findall('{%s}col' % SHEET_MAIN_NS)
for col in colNodes:
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)
if column not in ws.column_dimensions:
ws.column_dimensions[column] = ColumnDimension(column)
if col.get('width') is not None:
ws.column_dimensions[column].width = float(col.get('width'))
if col.get('bestFit') == '1':
ws.column_dimensions[column].auto_size = True
if col.get('hidden') == '1':
ws.column_dimensions[column].visible = False
if col.get('outlineLevel') is not None:
ws.column_dimensions[column].outline_level = int(col.get('outlineLevel'))
if col.get('collapsed') == '1':
ws.column_dimensions[column].collapsed = True
if col.get('style') is not None:
ws.column_dimensions[column].style_index = style_table.get(int(col.get('style')))
sheetData = root.find('{%s}sheetData' % SHEET_MAIN_NS)
if sheetData is not None:
rowNodes = sheetData.findall('{%s}row' % SHEET_MAIN_NS)
for row in rowNodes:
rowId = int(row.get('r'))
if rowId not in ws.row_dimensions:
ws.row_dimensions[rowId] = RowDimension(rowId)
if row.get('ht') is not None:
ws.row_dimensions[rowId].height = float(row.get('ht'))
printOptions = root.find('{%s}printOptions' % SHEET_MAIN_NS)
if printOptions is not None:
if printOptions.get('horizontalCentered') is not None:
ws.page_setup.horizontalCentered = printOptions.get('horizontalCentered')
if printOptions.get('verticalCentered') is not None:
ws.page_setup.verticalCentered = printOptions.get('verticalCentered')
pageMargins = root.find('{%s}pageMargins' % SHEET_MAIN_NS)
if pageMargins is not None:
if pageMargins.get('left') is not None:
ws.page_margins.left = float(pageMargins.get('left'))
if pageMargins.get('right') is not None:
ws.page_margins.right = float(pageMargins.get('right'))
if pageMargins.get('top') is not None:
ws.page_margins.top = float(pageMargins.get('top'))
if pageMargins.get('bottom') is not None:
ws.page_margins.bottom = float(pageMargins.get('bottom'))
if pageMargins.get('header') is not None:
ws.page_margins.header = float(pageMargins.get('header'))
if pageMargins.get('footer') is not None:
ws.page_margins.footer = float(pageMargins.get('footer'))
pageSetup = root.find('{%s}pageSetup' % SHEET_MAIN_NS)
if pageSetup is not None:
if pageSetup.get('orientation') is not None:
ws.page_setup.orientation = pageSetup.get('orientation')
if pageSetup.get('paperSize') is not None:
ws.page_setup.paperSize = pageSetup.get('paperSize')
if pageSetup.get('scale') is not None:
ws.page_setup.top = pageSetup.get('scale')
if pageSetup.get('fitToPage') is not None:
ws.page_setup.fitToPage = pageSetup.get('fitToPage')
if pageSetup.get('fitToHeight') is not None:
ws.page_setup.fitToHeight = pageSetup.get('fitToHeight')
if pageSetup.get('fitToWidth') is not None:
ws.page_setup.fitToWidth = pageSetup.get('fitToWidth')
if pageSetup.get('firstPageNumber') is not None:
ws.page_setup.firstPageNumber = pageSetup.get('firstPageNumber')
if pageSetup.get('useFirstPageNumber') is not None:
ws.page_setup.useFirstPageNumber = pageSetup.get('useFirstPageNumber')
headerFooter = root.find('{%s}headerFooter' % SHEET_MAIN_NS)
if headerFooter is not None:
oddHeader = headerFooter.find('{%s}oddHeader' % SHEET_MAIN_NS)
if oddHeader is not None and oddHeader.text is not None:
ws.header_footer.setHeader(oddHeader.text)
oddFooter = headerFooter.find('{%s}oddFooter' % SHEET_MAIN_NS)
if oddFooter is not None and oddFooter.text is not None:
ws.header_footer.setFooter(oddFooter.text)
conditionalFormattingNodes = root.findall('{%s}conditionalFormatting' % SHEET_MAIN_NS)
rules = {}
for cf in conditionalFormattingNodes:
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 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):
ws.conditional_formatting.setRules(rules)