def test_xfs_fills(self):
st = Style(fill=PatternFill(fill_type='solid',
start_color=Color(colors.DARKYELLOW)))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fonts = DummyElement()
fills = Element("fills")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyFill="1" borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
expected = """<fills count="3">
<fill>
<patternFill patternType="solid">
<fgColor rgb="00808000"></fgColor>
</patternFill>
</fill>
</fills>
"""
xml = get_xml(fills)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_protection(self):
prot = Protection(locked=True,
hidden=True)
self.worksheet.cell('A1').style = Style(protection=prot)
w = StyleWriter(self.workbook)
w._write_protection(w._root, prot)
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<protection hidden="1" locked="1"/>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
nft = fonts = borders = fills = Element('empty')
w._write_cell_xfs(nft, fonts, fills, borders)
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<protection hidden="1" locked="1"/>
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyProtection="1" borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0">
<protection hidden="1" locked="1"/>
</xf>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_font():
wb = DummyWorkbook()
from openpyxl.styles import Font
ft = Font(name='Calibri',
charset=204,
vertAlign='superscript',
underline=Font.UNDERLINE_SINGLE)
writer = StyleWriter(wb)
writer._write_font(writer._root, ft)
xml = get_xml(writer._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<font>
<vertAlign val="superscript"></vertAlign>
<sz val="11.0"></sz>
<color rgb="00000000"></color>
<name val="Calibri"></name>
<family val="2"></family>
<u></u>
<charset val="204"></charset>
</font>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_borders(self):
self.worksheet.cell(
'A1').style.borders.top.border_style = Border.BORDER_THIN
self.worksheet.cell(
'A1').style.borders.top.color.index = Color.DARKYELLOW
w = StyleWriter(self.workbook)
w._write_borders()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<borders count="2">
<border>
<left />
<right />
<top />
<bottom />
<diagonal />
</border>
<border>
<left />
<right />
<top style="thin">
<color rgb="FF808000" />
</top>
<bottom />
<diagonal />
</border>
</borders>
</styleSheet>
""")
assert diff is None, diff
def test_parse_dxfs(datadir):
datadir.chdir()
reference_file = 'conditional-formatting.xlsx'
wb = load_workbook(reference_file)
assert isinstance(wb, Workbook)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = 'dxf_style.xml'
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font'].color == Color('FF9C0006')
assert not cond_styles['font'].bold
assert not cond_styles['font'].italic
f = PatternFill(end_color=Color('FFFFC7CE'))
assert cond_styles['fill'] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = tostring(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop[2]) == len(wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop[2]):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader', 'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font']['color'] == Color('FF9C0006')
assert cond_styles['font']['bold'] == False
assert cond_styles['font']['italic'] == False
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'][0] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_fonts(self):
self.worksheet.cell('A1').style.font.size = 12
self.worksheet.cell('A1').style.font.bold = True
w = StyleWriter(self.workbook)
w._write_fonts()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">
<font>
<sz val="11" />
<color theme="1" />
<name val="Calibri" />
<family val="2" />
<scheme val="minor" />
</font>
<font>
<sz val="12" />
<color rgb="FF000000" />
<name val="Calibri" />
<family val="2" />
<b />
</font>
</fonts>
</styleSheet>
""")
assert diff is None, diff
def test_simple_styles(datadir):
wb = Workbook(guess_types=True)
ws = wb.active
now = datetime.datetime.now()
for idx, v in enumerate(
['12.34%', now, 'This is a test', '31.31415', None], 1):
ws.append([v])
_ = ws.cell(column=1, row=idx).style_id
# set explicit formats
ws['D9'].number_format = numbers.FORMAT_NUMBER_00
ws['D9'].protection = Protection(locked=True)
ws['D9'].style_id
ws['E1'].protection = Protection(hidden=True)
ws['E1'].style_id
assert len(wb._cell_styles) == 5
writer = StyleWriter(wb)
datadir.chdir()
with open('simple-styles.xml') as reference_file:
expected = reference_file.read()
xml = writer.write_table()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_borders(self):
st = Style(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW))))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_borders()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<borders count="2">
<border>
<left />
<right />
<top />
<bottom />
<diagonal />
</border>
<border>
<left />
<right />
<top style="thin">
<color rgb="0000FF00" />
</top>
<bottom />
<diagonal />
</border>
</borders>
</styleSheet>
""")
assert diff is None, diff
def test_borders(self):
self.worksheet.cell('A1').style.borders.top.border_style = Border.BORDER_THIN
self.worksheet.cell('A1').style.borders.top.color.index = Color.DARKYELLOW
w = StyleWriter(self.workbook)
w._write_borders()
eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><borders count="2"><border><left /><right /><top /><bottom /><diagonal /></border><border><left style="none"><color rgb="FF000000" /></left><right style="none"><color rgb="FF000000" /></right><top style="thin"><color rgb="FF808000" /></top><bottom style="none"><color rgb="FF000000" /></bottom><diagonal style="none"><color rgb="FF000000" /></diagonal></border></borders></styleSheet>')
def test_fonts(self):
self.worksheet.cell('A1').style.font.size = 12
self.worksheet.cell('A1').style.font.bold = True
w = StyleWriter(self.workbook)
w._write_fonts()
eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="2"><font><sz val="11" /><color theme="1" /><name val="Calibri" /><family val="2" /><scheme val="minor" /></font><font><sz val="12" /><color rgb="FF000000" /><name val="Calibri" /><family val="2" /><b /></font></fonts></styleSheet>')
def test_xfs_number_format(self):
for o in range(1, 4):
for i in range(1, 4):
# Two of these are custom, 0.0% and 0.000%. 0.00% is a built in format ID
self.worksheet.cell(row=o, column=i).number_format = '0.' + '0' * i + '%'
w = StyleWriter(self.workbook)
fonts = borders = fills = DummyElement()
nft = SubElement(w._root, 'numFmts')
w._write_cell_xfs(nft, fonts, fills, borders)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="2">
<numFmt formatCode="0.0%" numFmtId="165"/>
<numFmt formatCode="0.000%" numFmtId="166"/>
</numFmts>
<cellXfs count="4">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyNumberFormat="1" borderId="0" fillId="0" fontId="0" numFmtId="165" xfId="0"/>
<xf applyNumberFormat="1" borderId="0" fillId="0" fontId="0" numFmtId="10" xfId="0"/>
<xf applyNumberFormat="1" borderId="0" fillId="0" fontId="0" numFmtId="166" xfId="0"/>
</cellXfs>
</styleSheet>
"""
xml = tostring(w._root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_fills(self):
self.worksheet.cell('A1').style.fill.fill_type = 'solid'
self.worksheet.cell('A1').style.fill.start_color.index = Color.DARKYELLOW
w = StyleWriter(self.workbook)
w._write_fills()
eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fills count="3"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill><fill><patternFill patternType="solid"><fgColor rgb="FF808000" /></patternFill></fill></fills></styleSheet>')
def test_fills(self):
self.worksheet.cell('A1').style.fill.fill_type = 'solid'
self.worksheet.cell('A1').style.fill.start_color.index = Color.DARKYELLOW
w = StyleWriter(self.workbook)
w._write_fills()
eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fills count="3"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill><fill><patternFill patternType="solid"><fgColor rgb="FF808000" /></patternFill></fill></fills></styleSheet>')
def test_borders(self):
st = Style(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW))))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_borders()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<borders count="2">
<border>
<left />
<right />
<top />
<bottom />
<diagonal />
</border>
<border>
<left />
<right />
<top style="thin">
<color rgb="0000FF00" />
</top>
<bottom />
<diagonal />
</border>
</borders>
</styleSheet>
""")
assert diff is None, diff
def test_fills(self):
st = Style(fill=PatternFill(fill_type='solid',
start_color=Color(colors.DARKYELLOW)))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_fills()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fills count="3">
<fill>
<patternFill patternType="none" />
</fill>
<fill>
<patternFill patternType="gray125" />
</fill>
<fill>
<patternFill patternType="solid">
<fgColor rgb="0000FF00" />
</patternFill>
</fill>
</fills>
</styleSheet>
""")
assert diff is None, diff
def test_borders(self):
self.worksheet.cell('A1').style.borders.top.border_style = Border.BORDER_THIN
self.worksheet.cell('A1').style.borders.top.color.index = Color.DARKYELLOW
w = StyleWriter(self.workbook)
w._write_borders()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<borders count="2">
<border>
<left />
<right />
<top />
<bottom />
<diagonal />
</border>
<border>
<left />
<right />
<top style="thin">
<color rgb="FF808000" />
</top>
<bottom />
<diagonal />
</border>
</borders>
</styleSheet>
""")
assert diff is None, diff
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader',
'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font']['color'] == Color('FF9C0006')
assert cond_styles['font']['bold'] == False
assert cond_styles['font']['italic'] == False
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'][0] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(
wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_xfs_fills(self):
st = Style(fill=PatternFill(
fill_type='solid',
start_color=Color(colors.DARKYELLOW))
)
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fonts = DummyElement()
fills = Element("fills")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = tostring(w._root)
expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf applyFill="1" borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
expected = """<fills count="3">
<fill>
<patternFill patternType="solid">
<fgColor rgb="00808000"></fgColor>
</patternFill>
</fill>
</fills>
"""
xml = tostring(fills)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_fonts(self):
self.worksheet.cell('A1').style.font.size = 12
self.worksheet.cell('A1').style.font.bold = True
w = StyleWriter(self.workbook)
w._write_fonts()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">
<font>
<sz val="11" />
<color theme="1" />
<name val="Calibri" />
<family val="2" />
<scheme val="minor" />
</font>
<font>
<sz val="12" />
<color rgb="FF000000" />
<name val="Calibri" />
<family val="2" />
<b />
</font>
</fonts>
</styleSheet>
""")
assert diff is None, diff
def test_borders(self):
self.worksheet.cell('A1').style.borders.top.border_style = Border.BORDER_THIN
self.worksheet.cell('A1').style.borders.top.color.index = Color.DARKYELLOW
w = StyleWriter(self.workbook)
w._write_borders()
eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><borders count="2"><border><left /><right /><top /><bottom /><diagonal /></border><border><left /><right /><top style="thin"><color rgb="FF808000" /></top><bottom /><diagonal /></border></borders></styleSheet>')
def test_xfs_fonts(self):
st = Style(font=Font(size=12, bold=True))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fills = DummyElement()
fonts = Element("fonts")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = get_xml(w._root)
assert """applyFont="1" """ in xml
assert """fontId="1" """ in xml
expected = """
<fonts count="2">
<font>
<sz val="12.0" />
<color rgb="00000000"></color>
<name val="Calibri" />
<family val="2" />
<b></b>
</font>
</fonts>
"""
xml = get_xml(fonts)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_fonts_with_underline(self):
st = Style(
font=Font(size=12, bold=True, underline=Font.UNDERLINE_SINGLE))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_fonts()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">
<font>
<sz val="11" />
<color theme="1" />
<name val="Calibri" />
<family val="2" />
<scheme val="minor" />
</font>
<font>
<sz val="12.0" />
<color rgb="00000000" />
<name val="Calibri" />
<family val="2" />
<b />
<u />
</font>
</fonts>
</styleSheet>
""")
assert diff is None, diff
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_xfs_fonts(self):
st = Style(font=Font(size=12, bold=True))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = borders = fills = DummyElement()
fonts = Element("fonts")
w._write_cell_xfs(nft, fonts, fills, borders)
xml = unicode(tostring(w._root))
assert """applyFont="1" """ in xml
assert """fontId="1" """ in xml
expected = """
<fonts count="2">
<font>
<sz val="12.0" />
<color rgb="00000000"></color>
<name val="Calibri" />
<family val="2" />
<b></b>
</font>
</fonts>
"""
xml = tostring(fonts)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_fills(self):
st = Style(fill=PatternFill(fill_type='solid',
start_color=Color(colors.DARKYELLOW)))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_fills()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fills count="3">
<fill>
<patternFill patternType="none" />
</fill>
<fill>
<patternFill patternType="gray125" />
</fill>
<fill>
<patternFill patternType="solid">
<fgColor rgb="0000FF00" />
</patternFill>
</fill>
</fills>
</styleSheet>
""")
assert diff is None, diff
def test_parse_dxfs(datadir):
datadir.chdir()
reference_file = 'conditional-formatting.xlsx'
wb = load_workbook(reference_file)
assert isinstance(wb, Workbook)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = 'dxf_style.xml'
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font'].color == Color('FF9C0006')
assert not cond_styles['font'].bold
assert not cond_styles['font'].italic
f = PatternFill(end_color=Color('FFFFC7CE'))
assert cond_styles['fill'] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
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_fonts_with_underline(self):
st = Style(font=Font(size=12, bold=True,
underline=Font.UNDERLINE_SINGLE))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
w._write_fonts()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">
<font>
<sz val="11" />
<color theme="1" />
<name val="Calibri" />
<family val="2" />
<scheme val="minor" />
</font>
<font>
<sz val="12.0" />
<color rgb="00000000" />
<name val="Calibri" />
<family val="2" />
<b />
<u />
</font>
</fonts>
</styleSheet>
""")
assert diff is None, diff
def test_fonts(self):
self.worksheet.cell('A1').style.font.size = 12
self.worksheet.cell('A1').style.font.bold = True
w = StyleWriter(self.workbook)
w._write_fonts()
eq_(get_xml(w._root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="2"><font><sz val="11" /><color theme="1" /><name val="Calibri" /><family val="2" /><scheme val="minor" /></font><font><sz val="12" /><color rgb="FF000000" /><name val="Calibri" /><family val="2" /><b /></font></fonts></styleSheet>')
def test_nb_style(self):
for i in range(1, 6):
self.worksheet.cell(row=1, column=i).style.font.size += i
w = StyleWriter(self.workbook)
assert len(w.style_table) == 5
self.worksheet.cell('A10').style.borders.top = Border.BORDER_THIN
w = StyleWriter(self.workbook)
assert len(w.style_table) == 6
def test_fills(self):
self.worksheet.cell('A1').style.fill.fill_type = 'solid'
self.worksheet.cell('A1').style.fill.start_color.index = Color.DARKYELLOW
w = StyleWriter(self.workbook)
w._write_fills()
expected = """<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fills count="3"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill><fill><patternFill patternType="solid"><fgColor rgb="FF808000" /></patternFill></fill></fills></styleSheet>"""
xml = get_xml(w._root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_alignment(self):
self.worksheet.cell('A1').style.alignment.horizontal = 'center'
self.worksheet.cell('A1').style.alignment.vertical = 'center'
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'applyAlignment="1"' in xml
assert 'horizontal="center"' in xml
assert 'vertical="center"' in xml
def test_protection(self):
self.worksheet.cell('A1').style.protection.locked = Protection.PROTECTION_UNPROTECTED
self.worksheet.cell('A1').style.protection.hidden = Protection.PROTECTION_UNPROTECTED
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'protection' in xml
assert 'locked="0"' in xml
assert 'hidden="0"' in xml
def test_alignment(self):
self.worksheet.cell('A1').style.alignment.horizontal = 'center'
self.worksheet.cell('A1').style.alignment.vertical = 'center'
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
ok_('applyAlignment="1"' in xml)
ok_('horizontal="center"' in xml)
ok_('vertical="center"' in xml)
def test_alignment(self):
st = Style(alignment=Alignment(horizontal='center', vertical='center'))
self.worksheet.cell('A1').style = st
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'applyAlignment="1"' in xml
assert 'horizontal="center"' in xml
assert 'vertical="center"' in xml
def test_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "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
temp_buffer = StringIO()
doc = XMLGenerator(out=temp_buffer, encoding='utf-8')
write_worksheet_conditional_formatting(doc, worksheet)
doc.endDocument()
xml = temp_buffer.getvalue()
temp_buffer.close()
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 = 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" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def test_alignment_rotation(self):
self.worksheet.cell('A1').style.alignment.vertical = 'center'
self.worksheet.cell('A1').style.alignment.text_rotation = 90
self.worksheet.cell('A2').style.alignment.vertical = 'center'
self.worksheet.cell('A2').style.alignment.text_rotation = 135
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
ok_('textRotation="90"' in xml)
ok_('textRotation="135"' in xml)
def test_protection(self):
self.worksheet.cell('A1').style = Style(
protection=Protection(locked=Protection.PROTECTION_UNPROTECTED,
hidden=Protection.PROTECTION_UNPROTECTED))
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'protection' in xml
assert 'locked="0"' in xml
assert 'hidden="0"' in xml
def test_alignment_rotation(self):
self.worksheet.cell('A1').style = Style(alignment=Alignment(vertical='center', text_rotation=90))
self.worksheet.cell('A2').style = Style(alignment=Alignment(vertical='center', text_rotation=135))
self.worksheet.cell('A3').style = Style(alignment=Alignment(text_rotation=-34))
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'textRotation="90"' in xml
assert 'textRotation="135"' in xml
assert 'textRotation="124"' in xml
def test_alignment_rotation(self):
self.worksheet.cell('A1').style.alignment.vertical = 'center'
self.worksheet.cell('A1').style.alignment.text_rotation = 90
self.worksheet.cell('A2').style.alignment.vertical = 'center'
self.worksheet.cell('A2').style.alignment.text_rotation = 135
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
ok_('textRotation="90"' in xml)
ok_('textRotation="135"' in xml)
def test_nb_style(self):
for i in range(1, 6):
self.worksheet.cell(row=1,
column=i).style = Style(font=Font(size=i))
w = StyleWriter(self.workbook)
assert len(w.styles) == 6 # 5 + the default
self.worksheet.cell('A10').style = Style(border=Border(top=Side(
border_style=borders.BORDER_THIN)))
w = StyleWriter(self.workbook)
assert len(w.styles) == 7
def test_write_cell_xfs_1(self):
self.worksheet.cell('A1').style.font.size = 12
w = StyleWriter(self.workbook)
ft = w._write_fonts()
nft = w._write_number_formats()
w._write_cell_xfs(nft, ft, {}, {})
xml = get_xml(w._root)
assert 'applyFont="1"' in xml
assert 'applyFill="1"' not in xml
assert 'applyBorder="1"' not in xml
assert 'applyAlignment="1"' not in xml
def test_alignment_default(self):
w = StyleWriter(self.workbook)
al = Alignment()
w._write_alignment(w._root, al)
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<alignment/>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_alignment_default(self):
w = StyleWriter(self.workbook)
al = Alignment()
w._write_alignment(w._root, al)
xml = get_xml(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<alignment/>
</styleSheet>
"""
diff = compare_xml(xml, expected)
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.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_complex_styles(datadir):
"""Hold on to your hats"""
from openpyxl import load_workbook
datadir.join("..", "..", "..", "reader", "tests", "data").chdir()
wb = load_workbook("complex-styles.xlsx")
datadir.chdir()
with open("complex-styles.xml") as reference:
writer = StyleWriter(wb)
xml = writer.write_table()
expected = reference.read()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_complex_styles(datadir):
"""Hold on to your hats"""
from openpyxl import load_workbook
datadir.join("..", "..", "..", "reader", "tests", "data").chdir()
wb = load_workbook("complex-styles.xlsx")
datadir.chdir()
with open("complex-styles.xml") as reference:
writer = StyleWriter(wb)
xml = writer.write_table()
expected = reference.read()
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_style_names(self):
writer = StyleWriter(self.workbook)
writer._write_style_names()
xml = tostring(writer._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0" hidden="0"/>
</cellStyles>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_named_styles(self):
writer = StyleWriter(self.workbook)
writer._write_named_styles()
xml = tostring(writer._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellStyleXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0"></xf>
</cellStyleXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_nb_style(self):
for i in range(1, 6):
cell = self.worksheet.cell(row=1, column=i)
cell.font = Font(size=i)
_ = cell.style_id
w = StyleWriter(self.workbook)
assert len(w.wb._cell_styles) == 6 # 5 + the default
cell = self.worksheet.cell('A10')
cell.border=Border(top=Side(border_style=borders.BORDER_THIN))
_ = cell.style_id
w = StyleWriter(self.workbook)
assert len(w.wb._cell_styles) == 7
def test_alignment_indent(self):
self.worksheet.cell('A1').style.alignment.indent = 1
self.worksheet.cell('A2').style.alignment.indent = 4
self.worksheet.cell('A3').style.alignment.indent = 0
self.worksheet.cell('A3').style.alignment.indent = -1
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
ok_('indent="1"' in xml)
ok_('indent="4"' in xml)
#Indents not greater than zero are ignored when writing
ok_('indent="0"' not in xml)
ok_('indent="-1"' not in xml)
def test_alignment_indent(self):
self.worksheet.cell('A1').style.alignment.indent = 1
self.worksheet.cell('A2').style.alignment.indent = 4
self.worksheet.cell('A3').style.alignment.indent = 0
self.worksheet.cell('A3').style.alignment.indent = -1
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'indent="1"' in xml
assert 'indent="4"' in xml
#Indents not greater than zero are ignored when writing
assert 'indent="0"' not in xml
assert 'indent="-1"' not in xml
def test_default_xfs(self):
w = StyleWriter(self.workbook)
fonts = nft = borders = fills = DummyElement()
w._write_cell_styles()
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_default_xfs(self):
w = StyleWriter(self.workbook)
fonts = nft = borders = fills = DummyElement()
w._write_cell_styles()
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_alignment_indent(self):
self.worksheet.cell('A1').style = Style(alignment=Alignment(indent=1))
self.worksheet.cell('A2').style = Style(alignment=Alignment(indent=4))
self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=0))
self.worksheet.cell('A3').style = Style(alignment=Alignment(indent=-1))
w = StyleWriter(self.workbook)
nft = w._write_number_formats()
w._write_cell_xfs(nft, {}, {}, {})
xml = get_xml(w._root)
assert 'indent="1"' in xml
assert 'indent="4"' in xml
#Indents not greater than zero are ignored when writing
assert 'indent="0"' not in xml
assert 'indent="-1"' not in xml
def test_write_number_formats():
wb = DummyWorkbook()
wb._number_formats = ['YYYY']
writer = StyleWriter(wb)
writer._write_number_formats()
xml = tostring(writer._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="1">
<numFmt formatCode="YYYY" numFmtId="164"></numFmt>
</numFmts>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff