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_get_sheet_by_name():
wb = Workbook()
new_sheet = wb.create_sheet()
title = 'my sheet'
new_sheet.title = title
found_sheet = wb.get_sheet_by_name(title)
eq_(new_sheet, found_sheet)
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_get_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
wb.add_named_range(named_range)
found_named_range = wb.get_named_range('test_nr')
eq_(named_range, found_named_range)
def test_get_sheet_names():
wb = Workbook()
names = ['Sheet', 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5']
for count in range(5):
wb.create_sheet(0)
actual_names = wb.get_sheet_names()
eq_(sorted(actual_names), sorted(names))
def test_add_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
wb.add_named_range(named_range)
named_ranges_list = wb.get_named_ranges()
assert named_range in named_ranges_list
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_hyperlink_value():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').hyperlink = "http://test.com"
eq_("http://test.com", ws.cell('A1').value)
ws.cell('A1').value = "test"
eq_("test", ws.cell('A1').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_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 test_add_local_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
named_range.scope = new_sheet
wb.add_named_range(named_range)
dest_filename = os.path.join(TMPDIR, 'local_named_range_book.xlsx')
wb.save(dest_filename)
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_create_string_table():
wb = Workbook()
ws = wb.create_sheet()
ws.cell('B12').value = 'hello'
ws.cell('B13').value = 'world'
ws.cell('D28').value = 'hello'
table = create_string_table(wb)
eq_({'hello': 0, 'world': 1}, table)
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_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_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_hyperlink_image_rels(Workbook, Image):
wb = Workbook()
ws = wb.create_sheet()
ws.cell('A1').value = "test"
ws.cell('A1').hyperlink = "http://test.com/"
img = os.path.join(DATADIR, "plain.png")
i = Image(img)
ws.add_image(i)
raise ValueError("Resulting file is invalid")
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_equal_string():
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.append(['', '', None, '='])
wb.save(test_filename)
wb2 = load_workbook(test_filename, True)
last_cell = list(wb2.worksheets[0].iter_rows())[0][-1]
assert last_cell.data_type == 's'
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_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 _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_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_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_append_after_save():
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.append(['hello'])
wb.save(test_filename)
os.remove(test_filename)
ws.append(['hello'])
def test_read_cell_formulae():
from legacy_openpyxl.reader.worksheet import fast_parse
src_file = os.path.join(DATADIR, "reader", "worksheet_formula.xml")
wb = Workbook()
ws = wb.active
fast_parse(ws, open(src_file), {}, {}, None)
b1 = ws['B1']
assert b1.data_type == 'f'
assert b1.value == '=CONCATENATE(A1,A2)'
a6 = ws['A6']
assert a6.data_type == 'f'
assert a6.value == '=SUM(A4:A5)'
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_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_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