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_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_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)