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 test_read_style_iter(tmpdir):
'''
Test if cell styles are read properly in iter mode.
'''
tmpdir.chdir()
from openpyxl2 import Workbook
from openpyxl2.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 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_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_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_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_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_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_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_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_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']
def test_open_many_sheets(datadir):
datadir.join("reader").chdir()
wb = load_workbook("bigfoot.xlsx", True) # if
assert len(wb.worksheets) == 1024
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_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_workbook_is_template(datadir, tmpl, is_template):
datadir.chdir()
wb = load_workbook(tmpl)
assert wb.template is is_template
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_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)