def test_conditional_font(worksheet_with_cf, write_conditional_formatting):
"""Test to verify font style written correctly."""
# Create cf rule
from openpyxl25.styles import PatternFill, Font, Color
from openpyxl25.formatting.rule import CellIsRule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType='solid')
whiteFont = Font(color=Color("FFFFFFFF"))
ws = worksheet_with_cf
ws.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0">
<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 openpyxl25.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_fill(DummyWorksheet, Cell):
from openpyxl25.styles import PatternFill
fill = PatternFill(patternType="solid", fgColor="FF0000")
ws = DummyWorksheet
ws.parent._fills.add(fill)
cell = Cell(ws, column='A', row=1)
assert cell.fill == fill
def test_parse(DifferentialStyle, datadir):
datadir.chdir()
with open("dxf_style.xml") as content:
src = content.read()
xml = fromstring(src)
formats = []
for node in xml.findall("{%s}dxfs/{%s}dxf" %
(SHEET_MAIN_NS, SHEET_MAIN_NS)):
formats.append(DifferentialStyle.from_tree(node))
assert len(formats) == 164
cond = formats[1]
assert cond.font == Font(underline="double",
b=False,
color=Color(auto=1),
strikethrough=True,
italic=True)
assert cond.fill == PatternFill(end_color='FFFFC7CE')
def test_serialise(DifferentialStyle):
cond = DifferentialStyle()
cond.font = Font(name="Calibri", family=2, sz=11)
cond.fill = PatternFill()
xml = tostring(cond.to_tree())
expected = """
<dxf>
<font>
<name val="Calibri"></name>
<family val="2"></family>
<sz val="11"></sz>
</font>
<fill>
<patternFill />
</fill>
</dxf>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_cellis_rule():
from openpyxl25.formatting.rule import CellIsRule
from openpyxl25.styles import PatternFill
red_fill = PatternFill(start_color='FFEE1111',
end_color='FFEE1111',
fill_type='solid')
rule = CellIsRule(operator='<',
formula=['C$1'],
stopIfTrue=True,
fill=red_fill)
assert dict(rule) == {
'operator': 'lessThan',
'priority': '0',
'type': 'cellIs',
'stopIfTrue': '1'
}
assert rule.formula == ['C$1']
assert rule.dxf.fill == red_fill
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 openpyxl25.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_read(datadir):
datadir.chdir()
reference_file = 'conditional-formatting.xlsx'
wb = load_workbook(reference_file)
ws = wb.active
rules = ws.conditional_formatting
assert len(rules) == 30
# First test the conditional formatting rules read
rule = rules['A1:A1048576'][0]
assert dict(rule) == {
'priority': '30',
'type': 'colorScale',
}
rule = rules['B1:B10'][0]
assert dict(rule) == {'priority': '29', 'type': 'colorScale'}
rule = rules['C1:C10'][0]
assert dict(rule) == {'priority': '28', 'type': 'colorScale'}
rule = rules['D1:D10'][0]
assert dict(rule) == {
'priority': '27',
'type': 'colorScale',
}
rule = rules['E1:E10'][0]
assert dict(rule) == {
'priority': '26',
'type': 'colorScale',
}
rule = rules['F1:F10'][0]
assert dict(rule) == {
'priority': '25',
'type': 'colorScale',
}
rule = rules['G1:G10'][0]
assert dict(rule) == {
'priority': '24',
'type': 'colorScale',
}
rule = rules['H1:H10'][0]
assert dict(rule) == {
'priority': '23',
'type': 'colorScale',
}
rule = rules['I1:I10'][0]
assert dict(rule) == {
'priority': '22',
'type': 'colorScale',
}
rule = rules['J1:J10'][0]
assert dict(rule) == {
'priority': '21',
'type': 'colorScale',
}
rule = rules['K1:K10'][0]
assert dict(rule) == {'priority': '20', 'type': 'dataBar'}
rule = rules['L1:L10'][0]
assert dict(rule) == {'priority': '19', 'type': 'dataBar'}
rule = rules['M1:M10'][0]
assert dict(rule) == {'priority': '18', 'type': 'dataBar'}
rule = rules['N1:N10'][0]
assert dict(rule) == {'priority': '17', 'type': 'iconSet'}
rule = rules['O1:O10'][0]
assert dict(rule) == {'priority': '16', 'type': 'iconSet'}
rule = rules['P1:P10'][0]
assert dict(rule) == {'priority': '15', 'type': 'iconSet'}
rule = rules['Q1:Q10'][0]
assert dict(rule) == {
'text': '3',
'priority': '14',
'dxfId': '27',
'operator': 'containsText',
'type': 'containsText'
}
assert rule.dxf == DifferentialStyle(font=Font(color='FF9C0006'),
fill=PatternFill(bgColor='FFFFC7CE'))
rule = rules['R1:R10'][0]
assert dict(rule) == {
'operator': 'between',
'dxfId': '26',
'type': 'cellIs',
'priority': '13'
}
assert rule.dxf == DifferentialStyle(font=Font(color='FF9C6500'),
fill=PatternFill(bgColor='FFFFEB9C'))
rule = rules['S1:S10'][0]
assert dict(rule) == {
'priority': '12',
'dxfId': '25',
'percent': '1',
'type': 'top10',
'rank': '10'
}
rule = rules['T1:T10'][0]
assert dict(rule) == {
'priority': '11',
'dxfId': '24',
'type': 'top10',
'rank': '4',
'bottom': '1'
}
rule = rules['U1:U10'][0]
assert dict(rule) == {
'priority': '10',
'dxfId': '23',
'type': 'aboveAverage'
}
rule = rules['V1:V10'][0]
assert dict(rule) == {
'aboveAverage': '0',
'dxfId': '22',
'type': 'aboveAverage',
'priority': '9'
}
rule = rules['W1:W10'][0]
assert dict(rule) == {
'priority': '8',
'dxfId': '21',
'type': 'aboveAverage',
'equalAverage': '1'
}
rule = rules['X1:X10'][0]
assert dict(rule) == {
'aboveAverage': '0',
'dxfId': '20',
'priority': '7',
'type': 'aboveAverage',
'equalAverage': '1'
}
rule = rules['Y1:Y10'][0]
assert dict(rule) == {
'priority': '6',
'dxfId': '19',
'type': 'aboveAverage',
'stdDev': '1'
}
rule = rules['Z1:Z10'][0]
assert dict(rule) == {
'aboveAverage': '0',
'dxfId': '18',
'type': 'aboveAverage',
'stdDev': '1',
'priority': '5'
}
assert rule.dxf == DifferentialStyle(
font=Font(b=True, i=True, color='FF9C0006'),
fill=PatternFill(bgColor='FFFFC7CE'),
border=Border(left=Side(style='thin', color=Color(theme=5)),
right=Side(style='thin', color=Color(theme=5)),
top=Side(style='thin', color=Color(theme=5)),
bottom=Side(style='thin', color=Color(theme=5))))
rule = rules['AA1:AA10'][0]
assert dict(rule) == {
'priority': '4',
'dxfId': '17',
'type': 'aboveAverage',
'stdDev': '2'
}
rule = rules['AB1:AB10'][0]
assert dict(rule) == {
'priority': '3',
'dxfId': '16',
'type': 'duplicateValues'
}
rule = rules['AC1:AC10'][0]
assert dict(rule) == {
'priority': '2',
'dxfId': '15',
'type': 'uniqueValues'
}
rule = rules['AD1:AD10'][0]
assert dict(rule) == {
'priority': '1',
'dxfId': '14',
'type': 'expression',
}
for cell in rows[0]:
cell.border = cell.border + top
for cell in rows[-1]:
cell.border = cell.border + bottom
for row in rows:
l = row[0]
r = row[-1]
l.border = l.border + left
r.border = r.border + right
if fill:
for c in row:
c.fill = fill
wb = Workbook()
ws = wb.active
my_cell = ws['B2']
my_cell.value = "My Cell"
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
border = Border(top=double, left=thin, right=thin, bottom=double)
fill = PatternFill("solid", fgColor="DDDDDD")
fill = GradientFill(stop=("000000", "FFFFFF"))
font = Font(b=True, color="FF0000")
al = Alignment(horizontal="center", vertical="center")
style_range(ws, 'B2:F4', border=border, fill=fill, font=font, alignment=al)
wb.save("styled.xlsx")
def test_read_complex_style(datadir):
datadir.chdir()
wb = load_workbook("complex-styles.xlsx")
ws = wb.active
assert ws.column_dimensions['A'].width == 31.1640625
assert ws.column_dimensions['I'].font == Font(name="Calibri",
sz=12.0,
color='FF3300FF',
scheme='minor')
assert ws.column_dimensions['I'].fill == PatternFill(
patternType='solid', fgColor='FF006600', bgColor=Color(indexed=64))
assert ws['A2'].font == Font(sz=10, name='Arial', color=Color(theme=1))
assert ws['A3'].font == Font(sz=12,
name='Arial',
bold=True,
color=Color(theme=1))
assert ws['A4'].font == Font(sz=14,
name='Arial',
italic=True,
color=Color(theme=1))
assert ws['A5'].font.color.value == 'FF3300FF'
assert ws['A6'].font.color.value == 9
assert ws['A7'].fill.start_color.value == 'FFFFFF66'
assert ws['A8'].fill.start_color.value == 8
assert ws['A9'].alignment.horizontal == 'left'
assert ws['A10'].alignment.horizontal == 'right'
assert ws['A11'].alignment.horizontal == 'center'
assert ws['A12'].alignment.vertical == 'top'
assert ws['A13'].alignment.vertical == 'center'
assert ws['A15'].number_format == '0.00'
assert ws['A16'].number_format == 'mm-dd-yy'
assert ws['A17'].number_format == '0.00%'
assert 'A18:B18' in ws.merged_cells
assert ws['A19'].border == Border(
left=Side(style='thin', color='FF006600'),
top=Side(style='thin', color='FF006600'),
right=Side(style='thin', color='FF006600'),
bottom=Side(style='thin', color='FF006600'),
)
assert ws['A21'].border == Border(
left=Side(style='double', color=Color(theme=7)),
top=Side(style='double', color=Color(theme=7)),
right=Side(style='double', color=Color(theme=7)),
bottom=Side(style='double', color=Color(theme=7)),
)
assert ws['A23'].fill == PatternFill(patternType='solid',
start_color='FFCCCCFF',
end_color=(Color(indexed=64)))
assert ws['A23'].border.top == Side(style='mediumDashed',
color=Color(theme=6))
assert 'A23:B24' in ws.merged_cells
assert ws['A25'].alignment == Alignment(wrapText=True)
assert ws['A26'].alignment == Alignment(shrinkToFit=True)