def test_round_trip_through_json_yaml_and_pickle(excel, fixture_xls_path):
excel_compiler = ExcelCompiler(excel=excel)
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.filename + '.json')
excel_compiler_yaml = ExcelCompiler.from_file(excel.filename + '.yml')
excel_compiler = ExcelCompiler.from_file(excel.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_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_unbounded_countifs(fixture_xls_copy):
wb = Workbook()
ws = wb.active
ws['A1'] = 1
ws['A2'] = 2
ws['A3'] = 3
ws['A4'] = 4
ws['A5'] = 5
ws['B1'] = '1'
ws['B2'] = '2'
ws['B3'] = '3'
ws['B4'] = '4'
ws['B5'] = '5'
ws['C1'] = '=COUNTIFS(B:B,">3")'
ws['C2'] = '=SUMIFS(A:A,B:B,">3")'
excel_compiler = ExcelCompiler(filename='test_unbounded_countifs',
excel=wb)
output_addrs = 'Sheet!C1', 'Sheet!C2'
assert (2, 9) == excel_compiler.evaluate(output_addrs)
excel_compiler.recalculate()
assert (2, 9) == excel_compiler.evaluate(output_addrs)
# read the spreadsheet from pickle
excel_compiler.to_file(file_types=('pickle', ))
excel_compiler = ExcelCompiler.from_file(excel_compiler.filename)
# test evaluation
assert (2, 9) == excel_compiler.evaluate(output_addrs)
excel_compiler.recalculate()
assert (2, 9) == excel_compiler.evaluate(output_addrs)
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_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_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 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_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_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_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_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 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_evaluate_from_non_cells(excel):
excel_compiler = ExcelCompiler(excel=excel)
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 expected == pytest.approx(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_empty(excel):
excel_compiler = ExcelCompiler(excel=excel)
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_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_trim_cells_range(excel):
excel_compiler = ExcelCompiler(excel=excel)
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])
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])
def test_multi_area_range_defined_name(fixture_xls_copy):
wb = Workbook()
ws = wb.active
ws['A1'] = 1
ws['A2'] = 2
ws['A3'] = 3
ws['A4'] = 4
ws['B1'] = '=SUM(A1,A2)'
ws['B2'] = '=SUM(_a2,A3)'
ws['B3'] = '=SUM(_a1_a3)'
wb.defined_names.append(
DefinedName(name='_a2', attr_text='Sheet!$A$4,Sheet!$A$1:$A$2'))
wb.defined_names.append(
DefinedName(name='_a1_a3', attr_text='Sheet!$A$1,Sheet!$A$3'))
excel_compiler = ExcelCompiler(excel=wb)
output_addrs = ['Sheet!B1:B3']
assert (3, 10, 4) == excel_compiler.evaluate(output_addrs[0])
excel_compiler.recalculate()
assert (3, 10, 4) == excel_compiler.evaluate(output_addrs[0])
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_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)
# Test missing calcPr in WorkbookPackage
wb.calculation = None
excel_compiler = ExcelCompiler(excel=wb)
assert excel_compiler.evaluate('Sheet!B1:B4') == (1, 2, 3, 3)
def test_plot_graph(excel, tmpdir):
from unittest import mock
excel_compiler = ExcelCompiler(excel=excel)
with pytest.raises(ImportError,
match="Package 'matplotlib' is not installed"):
excel_compiler.plot_graph()
import sys
mock_imports = (
'matplotlib',
'matplotlib.pyplot',
'matplotlib.cbook',
'matplotlib.colors',
'matplotlib.collections',
'matplotlib.patches',
)
for mock_import in mock_imports:
sys.modules[mock_import] = mock.MagicMock()
out_address = 'trim-range!B2'
excel_compiler.evaluate(out_address)
with mock.patch('pycel.excelcompiler.nx'):
excel_compiler.plot_graph()
def test_evaluate_from_generator(excel):
excel_compiler = ExcelCompiler(excel=excel)
result = excel_compiler.evaluate(
a for a in ('trim-range!B1', 'trim-range!B2'))
assert (24, 136) == result
# ws['D10'] = 150
# ws['D11'] = 456
wb.save(dati_splittati + nome_file_pv)
from pycel.excelcompiler import ExcelCompiler
# file_list = []
# totale_vaccini = 0
# for file in os.listdir(dati_splittati):
# file_list.append(file)
# xc = ExcelCompiler(filename=(dati_splittati + file))
# a = xc.evaluate('Scheda!C18')
# print(file, a)
# totale_vaccini = totale_vaccini + a
#
# print(file_list)
# print('Totale vaccini: ', totale_vaccini)
file_list = []
totale_vaccini = 0
with os.scandir(dati_splittati) as it:
for entry in it:
if not entry.name.startswith('.') and entry.is_file():
file_list.append(entry.name)
xc = ExcelCompiler(filename=(dati_splittati + entry.name))
a = xc.evaluate('Scheda!C18')
print(entry.name, a)
totale_vaccini = totale_vaccini + a
print(file_list)
print('Totale vaccini: ', totale_vaccini)
