def test_round_trip_through_json_yaml_and_pickle(
excel_compiler, fixture_xls_path):
excel_compiler.evaluate('Sheet1!D1')
excel_compiler.extra_data = {1: 3}
excel_compiler.to_file(file_types=('pickle', ))
excel_compiler.to_file(file_types=('yml', ))
excel_compiler.to_file(file_types=('json', ))
# read the spreadsheet from json, yaml and pickle
excel_compiler_json = ExcelCompiler.from_file(
excel_compiler.filename + '.json')
excel_compiler_yaml = ExcelCompiler.from_file(
excel_compiler.filename + '.yml')
excel_compiler = ExcelCompiler.from_file(excel_compiler.filename)
# test evaluation
assert -0.02286 == round(excel_compiler_json.evaluate('Sheet1!D1'), 5)
assert -0.02286 == round(excel_compiler_yaml.evaluate('Sheet1!D1'), 5)
assert -0.02286 == round(excel_compiler.evaluate('Sheet1!D1'), 5)
excel_compiler_json.set_value('Sheet1!A1', 200)
assert -0.00331 == round(excel_compiler_json.evaluate('Sheet1!D1'), 5)
excel_compiler_yaml.set_value('Sheet1!A1', 200)
assert -0.00331 == round(excel_compiler_yaml.evaluate('Sheet1!D1'), 5)
excel_compiler.set_value('Sheet1!A1', 200)
assert -0.00331 == round(excel_compiler.evaluate('Sheet1!D1'), 5)
def test_filename_extension_errors(excel_compiler, fixture_xls_path):
with pytest.raises(ValueError, match='Unrecognized file type'):
ExcelCompiler.from_file(excel_compiler.filename + '.xyzzy')
with pytest.raises(ValueError, match='Only allowed one '):
excel_compiler.to_file(file_types=('pkl', 'pickle'))
with pytest.raises(ValueError, match='Only allowed one '):
excel_compiler.to_file(file_types=('pkl', 'yml', 'json'))
with pytest.raises(ValueError, match='Unknown file types: pkly'):
excel_compiler.to_file(file_types=('pkly',))
def test_evaluate_empty_intersection(fixture_dir):
excel_compiler = ExcelCompiler.from_file(
os.path.join(fixture_dir, 'fixture.xlsx.yml'))
address = AddressCell('s!A1')
excel_compiler.cell_map[str(address)] = _Cell(
address, None, '=_R_(str(_REF_("s!A1:A2") & _REF_("s!B1:B2")))',
excel_compiler.excel
)
assert excel_compiler.evaluate(address) == NULL_ERROR
def test_unknown_functions(fixture_dir, msg, formula):
excel_compiler = ExcelCompiler.from_file(
os.path.join(fixture_dir, 'fixture.xlsx.yml'))
address = AddressCell('s!A1')
excel_compiler.cell_map[str(address)] = _Cell(
address, None, formula, excel_compiler.excel
)
with pytest.raises(UnknownFunction, match=msg):
excel_compiler.evaluate(address)
result = excel_compiler.validate_calcs([address])
assert 'not-implemented' in result
assert len(result['not-implemented']) == 1
def test_compile_error_message_line_number(excel_compiler):
input_addrs = ['trim-range!D5']
output_addrs = ['trim-range!B2']
excel_compiler.trim_graph(input_addrs, output_addrs)
filename = excel_compiler.filename + '.pickle'
excel_compiler.to_file(filename)
excel_compiler = ExcelCompiler.from_file(filename)
formula = excel_compiler.cell_map[output_addrs[0]].formula
formula._python_code = '(x)'
formula.lineno = 3000
formula.filename = 'a_file'
with pytest.raises(UnknownFunction, match='File "a_file", line 3000'):
excel_compiler.evaluate(output_addrs[0])
def test_compile_error_message_line_number(excel_compiler):
input_addrs = ['trim-range!D5']
output_addrs = ['trim-range!B2']
excel_compiler.trim_graph(input_addrs, output_addrs)
filename = excel_compiler.filename + '.pickle'
excel_compiler.to_file(filename)
excel_compiler = ExcelCompiler.from_file(filename)
formula = excel_compiler.cell_map[output_addrs[0]].formula
formula._python_code = '(x)'
formula.lineno = 3000
formula.filename = 'a_file'
with pytest.raises(UnknownFunction, match='File "a_file", line 3000'):
excel_compiler.evaluate(output_addrs[0])
def test_save_restore_numpy_float(basic_ws, tmpdir):
addr = AddressCell('Sheet1!A1')
cell_value = basic_ws.evaluate(addr)
assert not isinstance(cell_value, np.float64)
basic_ws.set_value(addr, np.float64(8.0))
cell_value = basic_ws.evaluate(addr)
assert isinstance(cell_value, np.float64)
assert cell_value == 8.0
tmp_name = os.path.join(tmpdir, 'numpy_test')
basic_ws.to_file(tmp_name)
excel_compiler = ExcelCompiler.from_file(tmp_name)
cell_value = excel_compiler.evaluate(addr)
assert not isinstance(cell_value, np.float64)
assert cell_value == 8.0
def test_evaluate_exceptions(fixture_dir):
excel_compiler = ExcelCompiler.from_file(
os.path.join(fixture_dir, 'fixture.xlsx.yml'))
address = AddressCell('s!A1')
excel_compiler.cell_map[str(address)] = _Cell(address, None,
'=__REF__("s!A2")',
excel_compiler.excel)
address = AddressCell('s!A2')
excel_compiler.cell_map[str(address)] = _Cell(address, None, '=$',
excel_compiler.excel)
with pytest.raises(FormulaParserError):
excel_compiler.evaluate(address)
result = excel_compiler.validate_calcs(address)
assert 'exceptions' in result
assert len(result['exceptions']) == 1
def test_evaluate_from_non_cells(excel_compiler):
input_addrs = ['Sheet1!A11']
output_addrs = ['Sheet1!A11:A13', 'Sheet1!D1', 'Sheet1!B11', ]
old_values = excel_compiler.evaluate(output_addrs)
excel_compiler.trim_graph(input_addrs, output_addrs)
excel_compiler.to_file(file_types='yml')
excel_compiler = ExcelCompiler.from_file(excel_compiler.filename)
for expected, result in zip(
old_values, excel_compiler.evaluate(output_addrs)):
assert tuple(flatten(expected)) == pytest.approx(tuple(flatten(result)))
range_cell = excel_compiler.cell_map[output_addrs[0]]
excel_compiler._reset(range_cell)
range_value = excel_compiler.evaluate(range_cell.address)
assert old_values[0] == range_value
def test_evaluate_from_non_cells(excel_compiler):
input_addrs = ['Sheet1!A11']
output_addrs = ['Sheet1!A11:A13', 'Sheet1!D1', 'Sheet1!B11', ]
old_values = excel_compiler.evaluate(output_addrs)
excel_compiler.trim_graph(input_addrs, output_addrs)
excel_compiler.to_file(file_types='yml')
excel_compiler = ExcelCompiler.from_file(excel_compiler.filename)
for expected, result in zip(
old_values, excel_compiler.evaluate(output_addrs)):
assert tuple(flatten(expected)) == pytest.approx(tuple(flatten(result)))
range_cell = excel_compiler.cell_map[output_addrs[0]]
excel_compiler._reset(range_cell)
range_value = excel_compiler.evaluate(range_cell.address)
assert old_values[0] == range_value
def test_validate_circular_referenced(circular_ws):
b6_expect = pytest.approx(49.92)
b8_expect = pytest.approx(33.41312)
circular_ws.evaluate('Sheet1!B8', iterations=1)
circular_ws.set_value('Sheet1!B3', 0)
b8 = circular_ws.evaluate('Sheet1!B8', iterations=5000, tolerance=1e-20)
assert b8 == -50
circular_ws.set_value('Sheet1!B3', 100)
b8 = circular_ws.evaluate('Sheet1!B8', iterations=5000, tolerance=0.01)
assert b8 == b8_expect
circular_ws.set_value('Sheet1!B3', 0)
b6, b8 = circular_ws.evaluate(
['Sheet1!B6', 'Sheet1!B8'], iterations=5000, tolerance=1e-20)
assert (b6, b8) == (50, -50)
circular_ws.set_value('Sheet1!B3', 100)
b6, b8 = circular_ws.evaluate(
['Sheet1!B6', 'Sheet1!B8'], iterations=5000, tolerance=0.01)
assert b6 == b6_expect
assert b8 == b8_expect
circular_ws.set_value('Sheet1!B3', 0)
b6, b8 = circular_ws.evaluate(
['Sheet1!B6', 'Sheet1!B8'], iterations=5000, tolerance=1e-20)
assert (b6, b8) == (50, -50)
circular_ws.set_value('Sheet1!B3', 100)
b8, b6 = circular_ws.evaluate(
['Sheet1!B8', 'Sheet1!B6'], iterations=5000, tolerance=0.01)
assert b6 == b6_expect
assert b8 == b8_expect
# round trip cycle params through text file
circular_ws.to_file(file_types='yml')
excel_compiler = ExcelCompiler.from_file(circular_ws.filename)
excel_compiler.set_value('Sheet1!B3', 0)
b6, b8 = excel_compiler.evaluate(
['Sheet1!B6', 'Sheet1!B8'], iterations=5000, tolerance=1e-20)
assert (b6, b8) == (50, -50)
excel_compiler.set_value('Sheet1!B3', 100)
b8, b6 = excel_compiler.evaluate(
['Sheet1!B8', 'Sheet1!B6'], iterations=5000, tolerance=0.01)
assert b6 == b6_expect
assert b8 == b8_expect
def test_evaluate_exceptions(fixture_dir):
excel_compiler = ExcelCompiler.from_file(
os.path.join(fixture_dir, 'fixture.xlsx.yml'))
address = AddressCell('s!A1')
excel_compiler.cell_map[str(address)] = _Cell(
address, None, '=__REF__("s!A2")', excel_compiler.excel
)
address = AddressCell('s!A2')
excel_compiler.cell_map[str(address)] = _Cell(
address, None, '=$', excel_compiler.excel
)
with pytest.raises(FormulaParserError):
excel_compiler.evaluate(address)
result = excel_compiler.validate_calcs(address)
assert 'exceptions' in result
assert len(result['exceptions']) == 1
def test_obsolete_function_mappings(fixture_dir):
"""Verify that we are mapping the previous mapping to new function names
# Older mappings for excel functions that match Python built-in and keywords
old_map = {
"abs": "x_abs",
"and": "x_and",
"atan2": "xatan2",
"if": "x_if",
"int": "x_int",
"len": "x_len",
"max": "xmax",
"not": "x_not",
"or": "x_or",
"min": "xmin",
"round": "x_round",
"sum": "xsum",
"xor": "x_xor",
}
"""
excel_compiler = ExcelCompiler.from_file(
os.path.join(fixture_dir, 'fixture.xlsx.yml'))
result = [excel_compiler.evaluate(f'Sheet2!A{i}') for i in range(1, 14)]
assert result == [
abs(-1),
True,
math.atan2(1, 1),
2,
int(1.23),
len("Plugh"),
max(2, 3),
not True,
True or False,
min(2, 3),
round(2.34),
sum((1, 2)),
True ^ True,
]
def test_round_trip_through_json_yaml_and_pickle(excel_compiler,
fixture_xls_path, file_type):
extra_data = {1: 3}
plugins = ('pycel.excellib', )
excel_compiler.evaluate('Sheet1!D1')
excel_compiler.extra_data = extra_data
excel_compiler.to_file(file_types=(file_type, ))
# read the spreadsheet from {file_type}
extension = f'.{file_type}' if file_type != 'pickle' else ''
excel_compiler = ExcelCompiler.from_file(excel_compiler.filename +
extension,
plugins=plugins)
# test compiler data
assert excel_compiler.extra_data.keys() & extra_data.keys()
assert excel_compiler._plugin_modules == plugins
# test evaluation
assert -0.02286 == round(excel_compiler.evaluate('Sheet1!D1'), 5)
excel_compiler.set_value('Sheet1!A1', 200)
assert -0.00331 == round(excel_compiler.evaluate('Sheet1!D1'), 5)
def test_evaluate_not_pre_existing_range(fixture_dir):
excel_compiler = ExcelCompiler.from_file(
os.path.join(fixture_dir, 'fixture.xlsx.yml'))
assert excel_compiler.evaluate('Sheet1!B5:B8') == (18, 21, 24, 27)
