def test_conditional_formatting_update(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
rules = {'A1:A4': [{'type': 'colorScale', 'priority': 13,
'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}], 'color':
[Color('FFFF7128'), Color('FFFFEF9C')]}}]}
worksheet.conditional_formatting.update(rules)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(xml, """
<conditionalFormatting sqref="A1:A4">
<cfRule type="colorScale" priority="1">
<colorScale>
<cfvo type="min" />
<cfvo type="max" />
<color rgb="FFFF7128" />
<color rgb="FFFFEF9C" />
</colorScale>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_conditional_formatting_addCustomRule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
dxfId = worksheet.conditional_formatting.addDxfStyle(
self.workbook, None, None, None)
worksheet.conditional_formatting.addCustomRule(
'C1:C10', {
'type': 'expression',
'dxfId': dxfId,
'formula': ['ISBLANK(C1)'],
'stopIfTrue': '1'
})
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
assert dxfId == 0
expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_setRules(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
rules = {
'A1:A4': [{
'type': 'colorScale',
'priority': '13',
'colorScale': {
'cfvo': [{
'type': 'min'
}, {
'type': 'max'
}],
'color': [Color('FFFF7128'),
Color('FFFFEF9C')]
}
}]
}
worksheet.conditional_formatting.setRules(rules)
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
expected = '<conditionalFormatting sqref="A1:A4"><cfRule type="colorScale" priority="1"><colorScale><cfvo type="min"></cfvo><cfvo type="max"></cfvo><color rgb="FFFF7128"></color><color rgb="FFFFEF9C"></color></colorScale></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_customRule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
worksheet.conditional_formatting.add(
'C1:C10', {
'type': 'expression',
'formula': ['ISBLANK(C1)'],
'stopIfTrue': '1',
'dxf': {}
})
worksheet.conditional_formatting.setDxfStyles(self.workbook)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
worksheet.conditional_formatting.add('A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False,
font=whiteFont, fill=redFill))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
# First, verify conditional formatting xml
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
# Second, verify conditional formatting dxf styles
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def test_conditional_formatting_addCustomRule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
dxfId = worksheet.conditional_formatting.addDxfStyle(self.workbook, None, None, None)
worksheet.conditional_formatting.addCustomRule('C1:C10', {'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1'})
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
assert dxfId == 0
expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_setRules(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
rules = {'A1:A4': [{'type': 'colorScale', 'priority': '13',
'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}], 'color':
[Color('FFFF7128'), Color('FFFFEF9C')]}}]}
worksheet.conditional_formatting.setRules(rules)
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
expected = '<conditionalFormatting sqref="A1:A4"><cfRule type="colorScale" priority="1"><colorScale><cfvo type="min"></cfvo><cfvo type="max"></cfvo><color rgb="FFFF7128"></color><color rgb="FFFFEF9C"></color></colorScale></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_formatting_update(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
rules = {
'A1:A4': [{
'type': 'colorScale',
'priority': 13,
'colorScale': {
'cfvo': [{
'type': 'min'
}, {
'type': 'max'
}],
'color': [Color('FFFF7128'),
Color('FFFFEF9C')]
}
}]
}
worksheet.conditional_formatting.update(rules)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A4">
<cfRule type="colorScale" priority="1">
<colorScale>
<cfvo type="min" />
<cfvo type="max" />
<color rgb="FFFF7128" />
<color rgb="FFFFEF9C" />
</colorScale>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_formula_rule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
worksheet.conditional_formatting.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_formula_rule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
worksheet.conditional_formatting.add(
'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
worksheet.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
# First, verify conditional formatting xml
temp_buffer = BytesIO()
doc = XMLGenerator(out=temp_buffer)
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
# Second, verify conditional formatting dxf styles
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
