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) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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_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._save_styles(self.workbook) # First, verify conditional formatting xml cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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
def test_write_conditional_formatting(self): ws = self.ws cf = ConditionalFormattingList() ws.conditional_formatting = cf fill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) font = Font(name='Arial', size=12, bold=True, underline=Font.UNDERLINE_SINGLE) border = Border(top=Side(border_style=borders.BORDER_THIN, color=Color(colors.DARKYELLOW)), bottom=Side(border_style=borders.BORDER_THIN, color=Color(colors.BLACK))) cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=border, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) from openpyxl.writer.worksheet import write_conditional_formatting for _ in write_conditional_formatting(ws): pass # exhaust generator wb = ws.parent assert len(wb._differential_styles.styles) == 2 ft1, ft2 = wb._differential_styles.styles assert ft1.font == font assert ft1.border == border assert ft1.fill == fill assert ft2.fill == fill
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 cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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 = tostring(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_customRule(self): worksheet = self.ws worksheet.conditional_formatting.add( 'C1:C10', Rule( **{ 'type': 'expression', 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1', })) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="C1:C10"> <cfRule type="expression" stopIfTrue="1" priority="1"> <formula>ISBLANK(C1)</formula> </cfRule> </conditionalFormatting> """) 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) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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_write_conditional_formatting(self): ws = self.ws cf = ConditionalFormatting() ws.conditional_formatting = cf fill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) font = Font(name='Arial', size=12, bold=True, underline=Font.UNDERLINE_SINGLE) border = Border(top=Side(border_style=borders.BORDER_THIN, color=Color(colors.DARKYELLOW)), bottom=Side(border_style=borders.BORDER_THIN, color=Color(colors.BLACK))) cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=border, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) from openpyxl.writer.worksheet import write_conditional_formatting for _ in write_conditional_formatting(ws): pass # exhaust generator wb = ws.parent assert len(wb._differential_styles) == 2 ft1, ft2 = wb._differential_styles assert ft1.font == font assert ft1.border == border assert ft1.fill == fill assert ft2.fill == fill
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 cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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 = tostring(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_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) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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_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) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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_customRule(self): class DummyWorksheet(): conditional_formatting = ConditionalFormatting() worksheet = DummyWorksheet() worksheet.conditional_formatting.add('C1:C10', {'type': 'expression', 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1', 'dxf': {}}) worksheet.conditional_formatting._save_styles(self.workbook) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) 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_formatting_customRule(self): worksheet = self.ws worksheet.conditional_formatting.add('C1:C10', Rule(**{'type': 'expression', 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1',} ) ) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml(xml, """ <conditionalFormatting sqref="C1:C10"> <cfRule 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.""" ws = self.ws cf = ConditionalFormattingList() ws.conditional_formatting = cf # Create cf rule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF")) ws.conditional_formatting.add( 'A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) from openpyxl.writer.worksheet import write_conditional_formatting # First, verify conditional formatting xml cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule dxfId="0" operator="equal" priority="1" type="cellIs" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def _write_header(self): """ Generator that creates the XML file and the sheet header """ with xmlfile(self.filename) as xf: with xf.element("worksheet", xmlns=SHEET_MAIN_NS): if self.sheet_properties: pr = self.sheet_properties.to_tree() xf.write(pr) xf.write(self.views.to_tree()) cols = self.column_dimensions.to_tree() self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline xf.write(self.sheet_format.to_tree()) if cols is not None: xf.write(cols) with xf.element("sheetData"): cell = WriteOnlyCell(self) try: while True: row = (yield) row_idx = self._max_row attrs = {'r': '%d' % row_idx} if row_idx in self.row_dimensions: dim = self.row_dimensions[row_idx] attrs.update(dict(dim)) with xf.element("row", attrs): for col_idx, value in enumerate(row, 1): if value is None: continue try: cell.value = value except ValueError: if isinstance(value, Cell): cell = value else: raise ValueError cell.col_idx = col_idx cell.row = row_idx styled = cell.has_style write_cell(xf, self, cell, styled) if styled: # styled cell or datetime cell = WriteOnlyCell(self) except GeneratorExit: pass if self.protection.sheet: xf.write(self.protection.to_tree()) if self.auto_filter.ref: xf.write(self.auto_filter.to_tree()) if self.sort_state.ref: xf.write(self.sort_state.to_tree()) if self.conditional_formatting: cfs = write_conditional_formatting(self) for cf in cfs: xf.write(cf) if self.data_validations.count: xf.write(self.data_validations.to_tree()) if bool(self.HeaderFooter): xf.write(self.HeaderFooter.to_tree()) drawing = write_drawing(self) if drawing is not None: xf.write(drawing) if self._comments: legacyDrawing = Related(id="anysvml") xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml)