# Copyright (c) 2010-2021 openpyxl
import pytest
import datetime
from io import BytesIO
from lxml.etree import iterparse, fromstring
from openpyxl.xml.constants import SHEET_MAIN_NS
from openpyxl.utils.indexed_list import IndexedList
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.utils.datetime import CALENDAR_WINDOWS_1900, CALENDAR_MAC_1904
from openpyxl.styles.styleable import StyleArray
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formula.translate import Translator
from ..worksheet import Worksheet
from ..pagebreak import Break, RowBreak, ColBreak
from ..scenario import ScenarioList, Scenario, InputCells
@pytest.mark.parametrize("value, expected",
[
('4.2', 4.2),
('-42.000', -42),
('0', 0),
('0.9999', 0.9999),
('99E-02', 0.99),
('4', 4),
('-1E3', -1000),
('1E-3', 0.001),
('2e+2', 200.0),
]
)
def test_number_convesion(value, expected):
from .._reader import _cast_number
assert _cast_number(value) == expected
@pytest.fixture
def Workbook():
class DummyWorkbook:
data_only = False
_colors = []
encoding = "utf8"
epoch = CALENDAR_WINDOWS_1900
def __init__(self):
self._differential_styles = [DifferentialStyle()] * 5
self.shared_strings = ['a'] * 30
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList([DEFAULT_BORDER] * 30)
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
for i in range(29):
self._cell_styles.add((StyleArray([i]*9)))
self._cell_styles.add(StyleArray([0,4,6,0,0,1,0,0,0])) #fillId=4, borderId=6, alignmentId=1))
self.sheetnames = []
self._date_formats = set()
self._timedelta_formats = set()
def create_sheet(self, title):
return Worksheet(self)
return DummyWorkbook()
@pytest.fixture
def WorkSheetParser():
"""Setup a parser instance with an empty source"""
from .._reader import WorkSheetParser
styles = IndexedList()
for i in range(29):
styles.add((StyleArray([i]*9)))
styles.add(StyleArray([0,4,6,14,0,1,0,0,0])) #fillId=4, borderId=6, number_format=14 alignmentId=1))
date_formats = set([1, 29])
timedelta_formats = set([30])
return WorkSheetParser(None, {0:'a'}, date_formats=date_formats, timedelta_formats=timedelta_formats)
from warnings import simplefilter
simplefilter("always")
class TestWorksheetParser:
@pytest.mark.parametrize("filename, expected",
[
("dimension.xml", (4, 1, 27, 30)),
("no_dimension.xml", None),
("invalid_dimension.xml", (None, 1, None, 113)),
]
)
def test_read_dimension(self, WorkSheetParser, datadir, filename, expected):
datadir.chdir()
parser = WorkSheetParser
parser.source = filename
dimension = parser.parse_dimensions()
assert dimension == expected
def test_col_width(self, datadir, WorkSheetParser):
datadir.chdir()
parser = WorkSheetParser
with open("complex-styles-worksheet.xml", "rb") as src:
cols = iterparse(src, tag='{%s}col' % SHEET_MAIN_NS)
for _, col in cols:
parser.parse_column_dimensions(col)
assert set(parser.column_dimensions) == set(['A', 'C', 'E', 'I', 'G'])
assert parser.column_dimensions['A'] == {'max': '1', 'min': '1',
'index':'A', 'customWidth': '1', 'width': '31.1640625'}
def test_hidden_col(self, WorkSheetParser):
parser = WorkSheetParser
src = """
"""
element = fromstring(src)
parser.parse_column_dimensions(element)
assert 'D' in parser.column_dimensions
assert parser.column_dimensions['D'] == {'customWidth': '1', 'width':'0',
'hidden': '1', 'max': '4', 'min': '4', 'index':'D'}
def test_styled_col(self, datadir, WorkSheetParser):
datadir.chdir()
parser = WorkSheetParser
with open("complex-styles-worksheet.xml", "rb") as src:
cols = iterparse(src, tag='{%s}col' % SHEET_MAIN_NS)
for _, col in cols:
parser.parse_column_dimensions(col)
assert 'I' in parser.column_dimensions
cd = parser.column_dimensions['I']
assert cd == {'customWidth': '1', 'max': '9', 'min': '9', 'width': '25', 'index':'I', 'style':'28'}
def test_row_dimensions(self, WorkSheetParser):
src = """
"""
element = fromstring(src)
parser = WorkSheetParser
parser.parse_row(element)
assert 2 not in parser.row_dimensions
def test_hidden_row(self, WorkSheetParser):
parser = WorkSheetParser
src = """
"""
element = fromstring(src)
parser.parse_row(element)
assert parser.row_dimensions['2'] == {'r': '2', 'spans': '1:4', 'hidden':'1'}
def test_styled_row(self, datadir, WorkSheetParser):
parser = WorkSheetParser
src = """
"""
element = fromstring(src)
parser.parse_row(element)
rd = parser.row_dimensions['23']
assert rd == {'r': '23', 's': '28', 'spans': '1:8'}
def test_read_row_with_exponent(self, WorkSheetParser):
parser = WorkSheetParser
src = """
"""
element = fromstring(src)
parser.parse_row(element)
assert parser.row_counter == 1048573
def test_invalid_row_number(self, WorkSheetParser):
parser = WorkSheetParser
src = """
"""
element = fromstring(src)
with pytest.raises(ValueError):
parser.parse_row(element)
def test_sheet_protection(self, datadir, WorkSheetParser):
parser = WorkSheetParser
src = """
"""
element = fromstring(src)
parser.parse_sheet_protection(element)
assert dict(parser.protection) == {
'autoFilter': '0', 'deleteColumns': '0',
'deleteRows': '0', 'formatCells': '0', 'formatColumns': '0', 'formatRows':
'0', 'insertColumns': '0', 'insertHyperlinks': '0', 'insertRows': '0',
'objects': '0', 'password': 'DAA7', 'pivotTables': '0', 'scenarios': '0',
'selectLockedCells': '0', 'selectUnlockedCells': '0', 'sheet': '1', 'sort':
'0'
}
def test_formula(self, WorkSheetParser):
parser = WorkSheetParser
src = """
IF(TRUE, "y", "n")
y
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 'f', 'row': 1,
'style_id':0, 'value': '=IF(TRUE, "y", "n")'}
def test_formula_data_only(self, WorkSheetParser):
parser = WorkSheetParser
parser.data_only = True
src = """
1+2
3
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 'n', 'row': 1,
'style_id':0, 'value': 3}
def test_string_formula_data_only(self, WorkSheetParser):
parser = WorkSheetParser
parser.data_only = True
src = """
IF(TRUE, "y", "n")
y
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 's', 'row': 1,
'style_id':0, 'value': 'y'}
def test_number(self, WorkSheetParser):
parser = WorkSheetParser
src = """
1
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 'n', 'row': 1,
'style_id':0, 'value': 1}
def test_datetime(self, WorkSheetParser):
parser = WorkSheetParser
src = """
2011-12-25T14:23:55
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 'd', 'row': 1,
'style_id':0, 'value': datetime.datetime(2011, 12, 25, 14, 23, 55)}
def test_timedelta(self, WorkSheetParser):
parser = WorkSheetParser
src = """
1.25
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 'd', 'row': 1,
'style_id':30, 'value':datetime.timedelta(days=1, hours=6)}
def test_mac_date(self, WorkSheetParser):
parser = WorkSheetParser
parser.epoch = CALENDAR_MAC_1904
src = """
41184
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 'd', 'row': 1,
'style_id':29, 'value':datetime.datetime(2016, 10, 3, 0, 0)}
def test_string(self, WorkSheetParser):
parser = WorkSheetParser
src = """
0
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 's', 'row': 1,
'style_id':0, 'value':'a'}
def test_boolean(self, WorkSheetParser):
parser = WorkSheetParser
src = """
1
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 'b', 'row': 1,
'style_id':0, 'value':True}
def test_inline_string(self, WorkSheetParser):
parser = WorkSheetParser
src = """
ID
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 1, 'data_type': 's', 'row': 1,
'style_id':0, 'value':"ID"}
def test_inline_richtext(self, WorkSheetParser):
parser = WorkSheetParser
src = """
11 de September de 2014
"""
element = fromstring(src)
cell = parser.parse_cell(element)
assert cell == {'column': 18, 'data_type': 's', 'row': 2,
'style_id':4, 'value':"11 de September de 2014"}
def test_sheet_views(self, WorkSheetParser):
parser = WorkSheetParser
src = b"""
"""
parser.source = BytesIO(src)
for _ in parser.parse():
pass
view = parser.views.sheetView[0]
assert view.zoomScale == 200
assert len(view.selection) == 3
def test_shared_formula(self, WorkSheetParser):
parser = WorkSheetParser
src = """
9
"""
element = fromstring(src)
parser.shared_formulae['0'] = Translator("=A4*B4", "A1")
formula = parser.parse_formula(element)
assert formula == "=A12*B12"
def test_array_formula(self, WorkSheetParser, datadir):
parser = WorkSheetParser
src = """
SUM(A10:A14*B10:B14)
"""
element = fromstring(src)
formula = parser.parse_formula(element)
assert formula == "=SUM(A10:A14*B10:B14)"
assert parser.array_formulae['C10']['ref'] == 'C10:C14'
def test_extended_conditional_formatting(self, WorkSheetParser, recwarn):
parser = WorkSheetParser
src = """
NOT(ISERROR(SEARCH($AA$7,A1)))
$AA$7
"""
element = fromstring(src)
parser.parse_extensions(element)
w = recwarn.pop()
assert issubclass(w.category, UserWarning)
def test_bad_conditional_format_rule(self, WorkSheetParser, recwarn):
parser = WorkSheetParser
src = """
"""
element = fromstring(src)
parser.parse_formatting(element)
w = recwarn.pop()
assert issubclass(w.category, UserWarning)
def test_cell_without_coordinates(self, WorkSheetParser):
parser = WorkSheetParser
src = """
2
4
3
6
9
"""
element = fromstring(src)
parser.shared_strings = ["Whatever"] * 10
parser.parse_row(element)
assert parser.row_counter == 1
assert parser.col_counter == 5
def test_row_and_cell_without_coordinates(self, WorkSheetParser):
parser = WorkSheetParser
src = """
2
4
3
"""
element = fromstring(src)
max_row, cells = parser.parse_row(element)
expected = [
{'column': 1, 'row': 1, 'data_type': 'n', 'value': 2, 'style_id': 0},
{'column': 2, 'row': 1, 'data_type': 'n', 'value': 4, 'style_id': 0},
{'column': 3, 'row': 1, 'data_type': 'n', 'value': 3, 'style_id': 0},
]
for expected_cell, cell in zip(expected, cells):
assert expected_cell == cell
def test_row_and_cell_skipping_coordinates(self, WorkSheetParser):
parser = WorkSheetParser
src = """
1
2
3
4
"""
element = fromstring(src)
_, cells = parser.parse_row(element)
expected = [
{'column': 1, 'row': 1, 'data_type': 'n', 'value': 1, 'style_id': 0},
{'column': 4, 'row': 1, 'data_type': 'n', 'value': 2, 'style_id': 0},
{'column': 5, 'row': 1, 'data_type': 'n', 'value': 3, 'style_id': 0},
{'column': 7, 'row': 1, 'data_type': 'n', 'value': 4, 'style_id': 0},
]
assert len(cells) == len(expected)
for expected_cell, cell in zip(expected, cells):
assert expected_cell == cell
def test_second_row_cell_index_without_coordinates(self, WorkSheetParser):
parser = WorkSheetParser
src = """
2
"""
element = fromstring(src)
parser.parse_row(element)
max_row, cells = parser.parse_row(element)
expected = [
{'column': 1, 'row': 2, 'data_type': 'n', 'value': 2, 'style_id': 0},
]
for expected_cell, cell in zip(expected, cells):
assert expected_cell == cell
def test_external_hyperlinks(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
parser.parse()
assert len(parser.hyperlinks.hyperlink) == 1
def test_local_hyperlinks(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert len(parser.hyperlinks.hyperlink) == 1
def test_merge_cells(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert len(parser.merged_cells.mergeCell) == 3
def test_conditonal_formatting(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert len(parser.formatting) == 2
def test_sheet_properties(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert parser.sheet_properties.tabColor.rgb == "FF92D050"
assert parser.sheet_properties.codeName == "Sheet3"
def test_sheet_format(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert parser.sheet_format.defaultRowHeight == 14.25
assert parser.sheet_format.baseColWidth == 15
def test_tables(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert parser.tables.tablePart[0].id == "rId1"
def test_auto_filter(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert parser.auto_filter.ref == "A1:AK3237"
assert parser.auto_filter.sortState.ref == "A2:AM3269"
def test_page_row_break(self, WorkSheetParser):
src = b"""
"""
expected_pagebreak = RowBreak()
expected_pagebreak.append(Break(id=15))
parser = WorkSheetParser
el = fromstring(src)
parser.parse_row_breaks(el)
assert parser.row_breaks == expected_pagebreak
def test_col_break(self, WorkSheetParser):
src = b"""
"""
expected_pagebreak = ColBreak()
expected_pagebreak.append(Break(id=15))
parser = WorkSheetParser
el = fromstring(src)
parser.parse_col_breaks(el)
assert parser.col_breaks == expected_pagebreak
def test_scenarios(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
c = InputCells(r="B2", val="50000")
s = Scenario(name="Worst case", inputCells=[c], locked=True, user="User", comment="comment")
scenarios = ScenarioList(scenario=[s], current="0", show="0")
assert parser.scenarios == scenarios
def test_legacy(self, WorkSheetParser):
src = """
"""
parser = WorkSheetParser
element = fromstring(src)
parser.parse_legacy(element)
assert parser.legacy_drawing == "rId3"
def test_custom_views_breaks(self, WorkSheetParser):
src = b"""
"""
parser = WorkSheetParser
parser.source = BytesIO(src)
for _ in parser.parse():
pass
assert parser.row_breaks == RowBreak()
@pytest.fixture
def WorksheetReader():
from .._reader import WorksheetReader
return WorksheetReader
@pytest.fixture
def PrimedWorksheetReader(Workbook, WorksheetReader, datadir):
wb = Workbook
ws = wb.create_sheet("Sheet")
datadir.chdir()
src = "complex-styles-worksheet.xml"
reader = WorksheetReader(ws, src, wb.shared_strings, data_only=False)
return reader
class TestWorksheetReader:
def test_cells(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
assert ws['C1'].value == 'a'
assert ws.formula_attributes == {'E2': {'ref':"E2:E11", 't':"array"}}
assert ws['E2'].value == "=C2:C11*D2:D11"
def test_formatting(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
reader.bind_formatting()
fmts = ws.conditional_formatting
assert fmts['T1:T10'][-1].dxf == DifferentialStyle()
assert len(fmts['A1']) == 2
def test_merged(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
reader.bind_merged_cells()
assert ws.merged_cells == "G18:H18 G23:H24 A18:B18"
@pytest.mark.parametrize(argnames="input_coordinate,expected_coordinate",
argvalues=[("H18", "G18"), ("G18", "G18"), ("I18", None), ("H23", "G23")])
def test_normalize_merged_cell_link(self, PrimedWorksheetReader, input_coordinate, expected_coordinate):
reader = PrimedWorksheetReader
reader.bind_cells()
reader.bind_merged_cells()
normalized = reader.normalize_merged_cell_link(input_coordinate)
if expected_coordinate is None:
assert normalized is None
else:
assert normalized.coordinate == expected_coordinate
def test_external_hyperlinks(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_hyperlinks()
assert ws['A1'].hyperlink.target == "../"
def test_internal_hyperlinks(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_hyperlinks()
assert ws['B4'].hyperlink.location == "'STP nn000TL-10, PKG 2.52'!A1"
assert ws['B4'].hyperlink.ref == "B4"
def test_merged_hyperlinks(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_merged_cells()
reader.bind_hyperlinks()
assert ws.merged_cells == "G18:H18 G23:H24 A18:B18"
assert ws['A18'].hyperlink.display == 'http://test.com'
assert ws['B18'].hyperlink is None
# Link referencing H24 should be placed on G23 because H24 is a merged cell
# and G23 is the top-left cell in the merged range
assert ws["G23"].hyperlink.tooltip == "openpyxl"
assert ws["H24"].hyperlink is None
def test_tables(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="table", Id="rId1", Target="../tables/table1.xml")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_tables()
assert reader.tables == ["../tables/table1.xml"]
def test_cols(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
reader.bind_col_dimensions()
assert len(ws.column_dimensions) == 5
assert ws.column_dimensions['I'].parent == ws
def test_rows(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
reader.bind_row_dimensions()
assert len(ws.row_dimensions) == 7
assert ws.row_dimensions[4].parent == ws
def test_properties(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
reader.bind_properties()
for k in ('page_margins', 'page_setup', 'views', 'sheet_format',
'legacy_drawing', 'protection'):
assert getattr(ws, k) == getattr(reader.parser, k)
def test_more_rows_than_cells(self, Workbook, WorksheetReader, datadir):
ws = Workbook.create_sheet("Sheet")
datadir.chdir()
reader = WorksheetReader(ws, "more_rows_than_cells.xml", None, None)
reader.bind_cells()
assert ws._current_row == 3