def do_compilation(filename, seed, sheet=None): excel = ExcelComWrapper(filename, app=xl_app()) c = ExcelCompiler(filename=filename, excel=excel) sp = c.gen_graph(seed, sheet=sheet) sp.save_to_file(filename + ".pickle") sp.export_to_gexf(filename + ".gexf") return sp
def test_validate_calcs_empty_params(): data = [ x.strip().split() for x in """ =INDEX($G$2:$G$4,D2) =INDEX($G$2:$I$2,,D2) =INDEX($G$2:$I$2,$A$6,D2) =INDEX($G$2:$G$4,D3) =INDEX($G$2:$I$2,,D3) =INDEX($G$2:$I$2,$A$6,D3) =INDEX($G$2:$G$4,D4) =INDEX($G$2:$I$2,,D4) =INDEX($G$2:$I$2,$A$6,D4) =MATCH(E2,$F$2:$F$4) 0 1 a b c =MATCH(E3,$F$2:$F$4) 2 2 b =MATCH(E4,$F$2:$F$4) 4 3 c =IF(0,,2) =IF(1,,2) =IF(,1,2) =IF(,0,2) """.split('\n')[1:-1] ] wb = Workbook() ws = wb.active ws['A2'], ws['B2'], ws['C2'] = data[0] ws['A3'], ws['B3'], ws['C3'] = data[1] ws['A4'], ws['B4'], ws['C4'] = data[2] ws['D2'], ws['E2'], ws['F2'], ws['G2'], ws['H2'], ws['I2'] = data[3] ws['D3'], ws['E3'], ws['F3'], ws['G3'] = data[4] ws['D4'], ws['E4'], ws['F4'], ws['G4'] = data[5] ws['A5'], ws['B5'], ws['C5'], ws['D5'] = data[6] excel_compiler = ExcelCompiler(excel=wb) assert (NA_ERROR, ) * 3 == excel_compiler.evaluate('Sheet!A2:C2') assert ('b', ) * 3 == excel_compiler.evaluate('Sheet!A3:C3') assert ('c', ) * 3 == excel_compiler.evaluate('Sheet!A4:C4') assert (2, 0, 2, 2) == excel_compiler.evaluate('Sheet!A5:D5')
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_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_lookup_ws(fixture_xls_copy): INDIRECT_FORMULA_ADDRESS = AddressCell('Offset!B53') compiler = ExcelCompiler(fixture_xls_copy('lookup.xlsx')) # do an INDIRECT() before other cells are loaded to verify it can load what it needs result = compiler.validate_calcs([INDIRECT_FORMULA_ADDRESS]) assert result == {} # now load and check everything result = compiler.validate_serialized() assert result == {} # use indirect to an existing range loaded = ExcelCompiler.from_file(compiler.filename) loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'B2:F6') indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS) assert indirect == loaded.evaluate('Offset!B2') # use indirect to a non-pre-existing and empty range loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'H1:H2') indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS) assert indirect is None # use indirect to a non-pre-existing range to existing cells loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'D3:E3') indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS) assert indirect == 8
def __compute(self, address, sheet): # If the computation engine is not created yet, create it. if not hasattr(self, '__formulae_calculator'): self.__formulae_calculator = ExcelCompiler(self.__path) # Compute cell value. computation_graph = self.__formulae_calculator.gen_graph(address, sheet=sheet) return computation_graph.evaluate(f"{sheet}!{address}")
def test_gen_gexf(excel, tmpdir): excel_compiler = ExcelCompiler(excel=excel) filename = os.path.join(str(tmpdir), 'test.gexf') assert not os.path.exists(filename) excel_compiler.export_to_gexf(filename) # ::TODO: it would good to test this by comparing to an fixture/artifact assert os.path.exists(filename)
def test_evaluate_conditional_formatting(cond_format_ws): cells_addrs = [ AddressCell('B2'), AddressCell('Sheet1!B3'), AddressRange('Sheet1!B4:B6'), ] formats = cond_format_ws.eval_conditional_formats(cells_addrs) formats2 = cond_format_ws.eval_conditional_formats( (a for a in cells_addrs)) assert formats == list(formats2) # should match cells_addrs's type assert formats2 == tuple( formats2) # tuple since cells_addrs is a generator assert isinstance(formats[0], tuple) assert len(formats) == 3 assert len(formats[2]) == 3 # read the spreadsheet from yaml cond_format_ws.to_file(file_types=('yml', )) cond_format_ws_yaml = ExcelCompiler.from_file(cond_format_ws.filename + '.yml') cells_addrs[0] = AddressCell('Sheet1!B2') formats3 = cond_format_ws_yaml.eval_conditional_formats(tuple(cells_addrs)) assert formats2 == formats3 # read the spreadsheet from pickle cond_format_ws.to_file(file_types=('pkl', )) cond_format_ws_pkl = ExcelCompiler.from_file(cond_format_ws.filename + '.pkl') cells_addrs[0] = AddressCell('Sheet1!B2') formats4 = cond_format_ws_pkl.eval_conditional_formats(tuple(cells_addrs)) assert formats2 == formats4 formats.append(formats[2][0][0]) formats.append(formats[2][1][0]) formats.append(formats[2][2][0]) del formats[2] color_key = { ('FF006100', 'FFC6EFCE'): 'grn', ('FF9C5700', 'FFFFEB9C'): 'yel', ('FF9C0006', 'FFFFC7CE'): 'red', (None, 'FFFFC7CE'): 'nofont', } color_map = {} for idx, dxf in cond_format_ws.conditional_formats.items(): color_map[idx] = color_key[dxf.font and dxf.font.color.value, dxf.fill.bgColor.value] expected = [ ['red'], ['grn', 'yel', 'red'], ['yel', 'red'], ['nofont'], ['yel', 'red'], ] results = [[color_map[x] for x in y] for y in formats] assert results == expected
def test_recalculate(excel): excel_compiler = ExcelCompiler(excel=excel) out_address = 'Sheet1!D1' assert -0.02286 == round(excel_compiler.evaluate(out_address), 5) excel_compiler.cell_map[out_address].value = None excel_compiler.recalculate() assert -0.02286 == round(excel_compiler.cell_map[out_address].value, 5)
def test_end_2_end(excel, fixture_xls_path): # load & compile the file to a graph, starting from D1 for excel_compiler in (ExcelCompiler(excel=excel), ExcelCompiler(fixture_xls_path)): # 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_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_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_circular_disabled(fixture_xls_copy): excel_compiler = ExcelCompiler(fixture_xls_copy('circular.xlsx'), cycles=False) failed_cells = excel_compiler.validate_serialized() assert failed_cells == { 'exceptions': { 'RecursionError: Do you need to use cycles=True ?': [('Sheet1!B10', '=IF(B3=0, 0, B10+0.001)', 'Do you need to use cycles=True ?')] } }
def test_validate_calcs(excel, capsys): excel_compiler = ExcelCompiler(excel=excel) input_addrs = ['trim-range!D5'] output_addrs = ['trim-range!B2'] excel_compiler.trim_graph(input_addrs, output_addrs) excel_compiler.cell_map[output_addrs[0]].value = 'JUNK' failed_cells = excel_compiler.validate_calcs(output_addrs) assert {'mismatch': { 'trim-range!B2': ('JUNK', 136, '=B1+SUM(D4:E4)+D5')}} == failed_cells out, err = capsys.readouterr() assert '' == err assert 'JUNK' in out
def test_structured_ref(excel): excel_compiler = ExcelCompiler(excel=excel) input_addrs = ['sref!F3'] output_addrs = ['sref!B3'] assert 15 == excel_compiler.evaluate(output_addrs[0]) excel_compiler.trim_graph(input_addrs, output_addrs) assert 15 == excel_compiler.evaluate(output_addrs[0]) excel_compiler.set_value(input_addrs[0], 11) assert 20 == excel_compiler.evaluate(output_addrs[0])
def test_circular_mismatch_warning(fixture_xls_path, fixture_xls_path_circular): with mock.patch('pycel.excelcompiler.pycel_logger') as log: assert log.warning.call_count == 0 ExcelCompiler(fixture_xls_path, cycles=False) assert log.warning.call_count == 0 ExcelCompiler(fixture_xls_path, cycles=True) assert log.warning.call_count == 1 ExcelCompiler(fixture_xls_path_circular, cycles=False) assert log.warning.call_count == 2 ExcelCompiler(fixture_xls_path_circular, cycles=True) assert log.warning.call_count == 2
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_validate_count(): wb = Workbook() ws = wb.active ws['A1'] = 0 ws['A2'] = 1 ws['A3'] = 1.1 ws['A4'] = '1.1' ws['A5'] = True ws['A6'] = False ws['A7'] = 'A' ws['A8'] = 'TRUE' ws['A9'] = 'FALSE' ws['B1'] = '=COUNT(A2)' ws['B2'] = '=COUNT(A2:A3)' ws['B3'] = '=COUNT(A2:A3,A3)' ws['B4'] = '=COUNT(A1:A9,A4,A5,A6,A7,A8,A9)' excel_compiler = ExcelCompiler(excel=wb) assert excel_compiler.evaluate('Sheet!B1:B4') == (1, 2, 3, 3)
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_trim_cells_exception_input_unused(excel): excel_compiler = ExcelCompiler(excel=excel) input_addrs = ['trim-range!G1'] output_addrs = ['trim-range!B2'] excel_compiler.evaluate(output_addrs[0]) excel_compiler.evaluate(input_addrs[0]) with pytest.raises( ValueError, match=' which usually means no outputs are dependant on it'): excel_compiler.trim_graph(input_addrs, output_addrs)
def test_value_tree_str(excel): out_address = 'trim-range!B2' excel_compiler = ExcelCompiler(excel=excel) excel_compiler.evaluate(out_address) expected = [ 'trim-range!B2 = 136', ' trim-range!B1 = 24', ' trim-range!D1:E3 = [[1, 5], [2, 6], [3, 7]]', ' trim-range!D1 = 1', ' trim-range!D2 = 2', ' trim-range!D3 = 3', ' trim-range!E1 = 5', ' trim-range!E2 = 6', ' trim-range!E3 = 7', ' trim-range!D4:E4 = [4, 8]', ' trim-range!D4 = 4', ' trim-range!E4 = 8', ' trim-range!D5 = 100' ] assert expected == list(excel_compiler.value_tree_str(out_address))
def test_evaluate_entire_row_column(excel_compiler): value = excel_compiler.evaluate(AddressRange('Sheet1!A:A')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:A18')) assert value == expected assert len(value) == 18 assert not list_like(value[0]) value = excel_compiler.evaluate(AddressRange('Sheet1!1:1')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D1')) assert value == expected assert len(value) == 4 assert not list_like(value[0]) value = excel_compiler.evaluate(AddressRange('Sheet1!A:B')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:B18')) assert value == expected assert len(value) == 18 assert len(value[0]) == 2 value = excel_compiler.evaluate(AddressRange('Sheet1!1:2')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D2')) assert value == expected assert len(value) == 2 assert len(value[0]) == 4 # now from the text based file excel_compiler._to_text() text_excel_compiler = ExcelCompiler._from_text(excel_compiler.filename) value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:A')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:A18')) assert value == expected assert len(value) == 18 assert not list_like(value[0]) value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:1')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D1')) assert value == expected assert len(value) == 4 assert not list_like(value[0]) value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:B')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:B18')) assert len(value) == 18 assert len(value[0]) == 2 assert value == expected value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:2')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D2')) assert value == expected assert len(value) == 2 assert len(value[0]) == 4
def test_gen_graph(excel): excel_compiler = ExcelCompiler(excel=excel) excel.set_sheet('trim-range') excel_compiler._gen_graph('B2') with pytest.raises(ValueError, match='Unknown seed'): excel_compiler._gen_graph(None)
def test_trim_cells(excel): excel_compiler = ExcelCompiler(excel=excel) input_addrs = ['trim-range!D5'] output_addrs = ['trim-range!B2'] old_value = excel_compiler.evaluate(output_addrs[0]) excel_compiler.trim_graph(input_addrs, output_addrs) excel_compiler._to_text(is_json=True) new_value = ExcelCompiler._from_text( excel_compiler.filename, is_json=True).evaluate(output_addrs[0]) assert old_value == new_value
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_trim_cells(excel_compiler): input_addrs = ['trim-range!D5'] output_addrs = ['trim-range!B2'] old_value = excel_compiler.evaluate(output_addrs[0]) excel_compiler.trim_graph(input_addrs, output_addrs) excel_compiler._to_text(is_json=True) new_value = ExcelCompiler._from_text( excel_compiler.filename, is_json=True).evaluate(output_addrs[0]) assert old_value == new_value
def test_compile_error_message_line_number(excel): excel_compiler = ExcelCompiler(excel=excel) 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( FormulaEvalError, match='File "a_file", line 3000'): excel_compiler.evaluate(output_addrs[0])
def main(): xlsname = "C:/Users/anubhav/Desktop/Projects/Saturn/Saturn/TestModel_v2.xlsx" excel = ExcelCompiler(xlsname) cell2 = 'Sheet1!B11' excel.evaluate(cell2) logging.info("\n\n-----SET VALUE-----") excel.set_value('Sheet1!C4', '5') logging.info("\n\n----EVALUATE------") ret = excel.evaluate(cell2) print("Final value is: {}".format(ret))
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_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_trim_cells_warn_address_not_found(excel): excel_compiler = ExcelCompiler(excel=excel) input_addrs = ['trim-range!D5', 'trim-range!H1'] output_addrs = ['trim-range!B2'] excel_compiler.evaluate(output_addrs[0]) excel_compiler.log.warning = mock.Mock() excel_compiler.trim_graph(input_addrs, output_addrs) assert 1 == excel_compiler.log.warning.call_count
def test_filename_ext(excel, fixture_xls_path): excel_compiler = ExcelCompiler(excel=excel) excel_compiler.evaluate('Sheet1!D1') excel_compiler.extra_data = {1: 3} pickle_name = excel_compiler.filename + '.pkl' yaml_name = excel_compiler.filename + '.yml' json_name = excel_compiler.filename + '.json' for name in (pickle_name, yaml_name, json_name): try: os.unlink(name) except FileNotFoundError: pass excel_compiler.to_file(excel_compiler.filename) excel_compiler.to_file(json_name, file_types=('json', )) assert os.path.exists(pickle_name) assert os.path.exists(yaml_name) assert os.path.exists(json_name)
def test_trim_cells_info_buried_input(excel): excel_compiler = ExcelCompiler(excel=excel) input_addrs = ['trim-range!B1', 'trim-range!D1'] output_addrs = ['trim-range!B2'] excel_compiler.evaluate(output_addrs[0]) excel_compiler.log.info = mock.Mock() excel_compiler.trim_graph(input_addrs, output_addrs) assert 2 == excel_compiler.log.info.call_count assert 'not a leaf node' in excel_compiler.log.info.mock_calls[1][1][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_reset(excel): excel_compiler = ExcelCompiler(excel=excel) in_address = 'Sheet1!A1' out_address = 'Sheet1!D1' assert -0.02286 == round(excel_compiler.evaluate(out_address), 5) in_value = excel_compiler.cell_map[in_address].value excel_compiler._reset(excel_compiler.cell_map[in_address]) assert excel_compiler.cell_map[out_address].value is None excel_compiler._reset(excel_compiler.cell_map[in_address]) assert excel_compiler.cell_map[out_address].value is None excel_compiler.cell_map[in_address].value = in_value assert -0.02286 == round(excel_compiler.evaluate(out_address), 5) assert -0.02286 == round(excel_compiler.cell_map[out_address].value, 5)
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_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_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_empty(excel_compiler): assert 0 == excel_compiler.evaluate('Empty!B1') excel_compiler.recalculate() assert 0 == excel_compiler.evaluate('Empty!B1') input_addrs = ['Empty!C1', 'Empty!B2'] output_addrs = ['Empty!B1', 'Empty!B2'] excel_compiler.trim_graph(input_addrs, output_addrs) excel_compiler._to_text(is_json=True) text_excel_compiler = ExcelCompiler._from_text( excel_compiler.filename, is_json=True) assert [0, None] == text_excel_compiler.evaluate(output_addrs) text_excel_compiler.set_value(input_addrs[0], 10) assert [10, None] == text_excel_compiler.evaluate(output_addrs) text_excel_compiler.set_value(input_addrs[1], 20) assert [10, 20] == text_excel_compiler.evaluate(output_addrs)
def test_trim_cells_range(excel_compiler): input_addrs = [AddressRange('trim-range!D4:E4')] output_addrs = ['trim-range!B2'] old_value = excel_compiler.evaluate(output_addrs[0]) excel_compiler.trim_graph(input_addrs, output_addrs) excel_compiler._to_text() excel_compiler = ExcelCompiler._from_text(excel_compiler.filename) assert old_value == excel_compiler.evaluate(output_addrs[0]) excel_compiler.set_value(input_addrs[0], [5, 6], set_as_range=True) assert old_value - 1 == excel_compiler.evaluate(output_addrs[0]) excel_compiler.set_value(input_addrs[0], [4, 6]) assert old_value - 2 == excel_compiler.evaluate(output_addrs[0]) excel_compiler.set_value(tuple(next(input_addrs[0].rows)), [5, 6]) assert old_value - 1 == excel_compiler.evaluate(output_addrs[0])
import os import sys from pycel.excelutil import * from pycel.excellib import * from pycel.excelcompiler import ExcelCompiler from os.path import normpath,abspath if __name__ == '__main__': dir = os.path.dirname(__file__) fname = os.path.join(dir, "../example/example.xlsx") print "Loading %s..." % fname # load & compile the file to a graph, starting from D1 c = ExcelCompiler(filename=fname) print "Compiling..., starting from D1" sp = c.gen_graph('D1',sheet='Sheet1') # test evaluation print "D1 is %s" % sp.evaluate('Sheet1!D1') print "Setting A1 to 200" sp.set_value('Sheet1!A1',200) print "D1 is now %s (the same should happen in Excel)" % sp.evaluate('Sheet1!D1') # show the graph usisng matplotlib print "Plotting using matplotlib..." sp.plot_graph()
import os import sys from pycel.excelutil import * from pycel.excellib import * from pycel.excelcompiler import ExcelCompiler from os.path import normpath,abspath if __name__ == '__main__': dir = os.path.dirname(__file__) fname = os.path.join(dir, "../example/example.xlsx") print "Loading %s..." % fname # load & compile the file to a graph, starting from D2 c = ExcelCompiler(filename=fname) print "Compiling..., starting from D4" sp = c.gen_graph('A1:Z500',sheet='Sheet1') # test evaluation print "D9 is %s" % sp.evaluate('Sheet1!D9') print "Setting A4 to 10" sp.set_value('Sheet1!A4',10) print "D9 is now %s (the same should happen in Excel)" % sp.evaluate('Sheet1!D9') # show the graph usisng matplotlib print "Plotting using matplotlib..." sp.plot_graph()
""" Created on 3 Mar 2015 @author: mack0242 """ from __future__ import division from pycel.excelutil import * from pycel.excellib import * import os from pycel.excelcompiler import ExcelCompiler from os.path import normpath, abspath if __name__ == "__main__": fname = normpath(abspath("D:\\gis_files\\redfearn.xls")) print "Loading %s..." % fname # load & compile the file to a graph, starting from D1 c = ExcelCompiler(filename=fname) sheet_name = "E,N Zne to Latitude & Longitude" print "Compiling..., starting from B3" sp = c.gen_graph("B3", sheet=sheet_name) print "Setting E3 to 54" print sp sp.set_value(sheet_name + "!E3", 54) # test evaluation print "LAT,LNG is %s,%s" % (sp.evaluate(sheet_name + "!F43"), sp.evaluate(sheet_name + "!P43"))
from os.path import abspath, dirname, join from sys import path if abspath(join(dirname(__file__), '../src')) not in path: path.insert(0, join(dirname(__file__), '../src')) from pycel.excelutil import Cell from pycel.excelcompiler import ExcelCompiler # RUN AT THE ROOT LEVEL excel = ExcelCompiler(join(dirname(__file__), "../example/example.xlsx")).excel cursheet = excel.get_active_sheet() def make_cells(): global excel, cursheet my_input = ['A1', 'A2:B3'] output_cells = Cell.make_cells(excel, my_input, sheet=cursheet) assert len(output_cells) == 3 make_cells()