def test_extract_and_evaluate(self):
model_compiler = ModelCompiler()
reader_model = model_compiler.read_and_parse_archive(
testing.get_resource("model_compiler_and_evaluate.xlsx"))
extracted_model = ModelCompiler.extract(reader_model,
focus=['add_one'])
self.assertEqual(1, extracted_model.get_cell_value('Sheet1!A1'))
self.assertEqual(2, extracted_model.get_cell_value('Sheet1!B1'))
extracted_model.set_cell_value('Sheet1!A1', 2)
evaluator = Evaluator(extracted_model)
evaluator.evaluate('add_one')
self.assertEqual(3, extracted_model.get_cell_value('Sheet1!B1'))
def evaluate_koala_model_row(self,
model_name,
input_data,
model,
no_calc_when_zero=[]):
"""The function which sets the input values in the model and evaluates the Excel equation using koala"""
model = self.koala_models[str(model_name)]
for key in input_data.keys():
if key in no_calc_when_zero and input_data[key] == 0:
return
model.set_cell_value(key, input_data[key])
evaluator = Evaluator(model)
return evaluator.evaluate(model_name)
def test_dict_read_and_parse(self):
input_dict = {
"B4": 0.95,
"B2": 1000,
"B19": 0.001,
"B20": 4,
# B21
"B22": 1,
"B23": 2,
"B24": 3,
"B25": "=B2*B4",
"B26": 5,
"B27": 6,
"B28": "=B19*B20*B22",
"C22": "=SUM(B22:B28)",
}
model_compiler = ModelCompiler()
my_model = model_compiler.read_and_parse_dict(input_dict)
evaluator = Evaluator(my_model)
# cells need a sheet and Sheet1 is default.
evaluator.set_cell_value("Sheet1!B22", 100)
self.assertEqual(0.4, evaluator.evaluate("Sheet1!B28"))
self.assertEqual(1066.4, evaluator.evaluate("Sheet1!C22"))
def buy(self):
self.save()
compiler = ModelCompiler()
new_model = compiler.read_and_parse_archive(self.tmpfile)
self.remove()
evaluator = Evaluator(new_model)
balance = evaluator.evaluate(f'mywishlist!E{self.lastrow}')
if balance < 0:
print('Poor you')
else:
self.ws.cell(row=2, column=5, value=float(balance))
for i in range(3, self.lastrow):
owned = self.ws.cell(row=i, column=6).value
bought = self.ws.cell(row=i, column=4).value
owned += bought
if owned != 0:
self.ws.cell(row=i,
column=2,
value=self.content[self.ws.cell(
row=i, column=1).value])
self.ws.cell(row=i, column=6, value=owned)
self.ws.cell(row=i, column=4, value=0)
print('Purchased')
return
"B4": 0.95,
"B2": 1000,
"B19": 0.001,
"B20": 4,
# B21
"B22": 1,
"B23": 2,
"B24": 3,
"B25": "=B2*B4",
"B26": 5,
"B27": 6,
"B28": "=B19*B20*B22",
"C22": "=SUM(B22:B28)",
}
from xlcalculator import ModelCompiler
from xlcalculator import Model
from xlcalculator import Evaluator
compiler = ModelCompiler()
my_model = compiler.read_and_parse_dict(input_dict)
evaluator = Evaluator(my_model)
for formula in my_model.formulae:
print("Formula", formula, "evaluates to", evaluator.evaluate(formula))
# cells need a sheet and Sheet1 is default.
evaluator.set_cell_value("Sheet1!B22", 100)
print("Formula B28 now evaluates to", evaluator.evaluate("Sheet1!B28"))
print("Formula C22 now evaluates to", evaluator.evaluate("Sheet1!C22"))
from xlcalculator import Model
from xlcalculator import Evaluator
logging.basicConfig(level=logging.DEBUG)
json_file_name = r'use_case_01.json'
filename = r'use_case_01.xlsm'
compiler = ModelCompiler()
new_model = compiler.read_and_parse_archive(filename, build_code=False)
new_model.persist_to_json_file(json_file_name)
reconstituted_model = Model()
reconstituted_model.construct_from_json_file(json_file_name, build_code=True)
evaluator = Evaluator(reconstituted_model)
val1 = evaluator.evaluate('First!A2')
print("value 'evaluated' for First!A2 without a formula:", val1)
val2 = evaluator.evaluate('Seventh!C1')
print("value 'evaluated' for Seventh!C1 with a formula:", val2)
val3 = evaluator.evaluate('Ninth!B1')
print("value 'evaluated' for Ninth!B1 with a defined name:", val3)
val4 = evaluator.evaluate('Hundred')
print("value 'evaluated' for Hundred with a defined name:", val4)
val5 = evaluator.evaluate('Tenth!C1')
print("value 'evaluated' for Tenth!C1 with a defined name:", val5)
val6 = evaluator.evaluate('Tenth!C2')
print("value 'evaluated' for Tenth!C2 with a defined name:", val6)
val7 = evaluator.evaluate('Tenth!C3')
print("value 'evaluated' for Tenth!C3 with a defined name:", val7)
logging.basicConfig(level=logging.INFO)
beginning = datetime.now()
print("loading file")
filename = r'Nested_sum.xlsx'
compiler = ModelCompiler()
print("model compiler made", datetime.now() - beginning)
new_model = compiler.read_and_parse_archive(filename)
# print("new_model.cells", new_model.cells)
print("read_and_parse_archive took", datetime.now() - beginning)
new_model.build_code()
print("build_code took", datetime.now() - beginning)
print("now evaluating")
evaluator = Evaluator(new_model)
print("evaluator made", datetime.now() - beginning)
columns = [
'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
]
# columns = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U']
# columns = ['V', 'W', 'X', 'Y', 'Z']
# columns = ['H']
max_rows = 3
# max_rows = 3
addresses = [
'Sheet1!{}{}'.format(column, row) for row in range(2, max_rows)
for column in columns
logging.basicConfig(level=logging.INFO)
beginning = datetime.now()
print("loading file")
filename = r'row_column.xlsx'
compiler = ModelCompiler()
print("model compiler made", datetime.now() - beginning)
new_model = compiler.read_and_parse_archive(filename, build_code=False)
# print("new_model.cells", new_model.cells)
print("read_and_parse_archive took", datetime.now() - beginning)
new_model.build_code()
print("build_code took", datetime.now() - beginning)
print("now evaluating")
evaluator = Evaluator(new_model)
print("evaluator made", datetime.now() - beginning)
# columns = ['D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z']
# # columns = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U']
# # columns = ['V', 'W', 'X', 'Y', 'Z']
# # columns = ['H']
#
# max_rows = 3
# # max_rows = 3
# addresses = ['Sheet1!{}{}'.format(column, row) for row in range(2, max_rows) for column in columns]
#
# print("addresses made", datetime.now() - beginning)
second_beginning = datetime.now()
# for address in addresses:
# evaluated_value = evaluator.evaluate(address)