def test_simple_styles(datadir):
import datetime
from openpyxl2 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_cell_range_name(self):
wb = Workbook()
ws = wb.active
wb.create_named_range('test_range_single', ws, 'B12')
c_range_name = ws.get_named_range('test_range_single')
c_cell = ws['B12']
assert c_range_name == (c_cell,)
def test_get_named_range_wrong_sheet(self, Worksheet):
wb = Workbook()
ws1 = wb.create_sheet("Sheet1")
ws2 = wb.create_sheet("Sheet2")
wb.create_named_range('wrong_sheet_range', ws1, 'C5')
with pytest.raises(NamedRangeException):
ws2.get_named_range('wrong_sheet_range')
def test_get_named_range(self, Worksheet):
wb = Workbook()
ws = wb.active
wb.create_named_range('test_range', ws, value='C5')
xlrange = tuple(ws.get_named_range('test_range'))
cell = xlrange[0]
assert isinstance(cell, Cell)
assert cell.row == 5
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_values(self, Worksheet):
ws = Worksheet(Workbook())
ws.append([1, 2, 3])
ws.append([4, 5, 6])
vals = ws.values
assert next(vals) == (1, 2, 3)
assert next(vals) == (4, 5, 6)
def test_append_list(self, Worksheet):
ws = Worksheet(Workbook())
ws.append(['This is A1', 'This is B1'])
assert 'This is A1' == ws['A1'].value
assert 'This is B1' == ws['B1'].value
def test_append_dict_letter(self, Worksheet):
ws = Worksheet(Workbook())
ws.append({'A' : 'This is A1', 'C' : 'This is C1'})
assert 'This is A1' == ws['A1'].value
assert 'This is C1' == ws['C1'].value
def test_append_dict_index(self, Worksheet):
ws = Worksheet(Workbook())
ws.append({1 : 'This is A1', 3 : 'This is C1'})
assert 'This is A1' == ws['A1'].value
assert 'This is C1' == ws['C1'].value
def test_merged_cells_lookup(self, Worksheet):
ws = Worksheet(Workbook())
ws.merge_cells("A1:N50")
merged = ws.merged_cells
assert 'A1' in merged
assert 'N50' in merged
assert 'A51' not in merged
assert 'O1' not in merged
def test_fill_rows(self, Worksheet, row, column, coordinate):
ws = Worksheet(Workbook())
ws['A1'] = 'first'
ws['C9'] = 'last'
assert ws.calculate_dimension() == 'A1:C9'
rows = ws.iter_rows()
first_row = next(islice(rows, row - 1, row))
assert first_row[column].coordinate == coordinate
def test_getslice(self, Worksheet):
ws = Worksheet(Workbook())
ws['B2'] = "cell"
cell_range = ws['A1':'B2']
assert cell_range == (
(ws['A1'], ws['B1']),
(ws['A2'], ws['B2'])
)
def test_freeze_panes_horiz(Worksheet):
ws = Worksheet(Workbook())
ws.freeze_panes = 'A4'
view = ws.sheet_view
assert len(view.selection) == 1
assert dict(view.selection[0]) == {'activeCell': 'A1', 'pane': 'bottomLeft', 'sqref': 'A1'}
assert dict(view.pane) == {'activePane': 'bottomLeft', 'state': 'frozen',
'topLeftCell': 'A4', 'ySplit': '3'}
def test_freeze_panes_vert(Worksheet):
ws = Worksheet(Workbook())
ws.freeze_panes = 'D1'
view = ws.sheet_view
assert len(view.selection) == 1
assert dict(view.selection[0]) == {'activeCell': 'A1', 'pane': 'topRight', 'sqref': 'A1'}
assert dict(view.pane) == {'activePane': 'topRight', 'state': 'frozen',
'topLeftCell': 'D1', 'xSplit': '3'}
def test_append_iterator(self, Worksheet):
def itty():
for i in range(30):
yield i
ws = Worksheet(Workbook())
gen = itty()
ws.append(gen)
assert ws['AD1'].value == 29
def test_merge_range_string(self, Worksheet):
ws = Worksheet(Workbook())
ws['A1'] = 1
ws['D4'] = 16
assert (4, 4) in ws._cells
ws.merge_cells(range_string="A1:D4")
assert ws.merged_cells == "A1:D4"
assert (4, 4) not in ws._cells
assert (1, 1) in ws._cells
def test_no_styles():
from ..stylesheet import apply_stylesheet
from zipfile import ZipFile
from io import BytesIO
from openpyxl2.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_append_cell(self, Worksheet):
from openpyxl2.cell import Cell
cell = Cell(None, 'A', 1, 25)
ws = Worksheet(Workbook())
ws.append([])
ws.append([cell])
assert ws['A2'].value == 25
def test_squared_range(self, Worksheet):
ws = Worksheet(Workbook())
expected = [
('A1', 'B1', 'C1'),
('A2', 'B2', 'C2'),
('A3', 'B3', 'C3'),
('A4', 'B4', 'C4'),
]
rows = ws.get_squared_range(1, 1, 3, 4)
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
def test_freeze_panes_both(Worksheet):
ws = Worksheet(Workbook())
ws.freeze_panes = 'D4'
view = ws.sheet_view
assert len(view.selection) == 3
assert dict(view.selection[0]) == {'pane': 'topRight'}
assert dict(view.selection[1]) == {'pane': 'bottomLeft',}
assert dict(view.selection[2]) == {'activeCell': 'A1', 'pane': 'bottomRight', 'sqref': 'A1'}
assert dict(view.pane) == {'activePane': 'bottomRight', 'state': 'frozen',
'topLeftCell': 'D4', 'xSplit': '3', "ySplit":"3"}
def test_iter_rows(self, Worksheet):
ws = Worksheet(Workbook())
expected = [
('A1', 'B1', 'C1'),
('A2', 'B2', 'C2'),
('A3', 'B3', 'C3'),
('A4', 'B4', 'C4'),
]
rows = ws.iter_rows(min_row=1, min_col=1, max_row=4, max_col=3)
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
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_iter_rows_offset(self, Worksheet):
ws = Worksheet(Workbook())
rows = ws.iter_rows(min_row=1, min_col=1, max_row=4, max_col=3,
row_offset=1, column_offset=3)
expected = [
('D2', 'E2', 'F2'),
('D3', 'E3', 'F3'),
('D4', 'E4', 'F4'),
('D5', 'E5', 'F5'),
]
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
def test_freeze(self, Worksheet):
ws = Worksheet(Workbook())
ws.freeze_panes = ws['b2']
assert ws.freeze_panes == 'B2'
ws.freeze_panes = ''
assert ws.freeze_panes is None
ws.freeze_panes = 'C5'
assert ws.freeze_panes == 'C5'
ws.freeze_panes = ws['A1']
assert ws.freeze_panes is None
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_append_2d_list(self, Worksheet):
ws = Worksheet(Workbook())
ws.append(['This is A1', 'This is B1'])
ws.append(['This is A2', 'This is B2'])
vals = ws.iter_rows(min_row=1, min_col=1, max_row=2, max_col=2)
expected = (
('This is A1', 'This is B1'),
('This is A2', 'This is B2'),
)
for e, v in zip(expected, ws.values):
assert e == tuple(v)
def test_write_worksheet(Stylesheet):
from openpyxl2 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_rows(self, Worksheet):
ws = Worksheet(Workbook())
ws['A1'] = 'first'
ws['C9'] = 'last'
rows = tuple(ws.rows)
assert len(rows) == 9
first_row = rows[0]
last_row = rows[-1]
assert first_row[0].value == 'first' and first_row[0].coordinate == 'A1'
assert last_row[-1].value == 'last'