def test_update(rules): from .. import unpack_rules, ConditionalFormatting cf = ConditionalFormatting() cf.update(rules) assert cf.max_priority == 25 assert list(unpack_rules(cf.cf_rules)) == [ ('H1:H10', 0, 18), ('Q1:Q10', 0, 12), ('G1:G10', 0, 19), ('F1:F10', 0, 20), ('O1:O10', 0, 14), ('T1:T10', 0, 9), ('X1:X10', 0, 6), ('R1:R10', 0, 11), ('C1:C10', 0, 23), ('J1:J10', 0, 16), ('E1:E10', 0, 21), ('I1:I10', 0, 17), ('Z1:Z10', 0, 4), ('V1:V10', 0, 8), ('AC1:AC10', 0, 1), ('N1:N10', 0, 15), ('AA1:AA10', 0, 3), ('Y1:Y10', 0, 5), ('B1:B10', 0, 24), ('P1:P10', 0, 13), ('W1:W10', 0, 7), ('AB1:AB10', 0, 2), ('A1:A1048576', 0, 25), ('S1:S10', 0, 10), ('D1:D10', 0, 22), ]
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_three_colors(self): cf = ConditionalFormatting() cfRule = ColorScaleRule(start_type='percentile', start_value=10, start_color='FFAA0000', mid_type='percentile', mid_value=50, mid_color='FF0000AA', end_type='percentile', end_value=90, end_color='FF00AA00') cf.add('B1:B10', cfRule) rules = cf.cf_rules assert 'B1:B10' in rules assert len(cf.cf_rules['B1:B10']) == 1 assert rules['B1:B10'][0]['priority'] == 1 assert rules['B1:B10'][0]['type'] == 'colorScale' assert rules['B1:B10'][0]['colorScale']['cfvo'][0][ 'type'] == 'percentile' assert rules['B1:B10'][0]['colorScale']['cfvo'][0]['val'] == '10' assert rules['B1:B10'][0]['colorScale']['cfvo'][1][ 'type'] == 'percentile' assert rules['B1:B10'][0]['colorScale']['cfvo'][1]['val'] == '50' assert rules['B1:B10'][0]['colorScale']['cfvo'][2][ 'type'] == 'percentile' assert rules['B1:B10'][0]['colorScale']['cfvo'][2]['val'] == '90'
def test_write_dxf(self): redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), fill_type=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF"), bold=True, italic=True, underline='single', strikethrough=True) medium_blue = Side(border_style='medium', color=Color(colors.BLUE)) blueBorder = Border(left=medium_blue, right=medium_blue, top=medium_blue, bottom=medium_blue) cf = ConditionalFormatting() cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] 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" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="000000FF"></color> </left> <right style="medium"> <color rgb="000000FF"></color> </right> <top style="medium"> <color rgb="000000FF"></color> </top> <bottom style="medium"> <color rgb="000000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
def test_fix_priorities(rules): from ..formatting import unpack_rules, ConditionalFormatting cf = ConditionalFormatting() cf.cf_rules = rules cf._fix_priorities() assert cf.max_priority == 25 assert list(unpack_rules(cf.cf_rules)) == [ ('H1:H10', 0, 18), ('Q1:Q10', 0, 12), ('G1:G10', 0, 19), ('F1:F10', 0, 20), ('O1:O10', 0, 14), ('T1:T10', 0, 9), ('X1:X10', 0, 6), ('R1:R10', 0, 11), ('C1:C10', 0, 23), ('J1:J10', 0, 16), ('E1:E10', 0, 21), ('I1:I10', 0, 17), ('Z1:Z10', 0, 4), ('V1:V10', 0, 8), ('AC1:AC10', 0, 1), ('N1:N10', 0, 15), ('AA1:AA10', 0, 3), ('Y1:Y10', 0, 5), ('B1:B10', 0, 24), ('P1:P10', 0, 13), ('W1:W10', 0, 7), ('AB1:AB10', 0, 2), ('A1:A1048576', 0, 25), ('S1:S10', 0, 10), ('D1:D10', 0, 22), ]
def test_equal(self): cf = ConditionalFormatting() redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) cf.add('U10:U18', CellIsRule(operator='equal', formula=['U$7'], stopIfTrue=True, fill=redFill)) cf.add('V10:V18', CellIsRule(operator='=', formula=['V$7'], stopIfTrue=True, fill=redFill)) cf.add('W10:W18', CellIsRule(operator='==', formula=['W$7'], stopIfTrue=True, fill=redFill)) cf.setDxfStyles(self.workbook) rules = cf.cf_rules assert 'U10:U18' in rules assert len(cf.cf_rules['U10:U18']) == 1 assert rules['U10:U18'][0]['priority'] == 1 assert rules['U10:U18'][0]['type'] == 'cellIs' assert rules['U10:U18'][0]['dxfId'] == 0 assert rules['U10:U18'][0]['operator'] == 'equal' assert rules['U10:U18'][0]['formula'][0] == 'U$7' assert rules['U10:U18'][0]['stopIfTrue'] == '1' assert 'V10:V18' in rules assert len(cf.cf_rules['V10:V18']) == 1 assert rules['V10:V18'][0]['priority'] == 2 assert rules['V10:V18'][0]['type'] == 'cellIs' assert rules['V10:V18'][0]['dxfId'] == 1 assert rules['V10:V18'][0]['operator'] == 'equal' assert rules['V10:V18'][0]['formula'][0] == 'V$7' assert rules['V10:V18'][0]['stopIfTrue'] == '1' assert 'W10:W18' in rules assert len(cf.cf_rules['W10:W18']) == 1 assert rules['W10:W18'][0]['priority'] == 3 assert rules['W10:W18'][0]['type'] == 'cellIs' assert rules['W10:W18'][0]['dxfId'] == 2 assert rules['W10:W18'][0]['operator'] == 'equal' assert rules['W10:W18'][0]['formula'][0] == 'W$7' assert rules['W10:W18'][0]['stopIfTrue'] == '1'
def test_two_colors(self): cf = ConditionalFormatting() cfRule = ColorScaleRule(start_type='min', start_value=None, start_color='FFAA0000', end_type='max', end_value=None, end_color='FF00AA00') cf.add('A1:A10', cfRule) rules = cf.cf_rules assert 'A1:A10' in rules assert len(cf.cf_rules['A1:A10']) == 1 assert rules['A1:A10'][0]['priority'] == 1 assert rules['A1:A10'][0]['type'] == 'colorScale' assert rules['A1:A10'][0]['colorScale']['cfvo'][0]['type'] == 'min' assert rules['A1:A10'][0]['colorScale']['cfvo'][1]['type'] == 'max'
def test_equal(self): cf = ConditionalFormatting() redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID cf.add( 'U10:U18', CellIsRule(operator='equal', formula=['U$7'], stopIfTrue=True, fill=redFill)) cf.add( 'V10:V18', CellIsRule(operator='=', formula=['V$7'], stopIfTrue=True, fill=redFill)) cf.add( 'W10:W18', CellIsRule(operator='==', formula=['W$7'], stopIfTrue=True, fill=redFill)) cf.setDxfStyles(self.workbook) rules = cf.cf_rules assert 'U10:U18' in rules assert len(cf.cf_rules['U10:U18']) == 1 assert rules['U10:U18'][0]['priority'] == 1 assert rules['U10:U18'][0]['type'] == 'cellIs' assert rules['U10:U18'][0]['dxfId'] == 0 assert rules['U10:U18'][0]['operator'] == 'equal' assert rules['U10:U18'][0]['formula'][0] == 'U$7' assert rules['U10:U18'][0]['stopIfTrue'] == '1' assert 'V10:V18' in rules assert len(cf.cf_rules['V10:V18']) == 1 assert rules['V10:V18'][0]['priority'] == 2 assert rules['V10:V18'][0]['type'] == 'cellIs' assert rules['V10:V18'][0]['dxfId'] == 1 assert rules['V10:V18'][0]['operator'] == 'equal' assert rules['V10:V18'][0]['formula'][0] == 'V$7' assert rules['V10:V18'][0]['stopIfTrue'] == '1' assert 'W10:W18' in rules assert len(cf.cf_rules['W10:W18']) == 1 assert rules['W10:W18'][0]['priority'] == 3 assert rules['W10:W18'][0]['type'] == 'cellIs' assert rules['W10:W18'][0]['dxfId'] == 2 assert rules['W10:W18'][0]['operator'] == 'equal' assert rules['W10:W18'][0]['formula'][0] == 'W$7' assert rules['W10:W18'][0]['stopIfTrue'] == '1'
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == { 'font': font, 'border': borders, 'fill': fill } assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() 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)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == { 'font': font, 'border': border, 'fill': fill } assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() 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)) cf._save_styles(self.workbook) assert len(self.workbook._differential_styles) == 2 ft1, ft2 = self.workbook._differential_styles assert ft1.font == font assert ft1.border == border assert ft1.fill == fill assert ft2.fill == fill
def test_notEqual(self): cf = ConditionalFormatting() redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) cf.add( 'U10:U18', CellIsRule(operator='notEqual', formula=['U$7'], stopIfTrue=True, fill=redFill)) cf.add( 'V10:V18', CellIsRule(operator='!=', formula=['V$7'], stopIfTrue=True, fill=redFill)) cf.setDxfStyles(self.workbook) rules = cf.cf_rules assert 'U10:U18' in rules assert len(cf.cf_rules['U10:U18']) == 1 assert rules['U10:U18'][0]['priority'] == 1 assert rules['U10:U18'][0]['type'] == 'cellIs' assert rules['U10:U18'][0]['dxfId'] == 0 assert rules['U10:U18'][0]['operator'] == 'notEqual' assert rules['U10:U18'][0]['formula'][0] == 'U$7' assert rules['U10:U18'][0]['stopIfTrue'] == '1' assert 'V10:V18' in rules assert len(cf.cf_rules['V10:V18']) == 1 assert rules['V10:V18'][0]['priority'] == 2 assert rules['V10:V18'][0]['type'] == 'cellIs' assert rules['V10:V18'][0]['dxfId'] == 1 assert rules['V10:V18'][0]['operator'] == 'notEqual' assert rules['V10:V18'][0]['formula'][0] == 'V$7' assert rules['V10:V18'][0]['stopIfTrue'] == '1'
def __init__(self, parent, title=None): super(Worksheet, self).__init__(parent, title) self.row_dimensions = BoundDictionary("index", self._add_row) self.column_dimensions = DimensionHolder( worksheet=self, default_factory=self._add_column) self.page_breaks = PageBreak() self._cells = {} self._charts = [] self._images = [] self._rels = [] self._drawing = None self._comment_count = 0 self._merged_cells = [] self.hyperlinks = set() self._data_validations = [] self.sheet_state = self.SHEETSTATE_VISIBLE self.page_setup = PrintPageSetup(worksheet=self) self.print_options = PrintOptions() self.page_margins = PageMargins() self.header_footer = HeaderFooter() self.sheet_view = SheetView() self.protection = SheetProtection() self._current_row = 0 self._auto_filter = AutoFilter() self._freeze_panes = None self.paper_size = None self.formula_attributes = {} self.orientation = None self.conditional_formatting = ConditionalFormatting() self.legacy_drawing = None self.sheet_properties = WorksheetProperties()
def test_three_colors(self): cf = ConditionalFormatting() cfRule = ColorScaleRule(start_type='percentile', start_value=10, start_color='FFAA0000', mid_type='percentile', mid_value=50, mid_color='FF0000AA', end_type='percentile', end_value=90, end_color='FF00AA00') cf.add('B1:B10', cfRule) rules = cf.cf_rules assert 'B1:B10' in rules assert len(cf.cf_rules['B1:B10']) == 1 assert rules['B1:B10'][0]['priority'] == 1 assert rules['B1:B10'][0]['type'] == 'colorScale' assert rules['B1:B10'][0]['colorScale']['cfvo'][0]['type'] == 'percentile' assert rules['B1:B10'][0]['colorScale']['cfvo'][0]['val'] == '10' assert rules['B1:B10'][0]['colorScale']['cfvo'][1]['type'] == 'percentile' assert rules['B1:B10'][0]['colorScale']['cfvo'][1]['val'] == '50' assert rules['B1:B10'][0]['colorScale']['cfvo'][2]['type'] == 'percentile' assert rules['B1:B10'][0]['colorScale']['cfvo'][2]['val'] == '90'
def test_notEqual(self): cf = ConditionalFormatting() redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID cf.add('U10:U18', CellIsRule(operator='notEqual', formula=['U$7'], stopIfTrue=True, fill=redFill)) cf.add('V10:V18', CellIsRule(operator='!=', formula=['V$7'], stopIfTrue=True, fill=redFill)) cf.setDxfStyles(self.workbook) rules = cf.cf_rules assert 'U10:U18' in rules assert len(cf.cf_rules['U10:U18']) == 1 assert rules['U10:U18'][0]['priority'] == 1 assert rules['U10:U18'][0]['type'] == 'cellIs' assert rules['U10:U18'][0]['dxfId'] == 0 assert rules['U10:U18'][0]['operator'] == 'notEqual' assert rules['U10:U18'][0]['formula'][0] == 'U$7' assert rules['U10:U18'][0]['stopIfTrue'] == '1' assert 'V10:V18' in rules assert len(cf.cf_rules['V10:V18']) == 1 assert rules['V10:V18'][0]['priority'] == 2 assert rules['V10:V18'][0]['type'] == 'cellIs' assert rules['V10:V18'][0]['dxfId'] == 1 assert rules['V10:V18'][0]['operator'] == 'notEqual' assert rules['V10:V18'][0]['formula'][0] == 'V$7' assert rules['V10:V18'][0]['stopIfTrue'] == '1'
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_formatting_setDxfStyle(self): cf = ConditionalFormatting() 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)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': border, 'fill': fill} assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def __init__(self, parent_workbook, title='Sheet'): self._parent = parent_workbook self._title = '' if not title: self.title = 'Sheet%d' % (1 + len(self._parent.worksheets)) else: self.title = title self.row_dimensions = {} self.column_dimensions = DimensionHolder(worksheet=self, direction=[]) self.page_breaks = [] self._cells = {} self._styles = {} self._charts = [] self._images = [] self._comment_count = 0 self._merged_cells = [] self.relationships = [] self._data_validations = [] self.selected_cell = 'A1' self.active_cell = 'A1' self.sheet_state = self.SHEETSTATE_VISIBLE self.page_setup = PageSetup() self.page_margins = PageMargins() self.header_footer = HeaderFooter() self.sheet_view = SheetView() self.protection = SheetProtection() self.show_gridlines = True self.print_gridlines = False self.show_summary_below = True self.show_summary_right = True self.default_row_dimension = RowDimension(worksheet=self) self.default_column_dimension = ColumnDimension(worksheet=self) self._auto_filter = AutoFilter() self._freeze_panes = None self.paper_size = None self.formula_attributes = {} self.orientation = None self.conditional_formatting = ConditionalFormatting() self.vba_code = {} self.vba_controls = None
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() 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)) cf._save_styles(self.workbook) assert len(self.workbook._differential_styles) == 2 ft1, ft2 = self.workbook._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.""" ws = self.ws cf = ConditionalFormatting() 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 test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill} assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def worksheet_with_cf(worksheet): from openpyxl.formatting import ConditionalFormatting worksheet.conditional_formating = ConditionalFormatting() return worksheet
def test_write_dxf(self): redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID whiteFont = Font() whiteFont.color.index = "FFFFFFFF" whiteFont.bold = True whiteFont.italic = True whiteFont.underline = 'single' whiteFont.strikethrough = True blueBorder = Borders() blueBorder.left.border_style = 'medium' blueBorder.left.color = Color(Color.BLUE) blueBorder.right.border_style = 'medium' blueBorder.right.color = Color(Color.BLUE) blueBorder.top.border_style = 'medium' blueBorder.top.color = Color(Color.BLUE) blueBorder.bottom.border_style = 'medium' blueBorder.bottom.color = Color(Color.BLUE) cf = ConditionalFormatting() cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] 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" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="FF0000FF"></color> </left> <right style="medium"> <color rgb="FF0000FF"></color> </right> <top style="medium"> <color rgb="FF0000FF"></color> </top> <bottom style="medium"> <color rgb="FF0000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
def __init__(self): self.conditional_formatting = ConditionalFormatting() self.parent = DummyWorkbook()
def test_write_dxf(self): redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID whiteFont = Font() whiteFont.color.index = "FFFFFFFF" whiteFont.bold = True whiteFont.italic = True whiteFont.underline = 'single' whiteFont.strikethrough = True blueBorder = Borders() blueBorder.left.border_style = 'medium' blueBorder.left.color = Color(Color.BLUE) blueBorder.right.border_style = 'medium' blueBorder.right.color = Color(Color.BLUE) blueBorder.top.border_style = 'medium' blueBorder.top.color = Color(Color.BLUE) blueBorder.bottom.border_style = 'medium' blueBorder.bottom.color = Color(Color.BLUE) cf = ConditionalFormatting() cf.add( 'A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] 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" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="FF0000FF"></color> </left> <right style="medium"> <color rgb="FF0000FF"></color> </right> <top style="medium"> <color rgb="FF0000FF"></color> </top> <bottom style="medium"> <color rgb="FF0000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
class DummyWorksheet(): conditional_formatting = ConditionalFormatting()
class WS(): conditional_formatting = ConditionalFormatting()
def test_write_dxf(self): redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), fill_type=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF"), bold=True, italic=True, underline='single', strikethrough=True) medium_blue = Side(border_style='medium', color=Color(colors.BLUE)) blueBorder = Border(left=medium_blue, right=medium_blue, top=medium_blue, bottom=medium_blue) cf = ConditionalFormatting() cf.add( 'A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] 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" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="000000FF"></color> </left> <right style="medium"> <color rgb="000000FF"></color> </right> <top style="medium"> <color rgb="000000FF"></color> </top> <bottom style="medium"> <color rgb="000000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff