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]))
