def test_simple_styles(datadir):
import datetime
from openpyexcel import Workbook
from ..protection import Protection
from .. import numbers
from ..stylesheet import write_stylesheet
wb = Workbook()
wb.guess_types = True
ws = wb.active
now = datetime.date.today()
for idx, v in enumerate(
['12.34%', now, 'This is a test', '31.31415', None], 1):
ws.append([v])
_ = ws.cell(column=1, row=idx).style_id
# set explicit formats
ws['D9'].number_format = numbers.FORMAT_NUMBER_00
ws['D9'].protection = Protection(locked=True)
ws['D9'].style_id
ws['E1'].protection = Protection(hidden=True)
ws['E1'].style_id
assert len(wb._cell_styles) == 5
stylesheet = write_stylesheet(wb)
datadir.chdir()
with open('simple-styles.xml') as reference_file:
expected = reference_file.read()
xml = tostring(stylesheet)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_add_invalid_worksheet_class_instance():
class AlternativeWorksheet(object):
def __init__(self, parent_workbook, title=None):
self.parent_workbook = parent_workbook
if not title:
title = 'AlternativeSheet'
self.title = title
wb = Workbook()
ws = AlternativeWorksheet(parent_workbook=wb)
with pytest.raises(TypeError):
wb._add_sheet(worksheet=ws)
def test_set_active_by_index():
wb = Workbook()
names = [
'Sheet',
'Sheet1',
'Sheet2',
]
for n in names:
wb.create_sheet(n)
for idx, name in enumerate(names):
wb.active = idx
assert wb.active == wb.worksheets[idx]
def test_set_active_by_sheet():
wb = Workbook()
names = [
'Sheet',
'Sheet1',
'Sheet2',
]
for n in names:
wb.create_sheet(n)
for n in names:
sheet = wb[n]
wb.active = sheet
assert wb.active == wb[n]
def test_no_styles():
from ..stylesheet import apply_stylesheet
from zipfile import ZipFile
from io import BytesIO
from openpyexcel.workbook import Workbook
wb1 = wb2 = Workbook()
archive = ZipFile(BytesIO(), "a")
apply_stylesheet(archive, wb1)
assert wb1._cell_styles == wb2._cell_styles
assert wb2._named_styles == wb2._named_styles
def test_remove_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
wb.create_named_range('test_nr', new_sheet, 'A1')
del wb.defined_names['test_nr']
named_ranges_list = wb.get_named_ranges()
assert 'test_nr' not in named_ranges_list
def test_add_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = DefinedName('test_nr')
named_range.value = "Sheet!A1"
wb.add_named_range(named_range)
named_ranges_list = wb.get_named_ranges()
assert named_range in named_ranges_list
def test_write_worksheet(Stylesheet):
from openpyexcel import Workbook
wb = Workbook()
from ..stylesheet import write_stylesheet
node = write_stylesheet(wb)
xml = tostring(node)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="0" />
<fonts count="1">
<font>
<name val="Calibri"></name>
<family val="2"></family>
<color theme="1"></color>
<sz val="11"></sz>
<scheme val="minor"></scheme>
</font>
</fonts>
<fills count="2">
<fill>
<patternFill></patternFill>
</fill>
<fill>
<patternFill patternType="gray125"></patternFill>
</fill>
</fills>
<borders count="1">
<border>
<left></left>
<right></right>
<top></top>
<bottom></bottom>
<diagonal></diagonal>
</border>
</borders>
<cellStyleXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0"></xf>
</cellStyleXfs>
<cellXfs count="1">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" pivotButton="0" quotePrefix="0" xfId="0"></xf>
</cellXfs>
<cellStyles count="1">
<cellStyle builtinId="0" hidden="0" name="Normal" xfId="0"></cellStyle>
</cellStyles>
<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_split_named_styles(self, Stylesheet):
from openpyexcel.workbook import Workbook
import copy
wb = Workbook()
new_style = copy.copy(wb._named_styles[0])
# Set to an incorrect xfId, which will happen when we load a
# worksheet with unused styles. The unused styles never make it to the
# workbook, so the xfIds will be off by the trimmed number.
new_style.name = "Regression647"
wb._named_styles.append(new_style)
stylesheet = Stylesheet()
stylesheet._split_named_styles(wb)
assert stylesheet.cellStyles.cellStyle[-1].name == "Regression647"
assert stylesheet.cellStyles.cellStyle[
-1].xfId < stylesheet.cellStyleXfs.count
def create_comments():
wb = Workbook()
ws = wb.active
comment1 = Comment("text", "author")
comment2 = Comment("text2", "author2")
comment3 = Comment("text3", "author3")
ws["B2"].comment = comment1
ws["C7"].comment = comment2
ws["D9"].comment = comment3
comments = []
for coord, cell in sorted(ws._cells.items()):
if cell._comment is not None:
comment = CommentRecord.from_cell(cell)
comments.append((cell.coordinate, comment))
return comments
def test_contains(Workbook):
wb = Workbook()
assert "Sheet" in wb
assert "NotThere" not in wb
def test_iter(Workbook):
wb = Workbook()
for ws in wb:
pass
assert ws.title == "Sheet"
def test_get_active_sheet():
wb = Workbook()
assert wb.active == wb.worksheets[0]
def test_immutable_builtins(self):
wb1 = Workbook()
wb2 = Workbook()
normal = wb1._named_styles['Normal']
normal.font.color = "FF0000"
assert wb2._named_styles['Normal'].font.color.index == 1
def test_cannot_copy_writeonly(self):
wb = Workbook(write_only=True)
ws = wb.create_sheet()
with pytest.raises(ValueError):
wb.copy_worksheet(ws)
def test_get_sheet_names():
wb = Workbook()
names = ['Sheet', 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5']
for count in range(5):
wb.create_sheet(0)
assert wb.sheetnames == names
def test_set_invalid_child_as_active():
wb1 = Workbook()
wb2 = Workbook()
ws2 = wb2['Sheet']
with pytest.raises(ValueError):
wb1.active = ws2
def test_named_styles(self):
wb = Workbook()
assert wb.named_styles == ['Normal']
def test_template(self, has_vba, as_template, content_type):
from openpyexcel.workbook import Workbook
wb = Workbook()
wb.vba_archive = has_vba
wb.template = as_template
assert wb.mime_type == content_type
def test_remove_sheet_with_names():
wb = Workbook()
new_sheet = wb.create_sheet()
wb.create_named_range('test_nr', new_sheet, 'A1', 1)
del wb['Sheet1']
assert wb.defined_names.definedName == []
def test_index():
wb = Workbook()
new_sheet = wb.create_sheet()
sheet_index = wb.index(new_sheet)
assert sheet_index == 1
def test_get_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
wb.create_named_range('test_nr', new_sheet, 'A1')
assert wb.defined_names['test_nr'].value == "'Sheet1'!A1"
def test_set_invalid_active_index():
wb = Workbook()
with pytest.raises(ValueError):
wb.active = 1
def test_worksheet_copy_name(self, title, copy):
wb = Workbook()
ws1 = wb.active
ws1.title = title
ws2 = wb.copy_worksheet(ws1)
assert ws2.title == copy
def test_set_invalid_sheet_by_name():
wb = Workbook()
with pytest.raises(TypeError):
wb.active = "Sheet"
def test_cannot_copy_readonly(self):
wb = Workbook()
ws = wb.active
wb._read_only = True
with pytest.raises(ValueError):
wb.copy_worksheet(ws)
def test_set_hidden_sheet_as_active():
wb = Workbook()
ws = wb.create_sheet()
ws.sheet_state = 'hidden'
with pytest.raises(ValueError):
wb.active = ws
def test_get_named_ranges():
wb = Workbook()
assert wb.get_named_ranges() == wb.defined_names.definedName
def test_close_write(wo):
from openpyexcel.workbook import Workbook
wb = Workbook(write_only=wo)
wb.close()
def test_worksheet_copy(self):
wb = Workbook()
ws1 = wb.active
ws2 = wb.copy_worksheet(ws1)
assert ws2 is not None