def test_read_style_iter(tmpdir): ''' Test if cell styles are read properly in iter mode. ''' tmpdir.chdir() from openpyexcel import Workbook from openpyexcel.styles import Font FONT_NAME = "Times New Roman" FONT_SIZE = 15 ft = Font(name=FONT_NAME, size=FONT_SIZE) wb = Workbook() ws = wb.worksheets[0] cell = ws['A1'] cell.font = ft xlsx_file = "read_only_styles.xlsx" wb.save(xlsx_file) wb_iter = load_workbook(xlsx_file, read_only=True) ws_iter = wb_iter.worksheets[0] cell = ws_iter['A1'] assert cell.font == ft
def test_save_xl_as_template(datadir, tmpl, keep_vba, wb_type): datadir.chdir() wb = load_workbook(tmpl, keep_vba=keep_vba) wb.template = True archive = save_virtual_workbook(wb) check_content_type(wb_type, ZipFile(BytesIO(archive)))
def test_save_with_vba(datadir): datadir.join('reader').chdir() fname = 'vba-test.xlsm' wb = load_workbook(fname, keep_vba=True) buf = save_virtual_workbook(wb) files = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist()) expected = set([ 'xl/drawings/_rels/vmlDrawing1.vml.rels', 'xl/worksheets/_rels/sheet1.xml.rels', '[Content_Types].xml', 'xl/drawings/vmlDrawing1.vml', 'xl/ctrlProps/ctrlProp1.xml', 'xl/vbaProject.bin', 'docProps/core.xml', '_rels/.rels', 'xl/theme/theme1.xml', 'xl/_rels/workbook.xml.rels', 'customUI/customUI.xml', 'xl/styles.xml', 'xl/worksheets/sheet1.xml', 'xl/sharedStrings.xml', 'docProps/app.xml', 'xl/ctrlProps/ctrlProp2.xml', 'xl/workbook.xml', 'xl/activeX/activeX2.bin', 'xl/activeX/activeX1.bin', 'xl/media/image2.emf', 'xl/activeX/activeX1.xml', 'xl/activeX/_rels/activeX2.xml.rels', 'xl/media/image1.emf', 'xl/activeX/_rels/activeX1.xml.rels', 'xl/activeX/activeX2.xml', ]) assert files == expected
def test_comments_cell_association(datadir): datadir.chdir() wb = load_workbook('comments.xlsx') assert wb['Sheet1']["A1"].comment.author == "Cuke" assert wb['Sheet1']["A1"].comment.text == "Cuke:\nFirst Comment" assert wb['Sheet2']["A1"].comment is None assert wb['Sheet1']["D1"].comment.text == "Cuke:\nSecond Comment"
def sample_workbook(request, datadir): """Standard and read-only workbook""" datadir.join("genuine").chdir() wb = load_workbook(filename="sample.xlsx", read_only=request.param, data_only=True) return wb
def test_read_single_cell_formula(datadir, data_only, expected): datadir.join("genuine").chdir() wb = load_workbook("sample.xlsx", read_only=True, data_only=data_only) ws = wb["Sheet3 - Formulas"] rows = ws.iter_rows("D2") cell = list(rows)[0][0] assert ws.parent.data_only == data_only assert cell.value == expected
def test_save_same_file(tmpdir, datadir): fname = 'vba-test.xlsm' p1 = datadir.join('reader').join(fname) p2 = tmpdir.join(fname) p1.copy(p2) tmpdir.chdir() wb = load_workbook(fname, keep_vba=True) wb.save(fname)
def test_calculate_dimension(datadir): """ Behaviour differs between implementations """ datadir.join("genuine").chdir() wb = load_workbook(filename="sample.xlsx", read_only=True) sheet2 = wb['Sheet2 - Numbers'] assert sheet2.calculate_dimension() == 'D1:AA30'
def test_vba_comments(datadir, write_worksheet): datadir.chdir() fname = 'vba+comments.xlsm' wb = load_workbook(fname, keep_vba=True) ws = wb['Form Controls'] sheet = fromstring(write_worksheet(ws)) els = sheet.findall('{%s}legacyDrawing' % SHEET_MAIN_NS) assert len( els) == 1, "Wrong number of legacyDrawing elements %d" % len(els) assert els[0].get('{%s}id' % REL_NS) == 'anysvml'
def test_content_types(datadir): datadir.join('reader').chdir() fname = 'vba+comments.xlsm' wb = load_workbook(fname, keep_vba=True) buf = save_virtual_workbook(wb) ct = fromstring( zipfile.ZipFile(BytesIO(buf), 'r').open('[Content_Types].xml').read()) s = set() for el in ct.findall("{%s}Override" % CONTYPES_NS): pn = el.get('PartName') assert pn not in s, 'duplicate PartName in [Content_Types].xml' s.add(pn)
def test_read_complex_style(datadir): datadir.chdir() wb = load_workbook("complex-styles.xlsx") ws = wb.active assert ws.column_dimensions['A'].width == 31.1640625 assert ws.column_dimensions['I'].font == Font(name="Calibri", sz=12.0, color='FF3300FF', scheme='minor') assert ws.column_dimensions['I'].fill == PatternFill(patternType='solid', fgColor='FF006600', bgColor=Color(indexed=64)) assert ws['A2'].font == Font(sz=10, name='Arial', color=Color(theme=1)) assert ws['A3'].font == Font(sz=12, name='Arial', bold=True, color=Color(theme=1)) assert ws['A4'].font == Font(sz=14, name='Arial', italic=True, color=Color(theme=1)) assert ws['A5'].font.color.value == 'FF3300FF' assert ws['A6'].font.color.value == 9 assert ws['A7'].fill.start_color.value == 'FFFFFF66' assert ws['A8'].fill.start_color.value == 8 assert ws['A9'].alignment.horizontal == 'left' assert ws['A10'].alignment.horizontal == 'right' assert ws['A11'].alignment.horizontal == 'center' assert ws['A12'].alignment.vertical == 'top' assert ws['A13'].alignment.vertical == 'center' assert ws['A15'].number_format == '0.00' assert ws['A16'].number_format == 'mm-dd-yy' assert ws['A17'].number_format == '0.00%' assert 'A18:B18' in ws.merged_cells assert ws['A19'].border == Border( left=Side(style='thin', color='FF006600'), top=Side(style='thin', color='FF006600'), right=Side(style='thin', color='FF006600'), bottom=Side(style='thin', color='FF006600'), ) assert ws['A21'].border == Border( left=Side(style='double', color=Color(theme=7)), top=Side(style='double', color=Color(theme=7)), right=Side(style='double', color=Color(theme=7)), bottom=Side(style='double', color=Color(theme=7)), ) assert ws['A23'].fill == PatternFill(patternType='solid', start_color='FFCCCCFF', end_color=(Color(indexed=64))) assert ws['A23'].border.top == Side(style='mediumDashed', color=Color(theme=6)) assert 'A23:B24' in ws.merged_cells assert ws['A25'].alignment == Alignment(wrapText=True) assert ws['A26'].alignment == Alignment(shrinkToFit=True)
def test_save_with_saved_comments(datadir): datadir.join('reader').chdir() fname = 'vba-comments-saved.xlsm' wb = load_workbook(fname, keep_vba=True) buf = save_virtual_workbook(wb) files = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist()) expected = set([ 'xl/styles.xml', 'docProps/core.xml', 'xl/_rels/workbook.xml.rels', 'xl/drawings/vmlDrawing1.vml', 'xl/comments/comment1.xml', 'docProps/app.xml', '[Content_Types].xml', 'xl/worksheets/sheet1.xml', 'xl/sharedStrings.xml', 'xl/worksheets/_rels/sheet1.xml.rels', '_rels/.rels', 'xl/workbook.xml', 'xl/theme/theme1.xml' ]) assert files == expected
def test_file_descriptor_leak(datadir): datadir.join("genuine").chdir() try: gc.disable() gc.collect() num_fds_before = count_open_fds() wb = load_workbook(filename="sample.xlsx", read_only=True) wb.close() num_fds_after = count_open_fds() finally: gc.enable() assert num_fds_after == num_fds_before
def test_save_without_vba(datadir): datadir.join('reader').chdir() fname = 'vba-test.xlsm' vbFiles = set([ 'xl/activeX/activeX2.xml', 'xl/drawings/_rels/vmlDrawing1.vml.rels', 'xl/activeX/_rels/activeX1.xml.rels', 'xl/drawings/vmlDrawing1.vml', 'xl/activeX/activeX1.bin', 'xl/media/image1.emf', 'xl/vbaProject.bin', 'xl/activeX/_rels/activeX2.xml.rels', 'xl/worksheets/_rels/sheet1.xml.rels', 'customUI/customUI.xml', 'xl/media/image2.emf', 'xl/ctrlProps/ctrlProp1.xml', 'xl/activeX/activeX2.bin', 'xl/activeX/activeX1.xml', 'xl/ctrlProps/ctrlProp2.xml', 'xl/drawings/drawing1.xml' ]) wb = load_workbook(fname, keep_vba=False) buf = save_virtual_workbook(wb) files1 = set(zipfile.ZipFile(fname, 'r').namelist()) files2 = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist()) difference = files1.difference(files2) assert difference.issubset( vbFiles), "Missing files: %s" % ', '.join(difference - vbFiles)
def test_open_many_sheets(datadir): datadir.join("reader").chdir() wb = load_workbook("bigfoot.xlsx", True) # if assert len(wb.worksheets) == 1024
def test_workbook_is_template(datadir, tmpl, is_template): datadir.chdir() wb = load_workbook(tmpl) assert wb.template is is_template
def test_guess_types(datadir, guess_types, dtype): datadir.join("genuine").chdir() wb = load_workbook('guess_types.xlsx', guess_types=guess_types) ws = wb.active assert isinstance(ws['D2'].value, dtype)
def test_read_no_theme(datadir): datadir.join("genuine").chdir() wb = load_workbook('libreoffice_nrt.xlsx') assert wb
def test_read_general_style(datadir, cell, number_format): datadir.join("genuine").chdir() wb = load_workbook('empty-with-styles.xlsx') ws = wb["Sheet1"] assert ws[cell].number_format == number_format
def test_comments_with_iterators(datadir): datadir.chdir() wb = load_workbook('comments.xlsx', read_only=True) ws = wb['Sheet1'] with pytest.raises(AttributeError): assert ws["A1"].comment.author == "Cuke"
def test_read_mac_date(datadir, read_only): datadir.join("genuine").chdir() wb = load_workbook("mac_date.xlsx", read_only=read_only) ws = wb.active assert ws['A1'].value == datetime.datetime(2016, 10, 3, 0, 0)
def test_read_empty_sheet(datadir, read_only): datadir.join("genuine").chdir() wb = load_workbook("empty.xlsx", read_only=read_only) ws = wb.active assert tuple(ws.rows) == tuple(ws.iter_rows())
def test_conditional_formatting_read(datadir): datadir.chdir() reference_file = 'conditional-formatting.xlsx' wb = load_workbook(reference_file) ws = wb.active rules = ws.conditional_formatting assert len(rules) == 30 # First test the conditional formatting rules read rule = rules['A1:A1048576'][0] assert dict(rule) == { 'priority': '30', 'type': 'colorScale', } rule = rules['B1:B10'][0] assert dict(rule) == {'priority': '29', 'type': 'colorScale'} rule = rules['C1:C10'][0] assert dict(rule) == {'priority': '28', 'type': 'colorScale'} rule = rules['D1:D10'][0] assert dict(rule) == { 'priority': '27', 'type': 'colorScale', } rule = rules['E1:E10'][0] assert dict(rule) == { 'priority': '26', 'type': 'colorScale', } rule = rules['F1:F10'][0] assert dict(rule) == { 'priority': '25', 'type': 'colorScale', } rule = rules['G1:G10'][0] assert dict(rule) == { 'priority': '24', 'type': 'colorScale', } rule = rules['H1:H10'][0] assert dict(rule) == { 'priority': '23', 'type': 'colorScale', } rule = rules['I1:I10'][0] assert dict(rule) == { 'priority': '22', 'type': 'colorScale', } rule = rules['J1:J10'][0] assert dict(rule) == { 'priority': '21', 'type': 'colorScale', } rule = rules['K1:K10'][0] assert dict(rule) == {'priority': '20', 'type': 'dataBar'} rule = rules['L1:L10'][0] assert dict(rule) == {'priority': '19', 'type': 'dataBar'} rule = rules['M1:M10'][0] assert dict(rule) == {'priority': '18', 'type': 'dataBar'} rule = rules['N1:N10'][0] assert dict(rule) == {'priority': '17', 'type': 'iconSet'} rule = rules['O1:O10'][0] assert dict(rule) == {'priority': '16', 'type': 'iconSet'} rule = rules['P1:P10'][0] assert dict(rule) == {'priority': '15', 'type': 'iconSet'} rule = rules['Q1:Q10'][0] assert dict(rule) == { 'text': '3', 'priority': '14', 'dxfId': '27', 'operator': 'containsText', 'type': 'containsText' } assert rule.dxf == DifferentialStyle(font=Font(color='FF9C0006'), fill=PatternFill(bgColor='FFFFC7CE')) rule = rules['R1:R10'][0] assert dict(rule) == { 'operator': 'between', 'dxfId': '26', 'type': 'cellIs', 'priority': '13' } assert rule.dxf == DifferentialStyle(font=Font(color='FF9C6500'), fill=PatternFill(bgColor='FFFFEB9C')) rule = rules['S1:S10'][0] assert dict(rule) == { 'priority': '12', 'dxfId': '25', 'percent': '1', 'type': 'top10', 'rank': '10' } rule = rules['T1:T10'][0] assert dict(rule) == { 'priority': '11', 'dxfId': '24', 'type': 'top10', 'rank': '4', 'bottom': '1' } rule = rules['U1:U10'][0] assert dict(rule) == { 'priority': '10', 'dxfId': '23', 'type': 'aboveAverage' } rule = rules['V1:V10'][0] assert dict(rule) == { 'aboveAverage': '0', 'dxfId': '22', 'type': 'aboveAverage', 'priority': '9' } rule = rules['W1:W10'][0] assert dict(rule) == { 'priority': '8', 'dxfId': '21', 'type': 'aboveAverage', 'equalAverage': '1' } rule = rules['X1:X10'][0] assert dict(rule) == { 'aboveAverage': '0', 'dxfId': '20', 'priority': '7', 'type': 'aboveAverage', 'equalAverage': '1' } rule = rules['Y1:Y10'][0] assert dict(rule) == { 'priority': '6', 'dxfId': '19', 'type': 'aboveAverage', 'stdDev': '1' } rule = rules['Z1:Z10'][0] assert dict(rule) == { 'aboveAverage': '0', 'dxfId': '18', 'type': 'aboveAverage', 'stdDev': '1', 'priority': '5' } assert rule.dxf == DifferentialStyle( font=Font(b=True, i=True, color='FF9C0006'), fill=PatternFill(bgColor='FFFFC7CE'), border=Border(left=Side(style='thin', color=Color(theme=5)), right=Side(style='thin', color=Color(theme=5)), top=Side(style='thin', color=Color(theme=5)), bottom=Side(style='thin', color=Color(theme=5)))) rule = rules['AA1:AA10'][0] assert dict(rule) == { 'priority': '4', 'dxfId': '17', 'type': 'aboveAverage', 'stdDev': '2' } rule = rules['AB1:AB10'][0] assert dict(rule) == { 'priority': '3', 'dxfId': '16', 'type': 'duplicateValues' } rule = rules['AC1:AC10'][0] assert dict(rule) == { 'priority': '2', 'dxfId': '15', 'type': 'uniqueValues' } rule = rules['AD1:AD10'][0] assert dict(rule) == { 'priority': '1', 'dxfId': '14', 'type': 'expression', }
def test_nonstandard_name(datadir): datadir.join('reader').chdir() wb = load_workbook(filename="nonstandard_workbook_name.xlsx", read_only=True) assert wb.sheetnames == ['Sheet1']