def _create_ws():
wb = Workbook()
ws = Worksheet(wb)
comment1 = Comment("text", "author")
comment2 = Comment("text2", "author2")
comment3 = Comment("text3", "author3")
ws.cell(coordinate="B2").comment = comment1
ws.cell(coordinate="C7").comment = comment2
ws.cell(coordinate="D9").comment = comment3
return ws, comment1, comment2, comment3
def test_cell_range_name(self):
ws = Worksheet(self.wb)
self.wb.create_named_range('test_range_single', ws, 'B12')
with pytest.raises(CellCoordinatesException):
ws.cell('test_range_single')
c_range_name = ws.range('test_range_single')
c_range_coord = ws.range('B12')
c_cell = ws.cell('B12')
assert c_range_coord == c_range_name
assert c_range_coord == c_cell
def test_is_not_date_color_format():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = -13.5
cell.style.number_format.format_code = '0.00_);[Red]\(0.00\)'
eq_(cell.is_date(), False)
def _create_ws():
wb = Workbook()
ws = Worksheet(wb)
comment1 = Comment("text", "author")
comment2 = Comment("text2", "author2")
comment3 = Comment("text3", "author3")
ws["B2"].comment = comment1
ws["C7"].comment = comment2
ws["D9"].comment = comment3
return ws, comment1, comment2, comment3
def test_printer_settings(self):
ws = Worksheet(self.wb)
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
xml_string = write_worksheet(ws, None, None)
assert '<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"></pageSetup>' in xml_string
assert '<pageSetUpPr fitToPage="1"></pageSetUpPr>' in xml_string
assert '<printOptions horizontalCentered="1" verticalCentered="1">' in xml_string
ws = Worksheet(self.wb)
xml_string = write_worksheet(ws, None, None)
assert "<pageSetup" not in xml_string
assert "<pageSetUpPr" not in xml_string
assert "<printOptions" not in xml_string
def test_is_not_date_color_format():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = -13.5
cell.style = cell.style.copy(number_format='0.00_);[Red]\(0.00\)')
assert cell.is_date() is False
def read_worksheet(xml_source, parent, preset_title, shared_strings,
style_table, color_index=None, worksheet_path=None, keep_vba=False):
"""Read an xml worksheet"""
if worksheet_path:
ws = IterableWorksheet(parent, preset_title,
worksheet_path, xml_source, shared_strings, style_table)
else:
ws = Worksheet(parent, preset_title)
fast_parse(ws, xml_source, shared_strings, style_table, color_index)
return ws
def test_page_margins(self):
ws = Worksheet(self.wb)
ws.page_margins.left = 2.0
ws.page_margins.right = 2.0
ws.page_margins.top = 2.0
ws.page_margins.bottom = 2.0
ws.page_margins.header = 1.5
ws.page_margins.footer = 1.5
xml_string = write_worksheet(ws, None, None)
assert '<pageMargins left="2.00" right="2.00" top="2.00" bottom="2.00" header="1.50" footer="1.50"></pageMargins>' in xml_string
def test_auto_filter(self):
ws = Worksheet(self.wb)
ws.auto_filter = ws.range('a1:f1')
assert ws.auto_filter == 'A1:F1'
ws.auto_filter = ''
assert ws.auto_filter is None
ws.auto_filter = 'c1:g9'
assert ws.auto_filter == 'C1:G9'
def read_worksheet(xml_source, parent, preset_title, string_table,
style_table, workbook_name = None, sheet_codename = None):
"""Read an xml worksheet"""
if workbook_name and sheet_codename:
ws = IterableWorksheet(parent, preset_title, workbook_name,
sheet_codename, xml_source)
else:
ws = Worksheet(parent, preset_title)
fast_parse(ws, xml_source, string_table, style_table)
return ws
def test_read_comments(datadir, cell, author, text):
datadir.chdir()
with open("comments2.xml") as src:
xml = src.read()
wb = Workbook()
ws = Worksheet(wb)
reader.read_comments(ws, xml)
comment = ws[cell].comment
assert comment.author == author
assert comment.text == text
def test_squared_range(self):
ws = Worksheet(self.wb)
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_append_2d_list(self):
ws = Worksheet(self.wb)
ws.append(['This is A1', 'This is B1'])
ws.append(['This is A2', 'This is B2'])
vals = ws.range('A1:B2')
eq_((('This is A1', 'This is B1'),
('This is A2', 'This is B2'),), flatten(vals))
def test_iter_rows_offset(self):
ws = Worksheet(self.wb)
rows = ws.iter_rows('A1:C4', 1, 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_iter_rows(self, ):
ws = Worksheet(self.wb)
expected = [
('A1', 'B1', 'C1'),
('A2', 'B2', 'C2'),
('A3', 'B3', 'C3'),
('A4', 'B4', 'C4'),
]
rows = ws.iter_rows('A1:C4')
for row, coord in zip(rows, expected):
assert tuple(c.coordinate for c in row) == coord
def __init__(self, optimized_write=False):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__optimized_write = optimized_write
self.__optimized_read = False
self.strings_table_builder = StringTableBuilder()
if not optimized_write:
self.worksheets.append(Worksheet(self))
def read_worksheet(xml_source, parent, preset_title, string_table,
style_table, color_index=None, sheet_codename=None, keep_vba=False):
"""Read an xml worksheet"""
if sheet_codename:
from openpyxl.reader.iter_worksheet import IterableWorksheet
ws = IterableWorksheet(parent, preset_title, sheet_codename,
xml_source, string_table, style_table)
else:
ws = Worksheet(parent, preset_title)
fast_parse(ws, xml_source, string_table, style_table, color_index)
if keep_vba:
ws.xml_source = xml_source
return ws
def create_sheet(self, index=None, title=None):
"""Create a worksheet (at an optional index).
:param index: optional position at which the sheet will be inserted
:type index: int
"""
if self.__optimized_read:
raise ReadOnlyWorkbookException(
'Cannot create new sheet in a read-only workbook')
if self.__optimized_write:
new_ws = DumpWorksheet(parent_workbook=self, title=title)
else:
if title:
new_ws = Worksheet(parent_workbook=self, title=title)
else:
new_ws = Worksheet(parent_workbook=self)
self.add_sheet(worksheet=new_ws, index=index)
return new_ws
def test_cols(self):
ws = Worksheet(self.wb)
ws.cell('A1').value = 'first'
ws.cell('C9').value = 'last'
cols = ws.columns
assert len(cols) == 3
assert cols[0][0].value == 'first'
assert cols[-1][-1].value == 'last'
def test_rows(self):
ws = Worksheet(self.wb)
ws.cell('A1').value = 'first'
ws.cell('C9').value = 'last'
rows = ws.rows
assert len(rows) == 9
assert rows[0][0].value == 'first'
assert rows[-1][-1].value == 'last'
def test_comment_assignment():
wb = Workbook()
ws = Worksheet(wb)
c = Comment("text", "author")
ws.cell(coordinate="A1").comment = c
with pytest.raises(AttributeError):
ws.cell(coordinate="A2").commment = c
ws.cell(coordinate="A2").comment = Comment("text2", "author2")
with pytest.raises(AttributeError):
ws.cell(coordinate="A1").comment = ws.cell(coordinate="A2").comment
# this should orphan c, so that assigning it to A2 does not raise AttributeError
ws.cell(coordinate="A1").comment = None
ws.cell(coordinate="A2").comment = c
def test_time():
def check_time(raw_value, coerced_value):
cell.value = raw_value
eq_(cell.value, coerced_value)
eq_(cell.TYPE_NUMERIC, cell.data_type)
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
values = (('03:40:16', time(3, 40, 16)), ('03:40', time(3, 40)),)
for raw_value, coerced_value in values:
yield check_time, raw_value, coerced_value
def test_freeze(self):
ws = Worksheet(self.wb)
ws.freeze_panes = ws.cell('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.cell('A1')
assert ws.freeze_panes is None
def test_cols(self):
ws = Worksheet(self.wb)
ws.cell('A1').value = 'first'
ws.cell('C9').value = 'last'
cols = ws.columns
eq_(len(cols), 3)
eq_(cols[0][0].value, 'first')
eq_(cols[-1][-1].value, 'last')
def test_comment_count():
wb = Workbook()
ws = Worksheet(wb)
cell = ws.cell(coordinate="A1")
assert ws._comment_count == 0
cell.comment = Comment("text", "author")
assert ws._comment_count == 1
cell.comment = Comment("text", "author")
assert ws._comment_count == 1
cell.comment = None
assert ws._comment_count == 0
cell.comment = None
assert ws._comment_count == 0
def test_rows(self):
ws = Worksheet(self.wb)
ws.cell('A1').value = 'first'
ws.cell('C9').value = 'last'
rows = ws.rows
eq_(len(rows), 9)
eq_(rows[0][0].value, 'first')
eq_(rows[-1][-1].value, 'last')
def test_append_2d_list(self):
ws = Worksheet(self.wb)
ws.append(['This is A1', 'This is B1'])
ws.append(['This is A2', 'This is B2'])
vals = ws.iter_rows('A1:B2')
expected = (
('This is A1', 'This is B1'),
('This is A2', 'This is B2'),
)
for e, v in zip(expected, flatten(vals)):
assert e == tuple(v)
def print_EF_list(EF_list):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
result_filename = 'EF_print.xlsx'
wb = Workbook() #creating a workbook
ws = Worksheet(wb, title='EF_list') #creating a sheet inside the workbook
ws.freeze_panes = 'A2'
header = ['compartment', 'substance', 'subcompartment']
ws.append(header)
for EF in EF_list:
ws.append(EF)
print 'saving in excel sheet named: ' + result_filename
wb.add_sheet(ws)
wb.save(result_filename)
def test_rows(self):
ws = Worksheet(self.wb)
ws.cell('A1').value = 'first'
ws.cell('C9').value = 'last'
rows = 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_merge(self):
ws = Worksheet(self.wb)
string_table = {'': '', 'Cell A1': 'Cell A1', 'Cell B1': 'Cell B1'}
ws.cell('A1').value = 'Cell A1'
ws.cell('B1').value = 'Cell B1'
xml_string = write_worksheet(ws, string_table, None)
assert '<v>Cell B1</v>' in xml_string
ws.merge_cells('A1:B1')
xml_string = write_worksheet(ws, string_table, None)
assert '<v>Cell B1</v>' not in xml_string
assert '<mergeCells count="1"><mergeCell ref="A1:B1"></mergeCell></mergeCells>' in xml_string
ws.unmerge_cells('A1:B1')
xml_string = write_worksheet(ws, string_table, None)
assert '<mergeCell ref="A1:B1"/>' not in xml_string
