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