def test_load_workbook_from_fileobj(datadir, load_workbook):
""" can a workbook be loaded from a file object without exceptions
This tests for regressions of
https://bitbucket.org/openpyxl/openpyxl/issue/433
"""
datadir.chdir()
with open('empty_with_no_properties.xlsx', 'rb') as f:
load_workbook(f)
def test_load_workbook_with_vba(datadir, load_workbook):
datadir.chdir()
test_file = 'legacy_drawing.xlsm'
# open the workbook directly from the file
wb1 = load_workbook(test_file, keep_vba=True)
# open again from a BytesIO copy
with open(test_file, 'rb') as f:
wb2 = load_workbook(BytesIO(f.read()), keep_vba=True)
assert wb1.vba_archive.namelist() == wb2.vba_archive.namelist()
assert wb1.vba_archive.namelist() == ZipFile(test_file, 'r').namelist()
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_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_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_read_style_iter(tmpdir):
'''
Test if cell styles are read properly in iter mode.
'''
tmpdir.chdir()
from openpyxl25 import Workbook
from openpyxl25.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_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_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_close_read(datadir, load_workbook, ro):
datadir.chdir()
wb = load_workbook("complex-styles.xlsx", read_only=ro)
assert hasattr(wb, '_archive') is ro
wb.close()
if ro:
assert wb._archive.fp is None
def test_style_assignment(datadir, load_workbook):
datadir.chdir()
wb = load_workbook("complex-styles.xlsx")
assert len(wb._alignments) == 9
assert len(wb._fills) == 6
assert len(wb._fonts) == 8
assert len(wb._borders) == 7
assert len(wb._number_formats) == 0
assert len(wb._protections) == 1
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_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_workbook_is_template(datadir, tmpl, is_template):
datadir.chdir()
wb = load_workbook(tmpl)
assert wb.template is is_template
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_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_read_empty_file(datadir, load_workbook):
datadir.chdir()
with pytest.raises(BadZipfile):
load_workbook('null_file.xlsx')
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_nonstandard_name(datadir):
datadir.join('reader').chdir()
wb = load_workbook(filename="nonstandard_workbook_name.xlsx",
read_only=True)
assert wb.sheetnames == ['Sheet1']
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_invalid_file_extension(extension, load_workbook):
tmp = NamedTemporaryFile(suffix=extension)
with pytest.raises(InvalidFileException):
load_workbook(filename=tmp.name)
def test_read_stringio(load_workbook):
filelike = BytesIO(b"certainly not a valid XSLX content")
# Test invalid file-like objects are detected and not handled as regular files
with pytest.raises(BadZipfile):
load_workbook(filelike)
def test_open_many_sheets(datadir):
datadir.join("reader").chdir()
wb = load_workbook("bigfoot.xlsx", True) # if
assert len(wb.worksheets) == 1024
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_no_external_links(datadir, load_workbook):
datadir.chdir()
wb = load_workbook("bug137.xlsx", keep_links=False)
assert wb.keep_links is False
