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_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_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_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_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_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_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_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_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 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_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_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_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_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_good_encoding():
try:
# Python 2
pound = unichr(163)
except NameError:
# Python 3
pound = chr(163)
test_string = ('Compound Value (' + pound + ')').encode('latin1')
lat_book = Workbook(encoding='latin1')
lat_sheet = lat_book.get_active_sheet()
lat_sheet.cell('A1').value = test_string
def test_dump_sheet():
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
letters = [get_column_letter(x + 1) for x in xrange(20)]
expected_rows = []
for row in xrange(20):
expected_rows.append(
['%s%d' % (letter, row + 1) for letter in letters])
for row in xrange(20):
expected_rows.append([(row + 1) for letter in letters])
for row in xrange(10):
expected_rows.append([
datetime(2010, ((x % 12) + 1), row + 1)
for x in range(len(letters))
])
for row in xrange(20):
expected_rows.append(
['=%s%d' % (letter, row + 1) for letter in letters])
for row in expected_rows:
ws.append(row)
wb.save(test_filename)
wb2 = load_workbook(test_filename)
ws = wb2.worksheets[0]
for ex_row, ws_row in zip(expected_rows[:-20], ws.rows):
for ex_cell, ws_cell in zip(ex_row, ws_row):
eq_(ex_cell, ws_cell.value)
os.remove(test_filename)
def test_write_regular_float():
float_value = 1.0 / 3.0
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = float_value
dest_filename = os.path.join(TMPDIR, 'float_read_write_issue.xlsx')
book.save(dest_filename)
validate_archive(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
eq_(test_sheet.cell("A1").value, float_value)
def test_print_titles():
wb = Workbook()
ws1 = wb.create_sheet()
ws2 = wb.create_sheet()
ws1.add_print_title(2)
ws2.add_print_title(3, rows_or_cols='cols')
def mystr(nr):
return ','.join(['%s!%s' % (sheet.title, name) for sheet, name in nr.destinations])
actual_named_ranges = set([(nr.name, nr.scope, mystr(nr)) for nr in wb.get_named_ranges()])
expected_named_ranges = set([('_xlnm.Print_Titles', ws1, 'Sheet1!$1:$2'),
('_xlnm.Print_Titles', ws2, 'Sheet2!$A:$C')])
assert(actual_named_ranges == expected_named_ranges)
def test_dump_sheet_title():
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
ws = wb.create_sheet(title='Test1')
wb.save(test_filename)
wb2 = load_workbook(test_filename)
ws = wb2.get_sheet_by_name('Test1')
eq_('Test1', ws.title)
def test_remove_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
wb.add_named_range(named_range)
wb.remove_named_range(named_range)
named_ranges_list = wb.get_named_ranges()
assert named_range not in named_ranges_list
def test_write_regular_date():
today = datetime.datetime(2010, 1, 18, 14, 15, 20, 1600)
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = today
dest_filename = os.path.join(TMPDIR, 'date_read_write_issue.xlsx')
book.save(dest_filename)
validate_archive(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
eq_(test_sheet.cell("A1").value, today)
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_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_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_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_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_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_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