def test_ctor(self, ComplexObject):
style = ComplexObject()
ft1 = Font(family=2, sz=11, name="Arial")
ft2 = Font(bold=True)
style.fonts = [ft1, ft2]
expected = """
<style>
<fonts count="2">
<font>
<name val="Arial" />
<family val="2"></family>
<sz val="11"></sz>
</font>
<font>
<b val="1"></b>
</font>
</fonts>
</style>
"""
tree = style.__class__.fonts.to_tree('fonts', style.fonts)
tree = style.to_tree()
xml = tostring(tree)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_cell_copy_style(self, copier):
ws1 = copier.source
ws2 = copier.target
c1 = ws1['A1']
c1.font = Font(bold=True)
copier._copy_cells()
assert ws2['A1'].font == Font(bold=True)
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 openpyxl2.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_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 openpyxl2.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_read_style_iter(tmpdir):
'''
Test if cell styles are read properly in iter mode.
'''
tmpdir.chdir()
from openpyxl2 import Workbook
from openpyxl2.styles import Font
FONT_NAME = "Times New Roman"
FONT_SIZE = 15
ft = Font(name=FONT_NAME, size=FONT_SIZE)
wb = Workbook()
ws = wb.worksheets[0]
cell = ws['A1']
cell.font = ft
xlsx_file = "read_only_styles.xlsx"
wb.save(xlsx_file)
wb_iter = load_workbook(xlsx_file, read_only=True)
ws_iter = wb_iter.worksheets[0]
cell = ws_iter['A1']
assert cell.font == ft
def test_conditional_font(worksheet_with_cf, write_conditional_formatting):
"""Test to verify font style written correctly."""
# Create cf rule
from openpyxl2.styles import PatternFill, Font, Color
from openpyxl2.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_font(DummyWorksheet, Cell):
from openpyxl2.styles import Font
font = Font(bold=True)
ws = DummyWorksheet
ws.parent._fonts.add(font)
cell = Cell(ws, column='A', row=1)
assert cell.font == font
def test_col_style(self, ColumnDimension):
from ..worksheet import Worksheet
from openpyxl2 import Workbook
from openpyxl2.styles import Font
ws = Worksheet(Workbook())
cd = ColumnDimension(ws, index="A")
cd.font = Font(color="FF0000")
cd.reindex()
col = cd.to_tree()
xml = tostring(col)
expected = """<col max="1" min="1" style="1" />"""
diff = compare_xml(xml, expected)
assert diff is None, diff
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_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)