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_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_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_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_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_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()
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="FF808000" />
</patternFill>
</fill>
</fills>
</styleSheet>
""")
assert diff is None, diff
def test_fonts_with_underline(self):
self.worksheet.cell('A1').style.font.size = 12
self.worksheet.cell('A1').style.font.bold = True
self.worksheet.cell('A1').style.font.underline = Font.UNDERLINE_SINGLE
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 />
<u />
</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):
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_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_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_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 = get_xml(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 = get_xml(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_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_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_write_style():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = '13%'
style_id_by_hash = StyleWriter(wb).get_style_by_hash()
content = write_worksheet(ws, {}, style_id_by_hash)
assert_equals_file_content(os.path.join(DATADIR, 'writer', 'expected', \
'sheet1_style.xml'), content)
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_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_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_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 setup_class(cls):
now = datetime.datetime.now()
cls.workbook = Workbook()
cls.worksheet = cls.workbook.create_sheet()
cls.worksheet.cell(coordinate='A1').value = '12.34%'
cls.worksheet.cell(coordinate='B4').value = now
cls.worksheet.cell(coordinate='B5').value = now
cls.worksheet.cell(coordinate='C14').value = 'This is a test'
cls.worksheet.cell(coordinate='D9').value = '31.31415'
cls.worksheet.cell(coordinate='D9').style.number_format.format_code = NumberFormat.FORMAT_NUMBER_00
cls.writer = StyleWriter(cls.workbook)
def test_write_style():
wb = Workbook(guess_types=True)
ws = wb.create_sheet()
ws.cell('F1').value = '13%'
ws._styles['F'] = ws._styles['F1']
style_id_by_hash = StyleWriter(wb).get_style_by_hash()
content = write_worksheet(ws, {}, style_id_by_hash)
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1_style.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
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_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_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_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_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_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_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_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
