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_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_open_too_many_files():
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
for i in range(
200
): # over 200 worksheets should raise an OSError ('too many open files')
wb.create_sheet()
wb.save(test_filename)
os.remove(test_filename)
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_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_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_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_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_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_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_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_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_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_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_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_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_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_create_sheet_readonly():
wb = Workbook()
wb._set_optimized_read()
wb.create_sheet()
def test_create_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
eq_(new_sheet, wb.worksheets[0])
def test_create_sheet_with_name():
wb = Workbook()
new_sheet = wb.create_sheet(0, title='LikeThisName')
eq_(new_sheet, wb.worksheets[0])
def test_add_correct_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.add_sheet(new_sheet)
eq_(new_sheet, wb.worksheets[2])
def dump_workbook():
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.append(range(30))
wb.save(filename=StringIO())
def test_remove_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.remove_sheet(new_sheet)
assert new_sheet not in wb.worksheets