def test_ctor(self, WorksheetCopy):
wb = Workbook()
ws1 = wb.create_sheet()
ws2 = wb.create_sheet()
copier = WorksheetCopy(ws1, ws2)
assert copier.source == ws1
assert copier.target == ws2
def test_cannot_copy_between_workbooks(self, WorksheetCopy):
wb1 = Workbook()
ws1 = wb1.active
wb2 = Workbook()
ws2 = wb2.active
with pytest.raises(ValueError):
WorksheetCopy(ws1, ws2)
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_write_workbook_rels(datadir, vba, filename):
datadir.chdir()
wb = Workbook()
wb.vba_archive = vba
content = write_workbook_rels(wb)
with open(filename) as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_good_encoding(self):
from openpyxl25 import Workbook
wb = Workbook()
wb.encoding = 'latin1'
ws = wb.active
cell = ws['A1']
cell.value = self.test_string
def test_read_hyperlinks_read_only(datadir, Workbook, ReadOnlyWorksheet):
datadir.join("reader").chdir()
filename = 'bug328_hyperlinks.xml'
wb = Workbook()
wb._read_only = True
wb._data_only = True
ws = ReadOnlyWorksheet(wb, "Sheet", "", filename, ['SOMETEXT'])
assert ws['F2'].value is None
def test_chartsheet(ExcelWriter, archive):
wb = Workbook()
cs = wb.create_chartsheet()
writer = ExcelWriter(wb, archive)
writer._write_chartsheets()
assert cs.path in writer.manifest.filenames
assert cs.path[1:] in writer._archive.namelist()
def test_write_hyperlink_image_rels(Workbook, Image, datadir):
datadir.chdir()
wb = Workbook()
ws = wb.create_sheet()
ws['A1'].value = "test"
ws['A1'].hyperlink = "http://test.com/"
i = Image("plain.png")
ws.add_image(i)
raise ValueError("Resulting file is invalid")
def test_write_workbook_protection(datadir):
from openpyxl25.workbook.protection import WorkbookProtection
datadir.chdir()
wb = Workbook()
wb.security = WorkbookProtection(lockStructure=True)
wb.security.set_workbook_password('ABCD', already_hashed=True)
content = write_workbook(wb)
with open('workbook_protection.xml') as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_write_named_range():
wb = Workbook()
ws = wb.active
ws.title = "Test Sheet"
wb.create_named_range("test_range", ws, value="A1:B5")
xml = tostring(wb.defined_names.to_tree())
expected = """
<definedNames>
<definedName name="test_range">'Test Sheet'!A1:B5</definedName>
</definedNames>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_bind(self, NamedStyle):
style = NamedStyle(xfId=0)
wb = Workbook()
style.bind(wb)
assert style._wb is wb
def test_append(self, Manifest):
from openpyxl25 import Workbook
wb = Workbook()
ws = wb.active
manifest = Manifest()
manifest.append(ws)
assert len(manifest.Override) == 6
def test_write_hidden_single_worksheet():
wb = Workbook()
ws = wb.active
ws.sheet_state = "hidden"
from openpyxl25.writer.workbook import get_active_sheet
with pytest.raises(IndexError):
get_active_sheet(wb)
def test_write_workbook(datadir):
datadir.chdir()
wb = Workbook()
content = write_workbook(wb)
assert len(wb.rels) == 1
with open('workbook.xml') as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_worksheet(ExcelWriter, archive):
wb = Workbook()
ws = wb.active
writer = ExcelWriter(wb, archive)
writer._write_worksheets()
assert ws.path[1:] in archive.namelist()
assert ws.path in writer.manifest.filenames
def test_write(self, Manifest):
mf = Manifest()
from openpyxl25 import Workbook
wb = Workbook()
archive = ZipFile(BytesIO(), "w")
mf._write(archive, wb)
assert "/xl/workbook.xml" in mf.filenames
def test_media(self, Manifest, file, registration):
from openpyxl25 import Workbook
wb = Workbook()
manifest = Manifest()
manifest._register_mimetypes([file])
xml = tostring(manifest.Default[-1].to_tree())
diff = compare_xml(xml, registration)
assert diff is None, diff
def DummyWorkbook():
class Workbook:
excel_base_date = None
_cell_styles = [StyleArray([0, 0, 0, 0, 0, 0, 0, 0, 0])]
def __init__(self):
self.sheetnames = []
return Workbook()
def test_duplicate_chart(ExcelWriter, archive, Workbook):
from openpyxl25.chart import PieChart
pc = PieChart()
wb = Workbook()
writer = ExcelWriter(wb, archive)
writer._charts = [pc] * 2
with pytest.raises(InvalidFileException):
writer._write_charts()
def test_recalculate(self, NamedStyle, attr, key, collection, expected):
style = NamedStyle(xfId=0)
wb = Workbook()
wb._number_formats.append("###")
style.bind(wb)
style._style = StyleArray([1, 1, 1, 1, 1, 1, 1, 1, 1])
obj = getattr(wb, collection)[0]
setattr(style, attr, obj)
assert getattr(style._style, key) == expected
def test_bad_encoding(self):
from openpyxl25 import Workbook
wb = Workbook()
ws = wb.active
cell = ws['A1']
with pytest.raises(UnicodeDecodeError):
cell.check_string(self.test_string)
with pytest.raises(UnicodeDecodeError):
cell.value = self.test_string
def test_write_auto_filter(datadir):
datadir.chdir()
wb = Workbook()
ws = wb.active
ws['F42'].value = 'hello'
ws.auto_filter.ref = 'A1:F1'
content = write_workbook(wb)
with open('workbook_auto_filter.xml') as expected:
diff = compare_xml(content, expected.read())
assert diff is None, diff
def test_drawing(ExcelWriter, archive):
wb = Workbook()
ws = wb.active
drawing = SpreadsheetDrawing()
writer = ExcelWriter(wb, archive)
writer._write_drawing(drawing)
assert drawing.path == '/xl/drawings/drawing1.xml'
assert drawing.path[1:] in archive.namelist()
assert drawing.path in writer.manifest.filenames
def test_write_workbook_code_name():
wb = Workbook()
wb.code_name = u'MyWB'
content = write_workbook(wb)
expected = """
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<workbookPr codeName="MyWB"/>
<workbookProtection/>
<bookViews>
<workbookView activeTab="0"/>
</bookViews>
<sheets>
<sheet name="Sheet" sheetId="1" state="visible" r:id="rId1"/>
</sheets>
<definedNames/>
<calcPr calcId="124519" fullCalcOnLoad="1"/>
</workbook>
"""
diff = compare_xml(content, expected)
assert diff is None, diff
def test_tables(ExcelWriter, archive):
wb = Workbook()
ws = wb.active
ws.append(list(ascii_letters))
ws._rels = []
t = Table(displayName="Table1", ref="A1:D10")
ws.add_table(t)
writer = ExcelWriter(wb, archive)
writer._write_worksheets()
assert t.path[1:] in archive.namelist()
assert t.path in writer.manifest.filenames
def test_col_style(self, ColumnDimension):
from openpyxl25.worksheet.worksheet import Worksheet
from openpyxl25 import Workbook
from openpyxl25.styles import Font
ws = Worksheet(Workbook())
cd = ColumnDimension(ws, index="A")
cd.font = Font(color="FF0000")
cd.reindex()
col = cd.to_tree()
xml = tostring(col)
expected = """<col max="1" min="1" style="1" />"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_root_rels():
from openpyxl25.writer.workbook import write_root_rels
wb = Workbook()
xml = write_root_rels(wb)
expected = """
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Target="xl/workbook.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"/>
<Relationship Id="rId2" Target="docProps/core.xml" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"/>
<Relationship Id="rId3" Target="docProps/app.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"/>
</Relationships>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_hidden_worksheet():
wb = Workbook()
ws = wb.active
ws.sheet_state = ws.SHEETSTATE_HIDDEN
wb.create_sheet()
xml = write_workbook(wb)
expected = """
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<workbookPr/>
<workbookProtection/>
<bookViews>
<workbookView activeTab="1"/>
</bookViews>
<sheets>
<sheet name="Sheet" sheetId="1" state="hidden" r:id="rId1"/>
<sheet name="Sheet1" sheetId="2" state="visible" r:id="rId2"/>
</sheets>
<definedNames/>
<calcPr calcId="124519" fullCalcOnLoad="1"/>
</workbook>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_comment(ExcelWriter, archive):
from openpyxl25.comments import Comment
wb = Workbook()
ws = wb.active
ws['B5'].comment = Comment("A comment", "The Author")
writer = ExcelWriter(None, archive)
writer._write_comment(ws)
assert archive.namelist() == [
'xl/comments/comment1.xml', 'xl/drawings/commentsDrawing1.vml'
]
assert '/xl/comments/comment1.xml' in writer.manifest.filenames
assert ws.legacy_drawing == 'xl/drawings/commentsDrawing1.vml'
def test_save():
from tempfile import NamedTemporaryFile
filename = NamedTemporaryFile(delete=False)
from openpyxl25.workbook.workbook import Workbook
from openpyxl25.writer.excel import save_dump
wb = Workbook(write_only=True)
save_dump(wb, filename)
archive = ZipFile(filename)
assert archive.namelist() == [
'_rels/.rels', 'docProps/app.xml', 'docProps/core.xml',
'xl/theme/theme1.xml', 'xl/worksheets/sheet1.xml',
'xl/sharedStrings.xml', 'xl/styles.xml', 'xl/workbook.xml',
'xl/_rels/workbook.xml.rels', '[Content_Types].xml'
]