def setup_class(cls):
mac_wb_path = os.path.join(DATADIR, 'reader', 'date_1904.xlsx')
cls.mac_wb = load_workbook(mac_wb_path)
cls.mac_ws = cls.mac_wb.get_sheet_by_name('Sheet1')
win_wb_path = os.path.join(DATADIR, 'reader', 'date_1900.xlsx')
cls.win_wb = load_workbook(win_wb_path)
cls.win_ws = cls.win_wb.get_sheet_by_name('Sheet1')
def test_rewrite_styles(self):
"""Test to verify Bugfix # 46"""
self.worksheet['A1'].value = 'Value'
self.worksheet['B2'].value = '14%'
saved_wb = save_virtual_workbook(self.workbook)
second_wb = load_workbook(BytesIO(saved_wb))
assert isinstance(second_wb, Workbook)
ws = second_wb.get_sheet_by_name('Sheet1')
assert ws.cell('A1').value == 'Value'
ws['A2'].value = 'Bar!'
saved_wb = save_virtual_workbook(second_wb)
third_wb = load_workbook(BytesIO(saved_wb))
assert third_wb
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader', 'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font']['color'] == Color('FF9C0006')
assert cond_styles['font']['bold'] == False
assert cond_styles['font']['italic'] == False
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'][0] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_can_be_saved(self):
FNAME = os.path.join(TMPDIR, "foo.xlsx")
self.wb.save(FNAME)
wbcopy = load_workbook(FNAME)
eq_(['MyRef: Workbook=[range]', 'MySheetRef: Sheet1=[range]', 'MySheetRef: Sheet2=[range]', 'MySheetValue: Sheet1=3.33', 'MySheetValue: Sheet2=14.4', 'MyValue: Workbook=9.99'],
[self.range_as_string(range, include_value=True) for range in wbcopy.get_named_ranges()])
def test_comments_cell_association():
path = os.path.join(DATADIR, 'reader', 'comments.xlsx')
wb = load_workbook(path)
assert wb.worksheets[0].cell(coordinate="A1").comment.author == "Cuke"
assert wb.worksheets[0].cell(coordinate="A1").comment.text == "Cuke:\nFirst Comment"
assert wb.worksheets[1].cell(coordinate="A1").comment is None
assert wb.worksheets[0].cell(coordinate="D1").comment.text == "Cuke:\nSecond Comment"
def test_guess_types():
filename = os.path.join(DATADIR, 'genuine', 'guess_types.xlsx')
for guess, dtype in ((True, float), (False, unicode)):
wb = load_workbook(filename, guess_types=guess)
ws = wb.get_active_sheet()
assert isinstance(
ws.cell('D2').value,
dtype), 'wrong dtype (%s) when guess type is: %s (%s instead)' % (
dtype, guess, type(ws.cell('A1').value))
def test_read_worksheet():
path = os.path.join(DATADIR, 'genuine', 'empty.xlsx')
wb = load_workbook(path)
sheet2 = wb.get_sheet_by_name('Sheet2 - Numbers')
assert isinstance(sheet2, Worksheet)
assert 'This is cell G5' == sheet2['G5'].value
assert 18 == sheet2['D18'].value
assert sheet2['G9'].value is True
assert sheet2['G10'].value is False
def test_read_complex_formulae():
null_file = os.path.join(DATADIR, 'reader', 'formulae.xlsx')
wb = load_workbook(null_file)
ws = wb.get_active_sheet()
# Test normal forumlae
assert ws.cell('A1').data_type != 'f'
assert ws.cell('A2').data_type != 'f'
assert ws.cell('A3').data_type == 'f'
assert 'A3' not in ws.formula_attributes
assert ws.cell('A3').value == '=12345'
assert ws.cell('A4').data_type == 'f'
assert 'A4' not in ws.formula_attributes
assert ws.cell('A4').value == '=A2+A3'
assert ws.cell('A5').data_type == 'f'
assert 'A5' not in ws.formula_attributes
assert ws.cell('A5').value == '=SUM(A2:A4)'
# Test unicode
expected = '=IF(ISBLANK(B16), "Düsseldorf", B16)'
# Hack to prevent pytest doing it's own unicode conversion
try:
expected = unicode(expected, "UTF8")
except TypeError:
pass
assert ws['A16'].value == expected
# Test shared forumlae
assert ws.cell('B7').data_type == 'f'
assert ws.formula_attributes['B7']['t'] == 'shared'
assert ws.formula_attributes['B7']['si'] == '0'
assert ws.formula_attributes['B7']['ref'] == 'B7:E7'
assert ws.cell('B7').value == '=B4*2'
assert ws.cell('C7').data_type == 'f'
assert ws.formula_attributes['C7']['t'] == 'shared'
assert ws.formula_attributes['C7']['si'] == '0'
assert 'ref' not in ws.formula_attributes['C7']
assert ws.cell('C7').value == '='
assert ws.cell('D7').data_type == 'f'
assert ws.formula_attributes['D7']['t'] == 'shared'
assert ws.formula_attributes['D7']['si'] == '0'
assert 'ref' not in ws.formula_attributes['D7']
assert ws.cell('D7').value == '='
assert ws.cell('E7').data_type == 'f'
assert ws.formula_attributes['E7']['t'] == 'shared'
assert ws.formula_attributes['E7']['si'] == '0'
assert 'ref' not in ws.formula_attributes['E7']
assert ws.cell('E7').value == '='
# Test array forumlae
assert ws.cell('C10').data_type == 'f'
assert 'ref' not in ws.formula_attributes['C10']['ref']
assert ws.formula_attributes['C10']['t'] == 'array'
assert 'si' not in ws.formula_attributes['C10']
assert ws.formula_attributes['C10']['ref'] == 'C10:C14'
assert ws.cell('C10').value == '=SUM(A10:A14*B10:B14)'
assert ws.cell('C11').data_type != 'f'
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_data_only():
null_file = os.path.join(DATADIR, 'reader', 'formulae.xlsx')
wb = load_workbook(null_file, data_only=True)
ws = wb.get_active_sheet()
ws.parent.data_only = True
# Test cells returning values only, not formulae
assert ws.formula_attributes == {}
assert ws.cell('A2').data_type == 'n' and ws.cell('A2').value == 12345
assert ws.cell('A3').data_type == 'n' and ws.cell('A3').value == 12345
assert ws.cell('A4').data_type == 'n' and ws.cell('A4').value == 24690
assert ws.cell('A5').data_type == 'n' and ws.cell('A5').value == 49380
def test_merged_cells_named_range():
wb = load_workbook(os.path.join(DATADIR, 'genuine', 'merge_range.xlsx'),
use_iterators = False)
ws = wb.worksheets[0]
cell = ws.range('TRAP_3')
eq_('B15', cell.get_coordinate())
eq_(10, cell.value)
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_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_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_read_complex_style():
reference_file = os.path.join(DATADIR, 'reader', 'complex-styles.xlsx')
wb = load_workbook(reference_file)
ws = wb.get_active_sheet()
assert ws.column_dimensions['A'].width == 31.1640625
assert ws.cell('A2').style.font.name == 'Arial'
assert ws.cell('A2').style.font.size == '10'
assert not ws.cell('A2').style.font.bold
assert not ws.cell('A2').style.font.italic
assert ws.cell('A3').style.font.name == 'Arial'
assert ws.cell('A3').style.font.size == '12'
assert ws.cell('A3').style.font.bold
assert not ws.cell('A3').style.font.italic
assert ws.cell('A4').style.font.name == 'Arial'
assert ws.cell('A4').style.font.size == '14'
assert not ws.cell('A4').style.font.bold
assert ws.cell('A4').style.font.italic
assert ws.cell('A5').style.font.color.index == 'FF3300FF'
assert ws.cell('A6').style.font.color.index == 'theme:9:'
assert ws.cell('A7').style.fill.start_color.index == 'FFFFFF66'
assert ws.cell('A8').style.fill.start_color.index == 'theme:8:'
assert ws.cell('A9').style.alignment.horizontal == 'left'
assert ws.cell('A10').style.alignment.horizontal == 'right'
assert ws.cell('A11').style.alignment.horizontal == 'center'
assert ws.cell('A12').style.alignment.vertical == 'top'
assert ws.cell('A13').style.alignment.vertical == 'center'
assert ws.cell('A14').style.alignment.vertical == 'bottom'
assert ws.cell('A15').style.number_format == '0.00'
assert ws.cell('A16').style.number_format == 'mm-dd-yy'
assert ws.cell('A17').style.number_format == '0.00%'
assert 'A18:B18' in ws._merged_cells
assert ws.cell('B18').merged
assert ws.cell('A19').style.borders.top.color.index == 'FF006600'
assert ws.cell('A19').style.borders.bottom.color.index == 'FF006600'
assert ws.cell('A19').style.borders.left.color.index == 'FF006600'
assert ws.cell('A19').style.borders.right.color.index == 'FF006600'
assert ws.cell('A21').style.borders.top.color.index == 'theme:7:'
assert ws.cell('A21').style.borders.bottom.color.index == 'theme:7:'
assert ws.cell('A21').style.borders.left.color.index == 'theme:7:'
assert ws.cell('A21').style.borders.right.color.index == 'theme:7:'
assert ws.cell('A23').style.fill.start_color.index == 'FFCCCCFF'
assert ws.cell('A23').style.borders.top.color.index == 'theme:6:'
assert 'A23:B24' in ws._merged_cells
assert ws.cell('A24').merged
assert ws.cell('B23').merged
assert ws.cell('B24').merged
assert ws.cell('A25').style.alignment.wrap_text
assert ws.cell('A26').style.alignment.shrink_to_fit
def test_oddly_shaped_named_ranges():
ranges_counts = ((4, 'TEST_RANGE'),
(3, 'TRAP_1'),
(13, 'TRAP_2'))
def check_ranges(ws, count, range_name):
eq_(count, len(ws.range(range_name)))
wb = load_workbook(os.path.join(DATADIR, 'genuine', 'merge_range.xlsx'),
use_iterators = False)
ws = wb.worksheets[0]
for count, range_name in ranges_counts:
yield check_ranges, ws, count, range_name
def test_read_contains_chartsheet():
"""
Test reading workbook containing chartsheet.
"contains_chartsheets.xlsx" has the following sheets:
+---+------------+------------+
| # | Name | Type |
+===+============+============+
| 1 | "data" | worksheet |
+---+------------+------------+
| 2 | "chart" | chartsheet |
+---+------------+------------+
| 3 | "moredata" | worksheet |
+---+------------+------------+
"""
# test data
path = os.path.join(DATADIR, 'reader', 'contains_chartsheets.xlsx')
wb = load_workbook(path)
# workbook contains correct sheet names
sheet_names = wb.get_sheet_names()
eq_(sheet_names[0], 'data')
eq_(sheet_names[1], 'moredata')
def test_conditional_formatting_read():
reference_file = os.path.join(DATADIR, 'reader', 'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
ws = wb.get_active_sheet()
def test_comments_with_iterators():
wb = load_workbook(filename=os.path.join(DATADIR, 'reader', 'comments.xlsx'), use_iterators=True)
ws = wb.get_sheet_by_name(name='Sheet1')
def test_read_empty_archive():
null_file = os.path.join(DATADIR, 'reader', 'null_archive.xlsx')
wb = load_workbook(null_file)
def test_write_virtual_workbook():
old_wb = Workbook()
saved_wb = save_virtual_workbook(old_wb)
new_wb = load_workbook(BytesIO(saved_wb))
assert new_wb
def test_calculate_dimension_iter():
path = os.path.join(DATADIR, 'genuine', 'empty.xlsx')
wb = load_workbook(filename=path, use_iterators=True)
sheet2 = wb.get_sheet_by_name('Sheet2 - Numbers')
dimensions = sheet2.calculate_dimension()
eq_('%s%s:%s%s' % ('D', 1, 'AA', 30), dimensions)
def setup(self):
self.wb = load_workbook(os.path.join(DATADIR, 'genuine', 'NameWithValueBug.xlsx'))
self.ws = self.wb.get_sheet_by_name("Sheet1")
make_tmpdir()
def test_get_highest_row_iter():
path = os.path.join(DATADIR, 'genuine', 'empty.xlsx')
wb = load_workbook(filename=path, use_iterators=True)
sheet2 = wb.get_sheet_by_name('Sheet2 - Numbers')
max_row = sheet2.get_highest_row()
eq_(30, max_row)
def test_read_workbook_with_no_properties():
genuine_wb = os.path.join(DATADIR, 'genuine', \
'empty_with_no_properties.xlsx')
wb = load_workbook(filename=genuine_wb)
def test_read_standard_workbook_from_fileobj():
path = os.path.join(DATADIR, 'genuine', 'empty.xlsx')
fo = open(path, mode='rb')
wb = load_workbook(fo)
assert isinstance(wb, Workbook)
def test_read_standard_workbook():
path = os.path.join(DATADIR, 'genuine', 'empty.xlsx')
wb = load_workbook(path)
assert isinstance(wb, Workbook)
def setup_class(cls):
cls.genuine_wb = os.path.join(DATADIR, 'genuine', \
'empty-with-styles.xlsx')
wb = load_workbook(cls.genuine_wb)
cls.ws = wb.get_sheet_by_name('Sheet1')
def test_read_no_theme():
path = os.path.join(DATADIR, 'genuine', 'libreoffice_nrt.xlsx')
wb = load_workbook(path)
assert wb
