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