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_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_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