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_add_image(Worksheet):
from openpyxl.drawing.image import Image
from PIL.Image import Image as PILImage
ws = Worksheet(DummyWorkbook())
im = Image(PILImage())
ws.add_image(im, "D5")
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_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_max_row(Worksheet):
ws = Worksheet(DummyWorkbook())
ws.append([])
ws.append([5])
ws.append([])
ws.append([4])
assert ws.max_row == 4
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_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_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 dummy_worksheet(Worksheet):
"""
Creates a worksheet A1:H6 rows with values the same as cell coordinates
"""
ws = Worksheet(DummyWorkbook())
for row in ws.iter_rows(max_row=6, max_col=8):
for cell in row:
cell.value = cell.coordinate
return ws
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_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_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 __init__(
self,
write_only=False,
iso_dates=False,
):
self._sheets = []
self._pivots = []
self._active_sheet_index = 0
self.defined_names = DefinedNameList()
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = DifferentialStyleList()
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = "utf-8"
self.iso_dates = iso_dates
if not self.write_only:
self._sheets.append(Worksheet(self))
self.rels = RelationshipList()
self.calculation = CalcProperties()
self.views = [BookView()]
def test_group_columns_collapse(self):
from openpyxl25.worksheet.worksheet import Worksheet
ws = Worksheet(DummyWorkbook())
dims = ws.column_dimensions
dims.group('A', 'C', 1, hidden=True)
group = list(dims.values())[0]
assert group.hidden
def test_max_column(Worksheet):
ws = Worksheet(DummyWorkbook())
ws['F1'] = 10
ws['F2'] = 32
ws['F3'] = '=F1+F2'
ws['A4'] = '=A1+A2+A3'
assert ws.max_column == 6
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_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_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_group_columns_simple(self):
from openpyxl25.worksheet.worksheet import Worksheet
ws = Worksheet(DummyWorkbook())
dims = ws.column_dimensions
dims.group('A', 'C', 1)
assert len(dims) == 1
group = list(dims.values())[0]
assert group.outline_level == 1
assert group.min == 1
assert group.max == 3
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_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_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'
def test_append_cell(self, Worksheet):
from openpyxl25.cell.cell import Cell
cell = Cell(None, 'A', 1, 25)
ws = Worksheet(Workbook())
ws.append([])
ws.append([cell])
assert ws['A2'].value == 25
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_cols(self, Worksheet):
ws = Worksheet(Workbook())
ws['A1'] = 'first'
ws['C9'] = 'last'
expected = [
('A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9'),
('B1', 'B2', 'B3', 'B4', 'B5', 'B6', 'B7', 'B8', 'B9'),
('C1', 'C2', 'C3', 'C4', 'C5', 'C6', 'C7', 'C8', 'C9'),
]
cols = tuple(ws.columns)
for col, coord in zip(cols, expected):
assert tuple(c.coordinate for c in col) == coord
assert len(cols) == 3
assert cols[0][0].value == 'first'
assert cols[-1][-1].value == 'last'
def create_sheet(self, title=None, index=None):
"""Create a worksheet (at an optional index).
:param title: optional title of the sheet
:type title: unicode
:param index: optional position at which the sheet will be inserted
:type index: int
"""
if self.read_only:
raise ReadOnlyWorkbookException(
'Cannot create new sheet in a read-only workbook')
if self.write_only:
new_ws = WriteOnlyWorksheet(parent=self, title=title)
else:
new_ws = Worksheet(parent=self, title=title)
self._add_sheet(sheet=new_ws, index=index)
return new_ws
def create_sheet(self, title):
return Worksheet(self)
def test_add_chart(Worksheet):
from openpyxl.chart import BarChart
ws = Worksheet(DummyWorkbook())
chart = BarChart()
ws.add_chart(chart, "A1")
assert chart.anchor == "A1"