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