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_root_rels():
wb = Workbook()
content = write_root_rels(wb)
reference_file = os.path.join(DATADIR, 'writer', 'expected', '.rels')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_string_table():
table = {'hello': 1, 'world': 2, 'nice': 3}
content = write_string_table(table)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'sharedStrings.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_properties_app(self):
wb = Workbook()
wb.create_sheet()
wb.create_sheet()
content = write_properties_app(wb)
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'app.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None
def test_decimal():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = decimal.Decimal('3.14')
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'decimal.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
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()
expected = """<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>"""
xml = get_xml(w._root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_worksheet():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F42').value = 'hello'
content = write_worksheet(ws, {'hello': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'sheet1.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_auto_filter():
wb = Workbook()
ws = wb.worksheets[0]
ws.cell('F42').value = 'hello'
ws.auto_filter = 'A1:F1'
content = write_worksheet(ws, {'hello': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'sheet1_auto_filter.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None
content = write_workbook(wb)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'workbook_auto_filter.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_short_number():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = 1234567890
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'short_number.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_content_types():
wb = Workbook()
wb.create_sheet()
wb.create_sheet()
content = write_content_types(wb)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'[Content_Types].xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
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()
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_write_hyperlink():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = "test"
ws.cell('A1').hyperlink = "http://test.com"
content = write_worksheet(ws, {'test': 0}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'sheet1_hyperlink.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
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()
expected = """<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>"""
xml = get_xml(w._root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_height():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = 10
ws.row_dimensions[ws.cell('F1').row].height = 30
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'sheet1_height.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_properties_core(self):
self.prop.creator = 'TEST_USER'
self.prop.last_modified_by = 'SOMEBODY'
self.prop.created = datetime(2010, 4, 1, 20, 30, 00)
self.prop.modified = datetime(2010, 4, 5, 14, 5, 30)
content = write_properties_core(self.prop)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'core.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None
def test_write_style():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = '13%'
ws.column_dimensions['F'].style_index = 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_write_hyperlink_rels():
wb = Workbook()
ws = wb.create_sheet()
eq_(0, len(ws.relationships))
ws.cell('A1').value = "test"
ws.cell('A1').hyperlink = "http://test.com/"
eq_(1, len(ws.relationships))
ws.cell('A2').value = "test"
ws.cell('A2').hyperlink = "http://test2.com/"
eq_(2, len(ws.relationships))
content = write_worksheet_rels(ws, 1, 1)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'sheet1_hyperlink.xml.rels')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_comments_vml():
ws = _create_ws()[0]
cw = CommentWriter(ws)
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'commentsDrawing1.vml')
content = cw.write_comments_vml()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
correct_ids = []
correct_coords = []
check_ids = []
check_coords = []
for i in correct.findall("{%s}shape" % vmlns):
correct_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
correct_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
for i in check.findall("{%s}shape" % vmlns):
check_ids.append(i.attrib["id"])
row = i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text
col = i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text
check_coords.append((row, col))
# blank the data we are checking separately
i.attrib["id"] = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Row" %
excelns).text = "0"
i.find("{%s}ClientData" % excelns).find("{%s}Column" %
excelns).text = "0"
assert set(correct_coords) == set(check_coords)
assert set(correct_ids) == set(check_ids)
diff = compare_xml(get_document_content(correct),
get_document_content(check))
assert diff is None, diff
def test_write_comments():
ws = _create_ws()[0]
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'comments1.xml')
cw = CommentWriter(ws)
content = cw.write_comments()
with open(reference_file) as expected:
correct = fromstring(expected.read())
check = fromstring(content)
# check top-level elements have the same name
for i, j in zip(correct.getchildren(), check.getchildren()):
assert i.tag == j.tag
correct_comments = correct.find('{%s}commentList' %
SHEET_MAIN_NS).getchildren()
check_comments = check.find('{%s}commentList' %
SHEET_MAIN_NS).getchildren()
correct_authors = correct.find('{%s}authors' %
SHEET_MAIN_NS).getchildren()
check_authors = check.find('{%s}authors' % SHEET_MAIN_NS).getchildren()
# replace author ids with author names
for i in correct_comments:
i.attrib["authorId"] = correct_authors[int(
i.attrib["authorId"])].text
for i in check_comments:
i.attrib["authorId"] = check_authors[int(
i.attrib["authorId"])].text
# sort the comment list
correct_comments.sort(key=lambda tag: tag.attrib["ref"])
check_comments.sort(key=lambda tag: tag.attrib["ref"])
correct.find('{%s}commentList' % SHEET_MAIN_NS)[:] = correct_comments
check.find('{%s}commentList' % SHEET_MAIN_NS)[:] = check_comments
# sort the author list
correct_authors.sort(key=lambda tag: tag.text)
check_authors.sort(key=lambda tag: tag.text)
correct.find('{%s}authors' % SHEET_MAIN_NS)[:] = correct_authors
check.find('{%s}authors' % SHEET_MAIN_NS)[:] = check_authors
diff = compare_xml(get_document_content(correct),
get_document_content(check))
assert diff is None, diff
def test_conditional_formatting_addCustomRule(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
dxfId = worksheet.conditional_formatting.addDxfStyle(self.workbook, None, None, None)
worksheet.conditional_formatting.addCustomRule('C1:C10', {'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1'})
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()
assert dxfId == 0
expected = '<conditionalFormatting sqref="C1:C10"><cfRule dxfId="0" type="expression" stopIfTrue="1" priority="1"><formula>ISBLANK(C1)</formula></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_formula():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('F1').value = 10
ws.cell('F2').value = 32
ws.cell('F3').value = '=F1+F2'
ws.cell('A4').value = '=A1+A2+A3'
ws.formula_attributes['A4'] = {'t': 'shared', 'ref': 'A4:C4', 'si': '0'}
ws.cell('B4').value = '='
ws.formula_attributes['B4'] = {'t': 'shared', 'si': '0'}
ws.cell('C4').value = '='
ws.formula_attributes['C4'] = {'t': 'shared', 'si': '0'}
content = write_worksheet(ws, {}, {})
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'sheet1_formula.xml')
with open(reference_file) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_conditional_formatting_setRules(self):
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
rules = {'A1:A4': [{'type': 'colorScale', 'priority': '13',
'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}], 'color':
[Color('FFFF7128'), Color('FFFFEF9C')]}}]}
worksheet.conditional_formatting.setRules(rules)
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()
expected = '<conditionalFormatting sqref="A1:A4"><cfRule type="colorScale" priority="1"><colorScale><cfvo type="min"></cfvo><cfvo type="max"></cfvo><color rgb="FFFF7128"></color><color rgb="FFFFEF9C"></color></colorScale></cfRule></conditionalFormatting>'
diff = compare_xml(xml, expected)
assert diff is None, diff
