def test_append_list(self): ws = Worksheet(self.wb) ws.append(['This is A1', 'This is B1']) eq_('This is A1', ws.cell('A1').value) eq_('This is B1', ws.cell('B1').value)
def test_read_comments(): xml = """<?xml version="1.0" standalone="yes"?> <comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><authors> <author>Cuke</author><author>Not Cuke</author></authors><commentList><comment ref="A1" authorId="0" shapeId="0"><text><r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t>Cuke:\n</t></r><r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr> <t xml:space="preserve">First Comment</t></r></text></comment><comment ref="D1" authorId="0" shapeId="0"> <text><r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/> </rPr><t>Cuke:\n</t></r><r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/> <charset val="1"/></rPr><t xml:space="preserve">Second Comment</t></r></text></comment> <comment ref="A2" authorId="1" shapeId="0"><text><r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t>Not Cuke:\n</t></r><r><rPr> <sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr> <t xml:space="preserve">Third Comment</t></r></text></comment></commentList></comments>""" wb = Workbook() ws = Worksheet(wb) comments.read_comments(ws, xml) comments_expected = [['A1', 'Cuke', 'Cuke:\nFirst Comment'], ['D1', 'Cuke', 'Cuke:\nSecond Comment'], ['A2', 'Not Cuke', 'Not Cuke:\nThird Comment'] ] for cell, author, text in comments_expected: assert ws.cell(coordinate=cell).comment.author == author assert ws.cell(coordinate=cell).comment.text == text assert ws.cell(coordinate=cell).comment._parent == ws.cell(coordinate=cell)
def test_range_offset(self): ws = Worksheet(self.wb) xlrange = ws.range('A1:C4', 1, 3) assert isinstance(xlrange, tuple) eq_(4, len(xlrange)) eq_(3, len(xlrange[0])) eq_('D2', xlrange[0][0].get_coordinate())
def test_append_dict_index(self): ws = Worksheet(self.wb) ws.append({0: 'This is A1', 2: 'This is C1'}) eq_('This is A1', ws.cell('A1').value) eq_('This is C1', ws.cell('C1').value)
def test_header_footer(self): ws = Worksheet(self.wb) ws.header_footer.left_header.text = "Left Header Text" ws.header_footer.center_header.text = "Center Header Text" ws.header_footer.center_header.font_name = "Arial,Regular" ws.header_footer.center_header.font_size = 6 ws.header_footer.center_header.font_color = "445566" ws.header_footer.right_header.text = "Right Header Text" ws.header_footer.right_header.font_name = "Arial,Bold" ws.header_footer.right_header.font_size = 8 ws.header_footer.right_header.font_color = "112233" ws.header_footer.left_footer.text = "Left Footer Text\nAnd &[Date] and &[Time]" ws.header_footer.left_footer.font_name = "Times New Roman,Regular" ws.header_footer.left_footer.font_size = 10 ws.header_footer.left_footer.font_color = "445566" ws.header_footer.center_footer.text = "Center Footer Text &[Path]&[File] on &[Tab]" ws.header_footer.center_footer.font_name = "Times New Roman,Bold" ws.header_footer.center_footer.font_size = 12 ws.header_footer.center_footer.font_color = "778899" ws.header_footer.right_footer.text = "Right Footer Text &[Page] of &[Pages]" ws.header_footer.right_footer.font_name = "Times New Roman,Italic" ws.header_footer.right_footer.font_size = 14 ws.header_footer.right_footer.font_color = "AABBCC" xml_string = write_worksheet(ws, None, None) assert '<headerFooter>' in xml_string assert '<oddHeader>&L&"Calibri,Regular"&K000000Left Header Text&C&"Arial,Regular"&6&K445566Center Header Text&R&"Arial,Bold"&8&K112233Right Header Text</oddHeader>' in xml_string assert '<oddFooter>&L&"Times New Roman,Regular"&10&K445566Left Footer Text_x000D_And &D and &T&C&"Times New Roman,Bold"&12&K778899Center Footer Text &Z&F on &A&R&"Times New Roman,Italic"&14&KAABBCCRight Footer Text &P of &N</oddFooter></headerFooter>' in xml_string assert '</headerFooter>' in xml_string ws = Worksheet(self.wb) xml_string = write_worksheet(ws, None, None) assert "<headerFooter>" not in xml_string assert "<oddHeader>" not in xml_string assert "<oddFooter>" not in xml_string
def test_append_dict_letter(self): ws = Worksheet(self.wb) ws.append({'A': 'This is A1', 'C': 'This is C1'}) eq_('This is A1', ws.cell('A1').value) eq_('This is C1', ws.cell('C1').value)
def test_init(): wb = Workbook() ws = Worksheet(wb) c = Comment("text", "author") ws.cell(coordinate="A1").comment = c assert c._parent == ws.cell(coordinate="A1") assert c.text == "text" assert c.author == "author"
def test_cell_range_name(self): ws = Worksheet(self.wb) self.wb.create_named_range('test_range_single', ws, 'B12') assert_raises(CellCoordinatesException, ws.cell, 'test_range_single') c_range_name = ws.range('test_range_single') c_range_coord = ws.range('B12') c_cell = ws.cell('B12') eq_(c_range_coord, c_range_name) eq_(c_range_coord, c_cell)
def _create_ws(): wb = Workbook() ws = Worksheet(wb) comment1 = Comment("text", "author") comment2 = Comment("text2", "author2") comment3 = Comment("text3", "author3") ws.cell(coordinate="B2").comment = comment1 ws.cell(coordinate="C7").comment = comment2 ws.cell(coordinate="D9").comment = comment3 return ws, comment1, comment2, comment3
def test_cols(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' cols = ws.columns eq_(len(cols), 3) eq_(cols[0][0].value, 'first') eq_(cols[-1][-1].value, 'last')
def test_rows(self): ws = Worksheet(self.wb) ws.cell('A1').value = 'first' ws.cell('C9').value = 'last' rows = ws.rows eq_(len(rows), 9) eq_(rows[0][0].value, 'first') eq_(rows[-1][-1].value, 'last')
def test_comment_count(): wb = Workbook() ws = Worksheet(wb) cell = ws.cell(coordinate="A1") assert ws._comment_count == 0 cell.comment = Comment("text", "author") assert ws._comment_count == 1 cell.comment = Comment("text", "author") assert ws._comment_count == 1 cell.comment = None assert ws._comment_count == 0 cell.comment = None assert ws._comment_count == 0
def test_garbage_collect(self): ws = Worksheet(self.wb) ws.cell('A1').value = '' ws.cell('B2').value = '0' ws.cell('C4').value = 0 ws.cell('D1').comment = Comment('Comment', 'Comment') ws.garbage_collect() eq_(set(ws.get_cell_collection()), set([ws.cell('B2'), ws.cell('C4'), ws.cell('D1')]))
def test_append_2d_list(self): ws = Worksheet(self.wb) ws.append(['This is A1', 'This is B1']) ws.append(['This is A2', 'This is B2']) vals = ws.range('A1:B2') expected = ( ('This is A1', 'This is B1'), ('This is A2', 'This is B2'), ) for e, v in zip(expected, flatten(vals)): assert e == tuple(v)
def test_page_margins(self): ws = Worksheet(self.wb) ws.page_margins.left = 2.0 ws.page_margins.right = 2.0 ws.page_margins.top = 2.0 ws.page_margins.bottom = 2.0 ws.page_margins.header = 1.5 ws.page_margins.footer = 1.5 xml_string = write_worksheet(ws, None, None) assert '<pageMargins left="2.00" right="2.00" top="2.00" bottom="2.00" header="1.50" footer="1.50"></pageMargins>' in xml_string ws = Worksheet(self.wb) xml_string = write_worksheet(ws, None, None) assert '<pageMargins' not in xml_string
def test_set_get_date(): today = datetime(2010, 1, 18, 14, 15, 20, 1600) wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) cell.value = today eq_(today, cell.value)
def test_date_format_on_non_date(): wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) cell.value = datetime.now() cell.value = 'testme' eq_('testme', cell.value)
def test_timedelta(): wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) cell.value = timedelta(days=1, hours=3) eq_(cell.value, 1.125) eq_(cell.TYPE_NUMERIC, cell.data_type)
def __init__(self, parent_workbook, title, sheet_codename, xml_source, string_table, style_table): Worksheet.__init__(self, parent_workbook, title) self._sheet_codename = sheet_codename self._string_table = string_table self._style_table = style_table min_col, min_row, max_col, max_row = read_dimension( xml_source=self.xml_source) self.min_col = min_col self.min_row = min_row self.max_row = max_row self.max_col = max_col self._shared_date = SharedDate( base_date=parent_workbook.excel_base_date)
def test_is_date(): wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) cell.value = datetime.now() eq_(cell.is_date(), True) cell.value = 'testme' eq_('testme', cell.value) assert cell.is_date() is False
def test_printer_settings(self): ws = Worksheet(self.wb) ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID ws.page_setup.fitToPage = True ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 ws.page_setup.horizontalCentered = True ws.page_setup.verticalCentered = True xml_string = write_worksheet(ws, None, None) assert '<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"></pageSetup>' in xml_string assert '<pageSetUpPr fitToPage="1"></pageSetUpPr>' in xml_string assert '<printOptions horizontalCentered="1" verticalCentered="1">' in xml_string ws = Worksheet(self.wb) xml_string = write_worksheet(ws, None, None) assert "<pageSetup" not in xml_string assert "<pageSetUpPr" not in xml_string assert "<printOptions" not in xml_string
def test_is_not_date_color_format(): wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) cell.value = -13.5 cell.style.number_format.format_code = '0.00_);[Red]\(0.00\)' assert cell.is_date() is False
def test_comment_assignment(): wb = Workbook() ws = Worksheet(wb) c = Comment("text", "author") ws.cell(coordinate="A1").comment = c with pytest.raises(AttributeError): ws.cell(coordinate="A2").commment = c ws.cell(coordinate="A2").comment = Comment("text2", "author2") with pytest.raises(AttributeError): ws.cell(coordinate="A1").comment = ws.cell(coordinate="A2").comment # this should orphan c, so that assigning it to A2 does not raise AttributeError ws.cell(coordinate="A1").comment = None ws.cell(coordinate="A2").comment = c
def test_freeze(self): ws = Worksheet(self.wb) ws.freeze_panes = ws.cell('b2') assert ws.freeze_panes == 'B2' ws.freeze_panes = '' assert ws.freeze_panes is None ws.freeze_panes = 'c5' assert ws.freeze_panes == 'C5' ws.freeze_panes = ws.cell('A1') assert ws.freeze_panes is None
def test_time(): def check_time(raw_value, coerced_value): cell.value = raw_value eq_(cell.value, coerced_value) eq_(cell.TYPE_NUMERIC, cell.data_type) wb = Workbook() ws = Worksheet(wb) cell = Cell(ws, 'A', 1) values = ( ('03:40:16', time(3, 40, 16)), ('03:40', time(3, 40)), ) for raw_value, coerced_value in values: yield check_time, raw_value, coerced_value
def test_auto_filter(self): ws = Worksheet(self.wb) ws.auto_filter = ws.range('a1:f1') assert ws.auto_filter == 'A1:F1' ws.auto_filter = '' assert ws.auto_filter is None ws.auto_filter = 'c1:g9' assert ws.auto_filter == 'C1:G9'
def test_merge(self): ws = Worksheet(self.wb) string_table = {'': '', 'Cell A1': 'Cell A1', 'Cell B1': 'Cell B1'} ws.cell('A1').value = 'Cell A1' ws.cell('B1').value = 'Cell B1' xml_string = write_worksheet(ws, string_table, None) assert '<v>Cell B1</v>' in xml_string ws.merge_cells('A1:B1') xml_string = write_worksheet(ws, string_table, None) assert '<v>Cell B1</v>' not in xml_string assert '<mergeCells count="1"><mergeCell ref="A1:B1"></mergeCell></mergeCells>' in xml_string ws.unmerge_cells('A1:B1') xml_string = write_worksheet(ws, string_table, None) assert '<mergeCell ref="A1:B1"/>' not in xml_string
def test_hyperlink_relationships(self): ws = Worksheet(self.wb) eq_(len(ws.relationships), 0) ws.cell('A1').hyperlink = "http://test.com" eq_(len(ws.relationships), 1) eq_("rId1", ws.cell('A1').hyperlink_rel_id) eq_("rId1", ws.relationships[0].id) eq_("http://test.com", ws.relationships[0].target) eq_("External", ws.relationships[0].target_mode) ws.cell('A2').hyperlink = "http://test2.com" eq_(len(ws.relationships), 2) eq_("rId2", ws.cell('A2').hyperlink_rel_id) eq_("rId2", ws.relationships[1].id) eq_("http://test2.com", ws.relationships[1].target) eq_("External", ws.relationships[1].target_mode)
def test_worksheet_named_range(self): ws = Worksheet(self.wb) self.wb.create_named_range('test_range', ws, 'C5') xlrange = ws.range('test_range') assert isinstance(xlrange, Cell) eq_(5, xlrange.row)
def test_worksheet_range(self): ws = Worksheet(self.wb) xlrange = ws.range('A1:C4') assert isinstance(xlrange, tuple) eq_(4, len(xlrange)) eq_(3, len(xlrange[0]))