def reload_koala(self,
file_name,
ignore_sheets=None,
bootstrap_equations=None):
"""Loads the Excel workbook into a Python compatible object"""
if ignore_sheets is not None:
ignore_sheets = ignore_sheets.split('\\')
else:
ignore_sheets = self.ignore_sheets.split('\\')
logging.info("Loading workbook")
if file_name == '':
logging.debug(
"file_name is not set in Excel Ribbon using {}".format(
self.excel_file_name))
file_name = self.excel_file_name
self.excel_compiler = ModelCompiler()
self.excel_model = self.excel_compiler.read_and_parse_archive(
file_name, ignore_sheets=self.ignore_sheets)
logging.info("Workbook '{}' has been loaded.".format(file_name))
logging.info("Ignored worksheets {}".format(ignore_sheets))
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
def generate_model_graph(self, model, refresh=False):
"""The function that extracts a graph of a given model from the Spreadsheet"""
if isinstance(model, str):
if refresh == False and model in self.koala_models.keys():
return 'Model %s is already cached, set refresh True if you want it to refresh it' % model.name.name
else:
if refresh == False and model.name.name in self.koala_models.keys(
):
return 'Model %s is already cached, set refresh True if you want it to refresh it' % model.name.name
# logging.debug(parser.prettyprint())
inputs = [model.name.name]
inputs.extend(self.excel_model.formulae[model.name.name].terms)
self.koala_models[str(model.name.name)] = ModelCompiler.extract(
self.excel_model, focus=inputs)
logging.info("Successfully loaded model {}".format(model.name))
return 'Cached Model %s' % model.name
"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"))
class FlyingKoala():
excel_file_name = None
auto_load_value = False
koala_models = {}
excel_model = None
ignore_sheets_default = "FlyingKoala.conf\\_FlyingKoala.conf\\xlwings.conf\\_xlwings.conf"
ignore_sheets = None
def __init__(self, file_name, ignore_sheets=[], load_koala=False):
workbook = None
print("len(xw.apps)", len(xw.apps))
if len(xw.apps) != 0:
books = [book.name for book in xw.books]
print("books", books, file_name)
for book in books:
if book in file_name:
workbook = xw.books[book]
else:
xw.books.open(file_name)
workbook = xw.books[file_name]
print("workbook", workbook)
if 'FlyingKoala.conf' in [sheet.name for sheet in workbook.sheets]:
self.auto_load_value = workbook.sheets['FlyingKoala.conf'].range(
'B3').value
else:
self.auto_load_value = False
logging.error("Would be great to see a worksheet FlyingKoala.conf")
self.excel_file_name = workbook.fullname
self.ignore_sheets = "{}\\{}".format(
workbook.sheets['FlyingKoala.conf'].range('B2').value,
self.ignore_sheets_default)
if self.auto_load_value == True or load_koala == True:
self.reload_koala(self.excel_file_name,
ignore_sheets=self.ignore_sheets)
def generate_model_graph(self, model, refresh=False):
"""The function that extracts a graph of a given model from the Spreadsheet"""
if isinstance(model, str):
if refresh == False and model in self.koala_models.keys():
return 'Model %s is already cached, set refresh True if you want it to refresh it' % model.name.name
else:
if refresh == False and model.name.name in self.koala_models.keys(
):
return 'Model %s is already cached, set refresh True if you want it to refresh it' % model.name.name
# logging.debug(parser.prettyprint())
inputs = [model.name.name]
inputs.extend(self.excel_model.formulae[model.name.name].terms)
self.koala_models[str(model.name.name)] = ModelCompiler.extract(
self.excel_model, focus=inputs)
logging.info("Successfully loaded model {}".format(model.name))
return 'Cached Model %s' % model.name
def reload_koala(self,
file_name,
ignore_sheets=None,
bootstrap_equations=None):
"""Loads the Excel workbook into a Python compatible object"""
if ignore_sheets is not None:
ignore_sheets = ignore_sheets.split('\\')
else:
ignore_sheets = self.ignore_sheets.split('\\')
logging.info("Loading workbook")
if file_name == '':
logging.debug(
"file_name is not set in Excel Ribbon using {}".format(
self.excel_file_name))
file_name = self.excel_file_name
self.excel_compiler = ModelCompiler()
self.excel_model = self.excel_compiler.read_and_parse_archive(
file_name, ignore_sheets=self.ignore_sheets)
logging.info("Workbook '{}' has been loaded.".format(file_name))
logging.info("Ignored worksheets {}".format(ignore_sheets))
def reset_koala_model_cache(self):
global koala_models
self.koala_models = {}
def get_model_cache_count(self):
return len(self.koala_models.keys())
@staticmethod
def get_named_range_count():
wb = xw.books.active
return len(wb.names)
def get_cached_koala_model_names(self):
names_of_cached_models = ""
for model_name in self.koala_models.keys():
names_of_cached_models += "\r\n%s" % (model_name)
return names_of_cached_models
@staticmethod
def get_named_ranges():
returnable = ""
wb = xw.books.active
for name in wb.names:
returnable += "\r\n%s" % (name.name)
return returnable
def is_koala_model_cached(self, model_name):
return model_name in self.koala_models.keys()
def load_model(self, model_name):
"""Preparing model name from either a string or an xlwings Range and load it into cache."""
if self.excel_model is None:
self.reload_koala(self.excel_file_name,
ignore_sheets=self.ignore_sheets)
# figure out if we have a named range or a text name of the model
extracted_model_name = None
if model_name is not None:
if model_name.name is None:
extracted_model_name = model_name.value
else:
extracted_model_name = model_name.name.name
else:
raise Exception(
'The range you tried to use does not exist in the workbook, if named range exists check spelling.'
)
# ensure that model is cached
if extracted_model_name not in self.koala_models.keys():
model = None
wb = xw.books.active
for name in wb.names:
if extracted_model_name == name.name:
model = xw.Range(extracted_model_name)
self.generate_model_graph(model)
if model is None:
raise Exception(
'Model "%s" has not been loaded into cache, if named range exists check spelling.'
% extracted_model_name)
return extracted_model_name
def unload_koala_model_from_cache(self, model_name):
global koala_models
del (self.koala_models[model_name])
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 evaluate_koala_model(self, model_name, terms, no_calc_when_zero=[]):
"""The function that sets up the evaluation of the koala equation"""
if model_name not in self.koala_models.keys():
return 'Model %s has not been loaded into cache.' % model_name
def eval(row, model):
return self.evaluate_koala_model_row(model_name, row.to_dict(),
model, no_calc_when_zero)
current_model = self.koala_models[model_name]
results = terms.apply(eval, axis=1, model=current_model)
return results
import logging
from xlcalculator import ModelCompiler
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)
import logging
from datetime import datetime
from xlcalculator import ModelCompiler
from xlcalculator import Model
from xlcalculator import Evaluator
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']