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